Redo Log Recovery

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips


Oracle Version: 10.1.0.5.0
OS: Windows
Archivelog ENABLE
Backup Method RMAN

The method of recovery from loss of all members of an online log group depends on a number of factors, such as:


1. The state of the database (open, crashed, closed consistently, and so on)



If you lose the current group, and the database is not closed consistently (either it is open, or it has crashed), then you will have to restore an old backup and perform point-in-time recovery, followed by OPEN RESETLOGS. You will lose all transactions that were in the lost log. You should take a new full database backup immediately after the OPEN RESETLOGS. Backups from before the OPEN RESETLOGS will not be recoverable because of the lost log.



Error found in ALERT.LOG file

Mon Aug 13 16:45:58 2007
Errors in file c:\oracle\product\10.1.0\admin\orcl\udump\orcl_ora_4044.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


First check LOST redo group is current or non-current.


SQL> select group#,status from v$Log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT ----> lost group


Note: Lost group is related to current.


Logon to RMAN

RMAN>run
{
restore database;
recover database;
}



Log on to sqlplus prompt

SQL>recover database until cancel;
cancel;

SQL>alter database open resetlogs;

2. whether the lost redo log group was current



If you lose the current redo log group, and if the database is closed consistently, then you can perform OPEN RESETLOGS with no transaction loss. However, you should take a new full database backup. Backups from before the OPEN RESETLOGS will not be recoverable because of the lost log.



3. Whether the lost redo log group was archived



If you lose a noncurrent redo log group, then you can use the ALTER DATABASE CLEAR LOGFILE statement to re-create all members in the group. No transactions are lost. If the lost redo log group was archived before it was lost, then nothing further is required. Otherwise, you should immediately take a new full backup of your database. Backups from before the log was lost will not be recoverable because of the lost log.


Below Error message found in ALERT.LOG file and database is stop(SHUTDOWN ABORT) ...


Mon Aug 13 12:50:50 2007
Errors in file c:\oracle\product\10.1.0\admin\orcl\bdump\orcl_lgwr_3904.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


How to recover redo log files.

Note: We have last valid backup and all archivelog files.

Logon to SQL prompt with SYS user


SQL>conn / as sysdba
connected to idle instance

SQL>startup mount


SQL>--First check which REDO group is lost.


SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 INVALIDATED
2 UNUSED------>lost group
3 INACTIVE

SQL> --Note current REDOGRP is 1 and lost REDOLOG GRP 2.
Note: NON-Current group is LOST.


SQL> --Note current REDOGRP is 1 and lost REDOLOG GRP 2.


SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database open;

Database altered.



Logon to RMAN


RMAN> run
2> {
3> restore database;
4> recover database;
5> }