RMAN-20208: until change is before resetlogs change

Playing with one of my Lab databases regarding components removal, approach that i followed for an easy rollback is to create a restore point (as explained in the previous post). Flashback database is still not available at SE2😞.

But after the first rollback, when database is opened with "resetlogs", then a new incarnation is created at database level so all available archive log and backup become invalid. If you want to restore the database to a previous incarnation you need to set the incarnation level in RMAN for restore or recover old SCN.

Lets see in action what happens when i try to restore the database using a backup from prior incarnation:

DM99@srv1>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Feb 26 17:19:47 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area    3053449808 bytes

Fixed Size                     8901200 bytes
Variable Size                788529152 bytes
Database Buffers            2248146944 bytes
Redo Buffers                   7872512 bytes

RMAN> list restore point all;

using target database control file instead of recovery catalog
SCN              RSP Time  Type       Time      Name
---------------- --------- ---------- --------- ----
2840907                               26-FEB-21 RP_PRE_COMP_REMOVAL

RMAN> restore database until restore point RP_PRE_COMP_REMOVAL
2> ;

Starting restore at 26-FEB-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/26/2021 17:33:17
RMAN-20208: UNTIL CHANGE is before RESETLOGS change

I got error "RMAN-20208: until change is before resetlogs change" as expected, so at this point we need to check the SCN from incarnation commands:

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DM99     3332995293       PARENT  1          28-JAN-21
2       2       DM99     3332995293       CURRENT 2840909    26-FEB-21

to overcome this problem, we need to set the database to old incarnation and after that our restore & recover operation will succeed:

RMAN> reset database to incarnation 1; 

database reset to incarnation 1

RMAN> restore database until restore point RP_PRE_COMP_REMOVAL;

Starting restore at 26-FEB-21
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/DM99/DATAFILE/system.260.1063025065
channel ORA_DISK_1: restoring datafile 00002 to +DATA/DM99/DATAFILE/sysaux.261.1063025075
channel ORA_DISK_1: restoring datafile 00003 to +DATA/DM99/DATAFILE/undotbs1.262.1063025081
channel ORA_DISK_1: restoring datafile 00004 to +DATA/DM99/DATAFILE/users.264.1063025091
channel ORA_DISK_1: reading from backup piece +DATA/DM99/BACKUPSET/2021_02_26/nnndf0_tag20210226t140122_0.272.1065535283
channel ORA_DISK_1: piece handle=+DATA/DM99/BACKUPSET/2021_02_26/nnndf0_tag20210226t140122_0.272.1065535283 tag=TAG20210226T140122
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 26-FEB-21

RMAN> recover database until restore point RP_PRE_COMP_REMOVAL;

Starting recover at 26-FEB-21
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 12841 is already on disk as file +DATA/DM99/ARCHIVELOG/2021_02_26/thread_1_seq_12841.273.1065535309
archived log for thread 1 with sequence 12842 is already on disk as file +DATA/DM99/ARCHIVELOG/2021_02_26/thread_1_seq_12842.276.1065535607
archived log file name=+DATA/DM99/ARCHIVELOG/2021_02_26/thread_1_seq_12841.273.1065535309 thread=1 sequence=12841
archived log file name=+DATA/DM99/ARCHIVELOG/2021_02_26/thread_1_seq_12842.276.1065535607 thread=1 sequence=12842
media recovery complete, elapsed time: 00:00:00
Finished recover at 26-FEB-21

RMAN> alter database open resetlogs;

Statement processed

Then after restore you can check incarnation status again:

RMAN> list incarnation of database;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DM99     3332995293       PARENT  1          28-JAN-21
3       3       DM99     3332995293       CURRENT 2840909    26-FEB-21
2       2       DM99     3332995293       ORPHAN  2840909    26-FEB-21