Recently in Oracle Category

I have a DB which stores the archived logs in NFS file system, it's running Oracle 9i and mainly used for Logminer auditing.

Due to the business requirement, I upgraded it to 10g, after that, when I run the Logminer to process the archived log, I got the following errors:

ERROR at line 1:
ORA-01284: file /path/to/arc/100.arc cannot be opened
ORA-00308: cannot open archived log '/path/to/arc/100.arc'
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 2
ORA-06512: at "SYS.DBMS_LOGMNR", line 68
ORA-06512: at line 1

Explanation about ORA-27054:

27054, 00000, "NFS file system where the file is created or resides is not mounted with correct options"
// *Cause:The file was on an NFS partition and either reading the mount tab
// file failed or the partition wass not mounted with the correct
// mount option.
// *Action: Make sure mount tab file has read access for Oracle user and
// the NFS partition where the file resides is mounted correctly.
// For the list of mount options to use refer to your platform
// specific documentation

Solutions from Metalink:

1. Umount the NFS file system and remount it with the following options:
rw,hard,bg,proto=tcp,suid,rsize=32768,wsize=32768,noac

2. Apply patch 5146667.

In my this case, I tried the option 1 to fix the problem.

Usually, it will take around 1 hour to finish the 10g upgrade from 9i, but this time, it took me more than 4 hours, that was amazing!

During the upgrade, I found the the EVENT: ‘db file scattered read’ from v$session_wait, and Oracle was updating table ‘aud$’:

UPDATE aud$
SET ntimestamp# = SYS_EXTRACT_UTC (CAST(timestamp# AS TIMESTAMP WITH TIME ZONE))
WHERE ntimestamp# IS NULL

I enabled auditing on this 9i database, which generated 2GB size of table aud$, and you may know that Oracle 10g addes many new features for auditing(you may check the table definition of aud$ between 9i and 10g), so it will also update table aud$ during the 10g upgrade.

So, you can use CTAS to backup table aud$ and then truncate it directly before 10g upgrade.

Version: Oracle 10g (10203) Enterprise on Sun Sparc 64bit Solaris8

Capture of the error info from alert log:

Media Recovery Start
Managed Standby Recovery not using Real Time Apply
parallel recovery started with 10 processes
Sat Jun 30 02:25:01 2007
Media Recovery Log /oracle/TEST/archive/TEST_6.arc
Expanded controlfile section 4 from 30 to 60 records
Requested to grow by 30 records; added 1 blocks of records
Sat Jun 30 02:25:01 2007 Errors in file /oracle/TEST/home/admin/udump/test_ora_27380.trc:
ORA-00600: internal error code, arguments: [25016], [31], [4], [], [], [], [], []
Some recovered datafiles maybe left media fuzzy Media recovery may continue but open resetlogs may fail
Sat Jun 30 02:25:04 2007 Media Recovery failed with error 600 

Background:

The primary database was upgraded from 9205 to 10203, I added around 30 datafiles on primary, when the standby applied the archived log, it got the ORA 600 error as described above.

Root Cause:

When 10g standby expands the controlfile, it triggers Oracle Bug '6157529'.

Solution:

1. Apply Oracle patch '3569503'
2. Refresh the controlfile, and then try to recover. But you may get the ORA600 error again when standby expands controlfile next time.
3. Change the compatible parameter in init.ora configuration file: Change from: compatible = 9.2.0.5 to 10.2.0.3 (This change is dangerous in production environment! You may have trouble to downgrade.)
4. Upgrade to the last Oracle 10g Enterprise version.

Click here to get more details about this bug from Metalink.