Identifying Datafiles Requiring Media Recovery

1)Querying the V$DATAFILE_HEADER view.

COL FILE# FORMAT 99
COL STATUS FORMAT A7
COL ERROR FORMAT A15
COL TABLESPACE_NAME FORMAT A10
COL NAME FORMAT A30
SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER
WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

FILE# STATUS ERROR REC TABLESPACE NAME
----- ------- --------------- --- ---------- ------------------------------
6 OFFLINE YES TEST_RC /oradata2/3.dbf

The column RECOVER indicates whether it is needed media recovery or not.

If ERROR is not NULL, then the datafile header cannot be read and validated.

If the ERROR column is NULL and the RECOVER column is YES, then the file requires media recovery (and may also require a restore from backup).

2)Querying the V$RECOVER_FILE view.

SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME
FROM V$RECOVER_FILE;
FILE# ERROR ONLINE_ CHANGE# TIME
----- ---------- ------- ---------- ---------
15 OFFLINE 103059069 15-APR-08


3)To know datafile and Tablespace specifically,

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 9999999999
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#;

DF# DF_NAME TBSP_NA STATUS ERROR CHANGE# TIME
---- ----------------------------------- ------- ------- ---------- ----------- ---------
15 /oradata1/streams/tbs/streams_tbs.d STREAMS RECOVER 103059069 15-APR-08
bf _TBS