Re: Prevention of ORA-00333 in the future

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Mon, 19 Aug 2013 21:02:41 +0200
Message-ID: <52126bdf$0$2210$426a74cc_at_news.free.fr>

<mjmstud_at_gmail.com> a écrit dans le message de news: 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.

First thought (before searching in dictionary): v$log gives you the log groups and their sequences, you know the sequence of the begin of recovery: logseq 54 block 75223, error at block 4984, so logseq 55, now you have the log file.

Regards
Michel Received on Mon Aug 19 2013 - 21:02:41 CEST