Fix : RMAN restore fails with RMAN-06023 error

Fix : RMAN RESTORE FAILS WITH RMAN-06023 ALTHOUGH BACKUPS ARE AVAILABLE 

Issue Description:

I am upgrading one of my production database from 10.2.0.4 to 11.2.0.4. With one iteration I have upgraded successfully. When I try one more iteration then I faced below issues. Restore failed with "RMAN-06023" error. But restored the controlfile sucessfully and In the RMAN catalog I can see that there are available database backups.

Error reported:

Starting restore at 29-OCT-14

released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/29/2014 14:53:16
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 44 found to restore
RMAN-06023: no backup or copy of datafile 43 found to restore
....
....
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

RMAN>


Cause:

1) There may be issue with incarnation. When I verified, I found one database ID with current like below:

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PROD  4100727707       ORPHAN  1          12-MAR-08
2       2       PROD  4100727707       ORPHAN  602821     22-FEB-10
3       3       PROD  4100727707       ORPHAN  10224810000 04-AUG-11
4       4       PROD  4100727707       ORPHAN  10273513355 09-AUG-11
5       5       PROD  4100727707       ORPHAN  16112640652 02-MAR-12
6       6       PROD  4100727707       ORPHAN  49639514687 13-FEB-14
7       7       PROD  4100727707       CURRENT 93574835300 28-SEP-14

RMAN>

So, It it best practice do reset incarnation to 1 like below:

RMAN> reset database to incarnation 1;

database reset to incarnation 1
RMAN>

2) Another problem here is that there are some files in the Flash Recovery Area that belong to different incarnation than the available backups CURRENT incarnation. If we start a RESTORE database with a BACKUP controlfile and Flash Recovery Area is defined, RMAN execute and implicit crosscheck and
catalog of all the objects in the Flash Recovery Area.

RMAN will catalog any objects in the Flash Recovery Area that will not be registered in the controlfile and if any of this files belongs to an incarnation different from CURRENT incarnation in the controlfile then changes controlfile CURRENT incarnation to the one found in the file that is being cataloged.

This prevents database from restoring backups that belong to old CURRENT incarnation.
RMAN considers backup availble for being restored if the backup incarnation and CURRENT incarnation in controlfile are the same.

We can check that there are not backups belonging to CURRENT incarnation with command:

RMAN> LIST BACKUP RECOVERABLE;

This command only will show CURRENT incarnation available backups

Fix / Solution:

1) Reset database incarnation as described above.
2) Temporarily disable use of the flash recovery area for the duration of the restore and recovery commands.

Now restart the restore activity freshly. When I followed the above steps, my database has been restored successfully.


Note: Script used for restore:

-- executed again
RUN
{
allocate channel ch1 type disk;
SET NEWNAME FOR DATAFILE 1 TO '/u01/ORADATA/PROD/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/u01/ORADATA/PROD/undotbs01.dbf';
....
SET NEWNAME FOR DATAFILE 44 TO '/u01/ORADATA/PROD/hr02.dbf';
 RESTORE DATABASE;
 SWITCH DATAFILE ALL;
}

Thanks.
Cheers!!!