Consistency check before performing RMAN resetlogs.

The article covers consistency check before performing OPEN RESETLOGS after restoring a database using backup 

Performing the fuzziness check

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

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

FUZ STATUS  ERROR            CHECKPOINT_CHANGE#     CHECKPOINT_TIME        COUNT(*)
--- ------- ---------------  ------------------     --------------------   --------
YES ONLINE                   267865                15-AUG-2021 14:30:25    4
YES ONLINE                   269515                15-AUG-2021 16:59:13    1

2 rows selected.

 

As we can see the files are not consistent with different Checkpoint_change# and checkpoint_time.

Below shows all files are not consistent.

col file# for a10
col name for a70
select file#,name,checkpoint_time, checkpoint_change# , FUZZY,RECOVER,RESETLOGS_TIME,status from v$datafile_header order by 1;  

SQL> select checkpoint_change#,file# from v$datafile_header  where checkpoint_change#=0 order by checkpoint_change#;

SQL> select checkpoint_change#, ERROR, file# from v$datafile_header where ERROR like '%HEADER%';


 FILE# STATUS  ERROR       FUZ
------ ------- ----------  ---
     1 ONLINE              YES
     2 ONLINE              YES
     3 ONLINE              YES
     4 ONLINE              YES
     5 ONLINE              YES

5 rows selected.

Perform the required recovery the make the database files consistent

SQL> recover database using backup controlfile;

ORA-00279: change 5198247 generated at 08/15/2021 14:30:25 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ARC_SEQ_00009_056982475.001
ORA-00280: change 5198247 for thread 1 is in sequence #4

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/onlinelog/O1_OL_2_8HJWQ1_.log

Log applied.
Media recovery complete.

Validate again and perform resetlogs

SQL> select file#,status, error, recover,fuzzy from v$datafile_header where fuzzy='YES';  ---NO fuzzy

no rows selected


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

FUZ STATUS  ERROR            CHECKPOINT_CHANGE#     CHECKPOINT_TIME        COUNT(*)
--- ------- ---------------  ------------------     --------------------   --------
NO  ONLINE                   516973                 15-AUG-2021 16:30:00    5


1 row selected.

SQL>

-- // Good to go for resetlogs

 
SQL> alter database open resetlogs;
 
Database altered.
 
SQL>

Read more

RMAN Questions

RMAN Backup Validate

Cold backup using RMAN 

Oracle Database Block corruption

Loss of "Current" Online Redo Log Group

What happens during instance Recovery ?

ORA-01194: file 1 needs more recovery to be consistent