Translate

Search This Blog

ORA-00333: redo log read error block 9167 count 1073

If database is not able to start after failed crash automatic recovery with error you receive ORA-00333: redo log read error block xxxx count xxxx then you should try manually recover the database. 

[oracle@11gRAC1 11g]$ srvctl start database -d ORCL
PRCR-1079 : Failed to start resource ora.orcl.db
CRS-5017: The resource action "ora.orcl.db start" encountered the following erro
ORA-00333: redo log read error block 9167 count 8184
. For details refer to "(:CLSN00107:)" in "/u01/11.2.0/grid/log/11grac1/agent/cr

CRS-2674: Start of 'ora.orcl.db' on '11grac1' failed
CRS-2632: There are no more servers to try to place resource 'ora.orcl.db' on th
CRS-5017: The resource action "ora.orcl.db start" encountered the following erro
ORA-00333: redo log read error block 9167 count 8184
. For details refer to "(:CLSN00107:)" in "/u01/11.2.0/grid/log/11grac2/agent/cr

CRS-2674: Start of 'ora.orcl.db' on '11grac2' failed
[oracle@11gRAC1 11g]$ sqlplus / as sysdba
SQL> startup mount
ORACLE instance started.

Total System Global Area  534519808 bytes
Fixed Size                  2229944 bytes
Variable Size             314575176 bytes
Database Buffers          209715200 bytes
Redo Buffers                7999488 bytes
Database mounted.
SQL> select * from v$log ;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1        195  241172480        512          1 NO  CURRENT               64028116 18-MAY-14   2.8147E+14
         2          1        194  241172480        512          1 NO  INACTIVE              63987815 16-MAY-14     64028116 18-MAY-14
         3          2        151  241172480        512          1 NO  CURRENT               64007840 16-MAY-14   2.8147E+14
         4          2        150  241172480        512          1 NO  INACTIVE              63941096 16-MAY-14     64007840 16-MAY-14

         SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  +DATA2/orcl/redo01.log                             NO
         2         ONLINE  +DATA2/orcl/redo02.log                             NO
         3         ONLINE  +DATA2/orcl/redo03.log                             NO
         4         ONLINE  +DATA2/orcl/redo04.log                             NO

SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-00333: redo log read error block 8192 count 2048
SQL> select name,state from v$asm_diskgroup ;

NAME                           STATE
------------------------------ -----------
DATA2                          CONNECTED
DATA3                          CONNECTED
DATA                           CONNECTED

SQL> select FILE#,STATUS,ERROR,RECOVER,FUZZY,CHECKPOINT_CHANGE# from v$datafile_header;

Disk group DATA2 ON which redo log resides is mounted and DB has successfully connected to it

     FILE# STATUS  ERROR                                                             REC FUZ CHECKPOINT_CHANGE#
---------- ------- ----------------------------------------------------------------- --- --- ------------------
         1 ONLINE                                                                    NO  YES           63941096
         2 ONLINE                                                                    NO  YES           63941096
         3 ONLINE                                                                    NO  YES           63941096
         4 ONLINE                                                                    NO  YES           63941096
         5 ONLINE                                                                    NO  YES           63941096
         6 ONLINE                                                                    NO  YES           63941096
         7 ONLINE                                                                    NO  YES           63941096

7 rows selected.


 #rebooted  crs but database did not start and both its instances terminated after giving error

SQL> startup mount
ORACLE instance started.

Total System Global Area  534519808 bytes
Fixed Size                  2229944 bytes
Variable Size             314575176 bytes
Database Buffers          209715200 bytes
Redo Buffers                7999488 bytes
Database mounted.
SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-00333: redo log read error block 9167 count 1073

This time block number is different and so is count

SQL> shutdown immediate
startup ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area  534519808 bytes
Fixed Size                  2229944 bytes
Variable Size             314575176 bytes
Database Buffers          209715200 bytes
Redo Buffers                7999488 bytes
Database mounted.
SQL> recover database until cancel using backup controlfile ;ORA-00279: change 64028117 generated at 05/18/2014 11:34:34 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_195_847218868.dbf
ORA-00280: change 64028117 for thread 1 is in sequence #195

Specify log: {=suggested | filename | AUTO | CANCEL}
+DATA2/orcl/redo01.log
ORA-00279: change 64028117 generated at 05/16/2014 13:06:28 needed for thread 2
ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch2_151_847218868.dbf
ORA-00280: change 64028117 for thread 2 is in sequence #151

Specify log: {=suggested | filename | AUTO | CANCEL}
+DATA2/orcl/redo03.log
Log applied.
Media recovery complete.




SQL> select distinct FUZZY from v$datafile_header;

FUZ
----
NO

SQL> alter database open resetlogs ;
Database altered.


[ DB is opened and you are done. You may have some corruption in undo tablespaces , data files or index tablespaces- always check alert log even after crash recovery or instance recovery but DB is opened and you can tackle other error errors with available backup or  dropping/recreating undo [if it does not have active and if undo tablespace has active SMU then by marking them to be dropped by hidden parameter, post about this later on ]
-------------------------------------------------------------------------------------------------------
 
####### From alert log

Incomplete read from log member '+DATA2/orcl/redo03.log'. Trying next member.
Abort recovery for domain 0
Aborting crash recovery due to error 333
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_ora_5083.trc:
ORA-00333: redo log read error block 9167 count 8184
Abort recovery for domain 0
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_ora_5083.trc:
ORA-00333: redo log read error block 9167 count 8184
ORA-333 signalled during: ALTER DATABASE OPEN /* db agent *//* {1:22317:292} */...
Mon May 19 11:32:05 2014
Shutting down instance (abort)

snippet of trace file : /u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_ora_5083.trc

Successfully allocated 3 recovery slaves
Using 45 overflow buffers per recovery slave
Thread 2 checkpoint: logseq 151, block 2, scn 64033559
  cache-low rba: logseq 151, block 9159
    on-disk rba: logseq 151, block 9190, scn 64078136
  start recovery at logseq 151, block 9159, scn 0
Thread 1 checkpoint: logseq 195, block 2, scn 64028117
  cache-low rba: logseq 195, block 75202
    on-disk rba: logseq 195, block 75256, scn 64077704
  start recovery at logseq 195, block 75202, scn 0
Incomplete read from log member '+DATA2/orcl/redo03.log'. Trying next member.
2014-05-19 11:32:05.362112 : Abort recovery for domain 0, flags = 0x4
2014-05-19 11:32:05.362192 : kjb_abort_recovery: abort recovery for domain 0 @ inc 3 (0)
2014-05-19 11:32:05.362209 : kjb_abort_recovery: domain flags=0x0, valid=0
ORA-00333: redo log read error block 9167 count 8184
2014-05-19 11:32:05.391695 : Abort recovery for domain 0, flags = 0x4
2014-05-19 11:32:05.391717 : aborting recovery of 0 (0) with cluster inc 3 (0) recovery 1
2014-05-19 11:32:05.391730 : kjb_abort_recovery: domain flags=0x0, valid=0
ORA-00333: redo log read error block 9167 count 8184


----------------------------