recover et X$KCVFH

Recover  
Au lieu de se baser sur  V$datafile_header je travail avec  cette  tables:

 select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence
from X$KCVFH        ;


The FHSTA column of X$KCVFH shows the file-header status of the datafile. It always helps to take a look at this column immediately after the recovery and before OPENing the DB. The distinct FHSTA column values could be 0,1,4,8192,8196 etc. The reason I add etc is because if there are more I dont know of them yet.
Each value has a different meaning.

0 - Recovery complete.
1 - Still needs recovery. Apply more archives
4 - FUZZY state. One or more datafiles are in a FUZZY state. Was the backup good?

I have seen 8192 twice but don't know the significance of this status yet.

Immediately after the recovery and before OPENing the DB, the FHSTA should be all zeroes. This means the DB has been recovered until a consistent state and the DB can now be OPENed
without issues. If you wish to add more archives beyond this point, you may do so.

A status of 1 means the archives applied so far weren't sufficient to bring the DB in a consistent state. More archives need to be applied.
Apply the archives in serial order until you find the status change to 0.

A status of 4 means the DB is in a FUZZY state. This usually comes down to the backup not being good.

If the DB is in a consistent state and is ready to be opened in RESETLOGS mode, your query should return a single row with 0 as its value.
Then the DB can be opened in RESETLOGS without any errors.

A query to know more details about the datafiles and their header statuses is:

SELECT hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE FROM x$kcvfh;

You can find more information in this post:
http://drdatabase.wordpress.com/2010/07/09/how-much-recovery-is-enough-recovery/


base   open

19:52:12 SQL>  select fhsta, count(*) from X$KCVFH group by fhsta;

     FHSTA   COUNT(*)
---------- ----------
      8196          1
         4          6

en  mount :
SQL> select fhsta, count(*) from X$KCVFH group by fhsta;

     FHSTA   COUNT(*)
---------- ----------
         0          8
      8192          1

standby en  open:
SQL> select fhsta, count(*) from X$KCVFH group by fhsta;

     FHSTA   COUNT(*)
---------- ----------
     16400         27
     24592          1