Recovering After the Loss of All Members of an Online Redo Log Group

Recovering After the Loss of All Members of an Online Redo Log Group

If a media failure damages all members of an online redo log group, different scenarios can occur, depending on the type of online redo log group affected by the failure and the archiving mode of the database.

If the damaged log group is inactive, then it is not needed for instance recovery; if it is active, then it is needed for instance recovery.

If the group is... Then... And you should... Inactive It is not needed for instance recovery Clear the archived or unarchived group. Active It is needed for instance recovery Attempt to issue a checkpoint and clear the log; if impossible, then you must restore a backup and perform incomplete recovery up to the most recent available log. Current It is the log that Oracle is currently writing to Attempt to clear the log; if impossible, then you must restore a backup and perform incomplete recovery up to the most recent available log.

Your first task is to determine whether the damaged group is active or inactive.

To determine whether the damaged groups are active:

1. Locate the filename of the lost redo log in V$LOGFILE and then look for the group number corresponding to it. For example, enter:

SELECT group#, status, member FROM v$logfile;

GROUP# STATUS MEMBER
------- ----------- ---------------------
0001 /oracle/dbs/log1a.f
0001 /oracle/dbs/log1b.f
0002 INVALID /oracle/dbs/log2a.f
0002 INVALID /oracle/dbs/log2b.f
0003 /oracle/dbs/log3a.f
0003 /oracle/dbs/log3b.f

2. Determine which groups are active. For example, enter:

SELECT group#, members, status, archived FROM v$log;

GROUP# MEMBERS STATUS ARCHIVED
------ ------------ ---------- -----------
0001 2 INACTIVE YES
0002 2 ACTIVE NO
0003 2 CURRENT NO

3. If the affected group is inactive, follow the procedure in "Losing an Inactive Online Redo Log Group". If the affected group is active as in the above example, then follow the procedure in "Losing an Active Online Redo Log Group".

Losing an Inactive Online Redo Log Group

If all members of an online redo log group with INACTIVE status are damaged, then the procedure depends on whether you can fix the media problem that damaged the inactive redo log group.

Media Failure Procedure Temporary Fix the problem. LGWR can reuse the redo log group when required. Permanent The damaged inactive online redo log group eventually halts normal database operation. Reinitialize the damaged group manually using ALTER DATABASE CLEAR LOGFILE as described below.

You can clear an active redo log group when the database is open or closed. The procedure depends on whether the damaged group has been archived.

To clear an inactive, online redo log group that has been archived:

1. If the database is shut down, start a new instance and mount the database, but do not open it:

svrmgr> startup mount;

2. Reinitialize the damaged log group. For example, to clear redo log group 2, issue:

svrmgr> Alter database clear logfile group 2;

To clear an inactive, online redo log group that has not been archived:

1. If the database is shut down, then start a new instance and mount the database, but do not open it:

svrmgr> startup mount;

2. Clear the log using the UNARCHIVED keyword. For example, to clear log group 2, issue:

svrmgr> Alter database clear logfile unarchived group 2;

3. 
   

4. NOTES: If there is an offline datafile that requires the cleared unarchived log to bring it online,
5. then the keywords UNRECOVERABLE DATAFILE are required. The datafile and its entire
6. tablespace have to be dropped because the redo necessary to bring it online is being cleared,
7. and there is no copy of it. For example, enter:

svrmgr> Alter database clear logfile unarchived group 2 unrecoverable datafile;

8. 
   

9. Perform a closed whole backup
10. Back up the database's control file using the ALTER DATABASE statement. For example, enter:

svrmgr> ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/dbs/cf_backup.f';

Losing an Active Online Redo Log Group

If the database is still running and the lost active log is not the current log, then issue the ALTER SYSTEM CHECKPOINT statement. If successful, your active log is rendered inactive, and you can follow the procedure in "Losing an Inactive Online Redo Log Group". If unsuccessful, or if your database has halted, perform one of these procedures, depending on the archiving mode.

Note that the current log is the one LGWR is currently writing to. If a LGWR I/O fails, then LGWR terminates and the instance crashes. In this case, you must restore a backup, perform incomplete recovery, and open the database with the RESETLOGS option.

To recover from loss of an active online redo log group in NOARCHIVELOG mode:

1. If the media failure is temporary, correct the problem so that Oracle can reuse the group when required.
2. Restore the database from a whole database backup using an operating system utility. For example, enter:

NT > !copy c:\backup\*.dbf c:\data\

3. Mount the database:

SVRMGR> startup mount;

4. Open the database using the RESETLOGS option:

SVRMGR> alter database open resetlogs;

5. Shut down the database normally:

SVRMGR> shutdown immediate;

6. Perform a closed whole backup

To recover from loss of an active online redo log group in ARCHIVELOG mode:

1. If the media failure is temporary, then correct the problem so that Oracle can reuse the group when required.
2. Perform incomplete media recovery. Use the procedure given in Loss of Current Redo Logs, recovering up through the log before the damaged log.