Wikipedia

Search results

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





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 :    



No comments:

Post a Comment