Friday, March 14, 2014

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>

No comments: