The Oracle (tm) Users' Co-Operative FAQ

How Do I Recover From Loss of a Active Redo Log Group?

Author's name: Allan W. Tham

Author's Email:

Date written: Feb 27 2002

Oracle version(s):

This morning I found out that one of my redo log file went missing. I don't have multiplexing. I loss the only copy that I have. The database is halting now. What shall I do?

Back to index of questions

Work for Archive Log Mode ON

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 status of the invalid group
2. Now that you are confirmed that the lost redo file is an ACTIVE redo log, shutdown the database - shutdown immediate
3. Restore the 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
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