Resume an Oracle DUPLICATE database that failed on RECOVERY of datafiles

This post is also available in: Português

A time ago, the problem that I used to face was the failure of the recovery part of the duplicate script for any problems (like forgetting to set the db_recovery_area_dest_size big enough to hold the archives that will come from duplicate) and I ended up having to start the DUPLICATE command all over again. The recovery part would bring only some GBs, less than 1% compared to the size of the full backup restore part (about 500 GBs). So, running it again was a big waste of time.

To resume the DUPLICATE command, I started to dissect it. Here I will show an example of how to resume a tape backup duplicate that failed in the recovery part.

PS: Before starting, as stated in the comments, it's important to say that DUPLICATE is most of the times a RESUMABLE operation, as soon as you trigger it again WITH SAME SINTAX and with your destination database back in NOMOUNT. It will skip on most cases the already retrieved datafiles and archivelogs. This article is more intended to understand its internals in very particular cases or when you need a manual approach. Just consider it if you already tried running the same command again and it downloaded everything. =]

Scenario:

• Source DB: DBPROD
• Destination DB: DBTEST

I create my own pfile for DBTEST, set up the parameters and created the directories.

After starting the database in nomount, I issued the command:

-- My DUPLICATE command

connect target mdoxdba/"xxx"@dbprod
connect auxiliary mdoxdba/"xxx"@dbtest
connect catalog rman/"xxx"@rmanbkp

RUN
{
	SET UNTIL TIME "to_date('2015-01-06 00:00:00','yyyy-mm-dd hh24:mi:ss')";
	ALLOCATE AUXILIARY CHANNEL T1 TYPE 'sbt_tape';
	DUPLICATE TARGET DATABASE TO 'dbtest';
}

Note that my until time is 06/Jan. My latest full was on 03/Jan. So I have 3 days of archivelogs to be applied.

Then, when the script started the recovery phase, I aborted it (CTRL+C) to simulate a failure:

Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 9 12:15:49 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

RMAN>
connected to target database: DBPROD (DBID=2452006932)

RMAN>
connected to auxiliary database: DBTEST (not mounted)

RMAN>
connected to recovery catalog database

RMAN> 2> 3> 4> 5> 6> 7>
executing command: SET until clause

allocated channel: t1
channel t1: SID=554 device type=SBT_TAPE
channel t1: Data Protector A.08.10/205

Starting Duplicate Db at 09-JAN-15

