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;
5] If STATUS = ACTIVE and ARCHIVED = NO
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

===ARENA===

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;
    GROUP#  SEQUENCE#      BYTES FIRST_CHANGE# TIM               STATUS
---------- ---------- ---------- ------------- ----------------- ----------------
         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>

SQL> alter database open resetlogs;
Database altered.
SQL>

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