Jun 30, 2016 11:00:00 PM by Deiby Gomez
In previous versions, the only way to use Active Duplicate was with "image copies". However Oracle 12c introduced a new feature for Active Duplicate operations, now it is possible to do the duplication of the database with BackupSets. This way we avoid to transfer the complete file through the network. Using BackupSets allows oracle to use the special format in order to skip unused blocks, committed undo blocks etc..
In this article we will do an example about how to use it.
Firstable, let me show you my target database (the database that is being refreshed):
[oracle@db12102 Backup]$ ps -ef |grep pmon
grid 5928 1 0 00:20 ? 00:00:00 asm_pmon_+ASM
oracle 11659 1 0 01:25 ? 00:00:00 ora_pmon_db1
[oracle@db12102 Backup]$
Now I will create a parameter file of my database in order to build a new instance similar than "db1":
[oracle@db12102 Backup]$ sqlplus / as sysdba
SQL> create pfile='$ORACLE_HOME/dbs/initdb2.ora' from spfile;
File created.
I adapted the parameters inside the parameter file and now we are ready to start the "auxiliary" instance:
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initdb2.ora';
ORACLE instance started.
Total System Global Area 767557632 bytes
Fixed Size 2929112 bytes
Variable Size 310382120 bytes
Database Buffers 448790528 bytes
Redo Buffers 5455872 bytes
SQL>
The auxiliary Instance is called "db2":
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
db2
I will create a Password File :
[oracle@db12102 Backup]$ cd $ORACLE_HOME/dbs
[oracle@db12102 dbs]$ orapwd file=orapwdb2 password=manager1
I will create an Static Services using NETMGR:
[grid@db12102 dbs]$ netmgr
The Services look this way:
[oracle@db12102 dbs]$ lsnrctl services
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 29-MAY-2016 02:13:18
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "db2" has 1 instance(s).
Instance "db2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:221 refused:0
LOCAL SERVER
Now I will configure my tnsnames.ora file to create an entry for every database:
[oracle@db12102 dbs]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
DB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db12102)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db1)
)
)
DB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db12102)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db2)
(UR = A)
)
)
[oracle@db12102 dbs]$
Now we are ready to raise the duplicate operation:
oracle@db12102 ~]$ rman target sys/manager1@db1 auxiliary=sys/manager1@db2
Recovery Manager: Release 12.1.0.2.0 - Production on Sun May 29 07:43:57 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB1 (DBID=1506943872)
connected to auxiliary database: DB2 (not mounted)
RMAN> DUPLICATE TARGET DATABASE TO 'DB2'
FROM ACTIVE DATABASE
USING BACKUPSET;
Starting Duplicate Db at 29-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=22 device type=DISK
current log archived
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 767557632 bytes
Fixed Size 2929112 bytes
Variable Size 310382120 bytes
Database Buffers 448790528 bytes
Redo Buffers 5455872 bytes
contents of Memory Script:
{
sql clone "alter system set control_files =
''/data/db2/DB2/controlfile/o1_mf_cnnz809t_.ctl'', ''/data/db2/DB2/controlfile/o1_mf_cnnz80by_.ctl'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''DB1'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DB2'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone from service 'db1' primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set control_files = ''/data/db2/DB2/controlfile/o1_mf_cnnz809t_.ctl'',
''/data/db2/DB2/controlfile/o1_mf_cnnz80by_.ctl'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''DB1'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DB2'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 767557632 bytes
Fixed Size 2929112 bytes
Variable Size 310382120 bytes
Database Buffers 448790528 bytes
Redo Buffers 5455872 bytes
Starting restore at 29-MAY-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service db1
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data/db2/DB2/controlfile/o1_mf_cnnz809t_.ctl
output file name=/data/db2/DB2/controlfile/o1_mf_cnnz80by_.ctl
Finished restore at 29-MAY-16
database mounted
contents of Memory Script:
{
set newname for datafile 1 to
"/data/db2/DB1/datafile/o1_mf_system_cnnz5sx2_.dbf";
set newname for datafile 3 to
"/data/db2/DB1/datafile/o1_mf_sysaux_cnnz42rx_.dbf";
set newname for datafile 4 to
"/data/db2/DB1/datafile/o1_mf_undotbs1_cnnz784o_.dbf";
set newname for datafile 5 to
"/data/db2/DB1/datafile/o1_mf_users_cnony92f_.dbf";
set newname for datafile 6 to
"/data/db2/DB1/datafile/o1_mf_users_cnnz774t_.dbf";
restore
from service 'db1' clone database
;
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 restore at 29-MAY-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service db1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /data/db2/DB1/datafile/o1_mf_system_cnnz5sx2_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service db1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /data/db2/DB1/datafile/o1_mf_sysaux_cnnz42rx_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service db1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /data/db2/DB1/datafile/o1_mf_undotbs1_cnnz784o_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service db1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /data/db2/DB1/datafile/o1_mf_users_cnony92f_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service db1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /data/db2/DB1/datafile/o1_mf_users_cnnz774t_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 29-MAY-16
sql statement: alter system archive log current
current log archived
contents of Memory Script:
{
restore clone force from service 'db1'
archivelog from scn 1657545;
switch clone datafile all;
}
executing Memory Script
Starting restore at 29-MAY-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service db1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=17
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service db1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=18
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 29-MAY-16
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=913103165 file name=/data/db2/DB1/datafile/o1_mf_system_cnnz5sx2_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=913103165 file name=/data/db2/DB1/datafile/o1_mf_sysaux_cnnz42rx_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=913103165 file name=/data/db2/DB1/datafile/o1_mf_undotbs1_cnnz784o_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=913103165 file name=/data/db2/DB1/datafile/o1_mf_users_cnony92f_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=10 STAMP=913103165 file name=/data/db2/DB1/datafile/o1_mf_users_cnnz774t_.dbf
contents of Memory Script:
{
set until scn 1657807;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 29-MAY-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 17 is already on disk as file /data/db2/DB2/archivelog/2016_05_29/o1_mf_1_17_cnoomv9m_.arc
archived log for thread 1 with sequence 18 is already on disk as file /data/db2/DB2/archivelog/2016_05_29/o1_mf_1_18_cnoomwkm_.arc
archived log file name=/data/db2/DB2/archivelog/2016_05_29/o1_mf_1_17_cnoomv9m_.arc thread=1 sequence=17
archived log file name=/data/db2/DB2/archivelog/2016_05_29/o1_mf_1_18_cnoomwkm_.arc thread=1 sequence=18
media recovery complete, elapsed time: 00:00:00
Finished recover at 29-MAY-16
Oracle instance started
Total System Global Area 767557632 bytes
Fixed Size 2929112 bytes
Variable Size 310382120 bytes
Database Buffers 448790528 bytes
Redo Buffers 5455872 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DB2'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
}
executing Memory Script
sql statement: alter system set db_name = ''DB2'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance started
Total System Global Area 767557632 bytes
Fixed Size 2929112 bytes
Variable Size 310382120 bytes
Database Buffers 448790528 bytes
Redo Buffers 5455872 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DB2" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'/data/db2/DB1/datafile/o1_mf_system_cnnz5sx2_.dbf'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"/data/db2/DB1/datafile/o1_mf_temp_cnnz83hz_.tmp";
switch clone tempfile all;
catalog clone datafilecopy "/data/db2/DB1/datafile/o1_mf_sysaux_cnnz42rx_.dbf",
"/data/db2/DB1/datafile/o1_mf_undotbs1_cnnz784o_.dbf",
"/data/db2/DB1/datafile/o1_mf_users_cnony92f_.dbf",
"/data/db2/DB1/datafile/o1_mf_users_cnnz774t_.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /data/db2/DB1/datafile/o1_mf_temp_cnnz83hz_.tmp in control file
cataloged datafile copy
datafile copy file name=/data/db2/DB1/datafile/o1_mf_sysaux_cnnz42rx_.dbf RECID=1 STAMP=913103190
cataloged datafile copy
datafile copy file name=/data/db2/DB1/datafile/o1_mf_undotbs1_cnnz784o_.dbf RECID=2 STAMP=913103190
cataloged datafile copy
datafile copy file name=/data/db2/DB1/datafile/o1_mf_users_cnony92f_.dbf RECID=3 STAMP=913103190
cataloged datafile copy
datafile copy file name=/data/db2/DB1/datafile/o1_mf_users_cnnz774t_.dbf RECID=4 STAMP=913103190
datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=913103190 file name=/data/db2/DB1/datafile/o1_mf_sysaux_cnnz42rx_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=913103190 file name=/data/db2/DB1/datafile/o1_mf_undotbs1_cnnz784o_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=913103190 file name=/data/db2/DB1/datafile/o1_mf_users_cnony92f_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=913103190 file name=/data/db2/DB1/datafile/o1_mf_users_cnnz774t_.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Cannot remove created server parameter file
Finished Duplicate Db at 29-MAY-16
RMAN>
Tags: Oracle
Written by Deiby Gomez
Deiby Gómez is the first Oracle ACE Director of Guatemala. He has the highest technical certification in the world: "Oracle Certified Master 11g", "Oracle Certified Master 12c" and "Maximum Availability Architecture Oracle Certified Master 12c", he is the first person ever in Central America with all these certifications. Deiby likes to work with complex scenarios, huge and highly available critical databases where a deep knowledge of Oracle is needed. Deiby also has strong knowledge on Oracle Fusion Middleware and Oracle Cloud (PaaS & IaaS). Deiby was the winner of "IOUG SELECT Journal Editor’s Choice Award 2016" in Las Vegas, USA. He is a frequent speaker in Oracle Events around the World like OTN LAD Tour '13, '14, '15, '16, '17 (Colombia, Guatemala, El Salvador, Ecuador, Uruguay, Argentina, Brazil, Perú, Mexico, Costa Rica); Collaborate in Las Vegas, USA and Oracle Open World '15, '16, '17 (Brazil and USA). He was the first Guatemalan accepted as Beta Tester (12cR2) in San Francisco in 2015. Several articles have been published by him in English, Spanish and Portuguese in Oracle’s website, Toad World, and his own blog. Deiby appeared in the Official "Oracle Magazine" in Nov/Dec 2014 Edition as an outstanding expert. Deiby is the Technical Reviewer of the book “Oracle Database 12cR2 Multitenant - Oracle Press” and he is co-author of the book “Oracle Database 12cR2 Testing Tools and Techniques for Performance and Scalability - Oracle Press”. He loves to share his knowledge, to help people, to solve problems, to make friends and to play Chess.
Certifications:
We use cookies to improve your experience with our site. By continuing to use this site, you consent to our use of cookies. Learn more.