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.