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