ORA-01547 warning: RECOVER succeeded but OPEN RESETLOGS would get error below (Doc ID 1684437.1)

While Oracle DB recover getting below error.

RMAN> recover database;

Starting recover at  01-JAN-19

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_DISK_5

using channel ORA_DISK_6

using channel ORA_DISK_7

using channel ORA_DISK_8

starting media recovery

Oracle Error: 

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

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

ORA-01110: data file 1: '/u02/oradb/proddata/system01.dbf'

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 01/01/2019 05:09:54

RMAN-06053: unable to perform media recovery because of missing log

RMAN-06025: no backup of archived log for thread 1 with sequence 91550 and starting SCN of 149247095 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 91549 and starting SCN of 149235668 found to restore

I just move some missing archive log files to destination server.

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1

released channel: ORA_DISK_2

released channel: ORA_DISK_3

released channel: ORA_DISK_4

released channel: ORA_DISK_5

released channel: ORA_DISK_6

released channel: ORA_DISK_7

released channel: ORA_DISK_8

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=20 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=21 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=22 device type=DISK

allocated channel: ORA_DISK_5

channel ORA_DISK_5: SID=23 device type=DISK

allocated channel: ORA_DISK_6

channel ORA_DISK_6: SID=24 device type=DISK

allocated channel: ORA_DISK_7

channel ORA_DISK_7: SID=25 device type=DISK

allocated channel: ORA_DISK_8

channel ORA_DISK_8: SID=26 device type=DISK

validation succeeded for archived log

archived log file name=/u02/oradb/product/11.2.0/dbhome_1/dbs/arch1_91549_940168479.dbf RECID=90596 STAMP=1061762468

validation succeeded for archived log

archived log file name=/u02/oradb/product/11.2.0/dbhome_1/dbs/arch1_91550_940168479.dbf RECID=90597 STAMP=1061762775

Crosschecked 2 objects

RMAN> recover database;

Starting recover at 16-JAN-21

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_DISK_5

using channel ORA_DISK_6

using channel ORA_DISK_7

using channel ORA_DISK_8

starting media recovery

unable to find archived log

archived log thread=1 sequence=91551

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 01/16/2021 05:17:16

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 91551 and starting SCN of 149248093

RMAN> alter database open resetlogs;

database opened

RMAN> 

Refer: Oracle (Doc ID 1684437.1)

SOLUTION

The ORA-01547 before database open indicates that Oracle sees the datafiles as inconsistent.  This is resolved by applying more recovery, to make the datafiles consistent.  

1. Check the current status of the datafiles:

set numwidth 30;

set pagesize 50000;

alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';

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

The goal is to have the above query return 1 row and fuzzy column value as NO.

NOTE:  if checkpoint_change# in query returns 0, this indicates that Oracle cannot read the file header.  This is possibly because the location and name of the datafile within the controlfile is not the one on disk.  

2. Check the archivelog sequence numbers needed to recover the files.  Execute:

select min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;

This query will show the smallest and largest sequence needed by the datafiles.    To make the datafiles consistent, you must apply all archivelog files within the above range of sequence numbers.  This can be one or many archivelog files.  

NOTE:  This query will not be valid in a RAC environment.  In RAC, find the necessary sequences from all threads using the smallest (and largest) checkpoint_change# returned by the query in #1 and execute:

select thread#, first_change#, next_change# from v$archived_log where <smallest/largest checkpoint_change#> between first_change# and next_change#;

once the above is executed with the smallest and largest checkpoint_change#, you have the sequence range needed to be applied from all threads of the RAC database.  See Note 243760.1 'RMAN: RAC Backup and Recovery using RMAN' for more details.  

3. Once further recovery is applied, confirm the datafiles are consistent with query in #1, above, until the goal is accomplished.  

4. Once all datafiles are consistent and fuzzy=NO, the database can be opened and the ORA-01547 should no longer be returned.