This page contains materials that were copied from Oracle's on-line documents.

If you do not use RMAN, then you can restore backups with operating system utilities and then run the SQL*Plus RECOVER command to recover the database. When using SQL*Plus to perform media recovery, the easiest strategy is to perform automatic recovery. Automatic recovery initiates recovery without manually prompting SQL*Plus to apply each individual archived log.

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. The following steps restore backup datafiles to their default location:

  1. Determine which datafiles to recover by using the dynamic performance view V$RECOVER_FILE to determine which files to restore in preparation for media recovery. This view lists all files that need to be recovered, and explains why they need to be recovered. Query V$DATAFILE and V$TABLESPACE to obtain filenames and tablespace names for datafiles requiring recovery. 
COL df# FORMAT 999 
COL df_name FORMAT a20 
COL tbsp_name FORMAT a10 
COL status FORMAT a7 
COL error FORMAT a10 
SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name, 
d.STATUS, r.ERROR, r.CHANGE#, r.TIME 
FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t 
WHERE t.TS# = d.TS# AND d.FILE# = r.FILE# 
/
  1. If the database is open, then take the tablespaces containing the inaccessible datafiles offline: 
    ALTER TABLESPACE users OFFLINE IMMEDIATE;
  2. Copy backups of the damaged datafiles to their default location using operating system commands. 

  3. Assuming that you have all necessary archived redo logs, you can recover the datafile with the following SQL*Plus command:

    RECOVER AUTOMATIC DATAFILE 'name_of_datafile';
  4. Recover the affected tablespace: 
    RECOVER TABLESPACE users
  5. Bring the recovered tablespace online:
ALTER TABLESPACE users ONLINE;

Back to CS643 schedule