Restoring Datafiles and Archived Redo Logs

Restoring Datafiles with Operating System Utilities

If a media failure permanently damages one or more datafiles of a database, then you must restore backups of these datafiles before you can recover the damaged files. If you cannot restore a damaged datafile to its original location (for example, you must replace a disk, so you restore the files to an alternate disk), then you must indicate the new locations of these files to the control file.

If you are restoring a database file on a raw disk or partition, then the procedure is basically the same as when restoring to a file on a file system. However, be aware of the naming conventions for files on raw devices (which differ depending on the operating system), and use an operating system utility that supports raw devices.

To restore backup datafiles to their default location:

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

2. If the database is open, then take the tablespaces containing the inaccessible datafiles offline. For example, enter:

   ALTER TABLESPACE users OFFLINE IMMEDIATE;
   
   

3. Copy backups of the damaged datafiles to their default location using operating system commands. For example, to restore users01.dbf you might issue:

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

4. Recover the affected tablespace. For example, enter:

   RECOVER TABLESPACE users
   
   

5. Bring the recovered tablespace online. For example, enter:

   ALTER TABLESPACE users ONLINE;
   

Restoring Archived Redo Logs with Operating System Utilities

All archived redo logs generated between the time a restored backup was created and the target recovery time are required for the pending recovery. The archived logs will eventually need to be on disk so that they are available to the database.

To restore necessary archived redo logs:

1. To determine which archived redo log files are needed, query V$ARCHIVED_LOG and V$RECOVERY_LOG. V$ARCHIVED_LOG lists filenames for all archived logs. V$RECOVERY_LOG lists only the archived redo logs that the database needs to perform media recovery. It also includes the probable names of the files, using LOG_ARCHIVE_FORMAT.

   
   

   Note: V$RECOVERY_LOG is only populated when media recovery is required for a datafile. Hence, this view is not useful in the case of a planned recovery, such as recovery from a user error. If a datafile requires recovery, but no backup of the datafile exists, then you need all redo generated starting from the time when the datafile was added to the database.

   
   

2. If space is available, then restore the required archived redo log files to the location specified by LOG_ARCHIVE_DEST_1. The database locates the correct log automatically when required during media recovery. For example, enter:

   % cp /disk2/arch/* $ORACLE_HOME/oradata/trgt/arch
   
   

3. If sufficient space is not available at the location indicated by the archiving destination initialization parameter, restore some or all of the required archived redo log files to an alternate location. Specify the location before or during media recovery using the LOGSOURCE parameter of the SET statement in SQL*Plus or the RECOVER ... FROM parameter of the ALTER DATABASE statement in SQL. For example, enter:

   SET LOGSOURCE /tmp   # set location using SET statement
     DATABASE RECOVER FROM '/tmp';  # set location in RECOVER statement
   
   

4. After an archived log is applied, and after making sure that a copy of each archived log group still exists in offline storage, delete the restored copy of the archived redo log file to free disk space. For example:

   % rm /tmp/*.dbf
   

   
   

   See Also: Oracle Database Reference for more information about the data dictionary views, and "About User-Managed Media Recovery" for an overview of log application during media recovery