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

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.

See Also:

Oracle Database Reference for information about the V$ views