Using RMAN to Clone a Database into a Different Database on the Same Server

This was tested on Oracle 11.2. I had an input or target database as follows:

 

SQL> select name from v$database

  2  /

 

NAME

---------

ORCL1

 

SQL> l

  1  select tablespace_name, file_name

  2* from dba_data_files order by 1

SQL> /

 

TABLESPACE_NAME FILE_NAME

--------------- --------------------------------------

SYSAUX          /database/RMAN_test/ORCL1/sysaux1.dbf

SYSTEM          /database/RMAN_test/ORCL1/system1.dbf

UNDOTBS1        /database/RMAN_test/ORCL1/undo1.dbf

 

SQL>

 

I wanted to create an output or auxiliary or clone database called ORCL2. First I created the directory structures for it:

 

Solaris: pwd

/database/RMAN_test

Solaris: mkdir ORCL2

Solaris:

 

Next I added the auxiliary database to the server’s oratab:

 

Solaris: cat /var/opt/oracle/oratab|grep ORCL2

ORCL2:/oracle/app/oracle/product/11.2.0:N

Solaris:

 

Then I copied the target database’s parameter file to create the auxiliary database’s parameter file:

 

Solaris: pwd

/oracle/app/oracle/product/11.2.0/dbs

Solaris: cp initORCL1.ora initORCL2.ora

Solaris:

 

I customised the auxiliary database’s parameter file as shown below. Note the db_file_name_convert parameter, which tells Oracle where to put the files for the auxiliary database:

 

Solaris: cat initORCL2.ora

compatible=11.2.0

control_files=/database/RMAN_test/ORCL2/control1.ctl

db_block_size=2048

db_file_name_convert=('/database/RMAN_test/ORCL1','/database/RMAN_test/ORCL2')

db_name=ORCL2

db_recovery_file_dest=/database/RMAN_test/FRA

db_recovery_file_dest_size=5g

os_authent_prefix=''

processes=100

remote_login_passwordfile='EXCLUSIVE'

sga_target=500m

undo_management=auto

undo_retention=900

undo_tablespace='UNDOTBS1'

Solaris:

 

I set up a password file for the auxiliary database:

 

Solaris: . oraenv

ORACLE_SID = [ORCL2] ?

The Oracle base remains unchanged with value /oracle/app/oracle/product

Solaris: cd $ORACLE_HOME/dbs

Solaris: orapwd file=orapwORCL2 password=secret_password

Solaris: ls -l orapwORCL2

-rw-r-----   1 oracle   dba         1536 Mar 14 18:17 orapwORCL2

Solaris:

 

I created a test table in the target database:

 

Solaris: sqlplus system/secret_password

 

SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 14 17:14:31 2014

 

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

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> create table tab1 (col1 varchar2(30))

  2  /

 

Table created.

 

SQL> insert into tab1 values ('Andrew was here')

  2  /

 

1 row created.

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Solaris:

 

I backed up the target database:

 

Solaris: . oraenv

ORACLE_SID = [ORCL1] ?

The Oracle base remains unchanged with value /oracle/app/oracle/product

Solaris: rman target /

 

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Mar 14 18:29:32 2014

 

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

 

connected to target database: ORCL1 (DBID=1226554576)

 

RMAN> backup database;

 

Starting backup at 14-MAR-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=145 device type=DISK

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=/database/RMAN_test/ORCL1/system1.dbf

input datafile file number=00003 name=/database/RMAN_test/ORCL1/undo1.dbf

input datafile file number=00002 name=/database/RMAN_test/ORCL1/sysaux1.dbf

channel ORA_DISK_1: starting piece 1 at 14-MAR-14

channel ORA_DISK_1: finished piece 1 at 14-MAR-14

piece handle=/database/RMAN_test/FRA/ORCL1/backupset/2014_03_14/o1_mf_nnndf_TAG20140314T182942_9l6lnpnj_.bkp tag=TAG20140314T182942 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 14-MAR-14

channel ORA_DISK_1: finished piece 1 at 14-MAR-14

piece handle=/database/RMAN_test/FRA/ORCL1/backupset/2014_03_14/o1_mf_ncnnf_TAG20140314T182942_9l6lo8pk_.bkp tag=TAG20140314T182942 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 14-MAR-14

 

RMAN>

 

I opened the auxiliary database in NOMOUNT mode:

 

Solaris: . oraenv

ORACLE_SID = [ORCL1] ? ORCL2

The Oracle base remains unchanged with value /oracle/app/oracle/product

Solaris: sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 14 17:30:46 2014

 

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

 

Connected to an idle instance.

 

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area  521936896 bytes

Fixed Size                  2225704 bytes

Variable Size             159386072 bytes

