Prevention of ORA-00333 in the future

From: <mjmstud_at_gmail.com>
Date: Sun, 18 Aug 2013 22:20:50 -0700 (PDT)
Message-ID: <a959eae3-ff2e-40e5-b3d0-75cdca593888_at_googlegroups.com>

I had an issue recently with a database instance not coming up because of the error: ORA-00333: redo log read error block 329 count 7865

I was able to recover the database from rman backups.

What I am wanting to investigate is what I can do do prevent a similar problem ocurring in the future.  

The redo log groups are multiplexed (as shown in the listing below) :

SQL> select * from v$logfile
  2 order by group#
  3 ;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /export/redo/genparam/new_redo01.log               NO
         1         ONLINE  /export/redo_mirror/genparam/new_redo01_mirror.log NO
         2         ONLINE  /export/redo_mirror/genparam/new_redo02_mirror.log NO
         2         ONLINE  /export/redo/genparam/new_redo02.log               NO
         3         ONLINE  /export/redo_mirror/genparam/new_redo03_mirror.log NO
         3         ONLINE  /export/redo/genparam/new_redo03.log               NO

The sequence of events which lead to the error are as follows :

1. Our database server host crashed as a result of a power failure.
2. When the Oracle instance started up we got the following in the alert.log :

starting up 1 shared server(s) ...
ORACLE_BASE from environment = /export/u01/app/oracle Sun Aug 18 14:13:44 2013
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 1626257752 Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Sun Aug 18 14:13:49 2013
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Started redo scan
Aborting crash recovery due to error 333 Errors in file /export/u01/app/oracle/diag/rdbms/genparam/genparam/trace/genparam_ora_4052.trc: ORA-00333: redo log read error block 4984 count 3210 Errors in file /export/u01/app/oracle/diag/rdbms/genparam/genparam/trace/genparam_ora_4052.trc: ORA-00333: redo log read error block 4984 count 3210 ORA-333 signalled during: ALTER DATABASE OPEN...    

The contents of the trace file genparam_ora_4052.trc are :    

Trace file /export/u01/app/oracle/diag/rdbms/genparam/genparam/trace/genparam_ora_4052.trc Oracle Database 11g Release 11.2.0.2.0 - 64bit Production ORACLE_HOME = /export/u01/app/oracle/product/11.2.0/db_1 System name: SunOS

Node name:      genmiscdb
Release:        5.10
Version:        Generic_150401-01
Machine:        i86pc

Instance name: genparam
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 4052, image: oracle_at_genmiscdb (TNS V1-V3)

• 2013-08-18 14:13:49.426
• SESSION ID:(66.3) 2013-08-18 14:13:49.426
• CLIENT ID:() 2013-08-18 14:13:49.426
• SERVICE NAME:() 2013-08-18 14:13:49.426
• MODULE NAME:(sqlplus_at_genmiscdb (TNS V1-V3)) 2013-08-18 14:13:49.426
• ACTION NAME:() 2013-08-18 14:13:49.426

Thread 1 checkpoint: logseq 54, block 2, scn 20939522874   cache-low rba: logseq 54, block 75223
    on-disk rba: logseq 55, block 5021, scn 20939584846   start recovery at logseq 54, block 75223, scn 0 ORA-00333: redo log read error block 4984 count 3210 ORA-00333: redo log read error block 4984 count 3210  

The alert log entries don't give any indication of which redo log file is at fault.  

Our version is 11.2.0.2.0  

Any guidance here would be greatly appreciated. Received on Mon Aug 19 2013 - 07:20:50 CEST