This is a practical sample on cloning a database using the "CLONE FROM ACTIVE DATABASE" option.
In this sample we will clone the database "DBASDB" which is running on a node called "DPPUDBSO01". The clone database "DBASCPY" will be created on another host "DPPUDBSO02".
The source database DBASDB is a 11gR2 database installed on a solaris system.
The datafiles and control files are spreaded over 3 different filesystems (/u10/dbasdb/oradata, /u11/dbasdb/oradata and /u12/dbasdb/oradata), the database is runing in archive mode and the archives are located in /u60/dbasdb/arch and /u70/dbasdb/arch.
The filestructure for the cloned database will be different from the target database, datafiles controlfiles and redolog will be placed in /u20/dbascpy/oradata, and the archive will be placed in /u61/dbascpy/arch en /u71/dbascpy/arch.
Before we can start the duplicate process we have to perform some steps to prepare the environment.
Prepare the envrionment
Create the folders for the DBASCPY database
Create the folders for the DBASCPY database
mkdir -p /u20/dbascpy/oradata
Create and start a listener process for the DBASCPY database
my listener is named LISTENER_DBASCPY so i start it by:
lsnrctl start LISTENER_DBASCPY
Create a password file for the DBASCPY database
orapwd file=$ORACLE_HOME/dbs/orapwDBASCPY password=??? force=y nosysdba=n
Test the connectivity
From both nodes, you must be able to connect to both databases.On dppudbso02 you must be able to connect to the primary database
sqlplus sys@dbasdb as sysdba
On dppudbso01 you must be able to connect to the clone database
sqlplus sys@dbascpy as sysdba
Create a dummy init.ora
My $ORACLE_HOME/dbs/initDBASCPY.ora file contains only the following entry:
DB_NAME=dummy
Clone the database
There are two basic steps, first you need to start the clone database (DBASCPY) in NOMOUNT mode, next you start a rman process connecting to both databases and start the duplicate database process.
Start the clone database in NOMOUNT mode
sqlplus sys@DBASCPY as sysdba
SQL>startup nomount
Start RMAN and connect to both databases
This is done on the DPPUDBSO02 node.
rman target=sys@dbasdb.world auxiliary=sys@dbascpy.world
Duplicatie the database
run {
SET NEWNAME FOR DATABASE TO '/u20/dbascpy/oradata/%U';
DUPLICATE TARGET DATABASE TO DBASCPY
FROM ACTIVE DATABASE
PASSWORD FILE
SPFILE
SET log_archive_dest_1='location=/u61/dbascpy/arch'
SET log_archive_dest_2='location=/u71/dbascpy/arch'
SET control_files='/u20/dbascpy/oradata/control01.ctl'
RESET audit_file_dest
NOFILENAMECHECK
LOGFILE
GROUP 1 ('/u20/dbascpy/oradata/redo01a.log','/u20/dbascpy/oradata/redo01b.log') size 10M REUSE,
GROUP 2 ('/u20/dbascpy/oradata/redo02a.log','/u20/dbascpy/oradata/redo02b.log') size 10M REUSE
;
}
Because we have another filestructure on our clone-server, we need to set the right location for the archive destinations and controle files.
I had also an issue with the usage of the audit_file_dest. This parameter was set on the primary database, but that location did not exists on my clone database environment, which caused the following error : RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file
This was solved by using the RESET audit_file_dest parameter.
This was solved by using the RESET audit_file_dest parameter.
The complete output of the DUPLICATE process is listed here below
rman target=sys@dbasdb.world auxiliary=sys@dbascpy.world
Recovery Manager: Release 11.2.0.2.0 - Production on Mon Oct 18 08:56:48 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: DBASDB (DBID=1330205033)
auxiliary database Password:
connected to auxiliary database: DUMMY (not mounted)
RMAN> run {
2> SET NEWNAME FOR DATABASE TO '/u20/dbascpy/oradata/%U';
DUPLICATE TARGET DATABASE TO DBASCPY
FROM ACTIVE DATABASE
PASSWORD FILE
SPFILE
SET log_archive_dest_1='location=/u61/dbascpy/arch'
SET log_archive_dest_2='location=/u71/dbascpy/arch'
SET control_files='/u20/dbascpy/oradata/control01.ctl'
RESET audit_file_dest
NOFILENAMECHECK
LOGFILE
GROUP 1 ('/u20/dbascpy/oradata/redo01a.log','/u20/dbascpy/oradata/redo01b.log') size 10M REUSE,
GROUP 2 ('/u20/dbascpy/oradata/redo02a.log','/u20/dbascpy/oradata/redo02b.log') size 10M REUSE
;
}
3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16>
executing command: SET NEWNAME
Starting Duplicate Db at 18-OCT-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=137 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u00/app/oracle/product/11.2.0.2/dbs/orapwDBASDB' auxiliary format
'/u00/app/oracle/product/11.2.0.2/dbs/orapwDBASCPY' targetfile
'/u02/dbasdb/pfile/spfileDBASDB.ora' auxiliary format
'/u00/app/oracle/product/11.2.0.2/dbs/spfileDBASCPY.ora' ;
sql clone "alter system set spfile= ''/u00/app/oracle/product/11.2.0.2/dbs/spfileDBASCPY.ora''";
}
executing Memory Script
Starting backup at 18-OCT-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=110 device type=DISK
Finished backup at 18-OCT-10
sql statement: alter system set spfile= ''/u00/app/oracle/product/11.2.0.2/dbs/spfileDBASCPY.ora''
contents of Memory Script:
{
sql clone "alter system set db_name =
''DBASCPY'' comment=
''duplicate'' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''location=/u61/dbascpy/arch'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''location=/u71/dbascpy/arch'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u20/dbascpy/oradata/control01.ctl'' comment=
'''' scope=spfile";
sql clone "alter system reset audit_file_dest scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''DBASCPY'' comment= ''duplicate'' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''location=/u61/dbascpy/arch'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''location=/u71/dbascpy/arch'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u20/dbascpy/oradata/control01.ctl'' comment= '''' scope=spfile
sql statement: alter system reset audit_file_dest scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 4277059584 bytes
Fixed Size 2165168 bytes
Variable Size 2298484304 bytes
Database Buffers 1962934272 bytes
Redo Buffers 13475840 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DBASDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DBASCPY'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/u20/dbascpy/oradata/control01.ctl';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''DBASDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DBASCPY'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 4277059584 bytes
Fixed Size 2165168 bytes
Variable Size 2298484304 bytes
Database Buffers 1962934272 bytes
Redo Buffers 13475840 bytes
Starting backup at 18-OCT-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u00/app/oracle/product/11.2.0.2/dbs/snapcf_DBASDB.f tag=TAG20101018T085757 RECID=7 STAMP=732704279
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-OCT-10
database mounted
contents of Memory Script:
{
set newname for datafile 1 to
"/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSTEM_FNO-1";
set newname for datafile 2 to
"/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSAUX_FNO-2";
set newname for datafile 3 to
"/u20/dbascpy/oradata/data_D-DBASDB_TS-UNDOTBS1_FNO-3";
set newname for datafile 4 to
"/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSTEM_TOOLS_FNO-4";
set newname for datafile 5 to
"/u20/dbascpy/oradata/data_D-DBASDB_TS-USERS_FNO-5";
backup as copy reuse
datafile 1 auxiliary format
"/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSTEM_FNO-1" datafile
2 auxiliary format
"/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSAUX_FNO-2" datafile
3 auxiliary format
"/u20/dbascpy/oradata/data_D-DBASDB_TS-UNDOTBS1_FNO-3" datafile
4 auxiliary format
"/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSTEM_TOOLS_FNO-4" datafile
5 auxiliary format
"/u20/dbascpy/oradata/data_D-DBASDB_TS-USERS_FNO-5" ;
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
executing command: SET NEWNAME
Starting backup at 18-OCT-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u10/dbasdb/oradata/undotbs01.dbf
output file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-UNDOTBS1_FNO-3 tag=TAG20101018T085807
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=/u10/dbasdb/oradata/sysaux01.dbf
output file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSAUX_FNO-2 tag=TAG20101018T085807
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u10/dbasdb/oradata/system01.dbf
output file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSTEM_FNO-1 tag=TAG20101018T085807
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u12/dbasdb/oradata/users01.dbf
output file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-USERS_FNO-5 tag=TAG20101018T085807
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u12/dbasdb/oradata/system_tools01.dbf
output file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSTEM_TOOLS_FNO-4 tag=TAG20101018T085807
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18-OCT-10
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/u60/dbasdb/arch/DBASDB_76_1_732102377.arc" auxiliary format
"/u71/dbascpy/arch/DBASDB_76_1_732102377.arc" archivelog like
"/u70/dbasdb/arch/DBASDB_76_1_732102377.arc" auxiliary format
"/u71/dbascpy/arch/DBASDB_76_1_732102377.arc" ;
catalog clone archivelog "/u71/dbascpy/arch/DBASDB_76_1_732102377.arc";
catalog clone archivelog "/u71/dbascpy/arch/DBASDB_76_1_732102377.arc";
switch clone datafile all;
}
executing Memory Script
Starting backup at 18-OCT-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=76 RECID=51 STAMP=732704373
output file name=/u71/dbascpy/arch/DBASDB_76_1_732102377.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=76 RECID=52 STAMP=732704373
output file name=/u71/dbascpy/arch/DBASDB_76_1_732102377.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
Finished backup at 18-OCT-10
cataloged archived log
archived log file name=/u71/dbascpy/arch/DBASDB_76_1_732102377.arc RECID=51 STAMP=732704380
cataloged archived log
archived log file name=/u71/dbascpy/arch/DBASDB_76_1_732102377.arc RECID=52 STAMP=732704380
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=732704380 file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSTEM_FNO-1
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=732704380 file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSAUX_FNO-2
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=732704380 file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-UNDOTBS1_FNO-3
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=732704380 file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSTEM_TOOLS_FNO-4
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=732704380 file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-USERS_FNO-5
contents of Memory Script:
{
set until scn 27499550644;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 18-OCT-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=170 device type=DISK
starting media recovery
archived log for thread 1 with sequence 76 is already on disk as file /u71/dbascpy/arch/DBASDB_76_1_732102377.arc
archived log file name=/u71/dbascpy/arch/DBASDB_76_1_732102377.arc thread=1 sequence=76
media recovery complete, elapsed time: 00:00:02
Finished recover at 18-OCT-10
Oracle instance started
Total System Global Area 4277059584 bytes
Fixed Size 2165168 bytes
Variable Size 2298484304 bytes
Database Buffers 1962934272 bytes
Redo Buffers 13475840 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DBASCPY'' 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 = ''DBASCPY'' 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 4277059584 bytes
Fixed Size 2165168 bytes
Variable Size 2298484304 bytes
Database Buffers 1962934272 bytes
Redo Buffers 13475840 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DBASCPY" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u20/dbascpy/oradata/redo01a.log', '/u20/dbascpy/oradata/redo01b.log' ) SIZE 10 M REUSE,
GROUP 2 ( '/u20/dbascpy/oradata/redo02a.log', '/u20/dbascpy/oradata/redo02b.log' ) SIZE 10 M REUSE
DATAFILE
'/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSTEM_FNO-1'
CHARACTER SET WE8ISO8859P15
contents of Memory Script:
{
set newname for tempfile 1 to
"/u20/dbascpy/oradata/data_D-DBASDB_TS-TEMP_FNO-1";
switch clone tempfile all;
catalog clone datafilecopy "/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSAUX_FNO-2",
"/u20/dbascpy/oradata/data_D-DBASDB_TS-UNDOTBS1_FNO-3",
"/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSTEM_TOOLS_FNO-4",
"/u20/dbascpy/oradata/data_D-DBASDB_TS-USERS_FNO-5";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u20/dbascpy/oradata/data_D-DBASDB_TS-TEMP_FNO-1 in control file
cataloged datafile copy
datafile copy file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSAUX_FNO-2 RECID=1 STAMP=732704417
cataloged datafile copy
datafile copy file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-UNDOTBS1_FNO-3 RECID=2 STAMP=732704417
cataloged datafile copy
datafile copy file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSTEM_TOOLS_FNO-4 RECID=3 STAMP=732704417
cataloged datafile copy
datafile copy file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-USERS_FNO-5 RECID=4 STAMP=732704417
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=732704417 file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSAUX_FNO-2
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=732704417 file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-UNDOTBS1_FNO-3
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=732704417 file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-SYSTEM_TOOLS_FNO-4
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=732704417 file name=/u20/dbascpy/oradata/data_D-DBASDB_TS-USERS_FNO-5
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 18-OCT-10
Geen opmerkingen:
Een reactie posten