LOSS OF THE ONLINE REDO LOG FILES
Note : Database is running in ARCHIVELOG mode
It is highly recommended by Oracle that the online redo log groups are mirrored, with the mirrored members located on different disk drives. This is because loss of an online redo log file is a single point of failure requiring incomplete recovery.
If Oracle cannot write to one of the members of the online redo log group, it returns an error message in the alert.log file, but the database continues to operate normally unaffected.
Case (a) : Loss of 'Inactive' Online Redo log group
check the V$log view-
Ensure that the status of the lost redo log group is in fact 'INACTIVE'
check the v$logfile view -
Find out the name of the redo log file corresponding to the group#.
Drop the damaged Redo Log group.
SVRMGR> ALTER DATABASE DROP LOGFILE GROUP#;
SVRMGR> ALTER DATABASE ADD LOGFILE GROUP# '/ekddw3/ORACLE/beta/redo01.log' size 2m;
SVRMGR> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Case (b) : Loss of 'CURRENT' Online Redo Log File
If the LGWR while writing to the online redo log files in its circular manner comes finally tries to write to a redo log file which has been lost (i.e the current redo log file is lost), the instance will terminate with errors like
ORA-03113: end-of-file on communication channel
Or
ORA-01041: internal error. hostdef extension doesn't exist
If we check the alert.log file we would most likely see the following lines towards the end of the file :
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/sp105db04/ORACLE/rman/redo01.log'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
SVRMGR> SHUTDOWN ABORT
SVRMGR> STARTUP MOUNT
Query the v$log view to determine if the lost redo log group is the current one.
Restore all the datafiles and use the CURRENT control file.
Note the log sequence number of the current redo log group.
Recovery is to be cancelled one log file sequence# before the CURRENT log group.
SVRMGR> RECOVER DATABASE UNTIL CANCEL;
Apply archived redo log files
At the appropriate log sequence# enter CANCEL
Eg.
ORA-00280: change 292002 for thread 1 is in sequence #17
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SVRMGR> ALTER DATABASE OPEN RESETLOGS;
Check the database and try and determine the extent of the loss of data
(This would be the data in the current redo log group which was not archived).
SVRMGR> SHUTDOWN NORMAL
Imp : Take a Full Offline Backup of the System.
Case(c) : Loss Of All The Redo Log Files
We should restore the backup of all the datafiles, not the redo log files. We use the current control control file. We use the 'Recover Database Until Cancel' option, cancelling when prompted for the archived redo log pertaining to the missing online redo log file which was lost.