Oracle Recovery Manager (RMAN) should be one of the key technology in Disaster Recovery strategies for any company. If you are not using it, you should be. Combined with OEM Cloud Control 12c, it will make your DBA life so much easier. RMAN has the ability to duplicate a database from RMAN backup sets or from a active database. It can duplicate to either a remote host with similar or different file structure or same host with different file structure.
What I will be performing will be a backup based duplication.
Create a backup of the source database that you want to duplicate. (By default the backup set will reside in $ORACLE_HOME/dbs)
RMAN> rman target=/ RMAN> configure controlfile autobackup on; RMAN> backup database plus archivelog;
Create a pfile on the source database and copy it to the destination database later;
SQL> create pfile='/tmp/initDB11G' from spfile;
Create a password for both the source and destination database so that RMAN can connect to them later on.
# orapwd file=$ORACLE_HOME/dbs/orapwDB11G password=password
Create a entry in tnsnames.ora in the destination database. (We will run RMAN on the destination host to connect to the source DB)
DB11G = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(Host =DB11G_host)(Port = 1521)) (CONNECT_DATA = (SERVICE_NAME = DB11G)) )
Create all the required file structure from the destination host. Also, copy all the backup sets and pfile to $ORACLE_HOME/dbs on the destination host
On the destination database;
SQL> sqlplus / as sysdba
SQL> startup nomount;
SQL> exit
# rman
RMAN> connect target sys/password@DB11G
connected to target database: DB11G (DBID=12345678)
RMAN> connect auxiliary /
connected to auxiliary database: DB11G (not mounted)
RMAN> duplicate target database to DB11G
2> nofilenamecheck;
Starting Duplicate Db at 17-MAY-2013
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=292 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 1887350784 bytes
Fixed Size 2222600 bytes
Variable Size 889193976 bytes
Database Buffers 989855744 bytes
Redo Buffers 6078464 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DB11G '' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DB11G '' 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 = ''DB11G '' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DB11G '' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1887350784 bytes
Fixed Size 2222600 bytes
Variable Size 889193976 bytes
Database Buffers 989855744 bytes
Redo Buffers 6078464 bytes
Starting restore at 17-MAY-2013
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=292 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 /u01/app/oracle/product/11.2.0/dbs/c-1351804981-20130517-01
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbs/c-1351804981-20130517-01 tag=TAG20130517T144024
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/DB11G /control01.ctl
output file name=/u01/oradata/DB11G /control02.ctl
output file name=/u02/oradata/DB11G /control03.ctl
output file name=/u02/oradata/DB11G /control04.ctl
Finished restore at 17-MAY-2013
database mounted
contents of Memory Script:
{
set until scn 12574288802797;
set newname for datafile 1 to
"/u01/oradata/DB11G /system_01.dbf";
set newname for datafile 2 to
"/u01/oradata/DB11G /sysaux_01.dbf";
set newname for datafile 3 to
"/u02/oradata/DB11G /undo_01.dbf";
set newname for datafile 4 to
"/u02/oradata/DB11G /undo_02.dbf";
set newname for datafile 5 to
"/u01/oradata/DB11G /users_01.dbf";
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 17-MAY-2013
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/oradata/DB11G /system_01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/oradata/DB11G /sysaux_01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u02/oradata/DB11G /undo_01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u02/oradata/DB11G /undo_02.dbf
channel ORA_AUX_DISK_1: restoring datafile 00023 to /u01/oradata/DB11G /undo1_01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbs/08o9s7hu_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbs/08o9s7hu_1_1 tag=TAG20130517T143957
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 17-MAY-2013
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=815669681 file name=/u01/oradata/DB11G /system_01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=815669681 file name=/u01/oradata/DB11G /sysaux_01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=815669681 file name=/u02/oradata/DB11G /undo_01.dbf
datafile 4 switched to datafile copy
datafile 23 switched to datafile copy
input datafile copy RECID=23 STAMP=815669682 file name=/u01/oradata/DB11G /undo1_01.dbf
contents of Memory Script:
{
set until scn 12574288802797;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 17-MAY-2013
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=395
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbs/09o9s7in_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbs/09o9s7in_1_1 tag=TAG20130517T144023
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=/archive/archivelog/DB11G /T1S395R792783861.arc thread=1 sequence=395
channel clone_default: deleting archived log(s)
archived log file name=/archive/archivelog/DB11G /T1S395R792783861.arc RECID=306 STAMP=815669683
media recovery complete, elapsed time: 00:00:01
Finished recover at 17-MAY-2013
Oracle instance started
Total System Global Area 1887350784 bytes
Fixed Size 2222600 bytes
Variable Size 889193976 bytes
Database Buffers 989855744 bytes
Redo Buffers 6078464 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DB11G '' 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 = ''DB11G '' 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 1887350784 bytes
Fixed Size 2222600 bytes
Variable Size 889193976 bytes
Database Buffers 989855744 bytes
Redo Buffers 6078464 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DB11G " RESETLOGS ARCHIVELOG
MAXLOGFILES 255
MAXLOGMEMBERS 2
MAXDATAFILES 1022
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u02/oradata/DB11G /redolog_01a.rdo' ) SIZE 200 M REUSE,
GROUP 2 ( '/u02/oradata/DB11G /redolog_02a.rdo' ) SIZE 200 M REUSE,
GROUP 3 ( '/u02/oradata/DB11G /redolog_03a.rdo' ) SIZE 200 M REUSE
DATAFILE
'/u01/oradata/DB11G /system_01.dbf'
CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/oradata/DB11G /temp_01.dbf";
set newname for tempfile 2 to
"/u01/oradata/DB11G /temp_02.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u01/oradata/DB11G /sysaux_01.dbf",
"/u02/oradata/DB11G /undo_01.dbf",
"/u01/oradata/DB11G /undo1_01.dbf",
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/oradata/DB11G /temp_01.dbf in control file
renamed tempfile 2 to /u01/oradata/DB11G /temp_02.dbf in control file
cataloged datafile copy
datafile copy file name=/u01/oradata/DB11G /sysaux_01.dbf RECID=1 STAMP=815669697
datafile copy file name=/u01/oradata/DB11G /undo1_01.dbf RECID=22 STAMP=815669697
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=815669697 file name=/u01/oradata/DB11G /sysaux_01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=815669697 file name=/u02/oradata/DB11G /undo_01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 17-MAY-2013
Verify if the destination database is duplicated correctly.
#sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Fri May 17 14:55:13 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- DB11G
Now you get a duplicated test environment to play with!
Data Engineering Learnings 