RMAN Redo Log Failures

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_lgwr_5800.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01B.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Query V$LOG and V$LOGFILE views to determine the status of your log group and the
member files in each group:

SQL> select
2 a.group#, a.thread#,
3 a.status grp_status,
4 b.member member,
5 b.status mem_status
6 from v$log a,
7 v$logfile b
8 where a.group# = b.group#
9 order by a.group#, b.member


V$LOG Displays the online redo log group information stored in the control file.
V$LOGFILE Displays online redo log file member information.

Status Meaning

CURRENT The log group that is currently being written to by the log writer.
ACTIVE The log group is required for crash recovery and may or may not have
been archived.
CLEARING The log group is being cleared out by an alter database clear
logfile command.
CLEARING_CURRENT The current log group is being cleared of a closed thread.
INACTIVE The log group isn’t needed for crash recovery and may or may not have
been archived.
UNUSED The log group has never been written to; it was recently created.

Table 14-4. Status for Online Redo Log File Members in the V$LOGFILE View
Status Meaning

INVALID The log file member is inaccessible, or it has been recently created.
DELETED The log file member is no longer in use.
STALE The log file member’s contents are not complete.
NULL The log file member is being used by the database.

If the failed member is in the current log group, then use the alter system switch
logfile command to make the next group the current group. Then drop the failed member
as follows:

SQL> alter database drop logfile member '<\directory\member>';

SQL> alter database add logfile member '<\new directory\member>' to group ;


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

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


SQL> connect / as sysdba
SQL> startup mount;

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;
GROUP# STATUS ARC THREAD# SEQUENCE#
------ -------- --- ------- ----------
1 INACTIVE YES 1 44
3 INACTIVE YES 1 45
2 CURRENT NO 1 46


If the status is INACTIVE, then this log group is no longer needed for crash recovery , 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;

SQL> alter system checkpoint;

If the status is inactive and the log has been archived, you can use the clear logfile command to re-create the log group, as shown here:

SQL> alter database clear logfile group ;

If the status is inactive and the log group has not been archived, then re-create it with the clear unarchived logfile command, as shown here:

SQL> alter database clear unarchived logfile group ;

Recovering After Loss of All Members of the CURRENT Redo Log Group

Problem

All of the members of a current online redo log group in your database have experienced media failure.

SQL> shutdown immediate;
SQL> startup mount;
SQL> select group#, status, archived, thread#, sequence#, first_change# from v$log;
GROUP# STATUS ARC THREAD# SEQUENCE# FIRST_CHANGE#
------ -------- --- ------- ---------- -------------
1 INACTIVE YES 1 50 1800550
2 INACTIVE YES 1 49 1800468
3 CURRENT NO 1 51 1800573

RMAN> restore database until scn 1800573;
RMAN> recover database until scn 1800573;
RMAN> alter database open resetlogs;