ORA-00333: redo log read error 발생 시

출처 : http://shaharear.blogspot.kr/2013/06/recover-database-from-ora-00333-redo.html

아래의 step을 차근차근 따라하면 해소된다. 

빨간 글씨는 직접 입력 내용이다.

파란 글씨는 출력 내용중 핵심 내용이다.

In development environment, it is very common scenario that we have multiple databases in a single machine by using VMware (i.e, each VMware contains one database). Again those machines doesn't have consistant power backup. Therefore we have to face power failure or VMware hang-up. So, we are forced to restart the machine while databases are still up & running. After restarting the machine, we have mostly got he following error:

   ORA-00333: redo log read error block count .

Here are the steps to overcome the error

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: As 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#); 

 STATUS  MEMBER
 ------------- --------------------------------------
 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 (위의 step 1의 current log 파일을 입력하면 됨)

/oracle/fast_recovery_area/redo01.log

Log applied.
Media recovery complete.

Step 4: Open the database with reset logfile

SQL> alter database open resetlogs;

Database altered.