Database Buffers          352321536 bytes

Redo Buffers                8003584 bytes

SQL>

 

I got the target and auxiliary listening. I think the only one which HAS to be listening is the auxiliary. This involved adding them to /var/opt/oracle/tnsnames.ora:

 

Solaris: pwd

/var/opt/oracle

Solaris: cat tnsnames.ora

################

# tnsnames.ora #

################

#

ORCL1 =

   (DESCRIPTION =

     (ADDRESS_LIST =

          (ADDRESS =

             (PROTOCOL = TCP)

             (HOST = sge-mktred-lab2)

             (PORT = 1529)

          )

     )

     (CONNECT_DATA =

      (SID = ORCL1)

     )

   )

ORCL2 =

   (DESCRIPTION =

     (ADDRESS_LIST =

          (ADDRESS =

             (PROTOCOL = TCP)

             (HOST = sge-mktred-lab2)

             (PORT = 1529)

          )

     )

     (CONNECT_DATA =

      (SID = ORCL2)

     )

   )

Etc

Etc

 

… adding them to $ORACLE_HOME/network/admin/listener.ora:

 

Solaris: pwd

/oracle/app/oracle/product/11.2.0/network/admin

Solaris: cat listener.ora

SID_LIST_LISTENER_11 =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = ORCL1)

      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0)

    )

    (SID_DESC =

      (SID_NAME = ORCL2)

      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0)

    )

Etc

Etc

 

… restarting the listener and testing a remote connection:

 

Solaris: lsnrctl reload listener_11

 

LSNRCTL for Solaris: Version 11.2.0.2.0 - Production on 14-MAR-2014 17:54:55

 

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sge-mktred-lab2)(PORT=1529)))

The command completed successfully

Solaris: sqlplus system/secret_password@orcl1

 

SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 14 17:55:24 2014

 

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

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL>

 

I archived the redo in the target database:

 

Solaris: . oraenv

ORACLE_SID = [ORCL1] ?

The Oracle base remains unchanged with value /oracle/app/oracle/product

Solaris: sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 14 18:39:52 2014

 

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

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> alter system archive log current

  2  /

 

System altered.

 

SQL>

 

Then I connected to the target and used the duplicate command to clone it into the auxiliary as follows:

 

Solaris: . oraenv

ORACLE_SID = [ORCL1] ?

The Oracle base remains unchanged with value /oracle/app/oracle/product

Solaris: rman target /

 

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Mar 14 18:41:18 2014

 

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

 

connected to target database: ORCL1 (DBID=1226554576)

 

RMAN> connect auxiliary sys/secret_password@orcl2

 

connected to auxiliary database: ORCL2 (not mounted)

 

RMAN> duplicate target database to orcl2;

 

Starting Duplicate Db at 14-MAR-14

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=46 device type=DISK

 

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     521936896 bytes

 

Fixed Size                     2225704 bytes

Variable Size                163580376 bytes

Database Buffers             348127232 bytes

Redo Buffers                   8003584 bytes

 

contents of Memory Script:

{

   sql clone "alter system set  db_name =

''ORCL1'' comment=

''Modified by RMAN duplicate'' scope=spfile";

   sql clone "alter system set  db_unique_name =

''ORCL2'' comment=

''Modified by RMAN duplicate'' scope=spfile";

   shutdown clone immediate;

   startup clone force nomount

   restore clone primary controlfile;

   alter clone database mount;

}

executing Memory Script

 

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

 

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

 

Oracle instance shut down

 

Oracle instance started

 

Total System Global Area     521936896 bytes

 

Fixed Size                     2225704 bytes

Variable Size                163580376 bytes

Database Buffers             348127232 bytes

Redo Buffers                   8003584 bytes

 

Starting restore at 14-MAR-14

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=46 device type=DISK

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /database/RMAN_test/FRA/ORCL1/backupset/2014_03_14/o1_mf_ncnnf_TAG20140314T182942_9l6lo8pk_.bkp

channel ORA_AUX_DISK_1: piece handle=/database/RMAN_test/FRA/ORCL1/backupset/2014_03_14/o1_mf_ncnnf_TAG20140314T182942_9l6lo8pk_.bkp tag=TAG20140314T182942

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/database/RMAN_test/ORCL2/control1.ctl

Finished restore at 14-MAR-14

 

database mounted

 

contents of Memory Script:

{

   set until scn  400755;

   set newname for datafile  1 to

"/database/RMAN_test/ORCL2/system1.dbf";

   set newname for datafile  2 to

"/database/RMAN_test/ORCL2/sysaux1.dbf";

   set newname for datafile  3 to

"/database/RMAN_test/ORCL2/undo1.dbf";

   restore

   clone database

   ;

}

