• Phase I - Steps for Diagnosing a Problem
• Phase II - Restore Appropriate Files
• Phase III - Recover Database
• Phase IV - Back-Up Database

Failure / Recovery Scenarios

• Loss of "INACTIVE" Online Redo Log Group
• Loss of "Current" Online Redo Log Group
• Loss Control Files
• Loss of Media
• Loss of an Online Rollback Segment Datafile
• Loss of System Tablespace Datafile
• Loss of Non-System, Non-Rollback Segment Datafile
• Loss of a Datafile and Missing Archive Log File
• Loss of a Control File and Read-Only Tablespace
• Loss of Non-Essential Datafile when Database is Down
• Corrupted Data Block
• Recover from Users Errors
• Failure During Hot Backup
• Missing Datafile
• Recover a Lost Datafile with No Backup
• Missing Mirrored Online Redo Log Files

Implementing a Restore and Recovery Strategy

Phase I - Steps for Diagnosing a Problem

• Determine if the database instance is available and the database open.
• Attempt to start the instance and open the database.
• Shutdown the instance if problems occur whilst starting it or opening the database.
• Check the trace files for possible problems.
• Check the alert_(SID).log file for possible problems.
• Determine the appropriate recovery method by asking the following questions for each scenario:

• Which recover options are available?
• Complete recovery
• Closed database recovery
• Open-database, Offline Tablespace recovery
• Open-database, Offline Tablespace, Individual Data File recovery
• Incomplete Media Recovery
• Cancel-based recovery
• Time based recovery
• Change-based recovery
• Which recovery options are appropriate for this particular problem?
• Are disaster recovery procedures in place?
• What needs to be restored to proceed with recovery?

Phase II - Restore Appropriate Files

Phase III - Recover Database

Phase IV - Back-Up Database

Failure / Recovery Scenarios

Loss of "INACTIVE" Online Redo Log Group

• Shutdown the instance.
• Mount the instance.
• If the lost log file was archived, check the v$log view.
• Issue the alter database clear logfile command.
• If there is an off-line datafile (check v$datafile) that requires the cleared unarchived log to bring it online, the keywords unrecoverable datafile are required. The datafile and its entire tablespace must be dropped from the database because the redo necessary to bring it online is being cleared, and there is no copy of it.
• Issue the alter database backup controlfile command.
• Delete the operating system redo log files by hosing to the operating system and deleting the redo log files for the damaged redo log group.
• Drop the damaged redo log file group.
• Add a redo log group, using the information noted on the Database Configuration Checklist.
• Add a redo log file member using the information of the Database Configuration Checklist.
• Determine if a full off-line backup is required and perform one if necessary.
• Ensure the instance is started and the database is open.

Loss of "Current" Online Redo Log Group

• Start the instance if necessary.
• Attempt to alter the database and drop the redo log group. You will receive an ORA-00350 error stating that the redo log group needs to be archived.
• Shutdown the instance.
• Review the trace files.
• Review the alert.log file.
• Copy the datafiles and redo log files from the backup directory into their respective paths.
• Mount the instance.
• Query the v$log view to determine the sequence# of the current log group.
• Run archive log list.
• Recover the database until cancel, cancelling the recovery operation one log file sequence# before the current log group.
• Once recovery is complete open the database with the resetlogs command.
• Determine if a full off-line backup is required and perform one if necessary.
• Delete the trace files in the $HOME/trace directory.
• View the alert.log file for the recovery that was applied.
• Remove the archive log files from the archive directory.
• Remove the alert.log and trace files from the trace directory.
• Ensure the instance is started and the database is open.

Loss Control Files

• Start the instance if necessary and perform an  alter database backup controlfile to trace;
• Shutdown the instance if the start failed.
• Edit the init.ora file so that only one control file is listed in the control files parameter.
• Start the instance, if it's working, then close it, copy the binary control file that is working in another place and add it to the init.ora file.
• Using the trace file that was created using the alter database backup controlfile to trace command, connect to Server Manager and start the instance in nomount mode.
• Run the trace file script to recreate the control file.
• Shutdown the instance and mirror the control file.
• Determine if a full off-line backup is required and perform one if necessary.
• Ensure the instance is started and the database is open.

Loss of Media

• Determine which files to recover using v$datafile and v$recover_file.
• Determine what device has been damaged (the directory will be missing).
• Restore missing files from the backup directory to another available device.
• Rename the files so the changes are recorded in the control file.
• Use the alter database datafile offline command to take the datafiles off-line so the database can be opened.
• Once the database is open use the command alter tablespace off-line immediate so you can perform an open database off-line tablespace individual recovery.
• If one of the lost datafiles belongs to a rollback segment tablespace, comment out the rollback_segments parameter in the init.ora file.
• Open the database.
• Issue the command recover datafile to restore each individual datafile.
• Once the recovery is complete on the damaged files, bring the tablespaces online.
• Query the v$datafile view to check the status of the files.
• Query the v$recover_file view to check the status of damaged file.
• From the $HOME directory, create the subdirectory that was removed.
• Restore the datafiles to their original locations.
• Take the tablespaces off-line and make a physical copy of the datafile(s) to their original location.
• User the alter database rename datafile command to record the structural change in the control file.
• Bring the tablespace online.
• Determine if a full off-line backup is required and perform one if necessary.
• Ensure the instance is started and the database is open.

