What Happens When Active DB Duplication Goes Wrong?

Feb 18, 2014 / By Michael Dinh

Tags: , ,

There are many blog posts out there about active database duplication. However, they were all tested in an ideal environment or condition. What happens when a tablespace is created during the middle of active duplication and how to resolve the error? Read on if you would like to know.

For my test case, I created database db01 using OMF and will perform active duplication to db02 using OMF as well on the same host. While duplication was running, I created a new tablespace. Here are the details of the steps performed:

Review files and pfile for TARGET database:

[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ ll
total 7692
-rw-rw----. 1 oracle oinstall    1544 Feb 14 13:06 hc_db01.dat
-rwxr-x---. 1 oracle oinstall     590 Feb 13 08:14 initdb01.ora
-rwxr-x---. 1 oracle oinstall     590 Feb 13 08:14 initdb02.ora
-rw-r-----. 1 oracle oinstall      24 Feb 13 08:18 lkDB01
-rw-r-----. 1 oracle oinstall       0 Feb 14 13:06 lkinstdb01
-rw-r-----. 1 oracle oinstall    2048 Feb 11 14:00 orapwdb01
-rw-r-----. 1 oracle oinstall    2048 Feb 11 15:48 orapwdb02
-rw-r-----. 1 oracle oinstall 7847936 Feb 11 18:06 snapcf_db01.f
-rw-r-----. 1 oracle oinstall    3584 Feb 14 13:05 spfiledb01.ora

[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ cat initdb02.ora
*.audit_file_dest='/u01/app/oracle/admin/adump'
*.audit_trail='none'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_create_file_dest='/oradata'
*.db_domain=''
*.db_name='db02'
*.db_recovery_file_dest='/oradata/fra'
*.db_recovery_file_dest_size=4g
*.diagnostic_dest='/u01/app/oracle'
*.event='10795 trace name context forever, level 2'
*.fast_start_mttr_target=300
*.java_pool_size=0
*.local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1531))'
*.pga_aggregate_target=268435456
*.processes=100
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=805306368
*.undo_tablespace='UNDOTBS'

