Redo Failure : Active Redo lost

[oracle@node2 trace]$ rm /data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_cfxm48rs_.log
[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_cfxm48oq_.log
[oracle@node2 trace]$ rm /data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_4_cfxn7jjx_.log
[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_4_cfxn7jdj_.log
[oracle@node2 trace]$ rm /data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_5_cfxmvwmp_.log
[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_5_cfxmvwk5_.log
[oracle@node2 trace]$ rm /data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_6_cfxmvz8t_.log
[oracle@node2 trace]$ rm /data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_6_cfxmvz5l_.log

SQL> startup mount ;
ORACLE instance started.

Total System Global Area 1135747072 bytes
Fixed Size    2287528 bytes
Variable Size  771754072 bytes
Database Buffers  352321536 bytes
Redo Buffers    9383936 bytes
Database mounted.


SQL> Alter database open ;
Alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_cfxm48rs_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_cfxm48oq_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


MEMBER                                GROUP# STATUS ARC
--------------------------------------------------------------------------- ---------- ---------------- ------- ---
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_cfxmxybl_.log     1 INACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_cfxmxy5d_.log         1 INACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_cfxm48rs_.log     2 ACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_cfxm48oq_.log         2 ACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_cfxht6mt_.log     3 CURRENT NO
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_cfxht6jg_.log         3 CURRENT NO
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_4_cfxn7jjx_.log     4 ACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_4_cfxn7jdj_.log         4 ACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_5_cfxmvwmp_.log     5 ACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_5_cfxmvwk5_.log         5 ACTIVE YES
/data01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_6_cfxmvz8t_.log     6 ACTIVE YES
/data01/app/oracle/oradata/ORCL/onlinelog/o1_mf_6_cfxmvz5l_.log         6 ACTIVE YES

Perform the following steps when restoring an active online redo log group:

• Verify the damage to the members.
• Verify that the status is ACTIVE.
• Attempt to issue a checkpoint. (alter system checkpoint;) If the checkpoint completes successfully, then the active log group should be marked as INACTIVE. A successful checkpoint ensures that all modified database buffers have been written to disk, and at that point, only transactions contained in the CURRENT online redo log will be required for crash recovery.,so you can clear the log group.
• If the log group that was cleared was unarchived, back up your database immediately.
• If the checkpoint is unsuccessful, then you will have to perform incomplete recovery


Lets do checkpoint to see it completes or not :

SQL> alter system checkpoint ;
alter system checkpoint
*
ERROR at line 1:
ORA-01109: database not open


Checkpoint is unsuccessful, then you will have to perform incomplete recovery :

SQL> select group#, status, archived, thread#, sequence#, first_change# from v$log order by 1;

    GROUP# STATUS    ARC    THREAD#  SEQUENCE# FIRST_CHANGE#
---------- ---------------- --- ---------- ---------- -------------
1 INACTIVE       YES 1   41    1820096
2 ACTIVE       YES 1   42    1820099
6 ACTIVE       YES 1   44    1822759
4 ACTIVE       YES 1   45    1823084
5 ACTIVE       YES 1   43    1821412
3 CURRENT       NO 1   46    1823176

SQL> select group#, status, archived, thread#, sequence#, first_change# from v$log order by 1;

    GROUP# STATUS     ARC    THREAD#  SEQUENCE# FIRST_CHANGE#
---------- ---------------- --- ---------- ---------- -------------
1 INACTIVE    YES 1   41     1820096 ---------> Safe
2 ACTIVE     YES 1   42     1820099 --------> Damaged
3 CURRENT     NO 1   46     1823176 ---------> Safe
4 ACTIVE     YES 1   45     1823084 --------> Damaged
5 ACTIVE     YES 1   43     1821412 --------> Damaged
6 ACTIVE     YES 1   44     1822759 --------> Damaged


In my case I can restore and recover until SCN 1820096. Let's look the following steps:

RMAN> restore database until scn 1820096

RMAN> recover database until scn 1820096

RMAN> alter database open resetlogs;

Statement processed

To recover when you’ve lost all members of an inactive redo log group

To recover when you’ve lost all members of an inactive redo log group, perform the following steps:

• ·         Verify that all members of a group have been damaged.
• ·         Verify that the log group status is INACTIVE.
• ·         Re-create the log group with the clear logfile command.
• ·         If the re-created log group has not been archived, then immediately back up your database.

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

    GROUP#    THREAD# GROUPSTATUS      MEMBER
---------- ---------- ---------------- -------------------------------------------------- -------
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.

SQL> ! rm /data01/app/oracle/datafiles/racdb/redo012.log

SQL> ! rm /data01/app/oracle/datafiles/racdb/redo01.log

SQL> Alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> / -----------------------> hanged

If all members of an online redo log group are damaged, you won’t be able to open your database. In this situation, Oracle will allow you to only mount your database. Inspect your alert.log file, and verify that all members of a redo log group are damaged.

Alert log :
========
Sun Jun 07 15:18:13 2015
Errors in file /data01/app/oracle/diag/rdbms/racdb/racdb/trace/racdb_arc2_5173.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
ORA-00312: online log 1 thread 1: '/data01/app/oracle/datafiles/racdb/redo01.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_arc2_5173.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
ORA-00312: online log 1 thread 1: '/data01/app/oracle/datafiles/racdb/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Master background archival failure: 313


SQL> Alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> /

System altered.

Next, run the following query to verify that the damaged log group is INACTIVE and determine whether it has been archived:

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

If the status is INACTIVE, then this log group is no longer needed for crash recovery .Therefore, you can use the clear logfile command to re-create all members of a log group. The following example re-creates all log members of group 1:

SQL> alter database clear logfile group 1;

If the log group has not been archived, then you will need to use the clear unarchived logfile command as follows:

SQL> alter database clear unarchived logfile group 1;

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

    GROUP# STATUS     ARC    THREAD#  SEQUENCE#
---------- ---------------- --- ---------- ----------
 1 INACTIVE     NO  1    19
 2 INACTIVE     NO  1    20
 3 CURRENT     NO  1    21


SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of instance racdb (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: '/data01/app/oracle/datafiles/racdb/redo01.log'
ORA-00312: online log 1 thread 1: '/data01/app/oracle/datafiles/racdb/redo012.log'


SQL> alter database clear unarchived logfile group 1;

Database altered.

The clear logfile will drop and re-create all members of a log group for you. You can issue this
command even if you have only two log groups in your database.

If the online redo log group has not been archived, then it may be required for media recovery. In this case, use the clear unarchived logfile command to re-create the logfile group members. Back up your database as soon as possible in this situation.

The unarchived log group may be needed for media recovery if the last database backups were taken before the redo information in the log was created. This means if you attempt to perform media recovery, you won’t be able to recover any information in the damaged log file or any transactions that were created after that log. If the clear logfile command does not succeed because of an I/O error and it’s a permanent problem, then you will need to consider dropping the log group and re-creating it in a different location.

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.