RMAN
duplicate command is a very useful automated way to create a copy/clone of a
database rather than manually creating a clone of a database. Duplicate
database can be created on the same server were source database is running or
on a different server. Duplicate database can be created using RMAN backup of source database,
or directly from the live database.
or directly from the live database.
Here
I will discuss step by step method of creating a duplicate database on the same
server where source database is running. Steps of creating a clone on a
different server are also same. Moreover, steps are also same whether we are on
UNIX based platforms or on Windows.
I performed this duplicate on Windows platform.
Target database (form where database is being copied) is DEVDB and duplicate
database name is DUPDB.
1)
If using a different server for duplicate database, Install same Oracle software (including patches). Also make sure listener is running on the server.
If using a different server for duplicate database, Install same Oracle software (including patches). Also make sure listener is running on the server.
2)
Create password file
If you are on windows, use oradim.exe to create password file and OS level service
Create password file
If you are on windows, use oradim.exe to create password file and OS level service
C:\Users\salmqure>set
oracle_sid=DUPDB
C:\Users\salmqure>oradim
-new -sid DUPDB -intpwd oracle
Instance
created.
|
If
Linux, use orapwd to create the password file
$export
ORACLE_SID=DUPDB
$cd
$ORACLE_HOME/dbs
$orapwd
file=orapwDUPDB password=oracle
|
3)
Create initDUPDB.ora file under $ORACLE_HOME/dbs for Linux and %ORACLE_HOME%\database directory for Windows platform. Add following parameters in this file (modify paths to match your OS platform)
Create initDUPDB.ora file under $ORACLE_HOME/dbs for Linux and %ORACLE_HOME%\database directory for Windows platform. Add following parameters in this file (modify paths to match your OS platform)
*.audit_file_dest='d:\oracle\admin\DUPDB\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='d:\oracle\oradata\DUPDB\control01.ctl','d:\oracle\fast_recovery_area\DUPDB\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DUPDB'
*.db_recovery_file_dest='d:\oracle\fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='d:\oracle'
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=DUPDBXDB)'
*.open_cursors=300
*.pga_aggregate_target=288358400
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.memory_target=13213106176
*.undo_tablespace='UNDOTBS1'
*.db_create_file_dest='d:\oracle\oradata'
|
4)
Create following directories on duplicate database hot; as specified in the parameters of init file above. Change paths if you are using Linux
Create following directories on duplicate database hot; as specified in the parameters of init file above. Change paths if you are using Linux
d:\oracle\admin\DUPDB\adump
d:\oracle\oradata\DUPDB
d:\oracle\fast_recovery_area\DUPDB
|
5)
On both duplicate as well as target servers/database homes, edit TNSNAMES.ORA and create 2 TNS entries. One will
resolve to the target database in order to connect during execution of
“duplicate” command, and other will resolve to the duplicate database.
DEVDB
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <SOURCE_HOST_NAME>)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DEVDB)
)
)
DUPDB
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <DUPLICATE_HOST_NAME>)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DUPDB)
)
)
|
6)
On duplicate database host, register duplicate database statically with the listener by adding following lines in listener.ora file
On duplicate database host, register duplicate database statically with the listener by adding following lines in listener.ora file
SID_LIST_LISTENER
=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DUPDB)
(ORACLE_HOME = d:\oracle\product\11.2.0\dbhome_1)
(SID_NAME = DUPDB)
)
)
|
7)
If your source database is running in archived log mode, skip this step and go to next step. Otherwise stop your source database and start in mount mode.
C:\>set
oracle_sid=DEVDB
SQL> shutdown immediate
SQL>
startup mount
|
8)
Start duplicate database in nomount mode (using pfile created above)
Start duplicate database in nomount mode (using pfile created above)
C:\>set
oracle_sid=DUPDB
SQL> startup nomount |
9)
Start RMAN and connect to target and duplicate (auxiliary) and execute “duplicate command”. You should know SYS user password of source database. Duplicate database password is “oracle” in this case.
Start RMAN and connect to target and duplicate (auxiliary) and execute “duplicate command”. You should know SYS user password of source database. Duplicate database password is “oracle” in this case.
C:\Users\salmqure>rman
target sys/salman@devdb auxiliary sys/oracle@dupdb
Recovery
Manager: Release 11.2.0.3.0 - Production on Wed Jul 1 09:46:08 2015
Copyright
(c) 1982, 2011, Oracle and/or its affiliates.
All rights reserved.
connected
to target database: DEVDB (DBID=3275747663)
connected
to auxiliary database: DUPDB (not mounted)
RMAN>
duplicate target database to DUPDB from active database;
D:\Users\salmqure>rman
target sys/oracle@DEVDB auxiliary sys/oracle@dupdb
Recovery
Manager: Release 11.2.0.3.0 - Production on Wed Jul 1 09:53:01 2015
Copyright
(c) 1982, 2011, Oracle and/or its affiliates.
All rights reserved.
connected
to target database: DEVDB (DBID=3275747663)
connected
to auxiliary database: DUPDB (not mounted)
RMAN>
duplicate target database to DUPDB from active database;
Starting
Duplicate Db at 01-JUL-15
using
target database control file instead of recovery catalog
allocated
channel: ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: SID=129 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 13161414656 bytes
Fixed
Size 2267584 bytes
Variable
Size 2147485248 bytes
Database
Buffers 11005853696 bytes
Redo
Buffers 5808128 bytes
contents
of Memory Script:
{
sql clone "alter system set db_name =
''DEVDB'' comment=
''Modified by RMAN duplicate''
scope=spfile";
sql clone "alter system set db_unique_name =
''DUPDB'' comment=
''Modified by RMAN duplicate''
scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile
auxiliary format
'D:\ORACLE\ORADATA\DUPDB\CONTROL01.CTL';
restore clone controlfile to
'D:\ORACLE\FAST_RECOVERY_AREA\DUPDB\CONTROL02.CTL' from
'D:\ORACLE\ORADATA\DUPDB\CONTROL01.CTL';
alter clone database mount;
}
executing
Memory Script
sql
statement: alter system set db_name
= ''DEVDB'' comment= ''Modified by
RMAN duplicate'' scope=spfile
sql
statement: alter system set
db_unique_name = ''DUPDB''
comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle
instance shut down
Oracle
instance started
Total
System Global Area 13161414656 bytes
Fixed
Size 2267584 bytes
Variable
Size 2147485248 bytes
Database
Buffers 11005853696 bytes
Redo
Buffers 5808128 bytes
Starting
backup at 01-JUL-15
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: SID=191 device type=DISK
channel
ORA_DISK_1: starting datafile copy
copying
current control file
output
file name=D:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFDEVDB.ORA tag=TAG20150701T095343
RECID=4 STAMP=883907624
channel
ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished
backup at 01-JUL-15
Starting
restore at 01-JUL-15
allocated
channel: ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: SID=63 device type=DISK
channel
ORA_AUX_DISK_1: copied control file copy
Finished
restore at 01-JUL-15
database
mounted
contents
of Memory Script:
{
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
backup as copy reuse
datafile
1 auxiliary format new
datafile
2 auxiliary format new
datafile
3 auxiliary format new
datafile
4 auxiliary format new
;
sql 'alter system archive log current';
}
executing
Memory Script
executing
command: SET NEWNAME
executing
command: SET NEWNAME
executing
command: SET NEWNAME
executing
command: SET NEWNAME
Starting
backup at 01-JUL-15
using
channel ORA_DISK_1
channel
ORA_DISK_1: starting datafile copy
input
datafile file number=00001 name=D:\ORACLE\ORADATA\DEVDB\SYSTEM01.DBF
output
file
name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_SYSTEM_DATA_D-DEVDB_I-3275747663_TS-SYSTEM_FNO-1_07QAUN1H_.DBF
tag=TAG20150701T095353
channel
ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel
ORA_DISK_1: starting datafile copy
input
datafile file number=00002 name=D:\ORACLE\ORADATA\DEVDB\SYSAUX01.DBF
output
file name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_SYSAUX_DATA_D-DEVDB_I-3275747663_TS-SYSAUX_FNO-2_08QAUN2A_.DBF
tag=TAG20150701T095353
channel
ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel
ORA_DISK_1: starting datafile copy
input
datafile file number=00003 name=D:\ORACLE\ORADATA\DEVDB\UNDOTBS01.DBF
output
file
name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_UNDOTBS1_DATA_D-DEVDB_I-3275747663_TS-UNDOTBS1_FNO-3_09QAUN33_.DBF
tag=TAG20150701T095353
channel
ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel
ORA_DISK_1: starting datafile copy
input
datafile file number=00004 name=D:\ORACLE\ORADATA\DEVDB\USERS01.DBF
output
file
name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_USERS_DATA_D-DEVDB_I-3275747663_TS-USERS_FNO-4_0AQAUN36_.DBF
tag=TAG20150701T095353
channel
ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished
backup at 01-JUL-15
sql
statement: alter system archive log current
contents
of Memory Script:
{
backup as copy reuse
archivelog like "D:\ORACLE\ARCHIVE\DEVDB\ARC0000000009_0880976656.0001"
auxiliary format
"D:\ORACLE\FAST_RECOVERY_AREA\DUPDB\ARCHIVELOG\2015_07_01\O1_MF_1_9_%U_.ARC" ;
catalog clone recovery area;
switch clone datafile all;
}
executing
Memory Script
Starting
backup at 01-JUL-15
using
channel ORA_DISK_1
channel
ORA_DISK_1: starting archived log copy
input
archived log thread=1 sequence=9 RECID=2 STAMP=883907688
output
file
name=D:\ORACLE\FAST_RECOVERY_AREA\DUPDB\ARCHIVELOG\2015_07_01\O1_MF_1_9_ARCH_D-DEVDB_ID-3275747663_S-9_T-1_A-880976656_0BQAUN38_.ARC
RECID=0 STAMP=0
channel
ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished
backup at 01-JUL-15
searching
for all files in the recovery area
List
of Files Unknown to the Database
=====================================
File
Name:
D:\ORACLE\FAST_RECOVERY_AREA\DUPDB\ARCHIVELOG\2015_07_01\O1_MF_1_9_ARCH_D-DEVDB_ID-3275747663_S-9_T-1_A-880976656_0BQAUN38_.ARC
cataloging
files...
cataloging
done
List
of Cataloged Files
=======================
File
Name: D:\ORACLE\FAST_RECOVERY_AREA\DUPDB\ARCHIVELOG\2015_07_01\O1_MF_1_9_ARCH_D-DEVDB_ID-3275747663_S-9_T-1_A-880976656_0BQAUN38_.ARC
List
of files in Recovery Area not managed by the database
==========================================================
File
Name: D:\ORACLE\FAST_RECOVERY_AREA\DUPDB\CONTROL02.CTL
RMAN-07526: Reason: File is not an Oracle
Managed File
number
of files not managed by recovery area is 1, totaling 9.28MB
datafile
1 switched to datafile copy
input
datafile copy RECID=4 STAMP=883907689 file name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_SYSTEM_DATA_D-DEVDB_I-3275747663_TS-SYSTEM_FNO-1_07QAUN1H_.DBF
datafile
2 switched to datafile copy
input
datafile copy RECID=5 STAMP=883907689 file
name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_SYSAUX_DATA_D-DEVDB_I-3275747663_TS-SYSAUX_FNO-2_08QAUN2A_.DBF
datafile
3 switched to datafile copy
input
datafile copy RECID=6 STAMP=883907689 file
name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_UNDOTBS1_DATA_D-DEVDB_I-3275747663_TS-UNDOTBS1_FNO-3_09QAUN33_.DBF
datafile
4 switched to datafile copy
input
datafile copy RECID=7 STAMP=883907689 file
name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_USERS_DATA_D-DEVDB_I-3275747663_TS-USERS_FNO-4_0AQAUN36_.DBF
contents
of Memory Script:
{
set until scn 1164410;
recover
clone database
delete archivelog
;
}
executing
Memory Script
executing
command: SET until clause
Starting
recover at 01-JUL-15
using
channel ORA_AUX_DISK_1
starting
media recovery
archived
log for thread 1 with sequence 9 is already on disk as file D:\ORACLE\FAST_RECOVERY_AREA\DUPDB\ARCHIVELOG\2015_07_01\O1_MF_1_9_ARCH_D-DEVDB_ID-3275747663_S-9_T-1_A-880976656_0BQAUN38_.ARC
archived
log file
name=D:\ORACLE\FAST_RECOVERY_AREA\DUPDB\ARCHIVELOG\2015_07_01\O1_MF_1_9_ARCH_D-DEVDB_ID-3275747663_S-9_T-1_A-880976656_0BQAUN38_.ARC
thread=1 sequence=9
media
recovery complete, elapsed time: 00:00:01
Finished
recover at 01-JUL-15
Oracle
instance started
Total
System Global Area 13161414656 bytes
Fixed
Size 2267584 bytes
Variable
Size 2147485248 bytes
Database
Buffers 11005853696 bytes
Redo
Buffers 5808128 bytes
contents
of Memory Script:
{
sql clone "alter system set db_name =
''DUPDB'' 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
= ''DUPDB'' 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 13161414656 bytes
Fixed
Size 2267584 bytes
Variable
Size 2147485248 bytes
Database
Buffers 11005853696 bytes
Redo
Buffers 5808128 bytes
sql
statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB" RESETLOGS
ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP
1 SIZE 50 M ,
GROUP
2 SIZE 50 M ,
GROUP
3 SIZE 50 M
DATAFILE
'D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_SYSTEM_DATA_D-DEVDB_I-3275747663_TS-SYSTEM_FNO-1_07QAUN1H_.DBF'
CHARACTER SET WE8MSWIN1252
contents
of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy
"D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_SYSAUX_DATA_D-DEVDB_I-3275747663_TS-SYSAUX_FNO-2_08QAUN2A_.DBF",
"D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_UNDOTBS1_DATA_D-DEVDB_I-3275747663_TS-UNDOTBS1_FNO-3_09QAUN33_.DBF",
"D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_USERS_DATA_D-DEVDB_I-3275747663_TS-USERS_FNO-4_0AQAUN36_.DBF";
switch clone datafile all;
}
executing
Memory Script
executing
command: SET NEWNAME
renamed
tempfile 1 to D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_TEMP_%U_.TMP in control
file
cataloged
datafile copy
datafile
copy file
name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_SYSAUX_DATA_D-DEVDB_I-3275747663_TS-SYSAUX_FNO-2_08QAUN2A_.DBF
RECID=1 STAMP=883907722
cataloged
datafile copy
datafile
copy file
name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_UNDOTBS1_DATA_D-DEVDB_I-3275747663_TS-UNDOTBS1_FNO-3_09QAUN33_.DBF
RECID=2 STAMP=883907722
cataloged
datafile copy
datafile
copy file name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_USERS_DATA_D-DEVDB_I-3275747663_TS-USERS_FNO-4_0AQAUN36_.DBF
RECID=3 STAMP=883907722
datafile
2 switched to datafile copy
input
datafile copy RECID=1 STAMP=883907722 file
name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_SYSAUX_DATA_D-DEVDB_I-3275747663_TS-SYSAUX_FNO-2_08QAUN2A_.DBF
datafile
3 switched to datafile copy
input
datafile copy RECID=2 STAMP=883907722 file
name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_UNDOTBS1_DATA_D-DEVDB_I-3275747663_TS-UNDOTBS1_FNO-3_09QAUN33_.DBF
datafile
4 switched to datafile copy
input
datafile copy RECID=3 STAMP=883907722 file
name=D:\ORACLE\ORADATA\DUPDB\DATAFILE\O1_MF_USERS_DATA_D-DEVDB_I-3275747663_TS-USERS_FNO-4_0AQAUN36_.DBF
contents
of Memory Script:
{
Alter clone database open resetlogs;
}
executing
Memory Script
database
opened
Finished
Duplicate Db at 01-JUL-15
RMAN>
|
Important Points to be
Noted
If we don’t use “*.db_create_file_dest='d:\oracle\oradata'” parameter in the init file, duplicate datafiles would be created under same directory structure as source database and we should make sure that same directory structure exists on the duplicate database host to have duplicate command completed successfully
If we don’t use “*.db_create_file_dest='d:\oracle\oradata'” parameter in the init file, duplicate datafiles would be created under same directory structure as source database and we should make sure that same directory structure exists on the duplicate database host to have duplicate command completed successfully
If
duplicate database is being created on the same server as source database and
we don’t use “*.db_create_file_dest='d:\oracle\oradata'”, duplicate command
will try to overwrite existing datafiles of source database and would fail with
errors as follows
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-03002:
failure of Duplicate Db command at 07/01/2015 10:44:51
RMAN-05501:
aborting duplication of target database
RMAN-05001:
auxiliary file name D:\ORACLE\ORADATA\DEVDB\USERS01.DBF conflicts with a file
used by the target database
RMAN-05001:
auxiliary file name D:\ORACLE\ORADATA\DEVDB\UNDOTBS01.DBF conflicts with a
file used by the target database
RMAN-05001:
auxiliary file name D:\ORACLE\ORADATA\DEVDB\SYSAUX01.DBF conflicts with a
file used by the target database
RMAN-05001:
auxiliary file name D:\ORACLE\ORADATA\DEVDB\SYSTEM01.DBF conflicts with a
file used by the target database
|
If any of the required directories is not created manually as mentioned in step
4 above, duplicate command would fail with the error messages similar to the
following
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-03002:
failure of Duplicate Db command at 07/01/2015 09:51:09
RMAN-05501:
aborting duplication of target database
RMAN-03015:
error occurred in stored script Memory Script
RMAN-03009:
failure of backup command on ORA_DISK_1 channel at 07/01/2015 09:51:09
ORA-17628:
Oracle error 19505 returned by remote Oracle server
|
If
we don’t want to use Oracle Managed Files (OMF) for duplicate database and want
to specify our own paths and names for the datafiles and redo log files, we can
remove “*.db_create_file_dest='d:\oracle\oradata'” parameter from the init
file, but our duplicate command should use following syntax to specify the
locations and names of the duplicate database datafiles and log files. For SET NEWNAME, we can get (data)file# from v$database of source database and then mention its
path on the duplicate database host.
RMAN>
run{
set
newname for datafile 1 to 'D:\oracle\oradata\DUPDB\system01.dbf';
set
newname for datafile 2 to 'D:\oracle\oradata\DUPDB\sysaux01.dbf';
set
newname for datafile 3 to 'Dc:\oracle\oradata\DUPDB\undotbs01.dbf';
set
newname for datafile 4 to 'D:\oracle\oradata\DUPDB\users01.dbf';
set
newname for tempfile 1 to 'D:\oracle\oradata\DUPDB\temp01.dbf';
duplicate
target database to DUPDB from active database
logfile
group 1('D:\oracle\oradata\DUPDB\redo01.log') size 100m,
group
2('D:\oracle\oradata\DUPDB\redo02.log') size 100m;
}
|
No comments:
Post a Comment