Redo Failure Scenarios

Handling Redo Log Failure:

Determine the course of action and how to restore:

Yoy have experienced a problem with your online redo log files and need to determaine what shape they are in and what course of action to take.

Solution:

Follow these steps when dealing with online redo log failures:

1.Inspect the alert.log file to determine which redo log files have experienced a media failure.

2.Query v$log and v$logfile to determine the status of the log group and degree ogf multiplexing.

SELECT a.group#,a.thread#,a.status grp_status

From v$log a,

V$logfile b

Where a.group#=b.group#

Order by a.group#=b.group#

GROUP#	THREAD#	GRP_STATUS	MEMBER	STATUS
1	1	ACTIVE	/d1/rptprd01/logs01/redo01.log
1	1	ACTIVE	/d1/rptprd01/oracle/rptprd01/redologs/redo01b.log
2	1	ACTIVE	/d1/rptprd01/logs01/redo02.log
2	1	ACTIVE	/d1/rptprd01/oracle/rptprd01/redologs/redo02b.log
3	1	CURRENT	/d1/rptprd01/oracle/rptprd01/redologs/redo03b.log
3	1	CURRENT	/d1/rptprd01/logs01/redo03.log
4	1	INACTIVE	/d1/rptprd01/logs01/redo04.log
4	1	INACTIVE	/d1/rptprd01/oracle/rptprd01/redologs/redo04b.log
5	1	ACTIVE	/d1/rptprd01/oracle/rptprd01/redologs/redo05b.log
5	1	ACTIVE	/d1/rptprd01/logs01/redo05.log
6	1	ACTIVE	/d1/rptprd01/oracle/rptprd01/redologs/redo06b.log
6	1	ACTIVE	/d1/rptprd01/logs01/redo06.log

3. Depending on the status of the log group use the below table to determine what action to take:

When diagnosing online redo log issues, the V$LOG and V$LOGFILE views are particularly helpful. You can query these views while the database is mounted or open.

Type of failure	Status of v$log	Action
One member failed in multiplexed group	N/A	Re-create member.
All members of a group	Inactive	Clear logfile.
All members of a group	Active	Attempt checkpoint, and if successful, clear logfile. If checkpoint is unsuccessful, perform incomplete recovery.
All members of a group	Current	Attempt to clear log, and if unsuccessful, perform incomplete recovery.

Useful Views Related to Online Redo Logs
V$LOG	Displays the online redo log group information stored in the control file.
V$LOGFILE	Displays online redo log file member information.
Status for Online Redo Log Groups in the V$LOG View
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.
Status for Online Redo Log File Members in the V$LOGFILE View
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.

It’s important to differentiate between the STATUS column in V$LOG and the STATUS column in V$LOGFILE. The STATUS column in V$LOG reflects the status of the log group. The STATUS column in V$LOGFILE reports the status of the physical online redo log file member.

Restoring After Losing One Member of the Multiplexed Group

Problem

You are experiencing media failure with one member of a multiplexed online redo log group and need to restore the damaged online redo log file member.

Solution

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:

·         Identify the online redo log file experiencing media failure --- using alert log.

·          Ensure that the online redo log file is not part of the current online log group. (v$log & v$logfile) – If it is showing Current status , then use the alter system switchlogfile command to make the next group the current group.

·         Drop the damaged member.

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

·         Add a new member to the group.

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

If an unused log file already happens to exist in the target location, you can use the reuse parameter to overwrite and reuse that log file. The log file must be the same size as the other log files in the group.

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

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

Problem

You’re attempting to open your database and receive this message: Database mounted.

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\REDO01.LOG'

ORA-00312: online log 1 thread 1:'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01B.LOG'

The message indicates that two members of an online redo log group in your database have experienced a media failure. You wonder how you’re going to get your database open again.

Solution

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.

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.

You should see a message indicating that all members of an online redo log group are damaged and database cannot open:

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-00312: online log 1 thread 1:'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'

O/S-Error: (OS 2) The system cannot find the file specified.

ORA-313 signalled during: alter database open...

Next, ensure that your database is in mount mode:

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;

If the cleared log group had not been previously archived, it’s critical that you immediately create a backup of 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.

You can drop a log group with the drop logfile group command:

SQL> alter database drop logfile group <group #>;

You can add a new log group with the add logfile group command:

SQL> alter database add logfile group <group_#> ('\directory\file') SIZE <bytes> K|M;

You can specify the size of the log file in bytes, kilobytes, or megabytes. The following example adds log group 4 with two members sized at 50MB:

SQL> alter database add logfile group 4 ('C:\ORADATA\ORCL\REDO04A.LOG','D:\ORADATA\ORCL\REDO04B.LOG') SIZE 50M;

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

Problem

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

Solution

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

Verify the status of the log group to see whether it is now INACTIVE with this query:

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

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 <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 <group#>;

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

How It Works

An online redo log group with an ACTIVE status is still required for crash recovery. If all members of an active online redo log group experience media failure, then you must attempt to issue a checkpoint. If the checkpoint is successful, then you can clear the log group. If the checkpoint is unsuccessful, then you will have to perform an incomplete recovery.

If the checkpoint is successful and if the log group has not been archived, then the log may be required for media recovery. 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.

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.

Solution

Unfortunately, your alternatives are limited when you lose all members of a current online redo log group. Here are some possible options:

·         Perform an incomplete recovery up to the last good SCN.

·         If flashback is enabled, flash your database back to the last good SCN.

·         If you’re using Oracle Data Guard, fail over to your physical or logical standby database.

·         Contact Oracle Support for suggestions.

In preparation for an incomplete recovery, first determine the last good SCN by querying the FIRST_CHANGE# column from V$LOG. In this scenario, you’re missing only the current online redo logs. Therefore, you can perform an incomplete recovery up to, but not including, the FIRST_CHANGE# SCN of the current online redo log.

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

In this case, you can restore and recover up to, but not including, SCN 1800573. Here’s how you’d do that:

RMAN> restore database until scn 1800573;

RMAN> recover database until scn 1800573;

RMAN> alter database open resetlogs;

How It Works

Losing all members of your current online redo log group is arguably the worst thing that can happen to your database. If you experience media failure with all members of the current online redo group, then you will lose any transactions contained in those logs. In this situation, you will have to perform incomplete recovery before you can open your database.