Creating an NFS mount for use with RMAN

I needed more disk space on my database server for exports and RMAN backups (possible Flashback space). My colleague has an available Network File System (NFS), as he calls it, Network-attached storage (NAS). I have been given a few terabytes for use with my database. I will mount this same NAS on my production and development database servers for use with exports and RMAN backups.

Once the share is available:
1. Edit /etc/vfstab and add the mount.
Adding mount to vfstab

2. Add the directory to the server.
#cd /
#mkdir oracle_data

3. #mount -a

4. #chown oracle:oinstall oracle_data

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

RMAN error

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 2

I am using an NFS share for RMAN backups. After mounting the file system and trying testing a backup using RMAN I received the ORA-27054 error. This is due to the fact that mount was not established correctly. The following is the correct syntax for mounting NFS for RMAN to utilize. Since I am mounting two server versions of Solaris; 5.8 and 5.10 to the same NFS share.

Oracle 10gR2 on UNIX,

For SUN Solaris 5.8 sparc 64 or Solaris 5.9
mount rw,bg,intr, hard,timeo=600, wsize=32768, rsize=32768

Actual entry (all one line) in the vfstab file (where bigshare is the server):
bigshare:/export/oracle - /oracle_backups nfs - yes rw,bg,intr,hard,timeo=600,wsize=32768,rsize=32768

For Solaris 5.10 use
mount -F nfs -o hard, bg,proto=tcp, vers=3, suid, rsize=32768, wsize=32768, intr,noac

Actual entry (all one line) in the vfstab file (where bigshare is the server):
bigshare:/export/oracle - /oracle_backups nfs - yes hard, bg,proto=tcp, vers=3, suid, rsize=32768, wsize=32768, intr,noac

Indexing - Using functions in the WHERE Clause

Using a function on an indexed column in the WHERE clause will force the optimizer to bypass the desired index (does not apply to function-based indexes).
Common functions used in the WHERE clause are TRUNC, SUBSTR, TO_DATE, TO_CHAR, and INSTR.
The one I am guilty of is using TRUNC on a date column, as in (assuming there is an index on insert_date):

WHERE TRUNC(insert_date) = TRUNC(sysdate);
instead use something like:
WHERE insert_date >= TRUNC(sysdate) and insert_date <>
By taking the function TRUNC off of the indexed column, insert_date, the index on insert_date will be used.

Example:
SQL> set timing on

On our first run the data has not been cached and it take 5 seconds.
SQL> select count(last_updated) "Total" from barcodes where trunc(last_updated) > trunc(sysdate-60);

Total
-------------------
2114
Elapsed: 00:00:05.10

Same SQL but the data now in cache it takes 2.5 seconds.
SQL> select count(last_updated) "Total" from barcodes where trunc(last_updated) > trunc(sysdate-60);

Total
-------------------
2114
Elapsed: 00:00:02.53

Rewrite query to use the index on last_updated and the query takes 0.0 seconds.
SQL> select count(last_updated) "Total" from barcodes where last_updated > trunc(sysdate)-60 and last_updated <>

Total
-------------------
2114
Elapsed: 00:00:00.00

You can't always assume you will use an index just becuase it is available.

ORA-00354:, ORA-16014:

ORA-00354:
How to recover from
ORA-00354: corrupt redo log block header
also
ORA-00312: online log 1 thread 1: '/u02/oradata/DEV/redo01.log'
ORA-16038: log 1 sequence# 2530 cannot be archived
ORA-00354: corrupt redo log block header
also

SQL> alter database mount;
SQL> alter database open resetlogs;
SQL> alter database open;

ERROR at line 1:
ORA-16014: log 1 sequence# 2530 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/u02/oradata/DEV/redo01.log'

I have not tested with the database open. I crashed this instance and could not open due to the corrupted redo log. In mount state issue the following commands for your specific redo logs with corruption.

SQL> alter database clear unarchived logfile '/u02/oradata/DEV/redo01.log';
SQL> alter database clear unarchived logfile '/u03/oradata/DEV/redo02.log';
SQL> alter database clear unarchived logfile '/u04/oradata/DEV/redo03.log';
SQL> alter database open;


I restarted to be sure everything was good.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

_disable_logging = TRUE

ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 39540 change 3193784747 time 01/13/2009 22:08:29
ORA-00312: online log 1 thread 1: '/u02/oradata/DEV/redo01.log'
ORA-16038: log 1 sequence# 2530 cannot be archived

Beware of the hiddden parameter _disable_logging

My goal was to speed up an import of data by disabling archive logging without turning off the Archiver. Of course in hindsight I know now that this was a bad idea and simply puting the database in noarchivelog mode would have been much simpler. But it's always fun to learn new things.

SQL> alter database noarchivelog;

SQL> alter database open;

Once I set the hidden parameter _disable_logging=TRUE, I kicked of the import and pretty quick started recieving alert log errors notifying me of corrupt redo log block headers. My advice - do not use this parameter!

How to recover from
ORA-00354: corrupt redo log block header

Oracle DBA interview questions

How do you really prepare for technical questions you might get during an interview? I try to read as much as I can and review notes on basic questions or other typical interview questions regarding an Oracle DBA. On a recent job interview I was asked the following technical questions. I'll do my best to answer them as well, but if you have an opinion please fell free to chime in.

• How to you recover from block level corruption using RMAN?
• What is DBVerify and how do you use it?
• How do you dump your database to a file?
• How does export differ from datapump?
• How can you export and import an entire schema except for one table using datapump?
• A user has a SQL scripts that used to run in 1 hour and now takes 10 hours. The script has not changed. What could have caused this?
• What is the recommended time frame for gathering statistics in 10g and how is it done?