The Oracle (tm) Users' Co-Operative FAQ

How Do I Recover From Loss of an unarchived Redo Log Group?

Author's name: Allan W. Tham

Author's Email:

Date written: Feb 27 2002

Oracle version(s):

How do I recover an unarchived redo log group?

Back to index of questions

Work for Archive Log Mode ON

Media Recovery is needed

1. To investigate what status is the missing redo log is -
    a. select * from v$logfile shows which group is INVALID
    b. select * from v$log shows the archived status of the invalid group
2. Now that you are confirmed that the lost redo file is an unarchived redo log (archived=NO), shutdown the database - shutdown immediate
3. Restore all datafiles from backup
4. Mount the database - Startup mount
5. If the media is corrupted where the default filesystem cannot be written, move the file to other location -
    a. In OS, find a directory to host the new file and touch the file (same name)
    b. alter database rename file '/oldpath/filename.log' to '/newpath/filename.log'
    If the original filesystem is good, skip this step
6. Do a cancel based incomplete recovery -
    a. recover database until cancel
    b. Press Enter until you see ora-00308 and ora-27037
    c. Rerun recover database until cancel but type CANCEL at the prompt this time
7. Open the database and reset the logs - alter database open resetlogs
    (Note after opening using resetlogs will create online log files)
8. Do a user managed backups of the entire database

(Note: All updates from the point of the incomplete recovery to the present need to be re-executed!)

Further reading: Oracle9i User-Managed Backup and Recovery Guide

Back to top

Back to index of questions