Duplicate a database without connecting to a target database and a recovery catalog (11gR2 New Features)

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.