contents of Memory Script:
{
   set until scn  13169104914343;
   set newname for datafile  1 to
 "/oravl02/oradata/dbtest/system01.dbf";
   set newname for datafile  2 to
 "/oravl02/oradata/dbtest/sysaux01.dbf";
   set newname for datafile  3 to
 "/oravl02/oradata/dbtest/undotbs01.dbf";
   set newname for datafile  4 to
 "/oravl02/oradata/dbtest/users01.dbf";
   set newname for datafile  5 to
 "/oravl02/oradata/dbtest/usr01.dbf";
   set newname for datafile  6 to
 "/oravl02/oradata/dbtest/usr02.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 09-JAN-15

channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to /oravl02/oradata/dbtest/system01.dbf
channel t1: restoring datafile 00002 to /oravl02/oradata/dbtest/sysaux01.dbf
channel t1: restoring datafile 00003 to /oravl02/oradata/dbtest/undotbs01.dbf
channel t1: restoring datafile 00004 to /oravl02/oradata/dbtest/users01.dbf
channel t1: restoring datafile 00005 to /oravl02/oradata/dbtest/usr01.dbf
channel t1: restoring datafile 00006 to /oravl02/oradata/dbtest/usr02.dbf
channel t1: reading from backup piece 1hprsdqk_1_1
channel t1: piece handle=1hprsdqk_1_1 tag=FULL_DB_INCR_ONLINE
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:09:45
Finished restore at 09-JAN-15
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DBTEST" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/oravl02/oradata/dbtest/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/oravl02/oradata/dbtest/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/oravl02/oradata/dbtest/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/oravl02/oradata/dbtest/system01.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=868537545 file name=/oravl02/oradata/dbtest/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=868537545 file name=/oravl02/oradata/dbtest/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=868537545 file name=/oravl02/oradata/dbtest/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=868537545 file name=/oravl02/oradata/dbtest/usr01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=868537545 file name=/oravl02/oradata/dbtest/usr02.dbf

contents of Memory Script:
{
   set until time  "to_date('2015-01-06 00:00:00','yyyy-mm-dd hh24:mi:ss')";
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 09-JAN-15

starting media recovery

channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=7304
channel t1: restoring archived log
archived log thread=1 sequence=7305
channel t1: reading from backup piece 1jprsef0_1_1

user interrupt received
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: '/oravl02/oradata/dbtest/system01.dbf'

released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/09/2015 12:26:47
RMAN-03015: error occurred in stored script Memory Script
RMAN-03099: job cancelled at user request

RMAN>

Here we can see that the RESTORE completed successfully. After that, the DUPLICATE created the controlfile with only the SYSTEM datafile. It then performed a "switch datafile all" to add the rest.

When it started the RECOVER step, I aborted it.

So, how can we resume that?

First, we need to finish the recovery. Let's open again the rman connecting to auxiliary, target and catalog servers (if present) and then rerun the recovery step.

Note below that I allocate the auxiliary channel again and the DBTEST must remains in MOUNT state.

RMAN> run
2> {
3>    allocate auxiliary channel t1 type 'SBT_TAPE';
5>    set until time  "to_date('2015-01-06 00:00:00','yyyy-mm-dd hh24:mi:ss')";
6>    recover
7>    clone database
8>     delete archivelog
9>    ;
10> }

allocated channel: t1
channel t1: SID=552 device type=SBT_TAPE
channel t1: Data Protector A.08.10/205

executing command: SET until clause

Starting recover at 09-JAN-15
added tempfile /oravl04/oradata/dbprod/dbprod/temp01.dbf to tablespace TEMPORARY in control file

starting media recovery

channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=7304
channel t1: restoring archived log
archived log thread=1 sequence=7305
channel t1: reading from backup piece 1jprsef0_1_1
channel t1: piece handle=1jprsef0_1_1 tag=FULL_ARCHIVELOG_ONLINE
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:03:55
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7304_bbzsz4nh_.arc thread=1 sequence=7304
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7304_bbzsz4nh_.arc RECID=2 STAMP=868538309
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7305_bbzsz4o6_.arc thread=1 sequence=7305
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7305_bbzsz4o6_.arc RECID=1 STAMP=868538308
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=7306
channel t1: restoring archived log
archived log thread=1 sequence=7307
channel t1: restoring archived log
archived log thread=1 sequence=7308
channel t1: restoring archived log
archived log thread=1 sequence=7309
channel t1: reading from backup piece 1lprto0p_1_1
channel t1: piece handle=1lprto0p_1_1 tag=ARCHIVE_ONLINE
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:55
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7306_bbzt0nlk_.arc thread=1 sequence=7306
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7306_bbzt0nlk_.arc RECID=4 STAMP=868538362
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7307_bbzt0nns_.arc thread=1 sequence=7307
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7307_bbzt0nns_.arc RECID=6 STAMP=868538362
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7308_bbzt0nn1_.arc thread=1 sequence=7308
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7308_bbzt0nn1_.arc RECID=5 STAMP=868538362
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7309_bbzt0np3_.arc thread=1 sequence=7309
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7309_bbzt0np3_.arc RECID=3 STAMP=868538362
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=7310
channel t1: restoring archived log
archived log thread=1 sequence=7311
channel t1: restoring archived log
archived log thread=1 sequence=7312
channel t1: restoring archived log
archived log thread=1 sequence=7313
channel t1: restoring archived log
archived log thread=1 sequence=7314
channel t1: restoring archived log
archived log thread=1 sequence=7315
channel t1: restoring archived log
archived log thread=1 sequence=7316
channel t1: restoring archived log
archived log thread=1 sequence=7317
channel t1: reading from backup piece 1mps0cd1_1_1
channel t1: piece handle=1mps0cd1_1_1 tag=ARCHIVE_ONLINE
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:55
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7310_bbzt2v2x_.arc thread=1 sequence=7310
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7310_bbzt2v2x_.arc RECID=9 STAMP=868538434
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7311_bbzt2v3d_.arc thread=1 sequence=7311
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7311_bbzt2v3d_.arc RECID=10 STAMP=868538434
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7312_bbzt2v22_.arc thread=1 sequence=7312
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7312_bbzt2v22_.arc RECID=8 STAMP=868538434
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7313_bbzt2v3p_.arc thread=1 sequence=7313
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7313_bbzt2v3p_.arc RECID=11 STAMP=868538434
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7314_bbzt2v42_.arc thread=1 sequence=7314
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7314_bbzt2v42_.arc RECID=12 STAMP=868538434
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7315_bbzt2v4c_.arc thread=1 sequence=7315
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7315_bbzt2v4c_.arc RECID=13 STAMP=868538434
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7316_bbzt2v4t_.arc thread=1 sequence=7316
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7316_bbzt2v4t_.arc RECID=14 STAMP=868538434
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7317_bbzt2v55_.arc thread=1 sequence=7317
channel clone_default: deleting archived log(s)
archived log file name=/oravl02/oradata/dbtest/fast_recovery_area/DBTEST/archivelog/2015_01_09/o1_mf_1_7317_bbzt2v55_.arc RECID=7 STAMP=868538429
media recovery complete, elapsed time: 00:00:21
Finished recover at 09-JAN-15
released channel: t1

RMAN>

Perfect, the recovery worked. From here, we have 2 ways to finish our task: The easy and obvious way OR the DUPLICATE way (dissecting it). I will show you both:

1. The easy way:

Finally, let's open the database with resetlogs:

SQL> alter database open resetlogs;

Database altered.

SQL>

Ok. Your database is ready now.

2. The DUPLICATE dissected way:

The DUPLICATE command do a lot of extra steps, probably because it can receive a lot of combinations of conditions, such as skipping some tablespaces. It is a more generic implementation mode to handle a wider range of cases:

To simulate, first let's put the database in NOMOUNT state again to recreate the controlfile (note that I did it still using the RMAN session):

RMAN> RUN
2> {
3>    shutdown clone immediate;
4>    startup clone nomount ;
5> }

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    7081771008 bytes

Fixed Size                     2147080 bytes
Variable Size               3283610872 bytes
Database Buffers            3791650816 bytes
Redo Buffers                   4362240 bytes

RMAN>

Next, instead of recreating the controlfile with all datafiles, oracle will only create it with the SYSTEM datafile. Let's do that. (It is the same command that executed in the beginning):

SQL> CREATE CONTROLFILE REUSE SET DATABASE "DBTEST" RESETLOGS ARCHIVELOG
  2    MAXLOGFILES     16
  3    MAXLOGMEMBERS      3
  4    MAXDATAFILES      100
  5    MAXINSTANCES     8
  6    MAXLOGHISTORY      292
  7   LOGFILE
  8    GROUP  1 ( '/oravl02/oradata/dbtest/redo01.log' ) SIZE 50 M  REUSE,
  9    GROUP  2 ( '/oravl02/oradata/dbtest/redo02.log' ) SIZE 50 M  REUSE,
 10    GROUP  3 ( '/oravl02/oradata/dbtest/redo03.log' ) SIZE 50 M  REUSE
 11   DATAFILE
 12    '/oravl02/oradata/dbtest/system01.dbf'
 13   CHARACTER SET AL32UTF8;

Control file created.

SQL>

Now, let's add to the controlfile the others datafiles. Connect again RMAN to the auxiliary and target DBs:

RMAN> run
2> {
3>    set newname for tempfile  1 to "/oravl02/oradata/dbtest/temp01.dbf";
4>    switch clone tempfile all;
5>    set newname for datafile 1 to "/oravl02/oradata/dbtest/system01.dbf";
6>    set newname for datafile 2 to "/oravl02/oradata/dbtest/sysaux01.dbf";
7>    set newname for datafile 3 to "/oravl02/oradata/dbtest/undotbs01.dbf";
8>    set newname for datafile 4 to "/oravl02/oradata/dbtest/users01.dbf";
9>    set newname for datafile 5 to "/oravl02/oradata/dbtest/usr01.dbf";
10>    set newname for datafile 6 to "/oravl02/oradata/dbtest/usr02.dbf";
11>    catalog clone datafilecopy "/oravl02/oradata/dbtest/sysaux01.dbf";
12>    catalog clone datafilecopy "/oravl02/oradata/dbtest/undotbs01.dbf";
13>    catalog clone datafilecopy "/oravl02/oradata/dbtest/users01.dbf";
14>    catalog clone datafilecopy "/oravl02/oradata/dbtest/usr01.dbf";
15>    catalog clone datafilecopy "/oravl02/oradata/dbtest/usr02.dbf";
16>    switch clone datafile all;
17> }

executing command: SET NEWNAME

renamed tempfile 1 to /oravl02/oradata/dbtest/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

cataloged datafile copy
datafile copy file name=/oravl02/oradata/dbtest/sysaux01.dbf RECID=1 STAMP=868554741

cataloged datafile copy
datafile copy file name=/oravl02/oradata/dbtest/undotbs01.dbf RECID=2 STAMP=868554741

cataloged datafile copy
datafile copy file name=/oravl02/oradata/dbtest/users01.dbf RECID=3 STAMP=868554741

cataloged datafile copy
datafile copy file name=/oravl02/oradata/dbtest/usr01.dbf RECID=4 STAMP=868554742

cataloged datafile copy
datafile copy file name=/oravl02/oradata/dbtest/usr02.dbf RECID=5 STAMP=868554742

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=868554741 file name=/oravl02/oradata/dbtest/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=868554741 file name=/oravl02/oradata/dbtest/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=868554741 file name=/oravl02/oradata/dbtest/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=868554742 file name=/oravl02/oradata/dbtest/usr01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=868554742 file name=/oravl02/oradata/dbtest/usr02.dbf

RMAN>

P.S: Although not present in the trace of the DUPLICATE, here I've added the "set newname" clause for all datafiles. Otherwise, it would fail. In fact, when is RMAN that runs, it doesn't need to execute those "sets" in the final block because this was already done in the beggining. As the session is the same, it does not need to rerun them.

So, finally, open your DB with resetlogs:

RMAN> run
2> {
3>    Alter clone database open resetlogs;
4> }

database opened

RMAN>

And that's it! Using those methods, there is no need to restore all the datafiles again.

Some useful sites that helped me out and where you can find more information:

• http://www.pythian.com/blog/rman-duplicate-in-details
• https://support.oracle.com/epmos/faces/DocumentDisplay?id=360962.1
• https://oraclefunda.wordpress.com/2009/02/15/33/

+13