RMAN-Backup and Recovery : part - 3 (Loss of Redo Log Files) ! ** The Most Essential But The Most Ignored **

Loss of Redo Log Files

•Understanding STATUS column of V$LOG and V$LOGFILE views

•RLF1: Loosing a Member of Multiplexed Redo Log Files

•RLF2: Loosing INACTIVE Redo Log Files

•RLF3: Loosing CURRENT Redo Log Files

•RLF4: Loosing ACTIVE Redo Log Files

Understanding STATUS of Redo Log Group (V$LOG)

The STATUS column of V$LOG view reflects the status of the log group:

•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.

•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 as it was recently   created.

The STATUS column of V$LOGFILE reports the status of a online redo log file member :

•INVALID:  The log file member is inaccessible, or it has been recently   created.

•NULL:  The log file member is being used by the database.

Recovering from Redo Log File Failures - Flowchart

RLF1: Loosing a Member of Multiplexed Redo Log Files

–

•Problem:

A member of multiplexed redo log group is lost

–Database is UP

           •Solution(s):

–Fix the media or

–Drop the affected Redo Log File and Create a new one in a different location

•Perform few log switches and monitor alert.log

Errors in file c:\db10g\dump\db10g_lgwr_3724.trc:

ORA-00321: log 4 of thread 1, cannot update log file header

ORA-00312: online log 4 thread 1: 'E:\DB10G\DATA\REDO04_2.LOG'

ORA-27091: unable to queue I/O

ORA-27070: async read/write failed

OSD-04008: WriteFile() failure, unable to write to file

O/S-Error: (OS 1006) The volume for a file has been externally altered so that the opened file is no longer valid.

Sat Aug 14 20:43:50 2010

Errors in file c:\db10g\dump\db10g_lgwr_3724.trc:

ORA-00313: open failed for members of log group 4 of thread 1

Solution(s):

1)Fix the media (plug in the pen drive)

-Log Writer continues writing to the Redo Log File as if the problem never existed

-The V$LOG.STATUS is updated to NULL

2) Drop and recreate the affected member to a different location

RLF2: Loosing INACTIVE Redo Log Files

•Problem:

–The only member of the redo log group is lost

–Status of the Redo Log Group is INACTIVE

–Database is Up

•Solution(s):

1)If this is a temporary media failure, fix the issue and start database

2)If the redo log file is lost while the media remains available then clear the archvied/unarchived log file

3)If the media failure is permanent then drop and re-create the redo log group to a new location 

–Crash recovery information in database alert.log  

ALTER DATABASE OPEN

Sat Aug 28 01:32:17 2010

Beginning crash recovery of 1 threads

 parallel recovery started with 2 processes

Sat Aug 28 01:32:17 2010

Started redo scan

Sat Aug 28 01:32:18 2010

Completed redo scan

 0 redo blocks read, 0 data blocks need recovery

Sat Aug 28 01:32:18 2010

Started redo application at

 Thread 1: logseq 907, block 2, scn 90480632

Sat Aug 28 01:32:18 2010

Recovery of Online Redo Log: Thread 1 Group 2 Seq 907 Reading mem 0

  Mem# 0: C:\DB10G\DATA\REDO2.LOG

Sat Aug 28 01:32:18 2010

Completed redo application

Sat Aug 28 01:32:18 2010

Completed crash recovery at

 Thread 1: logseq 907, block 2, scn 90500633

 0 data blocks read, 0 data blocks written, 0 redo blocks read

Sat Aug 28 01:32:19 2010

2) If the redo log file is lost while the media remains available then clear the archived/unarchived log file

RLF3: Loosing CURRENT Redo Log Files

•Problem:

–All the member of an CURRENT redo log group are lost

–Valid database backup exist

•Solution:

–Startup database in MOUNT mode

–Identify the last good SCN

–Restore database until last good SCN

–Recover database until last good SCN

–Re-create the redo log group to a different location

–Open database with RESETLOGS option

RLF4: Loosing ACTIVE Redo Log Files :

•Problem:

–All the member of an ACTIVE redo log group are lost

–Database is Up

•Solution:

–Issue a Checkpoint

–Check redo log status

–If Checkpoint is SUCCESS then CLEAR redo log group.

–If Checkpoint FAILS to complete then perform incomplete recovery by identifying the last good SCN

•Simulating Media Failure

–Create a Redo Log Group with a single member on a pen drive

–Unplug the pen drive while the database is in open mode and V$LOG.STATUS = ACTIVE

Solution:

–If Checkpoint is SUCCESS then CLEAR redo log group.

👍👍👍👍👍👍👍

👇👨👇   

Loss of Control File :    
👇👨👇   

Loss of pfile/spfile :