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)
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.
RLF2: Loosing INACTIVE Redo Log Files
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
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
•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.
👍👍👍👍👍👍👍
