Determining Which Datafiles Require Recovery

You can use 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.

1. If you are planning to perf orm complete recovery rather than point-in-time recovery, you can recover only those datafiles which require recovery, rather than the whole database. (Note that for point-in-time recovery, you must restore and recover all datafiles, unless you perform tablespace point-in-time recovery as described inChapter 20, "Performing User-Managed TSPITR". You can also use Flashback Database as described in "User-Managed Flashback Features of Oracle", but this affects all datafiles and returns the entire database to a past time.)

   You can query V$RECOVER_FILE to list datafiles requiring recovery by datafile number with their status and error information.

   SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME 
          FROM V$RECOVER_FILE;
   
   

   
   

   Note: You cannot use V$RECOVER_FILE with a control file restored from backup or a control file that was re-created after the time of the media failure affecting the datafiles. A restored or re-created control file does not contain the information needed to update V$RECOVER_FILE accurately.

   
   

   You can also perform useful joins using the datafile number and the V$DATAFILE and V$TABLESPACE views, to get the datafile and tablespace names. Use the following SQL*Plus commands to format the output of the query:

   COL DF# FORMAT 999
   COL DF_NAME FORMAT A35
   COL TBSP_NAME FORMAT A7
   COL STATUS FORMAT A7
   COL ERROR FORMAT A10
   COL CHANGE# FORMAT 99999999
   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#
   ;
   
   

   The ERROR column identifies the problem for each file requiring recovery.