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, then 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 active, then it is needed for crash recovery; otherwise, it is not.

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

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

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 preceding 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.

Clearing Inactive, Archived Redo

You can clear an inactive 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, use the following procedure:

If the database is shut down, then start a new instance and mount the database:

STARTUP MOUNT

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

ALTER DATABASE CLEAR LOGFILE GROUP 2;