Loss of an Online Rollback Segment Datafile

• Start the instance if necessary. Reviewing trace files for indications of a corrupt rollback segment may also help.
• Reference bulletins 1013221.6 and 1010700.6 for resolving rollback segment datafile recovery.
• Restore the lost file from backup.
• Perform database recovery.
• Determine if a full offline backup is required and perform one if necessary.
• Ensure the instance is started and the database is open.

Loss of System Tablespace Datafile

• Start the instance if necessary.
• Shutdown the instance if the start failed.
• Restore the system tablespace file from the most recent backup to the correct directory.
• Use the mount command to mount the instance.
• Perform database recovery. recover database;
• Open the database
• Determine if a full off-line backup is required and perform one if necessary.
• Ensure the instance is started and the database open.

Loss of Non-System, Non-Rollback Segment Datafile

• Start the instance if necessary.
• Shutdown the instance if the start failed.
• Restore the datafile to the correct directory.
• Use the mount command to mount the instance.
• Perform database recovery.
• Determine if a full off-line backup is required and perform one if necessary.
• Ensure the instance is started and the database open.

Corrupted Data Block

• Perform a database recovery in accordance with the World Wide Support bulletins 1013621.6 and 1010640.6.
• Determine if a full off-line backup is required and perform one if necessary.
• Ensure the instance is started and the database open.

Recover from Users Errors

• Three recovery scenarios pertain to this failure. The objective is to minimise downtime and data loss.
• The entire database may be restored using a point-in-time recovery which means any transactions that occurred after the time recovered to will be lost.
• Export the individual table from a restore, and import the table into the primary database.
• Restore the table from an export file.
• Determine if a full off-line backup is required and perform one if necessary.
• Ensure the instance is started and the database open.

Failure During Hot Backup

• Use the mount command to mount the instance.
• Query the view v$recover_file.
• Query the view v$backup.
• Determine which file(s) were in backup mode when the database crashed.
• Take the suspect file out of backup mode or perform a recovery by issuing the command alter database datafile '' end backup;
• Open the database
• Query the v$recover_file, v$backup and v$datafile views.
• Determine if a full off-line backup is required and perform one if necessary.
• Ensure the instance is started and the database open.

Missing Datafile

• Perform recovery in accordance with bulletin 1005254.6.
• Determine if a full off-line backup is required and perform one if necessary.
• Ensure the instance is started and the database open.

Loss of a Datafile and Missing Archive Log File

• Restore the datafile to the correct directory.
• Use the mount command to mount the instance.
• Begin database recovery. A missing archived log file will prompt an error.
• Shutdown the instance.
• Determine which archived log file is missing.
• Restore datafiles, except control files from the backup source.
• Perform a cancelled based recovery, cancelling the operation at the appropriate point.
• Open the database using the resetlogs option.
• Determine if a full off-line backup is required and perform one if necessary.
• Ensure the instance is started and the database open.

Recover a Lost Datafile with No Backup

• Use the mount command to mount the instance.
• Query the v$recover_file view
• Query the v$datafile view
• Alter the database and create the new datafile as a new filespec.
• Recover the datafile.
• Open the database.
• Query the v$recover_file view
• Query the v$datafile view.
• Determine if a full off-line backup is required and perform one if necessary.
• Ensure the instance is started and the database open.

Missing Mirrored Online Redo Log Files

• Start the instance if necessary.
• Review the alert.log file and trace files for abnormal conditions.
• Query the v$logfile view.
• Switch the logfiles then query the v$logfile view.
• The problem is that only the mirrored redo log files are corrupted but the database is still available.
• Correct the problem by adding new log files using the naming conventions on the database configuration checklist.
• Determine if a full off-line backup is required and perform one if necessary.
• Ensure the instance is started and the database open.

Loss of a Control File and Read-Only Tablespace

• Start the instance if necessary
• Shutdown the instance if the start failed.
• View the alert.log file.
• Restore the missing datafile from backup.
• The file that is missing was in read only status when you initially created your control file trace.
• Therefore, recover the database using the backup control file.
• Copy the backup control file to a valid control filename. This may require the init.ora file being edited.
• Use the mount command to mount the database.
• Query the v$recover_file view
• Query the v$datafile view. select substr(name, 1, 50) name, status from v$datafile;
• Put datafile online if offline (not sure). alter database datafile '<filename>' online;
• Recover the database using the backup control file option. recover database using backup controlfile;
• Open the database using the resetlogs option.  alter database open resetlogs;
• Put the tablespace into read only mode.
• Determine if a full off-line backup is required and perform one if necessary.
• Ensure the instance is started and the database open.

Loss of Non-Essential Datafile when Database is Down

• Start the instance if necessary.
• Shutdown the instance if the start failed.
• Use the mount command to mount the database.
• Open the database.
• Query the v$recover_file view and note the filename.
• Alter the database to take the file off-line and drop it.
• Open the database
• Drop the appropriate tablespace.
• Create the tablespace using the same file name note in step 5 using the reuse option.
• Determine if a full off-line backup is required and perform one if necessary.
• Ensure the instance is started and the database open.