Recovery from the loss of Inactive 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] Mount the database
startup mount;
3] Check the status of the missing log file group from v$log view.
select * from v$log;
4]
a] If STATUS = INACTIVE and ARCHIVED = YES
alter database clear logfile group <grp number>;
b] If STATUS = INACTIVE and ARCHIVED = NO
STATUS = INACTIVE and ARCHIVED = NO
alter database clear UNARCHIVED logfile group <grp number>;
Since the unarchived log file is cleared, there will NOT be an archive log of that sequence.
Means, if log of seq#10 is cleared. Then archive log seq#10 will be not be created.
It will be like seq#8,seq#9,seq#11,seq#12. So, rollforward using archive logs will not be possible.
Also, all uncommitted transactions will be lost.
5] Open the database
alter database open;

6] Take a good backup

*The step 4(CLEAR) command will create the missing log files at the OS level.

===ARENA===

Below is the error in alert.log

Tue Oct 21 13:39:49 2008
Errors in file /u01/app/oracle/admin/burp/bdump/burp_arc0_23510.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
Tue Oct 21 13:39:49 2008

and i SHUTDOWN the Database.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              71304784 bytes
Database Buffers          209715200 bytes
Redo Buffers                2973696 bytes
Database mounted.
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'

The database cannot open as the online log file group 1 is missing. Now, we will check the status of the missing redo log group 1.


SQL> select group#,thread#,sequence#,bytes,archived,status,first_change#,to_char(first_time,'dd-mm-yy hh24:mi:ss') FIRST_TIME from v$log order by 1;

    GROUP#    THREAD#  SEQUENCE#      BYTES ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- --- ---------------- ------------- -----------------
         1          1        145    5242880 NO  INACTIVE                206100 21-10-08 13:34:50
         2          1        146    5242880 NO  INACTIVE                206188 21-10-08 13:36:59
         3          1        147    5242880 NO  CURRENT                 206304 21-10-08 13:38:48
SQL>

The redo log group 1 is INACTIVE and NOT ARCHIVED, so we will follow step 4b.

SQL> alter database clear unarchived logfile group 1;

Database altered.

The above CLEAR command have created the file /u01/burp/oradata/redo01.log, which was missing.

SQL> select group#,thread#,sequence#,bytes,archived,status,first_change#,to_char(first_time,'dd-mm-yy hh24:mi:ss') FIRST_TIME from v$log order by 1;

    GROUP#    THREAD#  SEQUENCE#      BYTES ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- --- ---------------- ------------- -----------------
         1          1          0    5242880 YES UNUSED                  206100 21-10-08 13:34:50
         2          1        146    5242880 YES INACTIVE                206188 21-10-08 13:36:59
         3          1        147    5242880 NO  CURRENT                 206304 21-10-08 13:38:48

SQL> alter database open;

Database altered.

SQL> select group#,thread#,sequence#,bytes,archived,status,first_change#,to_char(first_time,'dd-mm-yy hh24:mi:ss') FIRST_TIME from v$log order by 1;

    GROUP#    THREAD#  SEQUENCE#      BYTES ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- --- ---------------- ------------- -----------------
         1          1        148    5242880 NO  CURRENT                 206670 21-10-08 13:45:09
         2          1        146    5242880 YES INACTIVE                206188 21-10-08 13:36:59
         3          1        147    5242880 YES INACTIVE                206304 21-10-08 13:38:48

SQL> /

Excerpt from alert.log

===============

Tue Oct 21 13:44:12 2008
alter database clear unarchived logfile group 1
Tue Oct 21 13:44:12 2008
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
    BEFORE 10/21/2008 13:36:59 (CHANGE 206188) CANNOT BE USED FOR RECOVERY.
Clearing online log 1 of thread 1 sequence number 145
Tue Oct 21 13:44:12 2008
Errors in file /u01/app/oracle/admin/burp/udump/burp_ora_23573.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
Completed: alter database clear unarchived logfile group 1
Tue Oct 21 13:44:32 2008
Archiver process freed from errors. No longer stopped
Tue Oct 21 13:45:09 2008
alter database open
Tue Oct 21 13:45:09 2008
Thread 1 advanced to log sequence 148
Thread 1 opened at log sequence 148
  Current log# 1 seq# 148 mem# 0: /u01/burp/oradata/redo01.log
Successful open of redo thread 1

Since we have cleared the seq# 145, there will not be an archive log with sequence# 145. So complete rollforward using archive logs cannot be performed on backups taken prior to seq#145.

[oracle@spade burp]$ ls -ltr arch/
total 36104
-rw-r-----  1 oracle oinstall 5171712 Oct 21 13:30 1_140_667494933.arch
-rw-r-----  1 oracle oinstall 5237760 Oct 21 13:30 1_139_667494933.arch
-rw-r-----  1 oracle oinstall 5168128 Oct 21 13:30 1_141_667494933.arch
-rw-r-----  1 oracle oinstall 5168640 Oct 21 13:30 1_142_667494933.arch
-rw-r-----  1 oracle oinstall 5173248 Oct 21 13:30 1_143_667494933.arch
-rw-r-----  1 oracle oinstall 5169664 Oct 21 13:34 1_144_667494933.arch
-rw-r-----  1 oracle oinstall 5174784 Oct 21 13:44 1_146_667494933.arch
-rw-r-----  1 oracle oinstall  606208 Oct 21 13:45 1_147_667494933.arch
[oracle@spade burp]$
[oracle@spade burp]$
Advertisements

6 thoughts on “Recovery from the loss of Inactive redo log file

  1. Pingback: Index « My confrontations with oracle

  2. Holy Crap Marie!!!

    This article really got me out of a jam. A jr dba decided to delete a redo log file from the o/s with the database open before dropping the log file group.

    Thanks for the steps here. By clearing the unarchived file, it re-created the redo log file and we were back in business.

  3. Pingback: ORA-16014: log 3 sequence# 117 not archived, no available destinations | hiteshgondalia(OCE,OCP)

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s