[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ diff ./initdb01.ora ./initdb02.ora
7c7
< *.db_name='db01' --- > *.db_name='db02'
[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$

Notice there is only one difference between in the pfile for db01 and db02

Create same directory structures for TARGET database:

[oracle@arrow:]/oradata
$ ls DB*
DB01:
controlfile  datafile  onlinelog

DB02:
controlfile  datafile  onlinelog
[oracle@arrow:]/oradata
$

Startup NOMOUNT TARGET database:

[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 14 13:11:13 2014

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

Connected to an idle instance.

SYS@db02> startup nomount;
ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2257520 bytes
Variable Size             222301584 bytes
Database Buffers          570425344 bytes
Redo Buffers                6717440 bytes
SYS@db02>

Start active database duplication:

[oracle@arrow:db01]/media/sf_linux_x64/rman
$ rman @dupdbomf.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 14 13:12:04 2014

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

RMAN> connect target *
2> connect auxiliary *
3> run {
4> allocate channel c1 type disk maxopenfiles 1;
5> allocate auxiliary channel a1 type disk;
6> duplicate target database to db02
7>   from active database nofilenamecheck
8>   spfile
9>   PARAMETER_VALUE_CONVERT ('DB01','DB02')
10> ;
11> }
12> exit;
connected to target database: DB01 (DBID=1470673955)

connected to auxiliary database: DB02 (not mounted)

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=14 device type=DISK

allocated channel: a1
channel a1: SID=96 device type=DISK

Starting Duplicate Db at 14-FEB-2014 13:12:07

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb01.ora' auxiliary format
 '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora''";
}
executing Memory Script

Starting backup at 14-FEB-2014 13:12:08
Finished backup at 14-FEB-2014 13:12:09

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB02'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  control_files =
 ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DB02'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  control_files =  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.c                                                                              tl'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''DB01'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DB02'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl';
   restore clone controlfile to  '/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl' from
 '/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl';
   sql clone "alter system set  control_files =
  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''DB01'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DB02'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

Starting backup at 14-FEB-2014 13:12:55
channel c1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/snapcf_db01.f tag=TAG20140214T131255 RECID=1 STAMP=839509978
channel c1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-FEB-2014 13:13:03

Starting restore at 14-FEB-2014 13:13:03

channel a1: copied control file copy
Finished restore at 14-FEB-2014 13:13:04

sql statement: alter system set  control_files =   ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

database mounted

contents of Memory Script:
{
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 14-FEB-2014 13:13:21

----------------------------------------------------------------------
-- While duplication was running, create new tablespace at source
--
[oracle@arrow:db01]/home/oracle
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 14 13:13:22 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@db01):PRIMARY> create tablespace mdinh;

Tablespace created.

ARROW:(SYS@db01):PRIMARY>
----------------------------------------------------------------------

channel c1: starting datafile copy
input datafile file number=00001 name=/oradata/DB01/datafile/o1_mf_system_9hsw4shz_.dbf
output file name=/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf tag=TAG20140214T131321
channel c1: datafile copy complete, elapsed time: 00:01:26
channel c1: starting datafile copy
input datafile file number=00002 name=/oradata/DB01/datafile/o1_mf_sysaux_9hsw63d2_.dbf
output file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf tag=TAG20140214T131321
channel c1: datafile copy complete, elapsed time: 00:00:35
channel c1: starting datafile copy
input datafile file number=00003 name=/oradata/DB01/datafile/o1_mf_undotbs_9hsw75h4_.dbf
output file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf tag=TAG20140214T131321
channel c1: datafile copy complete, elapsed time: 00:00:35
channel c1: starting datafile copy
input datafile file number=00004 name=/oradata/DB01/datafile/o1_mf_users_9hsw880k_.dbf
output file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf tag=TAG20140214T131321
channel c1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 14-FEB-2014 13:16:32

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/oradata/fra/DB01/archivelog/2014_02_14/o1_mf_1_10_9hx1xkn1_.arc" auxiliary format
 "/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_%u_.arc"   ;
   catalog clone recovery area;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 14-FEB-2014 13:16:34
channel c1: starting archived log copy
input archived log thread=1 sequence=10 RECID=2 STAMP=839510193
output file name=/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc RECID=0 STAMP=0
channel c1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 14-FEB-2014 13:16:35

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_12/o1_mf_1_10_0cp0ekfe_.arc
File Name: /oradata/fra/DB02/controlfile/o1_mf_9hoq0kmv_.ctl
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_12/o1_mf_1_10_0cp0ekfe_.arc

List of Files Which Where Not Cataloged
=======================================
File Name: /oradata/fra/DB02/controlfile/o1_mf_9hoq0kmv_.ctl
  RMAN-07518: Reason: Foreign database file DBID: 1470537681  Database Name: DB01

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=839510196 file name=/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=839510196 file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=839510197 file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=839510197 file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf

contents of Memory Script:
{
   set until scn  227291;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 14-FEB-2014 13:16:39
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB02'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DB02'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/14/2014 13:17:01
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06094: datafile 5 must be restored

Recovery Manager complete.
[oracle@arrow:db01]/media/sf_linux_x64/rman
$

Remove spfile and misc files for TARGET database:

Startup NOMOUNT TARGET database:

[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ rm spfiledb02.ora lkDB02 hc_db02.dat
[oracle@arrow:db02]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 14 13:18:43 2014

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

Connected to an idle instance.

SYS@db02> startup nomount;
ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2257520 bytes
Variable Size             222301584 bytes
Database Buffers          570425344 bytes
Redo Buffers                6717440 bytes
SYS@db02> exit

RESTART active database duplication:

[oracle@arrow:db01]/media/sf_linux_x64/rman
$ rman @dupdbomf.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 14 13:18:52 2014

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

RMAN> connect target *
2> connect auxiliary *
3> run {
4> allocate channel c1 type disk maxopenfiles 1;
5> allocate auxiliary channel a1 type disk;
6> duplicate target database to db02
7>   from active database nofilenamecheck
8>   spfile
9>   PARAMETER_VALUE_CONVERT ('DB01','DB02')
10> ;
11> }
12> exit;
connected to target database: DB01 (DBID=1470673955)

connected to auxiliary database: DB02 (not mounted)

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=14 device type=DISK

allocated channel: a1
channel a1: SID=10 device type=DISK

Starting Duplicate Db at 14-FEB-2014 13:18:54

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb01.ora' auxiliary format
 '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora''";
}
executing Memory Script

Starting backup at 14-FEB-2014 13:18:54
Finished backup at 14-FEB-2014 13:18:55

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfiledb02.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB02'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  control_files =
 ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DB02'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  control_files =  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''DB01'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DB02'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl';
   restore clone controlfile to  '/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl' from
 '/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl';
   sql clone "alter system set  control_files =
  ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''DB01'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DB02'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

Starting backup at 14-FEB-2014 13:19:11
channel c1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/snapcf_db01.f tag=TAG20140214T131912 RECID=2 STAMP=839510353
channel c1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 14-FEB-2014 13:19:15

Starting restore at 14-FEB-2014 13:19:15

channel a1: copied control file copy
Finished restore at 14-FEB-2014 13:19:16

sql statement: alter system set  control_files =   ''/oradata/DB02/controlfile/o1_mf_9hsw332r_.ctl'', ''/oradata/fra/DB02/controlfile/o1_mf_9hsw33dm_.ctl'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK

database mounted

Using previous duplicated file /oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf for datafile 1 with checkpoint SCN of 226956
Using previous duplicated file /oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf for datafile 2 with checkpoint SCN of 227250
Using previous duplicated file /oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf for datafile 3 with checkpoint SCN of 227262
Using previous duplicated file /oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf for datafile 4 with checkpoint SCN of 227275

contents of Memory Script:
{
   set newname for datafile  1 to
 "/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf";
   set newname for datafile  2 to
 "/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf";
   set newname for datafile  3 to
 "/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf";
   set newname for datafile  4 to
 "/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf";
   set newname for clone datafile  5 to new;

   backup as copy reuse
   datafile  5 auxiliary format new
   ;
   
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 14-FEB-2014 13:19:31
channel c1: starting datafile copy
input datafile file number=00005 name=/oradata/DB01/datafile/o1_mf_mdinh_9hx1qqko_.dbf
output file name=/oradata/DB02/datafile/o1_mf_mdinh_08p0jqb4_.dbf tag=TAG20140214T131931
channel c1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 14-FEB-2014 13:19:47

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/oradata/fra/DB01/archivelog/2014_02_14/o1_mf_1_10_9hx1xkn1_.arc" auxiliary format
 "/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_%u_.arc"   archivelog like
 "/oradata/fra/DB01/archivelog/2014_02_14/o1_mf_1_11_9hx23s3n_.arc" auxiliary format
 "/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_%u_.arc"   ;
   catalog clone recovery area;
   catalog clone datafilecopy  "/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf",
 "/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf",
 "/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf",
 "/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf";
   switch clone datafile  1 to datafilecopy
 "/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf";
   switch clone datafile  2 to datafilecopy
 "/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf";
   switch clone datafile  3 to datafilecopy
 "/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf";
   switch clone datafile  4 to datafilecopy
 "/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 14-FEB-2014 13:19:53
channel c1: starting archived log copy
input archived log thread=1 sequence=10 RECID=2 STAMP=839510193
output file name=/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_09p0jqbq_.arc RECID=0 STAMP=0
channel c1: archived log copy complete, elapsed time: 00:00:01
channel c1: starting archived log copy
input archived log thread=1 sequence=11 RECID=3 STAMP=839510393
output file name=/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_0ap0jqbr_.arc RECID=0 STAMP=0
channel c1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 14-FEB-2014 13:19:56

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_0ap0jqbr_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_09p0jqbq_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_12/o1_mf_1_10_0cp0ekfe_.arc
File Name: /oradata/fra/DB02/controlfile/o1_mf_9hoq0kmv_.ctl
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_0ap0jqbr_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_09p0jqbq_.arc
File Name: /oradata/fra/DB02/archivelog/2014_02_12/o1_mf_1_10_0cp0ekfe_.arc

List of Files Which Where Not Cataloged
=======================================
File Name: /oradata/fra/DB02/controlfile/o1_mf_9hoq0kmv_.ctl
  RMAN-07518: Reason: Foreign database file DBID: 1470537681  Database Name: DB01

cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf RECID=2 STAMP=839510398
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf RECID=3 STAMP=839510398
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf RECID=4 STAMP=839510399
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf RECID=5 STAMP=839510399

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=839510398 file name=/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf

datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=839510398 file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf

datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=839510399 file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf

datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=839510399 file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf

datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=839510401 file name=/oradata/DB02/datafile/o1_mf_mdinh_08p0jqb4_.dbf

contents of Memory Script:
{
   set until scn  227620;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 14-FEB-2014 13:20:02

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc
archived log for thread 1 with sequence 11 is already on disk as file /oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_0ap0jqbr_.arc
archived log file name=/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_10_06p0jq5i_.arc thread=1 sequence=10
archived log file name=/oradata/fra/DB02/archivelog/2014_02_14/o1_mf_1_11_0ap0jqbr_.arc thread=1 sequence=11
media recovery complete, elapsed time: 00:00:09
Finished recover at 14-FEB-2014 13:20:14
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB02'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DB02'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     801701888 bytes

Fixed Size                     2257520 bytes
Variable Size                222301584 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6717440 bytes
allocated channel: a1
channel a1: SID=95 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DB02" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      2
  MAXDATAFILES       30
  MAXINSTANCES     1
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1  SIZE 100 M ,
  GROUP   2  SIZE 100 M ,
  GROUP   3  SIZE 100 M
 DATAFILE
  '/oradata/DB02/datafile/o1_mf_system_02p0jpvh_.dbf'
 CHARACTER SET AL32UTF8

contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf",
 "/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf",
 "/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf",
 "/oradata/DB02/datafile/o1_mf_mdinh_08p0jqb4_.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/DB02/datafile/o1_mf_temp_%u_.tmp in control file

cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf RECID=1 STAMP=839510428
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf RECID=2 STAMP=839510428
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf RECID=3 STAMP=839510428
cataloged datafile copy
datafile copy file name=/oradata/DB02/datafile/o1_mf_mdinh_08p0jqb4_.dbf RECID=4 STAMP=839510429

datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=839510429 file name=/oradata/DB02/datafile/o1_mf_mdinh_08p0jqb4_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=839510430 file name=/oradata/DB02/datafile/o1_mf_sysaux_03p0jq27_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=839510431 file name=/oradata/DB02/datafile/o1_mf_undotbs_04p0jq3a_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=839510432 file name=/oradata/DB02/datafile/o1_mf_users_05p0jq4d_.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 14-FEB-2014 13:21:48
released channel: c1
released channel: a1

Recovery Manager complete.
[oracle@arrow:db01]/media/sf_linux_x64/rman
$

Did you noticed duplication reused previous duplicated file versus duplicating it again?

4 Responses to “What Happens When Active DB Duplication Goes Wrong?”

  • Hi Michael!

    Thanks for sharing your tests.

    Database Backup and Recovery User’s Guide (11.2) ch.23 says:
    RMAN automatically optimizes a DUPLICATE command that is a repeat of a previously failed DUPLICATE command. The repeat DUPLICATE command notices which datafiles were successfully copied earlier and does not copy them again. This applies to all forms of duplication, whether they are backup-based (with and without a target connection) or active database duplication. The automatic optimization of the DUPLICATE command can be especially useful when a failure occurs during the duplication of very large databases.
    If a DUPLICATE operation fails, you need only run the DUPLICATE again, using the same parameters contained in the original DUPLICATE command.

    Regards
    Mahir M. Quluzade
    http://www.mahir-quluzade.com

  • Greg says:

    Hi Michael,
    in some circumstances You may hit Bug 13498382 ‘ORA-1122 ORA-1208 RMAN Duplicate overwrites datafiles at original location despite db_create_file_dest’ .
    I wonder why it did not happen during Your test .
    Regards
    Greg

    • Michael Dinh says:

      Hi Greg,

      I purposely did this test to see if I would hit the bug 13498382 with active duplication. Guess I just got lucky. One thing the note failed to mention if the issue occurs on a consistent basis and under what scenarios.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>