Recovering after loss of all members of the INACTIVE redo log group

You’re attempting to open your database and receive this message

ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo02.log’
ORA-00312: online log 2 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo02a.log’

The message indicates that two members of an online redo log group in your database have experienced a media failure.

To recoveer 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.

Recreate the log group with the clear logfile command.

If the recreated log group has not been archived,then immediately backup up your database.

SQL> select group#,members,status from v$log;
GROUP#    MEMBERS STATUS
———- ———- —————-
1          2 CURRENT
2          2 INACTIVE
3          2 ACTIVE

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

(Here I manually deleted redolog files members of group 2)
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo02.log’
ORA-00312: online log 2 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo02a.log’

If all members of an online redo log group are damaged, you won’t be able to open your database, oracle will allow you to only mount your database.

SQL> select status from v$instance;

STATUS
————
MOUNTED

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

GROUP# STATUS           ARC    THREAD#  SEQUENCE#
———- —————- — ———- ———-
1 CURRENT          NO           1         11
3 INACTIVE         YES          1         10
2 INACTIVE         YES          1          9

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 recreate all members of a log group.

SQL> alter database clear logfile group 2;

Database altered.

If the 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 2;

Database altered.

If the cleared log group had not been previously archived, it’s critical that you immediately create a backup of your database.

SQL> alter database open;

Database altered.

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

GROUP# STATUS           ARC    THREAD#  SEQUENCE#
———- —————- — ———- ———-
1 INACTIVE         YES          1         11
2 CURRENT          NO           1         12
3 INACTIVE         YES          1         10

SQL>