I've lost an archived/online REDO LOG file, can I get my DB back?

The following INIT.ORA/SPFILE parameter can be used if your current redologs are corrupted or blown away. It may also be handy if you do database recovery and one of the archived log files are missing and cannot be restored.
NOTE: Caution is advised when enabling this parameter as you might end-up losing your entire database. Please contact Oracle Support before using it.

_allow_resetlogs_corruption = true

This should allow you to open the database. However, after using this parameter your database will be inconsistent (some committed transactions may be lost or partially applied).
Steps:

  • Do a “SHUTDOWN NORMAL” of the database
  • Set the above parameter
  • Do a “STARTUP MOUNT” and “ALTER DATABASE OPEN RESETLOGS;”
  • If the database asks for recovery, use an UNTIL CANCEL type recovery and apply all available archive and on-line redo logs, then issue CANCEL and reissue the “ALTER DATABASE OPEN RESETLOGS;” command.
  • Wait a couple of minutes for Oracle to sort itself out
  • Do a “SHUTDOWN NORMAL”
  • Remove the above parameter!
  • Do a database “STARTUP” and check your ALERT.LOG file for errors.

Extract the data and rebuild the entire database.

Category: BackupDatabaseRecovery

Tags:

Leave a Reply

Article by: Shadab Mohammad