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]$
