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>