How to check that Database is consistent after incomplete recovery before OPEN RESETLOGS

For cold/offline backups, no archivelogs/recovery is necessary. You can simply open the database with resetlogs.
However for HOT/ONLINE backups, ALL archivelogs from backup start to backup end must be applied before the database can be opened this is the MINIMUM amount of recovery needed.

To determine which log was current at the time the backup completed, note the COMPLETION time of the database backup take this from the backup log.
If this is an RMAN backup you can also query the RMAN metadata. Ensure that the environment variable NLS_DATE_FORMAT is set before invoking rman so that timestamps as well as date are returned:

For unix:
export NLS_DATE_FORMAT='dd-mon-rr hh24:mi:ss'
rman target /

For windows:
 set nls_date_format='dd-mon-rr hh24:mi:ss'

 SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS' ;

 check 1. Checkpoint Time and Fuzziness

SQL> select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;

FUZZY   STATUS ERROR REC         CHECKPOINT_CHANGE#    CHECKPOINT_TIME     COUNT(*)
-------------------------------------------------------------------------------------
NO      ONLINE                    5311260              31-AUG-2011 23:10:14   6
YES     ONLINE                    5311260              31-AUG-2011 23:10:14   1

a) Verify that the checkpoint_time / checkpoint_change# is in line with your intended UNTIL TIME/SCN. If not, recover the database further if you have more archived logs available.
b) If FUZZY=YES for some datafiles, it means more recovery is required.

Above check can be considered PASSED when :
a) Verified that all the datafiles are at the some checkpoint_time, and this is your intended Point in time.
b) Fuzzy=NO for SYSTEM, UNDO and all intended datafiles. For datafiles with Fuzzy=YES, either recover them further or bring them OFFLINE if no further archived logs are available.

Check 2: Absolute Fuzzy

Occasionally, it is possible to see Fuzzy=NO and same checkpoint_change# for all the intended datafiles but OPEN RESETLOGS still fails

select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;

FUZZY   STATUS ERROR REC         CHECKPOINT_CHANGE#    CHECKPOINT_TIME     COUNT(*)
-------------------------------------------------------------------------------------
NO      ONLINE                    5311260              31-AUG-2011 23:10:14   7

SQL> ALTER DATABASE OPEN RESETLOGS ;
ORA01194:file 4 needs more recovery to be consistent
ORA01110:data file 3: '/u01/app/oracle/oradata/prod111/undotbs02.dbf'

Hence, we should perform additional fuzzy check known as Absolute Fuzzy Check:

SQL> select hxfil file#, substr(hxfnm, 1, 50) name, fhscn checkpoint_change#, fhafs Absolute_Fuzzy_SCN, max(fhafs) over () Min_PIT_SCN from x$kcvfh where fhafs!=0 ;

FILE#                NAME                           CHECKPOINT_CHANGE ABSOLUTE_FUZZY_SCN MIN_PIT_SCN
------------------------------------------------------------------------------------------------------
4    /u01/app/oracle/oradata/prod111/undotbs01.dbf     5311260            5311524            5311524
6    /u01/app/oracle/oradata/prod111/system01.dbf      5311260            5311379            5311524

Note: Column Min_PIT_SCN will return same value even for multiple rows as we have applied ANALYTICAL "MAX() OVER ()" function on it.
Above query indicates that the recovery must be performed at least UNTIL SCN 5311524 to make datafiles consistent and ready to OPEN. Since the checkpoint_change# is smaller than Min_PIT_SCN, the datafiles will ask for more recovery.

Above check can be considered PASSED when:
a) No rows selected from above query (i.e. Min_PIT_SCN is 0 (Zero) for all the datafiles)
b) Min_PIT_SCN is returned less than Checkpoint_Change#

Check 3: Archive Logs Required:

Query the controlfile to find the latest archivelog required for recovery. Lets say the backup completed at 31-AUG-2011 23:20:14:

SQL> V$ARCHIVED_LOG
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';
SQL> SELECT THREAD#, SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG WHERE '31-AUG-11 23:20:14' BETWEEN FIRST_TIME AND NEXT_TIME;

If the above query does not return any rows, it may be that the information has aged out of the controlfile run
the following query against v$log_history.



SQL> V$ LOG_HISTORY view does not have a column NEXT_TIME
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';
SQL> select a.THREAD#, a.SEQUENCE#, a.FIRST_TIME from V$LOG_HISTORY a
where FIRST_TIME =
( SELECT MAX(b.FIRST_TIME) FROM V$LOG_HISTORY b
WHERE b.FIRST_TIME < to_date('31-AUG-11 23:20:14', 'DD-MON-RR HH24:MI:SS')
) ;

The sequence# returned by the above query is the log sequence current at the time the backup ended let say 530 thread 1.

For minimum recovery use: (Sequence# as returned +1 ) :

RMAN> RUN
{
SET UNTIL SEQUENCE 531 THREAD 1;
RECOVER DATABASE;
}


If this is a RAC implementation the use this SQL instead to query the controlfile:

SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG WHERE '31-AUG-11 23:20:14' BETWEEN FIRST_TIME AND NEXT_TIME;

For minimum recovery use the log sequence and thread that has the lowest NEXT_CHANGE# returned by the above query.

Above check  can be considered PASSED when:
All archivelogs from the time of the backup to the end of the backup is available for use during recovery