Duplicate a database without connecting to a target database and
a recovery catalog (11gR2 New Features)
We can duplicate a database without connecting to a target
database. The only requirements for this operation are a connection to a
catalog and an auxiliary database. This new functionality is useful when the
target database is not always available. We can also duplicate a database
without connecting to a target database and a recovery catalog. In this case,
the only requirement is that you provide a disk backup location where RMAN can
find all the backups of datafiles, archived logs, and control file copies for
database duplication. This database duplication enhancement is helpful when it
is not possible to connect to the target database and the recovery catalog.
In my example i am going to create a duplicate database with the
name CLONEDB from the database NEERAJ
1. Take backup of target database.
2. Copy the backupsets to the machine where you would like to
clone the target database.
3. Create a new pfile for clone database or copy the pfile of
target database and replace/edit the pfile entries for the clone database.
4. Create a password file
5. Create required folders for dump and database files.
6. Add an entry in /etc/oratab file
7. Create pfile for duplicate database
8. Add required TNS entry in tnsnames.ora file
9. startup nomount the clone database.
10. Duplicate command in auxiliary instance.
Node 1:-
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
NEERAJ ARCHIVELOG
SQL> !rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 22
20:41:55 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
connected to target database: NEERAJ (DBID=332109549)
RMAN> backup database plus archivelog;
Starting backup at 22-JUL-14
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=853618743
input archived log thread=1 sequence=4 RECID=2 STAMP=853618743
input archived log thread=1 sequence=5 RECID=3 STAMP=853618746
input archived log thread=1 sequence=6 RECID=4 STAMP=853618748
input archived log thread=1 sequence=7 RECID=5 STAMP=853620138
channel ORA_DISK_1: starting piece 1 at 22-JUL-14
channel ORA_DISK_1: finished piece 1 at 22-JUL-14
piece handle=/u01/backup/01pe2dda_1_1 tag=TAG20140722T204218
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 22-JUL-14
Starting backup at 22-JUL-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001
name=/u01/app/oracle/oradata/neeraj/system01.dbf
input datafile file number=00002
name=/u01/app/oracle/oradata/neeraj/sysaux01.dbf
input datafile file number=00005
name=/u01/app/oracle/oradata/neeraj/example01.dbf
input datafile file number=00003
name=/u01/app/oracle/oradata/neeraj/undotbs01.dbf
input datafile file number=00004
name=/u01/app/oracle/oradata/neeraj/users01.dbf
input datafile file number=00006
name=/u01/app/oracle/oradata/neeraj/neer.dbf
channel ORA_DISK_1: starting piece 1 at 22-JUL-14
channel ORA_DISK_1: finished piece 1 at 22-JUL-14
piece handle=/u01/backup/02pe2dde_1_1 tag=TAG20140722T204222
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 22-JUL-14
Starting backup at 22-JUL-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=6 STAMP=853620187
channel ORA_DISK_1: starting piece 1 at 22-JUL-14
channel ORA_DISK_1: finished piece 1 at 22-JUL-14
piece handle=/u01/backup/03pe2des_1_1 tag=TAG20140722T204308
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-JUL-14
Starting Control File and SPFILE Autobackup at 22-JUL-14
piece handle=/u01/backup/c-332109549-20140722-00 comment=NONE
Finished Control File and SPFILE Autobackup at 22-JUL-14
RMAN>
RMAN> backup archivelog all;
Starting backup at 22-JUL-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=853618743
input archived log thread=1 sequence=4 RECID=2 STAMP=853618743
input archived log thread=1 sequence=5 RECID=3 STAMP=853618746
input archived log thread=1 sequence=6 RECID=4 STAMP=853618748
input archived log thread=1 sequence=7 RECID=5 STAMP=853620138
input archived log thread=1 sequence=8 RECID=6 STAMP=853620187
input archived log thread=1 sequence=9 RECID=7 STAMP=853620413
channel ORA_DISK_1: starting piece 1 at 22-JUL-14
channel ORA_DISK_1: finished piece 1 at 22-JUL-14
piece handle=/u01/backup/05pe2dlu_1_1 tag=TAG20140722T204653
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-JUL-14
Starting Control File and SPFILE Autobackup at 22-JUL-14
piece handle=/u01/backup/c-332109549-20140722-01 comment=NONE
Finished Control File and SPFILE Autobackup at 22-JUL-14
RMAN> exit
Recovery Manager complete.
NOTE:- copy the backups to the second server where you would
like to clone the database
[oracle@localhost ~]$ cd /u01/backup/
[oracle@localhost backup]$ ls
01pe2dda_1_1 02pe2dde_1_1 03pe2des_1_1
05pe2dlu_1_1 c-332109549-20140722-00
[oracle@localhost backup]$
[oracle@localhost backup]$ scp *
oracle@192.168.204.131:/u01/neeraj
The authenticity of host '192.168.204.131 (192.168.204.131)'
can't be established.
RSA key fingerprint is
cb:57:be:e9:4f:f9:d1:1b:b2:95:8f:ac:f2:d1:05:12.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.204.131' (RSA) to the list
of known hosts.
Address 192.168.204.131 maps to localhost.localdomain, but this
does not map back to the address - POSSIBLE BREAK-IN ATTEMPT!
oracle@192.168.204.131's password:
01pe2dda_1_1
100%
41MB 20.3MB/s 00:02
02pe2dde_1_1
100%
1062MB 11.6MB/s 01:32
03pe2des_1_1
100%
264KB 263.5KB/s 00:00
05pe2dlu_1_1
100%
41MB 13.7MB/s 00:03
c-332109549-20140722-00
100% 9600KB 9.4MB/s
00:00
c-332109549-20140722-01
100% 9600KB 9.4MB/s
00:01
[oracle@localhost dbs]$ sqlplus
SQL> create pfile='/u01/backup/neeraj.ora' from spfile;
File created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release
11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
[oracle@localhost dbs]$ cd /u01/backup/
[oracle@localhost backup]$ ls
01pe2dda_1_1 02pe2dde_1_1 03pe2des_1_1
05pe2dlu_1_1 c-332109549-20140722-00 c-332109549-20140722-01
neeraj.ora
[oracle@localhost backup]$ scp neeraj.ora
oracle@192.168.204.131:/u01/neeraj
Address 192.168.204.131 maps to localhost.localdomain, but this
does not map back to the address - POSSIBLE BREAK-IN ATTEMPT!
oracle@192.168.204.131's password:
neeraj.ora 100% 969
1.0KB/s 00:00
[oracle@localhost backup]$
Note:- ON the second node :
login as: oracle
oracle@192.168.204.131's password:
Last login: Tue Jul 22 14:07:04 2014
[oracle@localhost ~]$ cd /u01/neeraj/[oracle@localhost neeraj]$
ls
01pe2dda_1_1 02pe2dde_1_1 03pe2des_1_1
05pe2dlu_1_1 c-332109549-20140722-00 c-332109549-20140722-01
[oracle@localhost neeraj]$ ls -ltr
total 1192060
-rw-r----- 1 oracle dba 42621952 Jul 22 20:07
01pe2dda_1_1
-rw-r----- 1 oracle dba 269824 Jul 22 20:09
03pe2des_1_1
-rw-r----- 1 oracle dba 1113939968 Jul 22 20:09 02pe2dde_1_1
-rw-r----- 1 oracle dba 42945536 Jul 22 20:09
05pe2dlu_1_1
-rw-r----- 1 oracle dba 9830400 Jul 22 20:09
c-332109549-20140722-01
-rw-r----- 1 oracle dba 9830400 Jul 22 20:09
c-332109549-20140722-00
[oracle@localhost neeraj]$
[oracle@localhost neeraj]$ orapwd
file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdclonedb password=sys
[oracle@localhost neeraj]$
[oracle@localhost neeraj]$ mkdir -p
/u01/clonedb/admin/clonedb/adump
[oracle@localhost neeraj]$ mkdir -p /u01/clonedb/admin/clonedb/
[oracle@localhost neeraj]$ mkdir -p /u01/clonedb/
NOTE:- edit the pfile:-
clonedb.__db_cache_size=415236096
clonedb.__java_pool_size=4194304
clonedb.__large_pool_size=4194304
clonedb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from
environment
clonedb.__pga_aggregate_target=419430400
clonedb.__sga_target=624951296
clonedb.__shared_io_pool_size=0
clonedb.__shared_pool_size=192937984
clonedb.__streams_pool_size=0
*.audit_file_dest='/u01/clonedb/admin/clonedb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/clonedb/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='clonedb'
*.db_recovery_file_dest='/u01/clonedb/admin/clonedb'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=clonedbXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1042284544
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
db_file_name_convert='/u01/app/oracle/oradata/neeraj/','/u01/clonedb/'
log_file_name_convert='/u01/app/oracle/oradata/neeraj/','/u01/clonedb/'
[oracle@localhost neeraj]$ export ORACLE_SID=clonedb
[oracle@localhost neeraj]$ sqlplus
SQL> startup nomount pfile='/u01/neeraj/neeraj.ora'
ORACLE instance started.
Total System Global Area 1042038784 bytes
Fixed Size
1341112 bytes
Variable Size
620759368 bytes
Database Buffers 415236096
bytes
Redo Buffers
4702208 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release
11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
[oracle@localhost neeraj]$ rman auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 22
21:06:48 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
connected to auxiliary database: CLONEDB (not mounted)
RMAN> duplicate database to clonedb backup location
'/u01/neeraj' nofilenamecheck;
Starting Duplicate Db at 22-JUL-14
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 1042038784 bytes
Fixed Size
1341112 bytes
Variable Size
624953672 bytes
Database Buffers
411041792 bytes
Redo Buffers
4702208 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''NEERAJ'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set
db_unique_name =
''CLONEDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from
'/u01/neeraj/c-332109549-20140722-01';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''NEERAJ''
comment= ''Modified by RMAN duplicat
e''
scope=spfile
sql statement: alter system set db_unique_name =
''CLONEDB'' comment= ''Modified by RMAN
duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1042038784 bytes
Fixed Size
1341112 bytes
Variable Size
624953672 bytes
Database Buffers
411041792 bytes
Redo Buffers
4702208 bytes
Starting restore at 22-JUL-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/clonedb/control01.ctl
Finished restore at 22-JUL-14
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
set until scn 805128;
set newname for datafile 1 to
"/u01/clonedb/system01.dbf";
set newname for datafile 2 to
"/u01/clonedb/sysaux01.dbf";
set newname for datafile 3 to
"/u01/clonedb/undotbs01.dbf";
set newname for datafile 4 to
"/u01/clonedb/users01.dbf";
set newname for datafile 5 to
"/u01/clonedb/example01.dbf";
set newname for datafile 6 to
"/u01/clonedb/neer.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 22-JUL-14
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from
backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to
/u01/clonedb/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to
/u01/clonedb/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to
/u01/clonedb/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to
/u01/clonedb/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to
/u01/clonedb/example01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to
/u01/clonedb/neer.dbf
channel ORA_AUX_DISK_1: reading from backup piece
/u01/neeraj/02pe2dde_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/neeraj/02pe2dde_1_1
tag=TAG20140722T204222
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 22-JUL-14
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=853621761 file
name=/u01/clonedb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=853621761 file
name=/u01/clonedb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=853621761 file
name=/u01/clonedb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=853621761 file
name=/u01/clonedb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=853621762 file name=/u01/clonedb/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=853621762 file
name=/u01/clonedb/neer.dbf
contents of Memory Script:
{
set until scn 805128;
recover
clone database
delete archivelog ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 22-JUL-14
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default
destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=9
channel ORA_AUX_DISK_1: reading from backup piece
/u01/neeraj/05pe2dlu_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/neeraj/05pe2dlu_1_1
tag=TAG20140722T204653
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file
name=/u01/clonedb/admin/clonedb/CLONEDB/archivelog/2014_07_22/o1_mf_1_8_9
wx1fcx4_.arc thread=1 sequence=8
channel clone_default: deleting archived log(s)
archived log file
name=/u01/clonedb/admin/clonedb/CLONEDB/archivelog/2014_07_22/o1_mf_1_8_9
wx1fcx4_.arc RECID=1 STAMP=853621763
archived log file
name=/u01/clonedb/admin/clonedb/CLONEDB/archivelog/2014_07_22/o1_mf_1_9_9
wx1fcxn_.arc thread=1 sequence=9
channel clone_default: deleting archived log(s)
archived log file
name=/u01/clonedb/admin/clonedb/CLONEDB/archivelog/2014_07_22/o1_mf_1_9_9
wx1fcxn_.arc RECID=2 STAMP=853621763
media recovery complete, elapsed time: 00:00:03
Finished recover at 22-JUL-14
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''CLONEDB'' 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
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1042038784 bytes
Fixed Size
1341112 bytes
Variable Size
624953672 bytes
Database Buffers
411041792 bytes
Redo Buffers
4702208 bytes
sql statement: alter system set db_name =
''CLONEDB'' 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 1042038784 bytes
Fixed Size
1341112 bytes
Variable Size
624953672 bytes
Database Buffers
411041792 bytes
Redo Buffers
4702208 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE
"CLONEDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/clonedb/redo01.log' ) SIZE 50 M
REUSE,
GROUP 2 ( '/u01/clonedb/redo02.log' ) SIZE 50 M
REUSE,
GROUP 3 ( '/u01/clonedb/redo03.log' ) SIZE 50 M
REUSE
DATAFILE
'/u01/clonedb/system01.dbf'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/clonedb/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy
"/u01/clonedb/sysaux01.dbf",
"/u01/clonedb/undotbs01.dbf",
"/u01/clonedb/users01.dbf",
"/u01/clonedb/example01.dbf",
"/u01/clonedb/neer.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/clonedb/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/u01/clonedb/sysaux01.dbf RECID=1
STAMP=853621781
cataloged datafile copy
datafile copy file name=/u01/clonedb/undotbs01.dbf RECID=2
STAMP=853621781
cataloged datafile copy
datafile copy file name=/u01/clonedb/users01.dbf RECID=3
STAMP=853621781
cataloged datafile copy
datafile copy file name=/u01/clonedb/example01.dbf RECID=4
STAMP=853621781
cataloged datafile copy
datafile copy file name=/u01/clonedb/neer.dbf RECID=5
STAMP=853621781
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=853621781 file
name=/u01/clonedb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=853621781 file
name=/u01/clonedb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=853621781 file
name=/u01/clonedb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=853621781 file
name=/u01/clonedb/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=853621781 file
name=/u01/clonedb/neer.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 22-JUL-14
RMAN>
[oracle@localhost neeraj]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 22 21:11:48
2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
SQL> select name from v$database;
NAME
---------
CLONEDB
SQL> select status from v$instance;
STATUS
------------
OPEN
All The Best.... :)
Please share your suggestion if i need to
add something more in the post which i have missed.
