Performing Complete User-Managed Media Recovery

If the database is open, then shut it down. For example:

SHUTDOWN IMMEDIATE

If you are recovering from a media error, then correct it if possible. If the hardware problem that caused the media failure was temporary, and if the data was undamaged (for example, a disk or controller power failure), then no media recovery is required: simply start the database and resume normal operations. If you cannot repair the problem, then proceed to the next stage.

Determine which datafiles to recover by using the techniques described in "Determining Which Datafiles Require Recovery".

If the files are permanently damaged, then identify the most recent backups for the damaged files. Restore only the datafiles damaged by the media failure: do not restore any undamaged datafiles or any online redo log files.

For example, if ORACLE_HOME/oradata/trgt/users01.dbf is the only damaged file, then you may determine that /backup/users01_10_24_02.dbf is the most recent backup of this file. If you do not have a backup of a specific datafile, then you may be able to create an empty replacement file that can be recovered.

Use an operating system utility to restore the files to their default location or to a new location. Restore the necessary files as described in "Restoring Datafiles and Archived Redo Logs". For example, a UNIX user restoring users01.dbf to its default location might enter:

% cp /backup/users01_10_24_02.dbf $ORACLE_HOME/oradata/trgt/users01.dbf

Use the following guidelines when determining where to restore datafile backups.

Connect to the database with administrator privileges, then start a new instance and mount, but do not open, the database. For example, enter:

STARTUP MOUNT

Obtain the datafile names and statuses of all datafiles by checking the list of datafiles that normally accompanies the current control file or querying the V$DATAFILE view. For example, enter:

SELECT NAME,STATUS FROM V$DATAFILE;

Ensure that all datafiles of the database are online. All datafiles of the database requiring recovery must be online unless an offline tablespace was taken offline normally or is part of a read-only tablespace. For example, to guarantee that a datafile named /oracle/dbs/tbs_10.f is online, enter the following:

ALTER DATABASE DATAFILE '/oracle/dbs/tbs_10.f' ONLINE;

If a specified datafile is already online, then the database ignores the statement. If you prefer, create a script to bring all datafiles online at once as in the following:

SPOOL onlineall.sql
SELECT 'ALTER DATABASE DATAFILE '''||name||''' ONLINE;' FROM V$DATAFILE;
SPOOL OFF

SQL> @onlineall

Issue the statement to recover the database, tablespace, or datafile. For example, enter one of the following RECOVER command:

RECOVER DATABASE   # recovers whole database
RECOVER TABLESPACE users   # recovers specific tablespace
RECOVER DATAFILE '?/oradata/trgt/users01.dbf';   # recovers specific datafile

Follow these guidelines when deciding which statement to execute:

If you choose not to automate the application of archived logs, then you must accept or reject each prompted log. If you automate recovery, then the database applies the logs automatically. Recovery continues until all required archived and online redo logs have been applied to the restored datafiles.

The database notifies you when media recovery is complete:

Media recovery complete.

If no archived redo log files are required for complete media recovery, then the database applies all necessary online redo log files and terminates recovery.

After recovery terminates, open the database for use:

ALTER DATABASE OPEN;

If the database is open when you discover that recovery is required, take all tablespaces containing damaged datafiles offline. For example, if tablespace users and tools contain damaged datafiles, enter:

ALTER TABLESPACE users OFFLINE TEMPORARY;
ALTER TABLESPACE tools OFFLINE TEMPORARY;

Correct the hardware problem that caused the media failure. If the hardware problem cannot be repaired quickly, proceed with database recovery by restoring damaged files to an alternative storage device.

If files are permanently damaged, then restore the most recent backup files of only the datafiles damaged by the media failure. Do not restore undamaged datafiles, online redo logs, or control files. If the hardware problem is fixed and the datafiles can be restored to their original locations, then do so. Otherwise, restore the datafiles to an alternative storage device.

If you restored one or more damaged datafiles to alternative locations, update the control file of the database to reflect the new datafile names. For example, to change the filename of the datafile in tablespace users you might enter:

ALTER DATABASE RENAME FILE '?/oradata/trgt/users01.dbf' TO
                           '/disk2/users01.dbf';

Connect to the database with administrator privileges, and start offline tablespace recovery of all damaged datafiles in one or more offline tablespaces using one step. For example, recover users and tools:

RECOVER TABLESPACE users, tools;

The database begins the roll forward phase of media recovery by applying the necessary redo logs (archived and online) to reconstruct the restored datafiles. Unless the applying of files is automated with RECOVER AUTOMATIC or SET AUTORECOVERY ON, the database prompts for each required redo log file.

Recovery continues until all required archived logs have been applied to the datafiles. The online redo logs are then automatically applied to the restored datafiles to complete media recovery. If no archived redo logs are required for complete media recovery, then the database does not prompt for any. Instead, all necessary online redo logs are applied, and media recovery is complete.

When the damaged tablespaces are recovered up to the moment that media failure occurred, bring the offline tablespaces online. For example, to bring tablespaces users and tools online, issue the following statements:

ALTER TABLESPACE users ONLINE;
ALTER TABLESPACE tools ONLINE;