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