executing Memory Script

 

executing command: SET until clause

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting restore at 14-MAR-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 /database/RMAN_test/ORCL2/system1.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /database/RMAN_test/ORCL2/sysaux1.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /database/RMAN_test/ORCL2/undo1.dbf

channel ORA_AUX_DISK_1: reading from backup piece /database/RMAN_test/FRA/ORCL1/backupset/2014_03_14/o1_mf_nnndf_TAG20140314T182942_9l6lnpnj_.bkp

channel ORA_AUX_DISK_1: piece handle=/database/RMAN_test/FRA/ORCL1/backupset/2014_03_14/o1_mf_nnndf_TAG20140314T182942_9l6lnpnj_.bkp tag=TAG20140314T182942

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25

Finished restore at 14-MAR-14

 

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

 

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=842208178 file name=/database/RMAN_test/ORCL2/system1.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=5 STAMP=842208178 file name=/database/RMAN_test/ORCL2/sysaux1.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=842208178 file name=/database/RMAN_test/ORCL2/undo1.dbf

 

contents of Memory Script:

{

   set until scn  400755;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

 

executing command: SET until clause

 

Starting recover at 14-MAR-14

using channel ORA_AUX_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 18 is already on disk as file /database/RMAN_test/FRA/ORCL1/archivelog/2014_03_14/o1_mf_1_18_9l6m849g_.arc

archived log file name=/database/RMAN_test/FRA/ORCL1/archivelog/2014_03_14/o1_mf_1_18_9l6m849g_.arc thread=1 sequence=18

media recovery complete, elapsed time: 00:00:01

Finished recover at 14-MAR-14

Oracle instance started

 

Total System Global Area     521936896 bytes

 

Fixed Size                     2225704 bytes

Variable Size                167774680 bytes

Database Buffers             343932928 bytes

Redo Buffers                   8003584 bytes

 

contents of Memory Script:

{

   sql clone "alter system set  db_name =

''ORCL2'' 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 =  ''ORCL2'' 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     521936896 bytes

 

Fixed Size                     2225704 bytes

Variable Size                167774680 bytes

Database Buffers             343932928 bytes

Redo Buffers                   8003584 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL2" RESETLOGS ARCHIVELOG

  MAXLOGFILES      3

  MAXLOGMEMBERS      3

  MAXDATAFILES       30

  MAXINSTANCES     1

  MAXLOGHISTORY      292

LOGFILE

  GROUP  1  SIZE 50 M ,

  GROUP  2  SIZE 50 M

DATAFILE

  '/database/RMAN_test/ORCL2/system1.dbf'

CHARACTER SET WE8ISO8859P1

 

contents of Memory Script:

{

   set newname for tempfile  1 to

"/database/RMAN_test/ORCL2/temp1.dbf";

   switch clone tempfile all;

   catalog clone datafilecopy  "/database/RMAN_test/ORCL2/sysaux1.dbf",

"/database/RMAN_test/ORCL2/undo1.dbf";

   switch clone datafile all;

}

executing Memory Script

 

executing command: SET NEWNAME

 

renamed tempfile 1 to /database/RMAN_test/ORCL2/temp1.dbf in control file

 

cataloged datafile copy

datafile copy file name=/database/RMAN_test/ORCL2/sysaux1.dbf RECID=1 STAMP=842208205

cataloged datafile copy

datafile copy file name=/database/RMAN_test/ORCL2/undo1.dbf RECID=2 STAMP=842208206

 

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=842208205 file name=/database/RMAN_test/ORCL2/sysaux1.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=842208206 file name=/database/RMAN_test/ORCL2/undo1.dbf

 

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

 

database opened

Finished Duplicate Db at 14-MAR-14

 

RMAN>

 

Finally, I logged into the auxiliary database, made sure that the datafiles were in the correct place and checked the contents of the test table, which I created in the target earlier:

 

Solaris: . oraenv

ORACLE_SID = [ORCL1] ? ORCL2

The Oracle base remains unchanged with value /oracle/app/oracle/product

Solaris: sqlplus system/secret_password

 

SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 14 18:50:30 2014

 

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

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> select tablespace_name, file_name

  2  from dba_data_files

  3  order by 1

  4  /

 

TABLESPACE_NAME  FILE_NAME

---------------- --------------------------------------

SYSAUX           /database/RMAN_test/ORCL2/sysaux1.dbf

SYSTEM           /database/RMAN_test/ORCL2/system1.dbf

UNDOTBS1         /database/RMAN_test/ORCL2/undo1.dbf

 

SQL> select * from tab1

  2  /

 

COL1

------------------------------

Andrew was here

 

SQL>