How do I recover from ... loss of an unarchived online log file

The Oracle (tm) Users' Co-Operative FAQ

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

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
9. DONE

(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