Oracle: recovery optionsΒΆ

Title:Oracle: recovery options
Author:Douglas O’Leary <dkoleary@olearycomputers.com>
Description:Oracle: recovery options
Date created:08/03/2010
Date updated:08/03/2010
Disclaimer:Standard: Use the information that follows at your own risk. If you screw up a system, don’t blame it on me...
  • Problem: Controlfile loss
    • Symptom: DB crash; ORA-00205 in sqlplus on startup; ORA-00202 and ORA-27037 in alert log.
    • Resolution: Copy multiplexed control file; rman restore/recover. controlfile recovery can be either really easy or really difficult depending on the amount of prework you’ve done.
  • Problem: Loss of online redo log
    • Symptom: If all members of the current log group go away, the database will crash to preserve data (need confirmation). Otherwise, ORA-00313, ORA-00312, and ORA-27037 errors in the alert log
    • Resolution: alter system switch logfile until affected group is not current/active. alter database clear logfile group [#] to recreate the missing/corrupted member.
  • Problem: Datafile loss
    • Symptom: DB crash if critical datafile (system or undo tablespaces); otherwise, ORA-01116, ORA-01110, and ORA-27041 errors in sqlplus and ORA-1116 in alert log.
    • Resolution: restore/recover missing datafile
  • Problem: Temp space loss
    • Symptom: Need exact errrors; sorting errors, probably.
    • Resolution: Add a new temp datafile; offline the missing/corrupted one, drop the missing/corrupted one.
  • Problem: Loss of index tablespace
    • Symptom: Errors on inserts/deletes
    • Resolution: Restore/recover OR ID the indecies using the damaged tablespace, offline/drop the tablespace, recreate the tablepace, and recreate the indecies.
  • Problem: Loss of read-only tablespace
    • Symptom: Standard query/alert log errros
    • Resolution: Restore/recover. Issues come in if TS was put read/write after the read-only backup and before the problems. You’ll need all recovery logs since table initially put into read-only mode to sync up SCNs. Ensure you run backups of the tablespace after every mode switch
  • Problem: Dropped table
    • Symptom: Screaming/panicing, gnashing of teeth. Not an oracle error, so nothing in alert log
    • Resolution: flashback drop, flashback database, incomplete/point in time recovery, tablespace point in time recovery
  • Problem: Truncated table
    • Symptom: Screaming/panicing, gnashing of teeth. ResolutionNot an oracle error, so nothing in alert log
    • Resolution: Flashback database, tablespace point in time recovery, database point in time recovery
  • Problem: Incorrectly added/deleted/modified data.
    • Symptomp: Screaming/panicing, gnashing of teeth. Not an oracle error, so nothing in alert log
    • Resolution: Flashback query, flashback versions query, flashback database, tablespace point in time recovery, database point in time recovery