Recovery From Current Redolog Corruption

1. startup error message

ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) cannot find file

5.1.2 Current Redolog Corruption
Two case:
A. shutdown normally, no transaction to recovery, recreate log group with 'alter database clear unarchived logfile group n'
B. active transaction exists. database need media recovery. log group need synchronized. there are 2 methods:
a. imcomplete recovery. keep the consistence of database. but this method requires archivelog mode and valid backup.
b. force recovery. but may cause inconsistence.

5.1.2.1 recovery with backup
1. accounting a error when open database

ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) OS cannot found file

2. check V$log, and notice the current redolog corrupted

SQL> select group#,sequence#,archived,status from v$log;
 
    GROUP#  SEQUENCE# ARCHIVED STATUS
---------- ---------- -------- ----------------
         1          1 NO       CURRENT
         2          2 YES      INACTIVE
         3          3 YES      INACTIVE

3. cannot clear the current redolog

SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'

4. copy a full backup and recover database

until scn or until cnacel

recover database until cancel

select auto recover with all the valid archivelogs and redologs, and

recover database until cancel

enter cancel to do a incomplete recovery (that's need recover twice)

Example:

SQL> recover database until cancel;
Auto
...
SQL> recover database until cancel;
Cancel;

5. open database with: alter database open resetlogs
Description:
1. incomplete recovery may cause data lost of current redolog.
2. need archivelog mode and full backup.
3. backup database after recovery.
4. strongly recomment that make log mirror on different disks.

5.1.2.2 force recovery without backup
1. accounting a error

ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2)

2、notice curruent redolog corrupted

SQL> select group#,sequence#,archived,status from v$log;
 
    GROUP#  SEQUENCE# ARCHIVED STATUS
---------- ---------- -------- ----------------
         1          1 NO       CURRENT
         2          2 YES      INACTIVE
         3          3 YES      INACTIVE

3、cannot clear

SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'

4、shutdown database

SQL>shutdown immediate

5、add the following parameter to init<sid>.ora

_allow_resetlogs_corruption=TRUE

6、restart database and recover with 'until cancel'

SQL>recover database until cancel; 
Cancel
 
SQL>alter database open resetlogs;

7、full export database after open database successfully

8、shutdown database, remove init parameter

_all_resetlogs_corrupt

9、recreate database

10、import and finish recovery

11、recomment to run

ANALYZE TABLE ...VALIDATE STRUCTURE CASCADE;

Description
1. use this method only when no other method. because it may cause database inconsistent
2. cause data loss, but less than recover with backup. because the data without commit be recovered too.
3. strongly recomment run complete recreation and valication check.
4. should backup database fullly.
5. strongly recomment that make log mirror on different disks, no data loss can be accept on production environment.

Reference http://www.itpub.net/thread-126320-1-1.html