Recovering After Losing a Member of a Multiplexed Online Redo Log Group

If the online redo log of a database is multiplexed, and if at least one member of each online redo log group is not affected by the media failure, then the database continues functioning as normal, but error messages are written to the log writer trace file and the alert_SID.log of the database.

Solve the problem by taking one of the following steps:

1. If the hardware problem is temporary, then correct it. The log writer process accesses the previously unavailable online redo log files as if the problem never existed.

2. If the hardware problem is permanent, then drop the damaged member and add a new member by using the following procedure.

3. Locate the filename of the damaged member in V$LOGFILE. The status is INVALID if the file is inaccessible:

SELECT GROUP#, STATUS, MEMBER 
FROM V$LOGFILE
WHERE STATUS='INVALID';

GROUP#       STATUS            MEMBER
------------      --------------        ---------------------
   0002            INVALID      /oracle/oradata/trgt/redo02.log


STATUS Column of V$LOG

UNUSED: The online redo log has never been written to.

CURRENT: The online redo log is active, that is, needed for instance recovery, and it is the log to which the database is currently writing. The redo log can be open or closed.

ACTIVE: The online redo log is active, that is, needed for instance recovery, but is not the log to which the database is currently writing.It may be in use for block recovery, and may or may not be archived.

CLEARING: The log is being re-created as an empty log after an ALTER DATABASE CLEAR
LOGFILE statement. After the log is cleared, then the status changes to UNUSED

CLEARING_CURRENT: The current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.

INACTIVE: The log is no longer needed for instance recovery. It may be in use for media recovery, and may or may not be archived.

4. Drop the damaged member. For example, to drop member redo01.log from group 2, issue:

ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/trgt/redo02.log';

5. Add a new member to the group. For example, to add redo02.log to group 2, issue:

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/oradata/trgt/redo02b.log'  TO GROUP 2;

6. If the file you want to add already exists, then it must be the same size as the other group members, and you must specify REUSE. For example:

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/oradata/trgt/redo02b.log' REUSE TO GROUP 2;