Media failure with one member of a multiplexed online non-current redo log group

If your online redo log file members are multiplexed, the log writer will continue to function as long as it can successfully write to one member of the current log group. If the problem is temporary, then as soon as the online redo log file becomes available, the log writer will start to write to the online redo log file as if there was never an issue. If the media failure is permanent (such as a bad disk), then you’ll need to replace the disk and drop and re-create the bad member to its original location. If you don’t have the option of replacing the bad disk, then you’ll need to drop the bad member and re-create it in an alternate location.

For permanent media failures, here are the instructions for dropping and re-creating one member of an online redo log group:

SQL> SELECT a.group#,a.thread#,a.status groupStatus , b.member , b.STATUS fileStatus From v$log a , V$logfile b Where a.group#=b.group# Order by a.group#, b.group# ;

    GROUP#    THREAD# GROUPSTATUS      MEMBER              FILESTATUS
---------- ---------- ---------------- ---------------------------------------------------------------------- -------------
1    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo01.log
1    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo012.log
2    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo022.log
2    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo02.log
3    1 CURRENT       /data01/app/oracle/datafiles/racdb/redo032.log
3    1 CURRENT       /data01/app/oracle/datafiles/racdb/redo03.log

rm /data01/app/oracle/datafiles/racdb/redo012.log -----> Drop a redo from group1 which is inactive and have one more multiplexed member .

Do few log switch and check the status again after drop :

SQL> SELECT a.group#,a.thread#,a.status groupStatus , b.member , b.STATUS fileStatus From v$log a , V$logfile b Where a.group#=b.group# Order by a.group#, b.group# ;

    GROUP#    THREAD# GROUPSTATUS      MEMBER      FILESTA
---------- ---------- ---------------- ---------------------------------------------------------------------- -------
1    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo01.log
1    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo012.log      INVALID
2    1 CURRENT       /data01/app/oracle/datafiles/racdb/redo022.log
2    1 CURRENT       /data01/app/oracle/datafiles/racdb/redo02.log
3    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo032.log
3    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo03.log

6 rows selected.


Alert log :
========
Sun Jun 07 14:43:55 2015
Errors in file /data01/app/oracle/diag/rdbms/racdb/racdb/trace/racdb_arc3_4548.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/data01/app/oracle/datafiles/racdb/redo012.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /data01/app/oracle/diag/rdbms/racdb/racdb/trace/racdb_arc3_4548.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/data01/app/oracle/datafiles/racdb/redo012.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Archived Log entry 12 added for thread 1 sequence 13 ID 0x346a8c39 dest 1:
Sun Jun 07 14:43:56 2015
Errors in file /data01/app/oracle/diag/rdbms/racdb/racdb/trace/racdb_m000_5083.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/data01/app/oracle/datafiles/racdb/redo012.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Checker run found 1 new persistent data failures


SQL> alter database add logfile member '/data01/app/oracle/datafiles/racdb/redo012.log' reuse to group 1 ;

Database altered.

SQL> SELECT a.group#,a.thread#,a.status groupStatus , b.member , b.STATUS fileStatus From v$log a , V$logfile b Where a.group#=b.group# Order by a.group#, b.group# ;

GROUP# THREAD# GROUPSTATUS      MEMBER  FILESTATUS
---------- ---------- ---------------- ---------------------------------------------------------------------- -------
 1    1 INACTIVE      /data01/app/oracle/datafiles/racdb/redo01.log
1    1  INACTIVE       /data01/app/oracle/datafiles/racdb/redo012.log         INVALID
2    1 CURRENT      /data01/app/oracle/datafiles/racdb/redo022.log
2    1 CURRENT      /data01/app/oracle/datafiles/racdb/redo02.log
3    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo032.log
3    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo03.log

6 rows selected.

SQL> Alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> SELECT a.group#,a.thread#,a.status groupStatus , b.member , b.STATUS fileStatus From v$log a , V$logfile b Where a.group#=b.group# Order by a.group#, b.group# ;

    GROUP#    THREAD# GROUPSTATUS      MEMBER   FILESTA
---------- ---------- ---------------- ---------------------------------------------------------------------- -------
1    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo01.log
1    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo012.log
2    1 CURRENT       /data01/app/oracle/datafiles/racdb/redo022.log
2    1 CURRENT       /data01/app/oracle/datafiles/racdb/redo02.log
3    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo032.log
3    1 INACTIVE       /data01/app/oracle/datafiles/racdb/redo03.log

6 rows selected.