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
