Skip Headers
Oracle® Database Backup and Recovery Advanced User's Guide
10g Release 2 (10.2)

Part Number B14191-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Performing Complete User-Managed Media Recovery

When you perform complete recovery, you recover the backups to the current SCN. You can either recover the whole database at once or recover individual tablespaces or datafiles. Because you do not have to open the database with the RESETLOGS option after complete recovery as you do after incomplete recovery, you have the option of recovering some datafiles at one time and the remaining datafiles later.

This section describes the steps necessary to complete media recovery operations, and includes the following topics:

Performing Closed Database Recovery

This section describes steps to perform complete recovery while the database is not open. You can recover either all damaged datafiles in one operation, or perform individual recovery of each damaged datafile in separate operations.

Perform the media recovery in the following stages:

  1. Prepare for closed database recovery as described in "Preparing for Closed Database Recovery".

  2. Restore the necessary files as described in "Restoring Backups of the Damaged or Missing Files".

  3. Recover the restored datafiles as described in "Recovering the Database".

Preparing for Closed Database Recovery

In this stage, you shut down the instance and inspect the media device that is causing the problem.

To prepare for closed database recovery:

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

    SHUTDOWN IMMEDIATE
    
    
  2. 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.

Restoring Backups of the Damaged or Missing Files

In this stage, you restore all necessary backups.

To restore the necessary files:

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

  2. 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.

  3. 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.

    If . . . Then . . .
    The hardware problem is repaired and you can restore the datafiles to their default locations Restore the datafiles to their default locations and begin media recovery.
    The hardware problem persists and you cannot restore datafiles to their original locations Restore the datafiles to an alternative storage device. Indicate the new location of these files in the control file with ALTER DATABASE RENAME FILE. Use the operation described in "Renaming and Relocating Datafiles" in the Oracle Database Administrator's Guide, as necessary.

Recovering the Database

In the final stage, you recover the datafiles that you have restored.

To recover the restored datafiles:

  1. 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
    
    
  2. 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;
    
    
  3. 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
    
    
  4. 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 want to . . . Then . . .
    Recover all damaged files in one step Execute RECOVER DATABASE
    Recover an individual tablespace Execute RECOVER TABLESPACE
    Recover an individual damaged datafile Execute RECOVER DATAFILE
    Prevent parallelization of recovery on multiple-CPU systems Refer to "Controlling Parallel Media Recovery"

  5. 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.

  6. 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.

  7. After recovery terminates, open the database for use:

    ALTER DATABASE OPEN;
    

    See Also:

    "About User-Managed Media Recovery" for more information about applying redo log files

Performing Datafile Recovery in an Open Database

It is possible for a media failure to occur while the database remains open, leaving the undamaged datafiles online and available for use. Damaged datafiles—but not the tablespaces that contain them—are automatically taken offlineif the database writer is unable to write to them. Queries that cannot read damaged files return errors, but the datafiles are not taken offline because of the failed queries. For example, you may run a query and see output such as:

ERROR at line 1:
ORA-01116: error in opening database file 3
ORA-01110: data file 11: '/oracle/oradata/trgt/cwmlite02.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

The procedure in this section cannot be used to perform complete media recovery on the datafiles of the SYSTEM tablespace while the database is open. If the media failure damages datafiles of the SYSTEM tablespace, then the database automatically shuts down.

Perform media recovery in these stages:

  1. Prepare the database for recovery by making sure it is open and taking the tablespaces requiring recovery offline, as described in "Preparing for Open Database Recovery".

  2. Restore the necessary files in the affected tablespaces as described in "Restoring Backups of the Inaccessible Datafiles".

  3. Recover the affected tablespaces as described in "Recovering Offline Tablespaces in an Open Database".

    See Also:

Preparing for Open Database Recovery

In this stage, you take affected tablespaces offline and inspect the media device that is causing the problem.

To prepare for datafile recovery when the database is open:

  1. 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;
    
    
  2. 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.

Restoring Backups of the Inaccessible Datafiles

In this stage, you restore all necessary backups in the offline tablespaces.

To restore datafiles in an open database:

  1. 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.

    Note:

    In some circumstances, if you do not have a backup of a specific datafile, you can use ALTER DATABASE CREATE DATAFILE to create an empty replacement file that is recoverable.
  2. 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';
    

    See Also:

    Oracle Database SQL Reference for more information about ALTER DATABASE RENAME FILE

Recovering Offline Tablespaces in an Open Database

In the final stage, you recover the datafiles in the offline tablespaces.

To recover offline tablespaces in an open database:

  1. 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;
    
    
  2. 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.

  3. 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;
    

    See Also:

    Oracle Database Administrator's Guide for more information about creating datafiles