Recover from loss of Active redo log file

LGWR,logwriter writes the redo data from the log buffer cache to the redo log files when,
1] A transaction is commited
2] Every 3 Seconds
3] When redo log buffer is 1/3 full

The LGWR process writes to the redo log file in a circluar fashion. After log group 1 is written, the LGWR will move to log group 2 and meanwhile ARC(archiver) will archive log group 1. The LGWR will complain if one or all of the members of a log group are missing. The database will be  unable to serve any requests if all members of group are missing.

If all the members of a redo log group goes missing before the ARC process could archive it.
Oracle complains with error ORA-00313,ORA-00312,ORA-27037 once all members of the log file group goes missing.
The database halts unable to switch the log file.

1] Shutdown the database
2] Restore the datafiles and proceed for an incomplete recovery until the missing log sequence.
2] Mount the database
startup mount;
3] Check the status of the missing log file group from v$log view.
select group#,sequence#,bytes,first_change#,to_char(first_time,’DD-MM-YY hh24:mi:ss’) tim,status from v$log;
recover database until time ‘yyyy-mm-dd:hh24:mi:ss’;
* ‘ss’ in the seconds should be 1 second less than the first_time of the missing redo log file.
6] Open the database with resetlogs option
7] Take a solid backup


Mon Oct 27 15:04:18 2008
Errors in file /u01/app/oracle/admin/burp/bdump/burp_arc0_5826.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/burp/oradata/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

I did shutdown the DB and restored the *.dbf files.

cp -rp /u01/burp/backup u01/burp/oradata/

Startup mount;

SQL> select group#,sequence#,bytes,first_change#,to_char(first_time,'DD-MM-YY hh24:mi:ss') tim,status from v$log;
---------- ---------- ---------- ------------- ----------------- ----------------
         1        148    5242880        206241 27-10-08 15:02:14 INACTIVE
         3        147    5242880        206196 27-10-08 15:02:13 INACTIVE
         2        149    5242880        206336 27-10-08 15:04:18 CURRENT
SQL> recover database until time '2008-10-27:15:02:13';
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.

All data after 27-10-08 15:02:13 will be lost.


One thought on “Recover from loss of Active redo log file

  1. Pingback: Index « My confrontations with oracle

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s