(Picture by OpenClipart-Vectors [CC BY-SA 3.0], via Pixabay)

Like most DBA, sometimes I need to generate AWR reports, nothing very complex so far. But occasionally, I need to cover a period of time which is BEFORE the database I am working on has been overwritten with a duplicate operation … Which means it does not contain relevant AWR snapshots anymore.

First, please consider that I use RMAN catalog for backups, RMAN is correctly configured on databases, I do have backups, and I regularly test recovery procedures.
Now let’s say I have a 12c database called FLORADEV, which was overwritten on March 27th at 19:00 for development purpose. But I need AWR snapshots from March 25th between 01:00 and 02:00. And I can’t restore the database right now because the developer is currently working.

Let’s try to duplicate FLORADEV on an auxiliary test database called FLORATST in order to generate the long-awaited AWR report from FLORATST instead :

# rman
RMAN> connect catalog rman/verynicepassword@rmancata
RMAN> connect target sys/awesomepassword@floradev
RMAN> connect auxiliary /
RMAN> run {
set until time "to_date('25.03.2018 03:00:00', 'DD.MM.YYYY HH24:MI:SS')";
duplicate target database to floratst;
}

executing command: SET until clause

Starting Duplicate Db at 29.03.2018 17:30:14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/29/2018 17:30:15
RMAN-05501: aborting duplication of target database
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

Of course it does not work … Because DBID and incarnation of FLORADEV changed on March 27th at 19:00, after duplication …

As I don’t want to touch anything on target database FLORADEV, I’ll specify dbid and incarnation with appropriate options of duplicate clause : dbid and incarnation 🙂
But how do I get those values ? By browsing RMAN catalog :

# rman
RMAN> connect catalog rman/verynicepassword@rmancata
RMAN> list incarnation of database floradev ;

List of Database Incarnations
DB Key     Inc Key   DB Name  DB ID            STATUS   Reset SCN  	Reset Time
-------    -------   -------- ---------------- ---	----------	----------
1592914147 1592914169 FLORADEV 2406759844       PARENT  1		13.02.2017 09:47:24
1592914147 1592914148 FLORADEV 2406759844       CURRENT 299936708154 	06.02.2018 09:03:30
1605937125 1605937147 FLORADEV 2410224555       PARENT  1		13.02.2017 09:47:24
1605937125 1605937126 FLORADEV 2410224555       CURRENT 302151698724 	15.03.2018 11:28:42
1608176511 1608176591 FLORADEV 2410750461       PARENT  1		13.02.2017 09:47:24
1608176511 1608176512 FLORADEV 2410750461       CURRENT 302319507403 	21.03.2018 13:33:48
1608559043 1608559065 FLORADEV 2410843455       PARENT  1		13.02.2017 09:47:24
1608559043 1608559044 FLORADEV 2410843455       CURRENT 302790435374 	22.03.2018 15:23:43
1610349339 1610349361 FLORADEV 2411288665       PARENT  1		13.02.2017 09:47:24
1610349339 1610349340 FLORADEV 2411288665       CURRENT 303244612730 	27.03.2018 19:03:53

The point in time I am interested in is between March 22th 15:23:43 and March 27th 19:03:53, which means incarnation=1608559044 and DBID=2410843455. Let’s try again without connecting to target database with the appropriate options :

# rman
RMAN> connect catalog rman/verynicepassword@rmancata
RMAN> connect auxiliary /
RMAN> run {
set until time "to_date('25.03.2018 03:00:00', 'DD.MM.YYYY HH24:MI:SS')";
duplicate database floradev dbid 2410843455 incarnation 1608559044 to floratst ;
}

And now, it works 🙂