V$DATAFILE status shows RECOVER after creation of controlfile

Anand Reddy pinged me today with a basic question.  He had received cold backup of a database from Production support team.  After creation of control file, he checked the status of datafiles:

select distinct status from v$datafile;

RECOVER

SYSTEM

This is expected behavior.  I googled for this and found this article on Saibabu's Oracle blog.

To get the true status of datafile, you need to query v$datafile_header.

select status from v$datafile_header;

Here's some more information from Official Oracle manuals:

To determine whether datafiles require media recovery:

1. Start SQL*Plus and connect to the target database instance. For example, issue the following commands to connect to trgt:

   % sqlplus  SQL> CONNECT SYS/password@trgt AS SYSDBA 

2. Determine the status of the database by executing the following SQL query:

   SELECT STATUS FROM V$INSTANCE; 

   If the status is OPEN, then the database is open. Nevertheless, some datafiles may require media recovery.

3. Query V$DATAFILE_HEADER to determine the status of your datafiles. Run the following SQL statements to check the datafile headers:

   COL FILE# FORMAT 999 COL STATUS FORMAT A7 COL ERROR FORMAT A10 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); 

   Each row returned represents a datafile that either requires media recovery or has an error requiring a restore. Check the RECOVER and ERROR columns. RECOVER indicates whether a file needs media recovery, and ERROR indicates whether there was an error reading and validating the datafile header.

   If ERROR is not NULL, then the datafile header cannot be read and validated. Check for a temporary hardware or operating system problem causing the error. If there is no such problem, you must restore the file or switch to a copy.

   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).

   Note:

   Because V$DATAFILE_HEADER only reads the header block of each datafile, it does not detect all problems that require the datafile to be restored. For example, this view cannot tell whether a datafile contains corrupt data blocks.

4. Optionally, query V$RECOVER_FILE to list datafiles requiring recovery by datafile number with their status and error information. For example, execute the following query:

   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.

   To find datafile and tablespace names, you can also perform useful joins using the datafile number and the V$DATAFILE and V$TABLESPACE views. For example:

   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.