Oracle: Finding Oracle DataBase files that need to be recovered

Run query: SELECT * FROM V$RECOVER_FILE; 

If all files are in proper shape, that is no recovery is needed then it results in below message:

SQL> SELECT * FROM V$RECOVER_FILE;

no rows selected

SQL>

If there are any files that found to be in incorrect status, that is which needs the recovery:

SQL> SELECT * FROM V$RECOVER_FILE;

FILE# ONLINE

———- ——-

ERROR CHANGE#

—————————————————————– ———-

TIME

———

1 ONLINE

339928152

20-MAR-14

3 ONLINE

339920405

20-MAR-14

FILE# ONLINE

———- ——-

ERROR CHANGE#

—————————————————————– ———-

TIME

———

5 ONLINE

339928152

20-MAR-14

3 rows selected.

SQL>

 

To get the list along with filenames showing up as Recovery needed status :

SQL> 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
——————————
NAME
——————————————————————————–
         1 ONLINE
                                                                  YES
SYSTEM
/opt/dbfiles/oradata/ins3/system01ins03.dbf

     FILE# STATUS
———- ——-
ERROR                                                             REC
—————————————————————– —
TABLESPACE_NAME
——————————
NAME
——————————————————————————–
         3 ONLINE
                                                                  YES
TEMP
/opt/dbfiles/oradata/ins3/temp01ins03.dbf

     FILE# STATUS
———- ——-
ERROR                                                             REC
—————————————————————– —
TABLESPACE_NAME
——————————
NAME
——————————————————————————–
         5 ONLINE
                                                                  YES
GAPP_IX
/opt/dbfiles/oradata/ins3/goapp_01.dbf

3 rows selected.

SQL>

 

To get the list along with file names, run below query:

SQL> 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_NAME                      STATUS
—————————— ——-
ERROR                                                                CHANGE#
—————————————————————– ———-
TIME
———
         1
/opt/dbfiles/oradata/ins3/system01ins03.dbf
SYSTEM                         SYSTEM

&
#160;      DF#
———-
DF_NAME
——————————————————————————–
TBSP_NAME                      STATUS
—————————— ——-
ERROR                                                                CHANGE#
—————————————————————– ———-
TIME
———
                                                                   339928152
20-MAR-14

       DF#
———-
DF_NAME
——————————————————————————–
TBSP_NAME                      STATUS
—————————— ——-
ERROR                                                                CHANGE#
—————————————————————– ———-
TIME
———
         3
/opt/dbfiles/oradata/ins3/temp01ins03.dbf
TEMP                           ONLINE

       DF#
———-
DF_NAME
——————————————————————————–
TBSP_NAME                      STATUS
—————————— ——-
ERROR                                                                CHANGE#
—————————————————————– ———-
TIME
———
                                                                   339920405
20-MAR-14

       DF#
———-
DF_NAME
——————————————————————————–
TBSP_NAME                      STATUS
—————————— ——-
ERROR                                                                CHANGE#
—————————————————————– ———-
TIME
———
         5
/opt/dbfiles/oradata/ins3/goapp_01.dbf
GAPP_IX                        ONLINE

       DF#
———-
DF_NAME
——————————————————————————–
TBSP_NAME                      STATUS
—————————— ——-
ERROR                                                                CHANGE#
—————————————————————– ———-
TIME
———
                                                                   339928152
20-MAR-14

3 rows selected.

SQL>