Friday, August 15, 2014

Recover Database from ORA-00333: redo log read error

I got the above error when i start My DB.
I followed the below steps to recover this.

SQL> startup
ORACLE instance started.
Total System Global Area ***** bytes
Fixed Size               ***** bytes
Variable Size            ***** bytes
Database Buffers         ***** bytes
Redo Buffers             ***** bytes
Database mounted.
 ORA-00333: redo log read error block *Number* count *Number*

Step 1: Since the DB is in mount mode, We can query v$log & v$logfile to identify the status of log file group and their member.

 SQL> select l.status, member from v$logfile inner join v$log l using (group#); 
 ------------- --------------------------------------
 CURRENT /oracle/fast_recovery_area/redo01.log
 INACTIVE /oracle/fast_recovery_area/redo02.log
 INACTIVE /oracle/fast_recovery_area/redo03.log

Step 2: Recover the database using ackup controlfile.

 SQL> recover database using backup controlfile;
ORA-00279: change  generated at  needed for thread 1
ORA-00289: suggestion : /oracle/fast_recovery_area/archivelog/o1_mf_1_634_%u_.arc
ORA-00280: change  for thread 1 is in sequence #
Specify log: {=suggested | filename | AUTO | CANCEL}

Step3: Give 'CURRENT' log file member along with location as input. If it does not work give other log file members along with location in input prompt. In our case we give