Open resetlogs checks for the Oracle database

Last updated on February 23rd, 2019 at 05:48 pm

You can restored a database from the backup and applied lots of archive to make it consistent. Now  you would like to make sure open resets logs goes fine.
Here is the open resetlogs checks for the oracle database  from google and metalink

The below statement set the date format to extended form as we would be requiring this many times  in our analysis

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

Check 1:

Objective: Verify that the datafiles are recovered to the intended point in time (PIT) and they are consistent (FUZZY=NO)
Query the current status and PIT (P-oint I-n T-ime upto which the datafiles have been recovered) of datafiles by reading datafile headers directly from the physical datafiles:

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 ;
  • 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.
  • If FUZZY=YES for some datafiles, it means more recovery is required. If no more archived logs are available, identify such datafiles and determine if we can take them offline because we will loose the data in those datafiles. If the datafiles belong to SYSTEM or UNDO tablespace, we can / MUST not bring such datafile offline without proper analysis. Please consult Oracle Support for further actions.
SQL> select file#, substr(name, 1, 50), substr(tablespace_name, 1, 15), undo_opt_current_change# from v$datafile_header where fuzzy='YES' ;

Occasionally, if the tablespace name doesn’t indicate it to be UNDO tablespace, if we see non-zero value in column UNDO_OPT_CURRENT_CHANGE#, it indicates that the datafile contains undo segments.

To bring a datafile offline :

SQL> alter database datafile offline ;

Check 1 can be considered Passed when :
+ Verified that all the datafiles have been recovered upto the intended Point in time.
+ 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 available.

Check 2:

Objective: Verify that the files with status=RECOVER are not OFFLINE unintentionally

SQL> select status, enabled, count(*) from v$datafile group by status, enabled ;
------- ---------- ----------
SYSTEM  DISABLED            1
ONLINE  READ WRITE          1114

If the files are in RECOVER status, verify if they are OFFLINE :

SQL> select file#, substr(name, 1, 50), status, error, recover from v$datafile_header ;

If you want the data for these files to be accessible, then bring them ONLINE :

SQL> alter database datafile ONLINE ;

If a file remains offline at the time of OPEN RESETLOGS, the datafile may not be brought back online again in the same OPENED database.
Check 2 can be considered Passed when:
a) All the intended datafiles are not OFFLINE

Check 3:

Objective: Additional Fuzzy check (Absolute Fuzzy check)

Occasionally, it is possible to see Fuzzy=NO and same checkpoint_change# for all the intended datafiles ; still some of the datafiles might be fuzzy and OPEN RESETLOGS will return error, e.g.

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 ;
--- ------- --------------- --- ------------------ -------------------- ----------
NO  ONLINE                                 5375858580 31-OCT-2011 23:10:14          7

ORA-01194: file 14 needs more recovery to be consistent
ORA-01110: data file 3: '/u01/app/oracle/oradata/xxxx/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 ;

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.

Check 3 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 4 (After successful OPEN RESETLOGS) :

Monitor the alert.log for the time of OPEN RESETLOGS activities. You might see some messages like below during dictionary check:

Creating OFFLINE file ‘MISSING00004’ in the controlfile

Leave a Reply