How To Clone A Windows Database

How to clone a Windows database Oracle Tips by Burleson Consulting May 3, 2008

Question: How can I copy my Oracle database from one Windows machine to another? Is copying the same as cloning? Answer: Yes, copying and cloning are synonymous, and there are several ways to copy a database: • • •

RMAN restore to new server DataPump (export. import) File cloning (the fastest method)

Common reasons for copying a Windows database include creating a testing and development instance and copying a production Windows database for ease by reporting. Here is a generic clone procedure that works on all OS environments, and is a fast way to copy a Windows Oracle database from one machine to another: • •

Cloning a Windows database Cloning a RAC Windows database

For HA systems, there is a minimum downtime approach to copying a Windows instance: •

Super-fast cloning of a Windows database

See these related notes on copying Windows databases: • • • • • •

Clone 9.0.1.3.1 to 10.2.0.1 Database Cloning RMAN clone database functionality Clone Database RMAN to duplicate a Windows database Migration from SQL Server 2000 to Oracle 10g

Super fast Database Copying/Cloning Oracle Tips by Burleson Consulting

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas. This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another. It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database. STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells

oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this: STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 2 MAXDATAFILES 240 MAXINSTANCES 1 MAXLOGHISTORY 113 LOGFILE GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf', '/u03/oradata/olslsq/log1b.dbf') SIZE 30M, GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf', '/u04/oradata/oldlsq/log2b.dbf') SIZE 30M DATAFILE '/u01/oradata/oldlsq/system01.dbf', '/u01/oradata/oldlsq/mydatabase.dbf' ; # Recovery is required if any of the datafiles are restored # backups, or if the last shutdown was not normal or immediate. RECOVER DATABASE # Database can now be opened normally. ALTER DATABASE OPEN;

STEP 2: Shutdown the old database STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server. rcp rcp rcp rcp

/u01/oradata/oldlsq/* /u01/oradata/oldlsq/* /u03/oradata/oldlsq/* /u04/oradata/oldlsq/*

newhost:/u01/oradata/newlsq newhost:/u01/oradata/newlsq newhost:/u03/oradata/newlsq newhost:/u04/oradata/newlsq

STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following: Old: CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

New: CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS

STEP 5: Remove the “recover database” and “alter database open” syntax # Recovery is required if any of the datafiles are restored # backups, or if the last shutdown was not normal or immediate. RECOVER DATABASE # Database can now be opened normally. ALTER DATABASE OPEN;

STEP 6: Re-names of the data files names that have changed. Save as db_create_controlfile.sql. Old:

DATAFILE '/u01/oradata/oldlsq/system01.dbf', '/u01/oradata/oldlsq/mydatabase.dbf'

New: DATAFILE '/u01/oradata/newlsq/system01.dbf', '/u01/oradata/newlsq/mydatabase.dbf'

STEP 7: Create the bdump, udump and cdump directories cd $DBA/admin mkdir newlsq cd newlsq mkdir bdump mkdir udump mkdir cdump mkdir pfile

STEP 8: Copy-over the old init.ora file rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile

STEP 9: Start the new database @db_create_controlfile.sql

STEP 10: Place the new database in archivelog mode

Cloning a large RAC database Oracle Tips by Steve Karam See related notes: Fast cloning of Oracle databases Question: We have a specific need to clone a RAC database that is in archivelog mode and is up and running and is about 2 terabytes. We’ve been able to clone the smaller databases in archivelog mode with little impact on the database being cloned and would like to use this option for the multi terabyte RAC clusters database. How do you clone a large multi terabyte RAC database? Answer: Oracle's Grid Control uses the RMAN DUPLICATE feature in order to do its RAC cloning. This RMAN feature uses a database backup as the basis for the cloning procedure. The backup is transferred and restored on another server and put into action. Unfortunately, the only real way to customize a large RAC clone with RMAN this is to allocate more channels. You can run the DUPLICATE command on your own in RMAN, without using RAC Grid Control. When you do so, just specify multiple AUXILIARY channels of device type DISK. The more you specify, the faster your duplicate will take place. You can find a better description of RMAN terminology (including how channels work) here.

You may be able to allocate more AUXILIARY channels in RAC Grid Control as well, but I'm somewhat sure you cannot. I don't have a Grid Control server to look at at the moment, but if you see options to allocate more channels when cloning that's what you want in order to speed up the cloning process. With a multi terabyte RAC cluster database, you may want to consider a combination of RMAN or Transportable Tablespaces and some sort of hardware cloning/snapshot if possible. If you are using NetApp or EMC for data storage, there may be snapshot capabilities you can take advantage of in order to clone the datafiles quickly. If you have no option to use a snapshot technology, another approach to RAC cloning is to mount the target system's drives locally on the primary system. This way you can use RMAN to backup the datafiles as datafile copies straight to the target disk instead of having the files go over the network (like the DUPLICATE command does). Once you're done with the RAC database clone, dismount the target's disks from the primary and mount them to the target server. Transportable Tablespaces are also well liked for large databases, but they cause tablespaces to be in READ ONLY mode while copying...meaning you will not have 100% uptime on the target.

Clone an Oracle database using RMAN duplicate (same server) tnsManager - Distribute tnsnames the easy way and for free! This procedure will clone a database onto the same server using RMAN duplicate. •

• • •

1. Backup the source database. To use RMAN duplicate an RMAN backup of the source database is required. If there is already one available, skip to step 2. If not, here is a quick example of how to produce an RMAN backup. This example assumes that there is no recovery catalog available: rman target sys@<source database> nocatalog backup database plus archivelog format '/u01/ora_backup/rman/%d_%u_%s'; This will backup the database and archive logs. The format string defines the location of the backup files. Alter it to a suitable location.

•

•

2. Produce a pfile for the new database This step assumes that the source database is using a spfile. If that is not the case, simply make a copy the existing pfile. Connect to the source database as sysdba and run the following: create pfile='init.ora' from spfile; This will create a new pfile in the $ORACLE_HOME/dbs directory. The new pfile will need to be edited immediately. If the cloned database is to have a different name to the source, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Because in this example the cloned database will reside on the same machine as the source, Oracle must be told how convert the filenames during the RMAN duplicate operation. This is achieved by adding the following lines to the newly created pfile: db_file_name_convert=(<source_db_path>,) log_file_name_convert=(<source_db_path>,) Here is an example where the source database scr9 is being cloned to dg9a. Note the trailing slashes and lack of quotes:

db_file_name_convert=(/u01/oradata/scr9/,/u03/oradata/dg9a/) log_file_name_convert=(/u01/oradata/scr9/,/u03/oradata/dg9a/)

•

3. Create bdump, udump & cdump directories Create bdump, udump & cdump directories as specified in the pfile from the previous step.

•

4. Add a new entry to oratab, and source the environment Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.

•

Source the new environment with '. oraenv' and verify that it has worked by issuing the following command: echo $ORACLE_SID If this doesn't output the new database sid go back and investigate why not.

• • • •

5. Create a password file Use the following command to create a password file (add an appropriate password to the end of it): orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password= 6. Duplicate the database From sqlplus, start the instance up in nomount mode: startup nomount Exit sqlplus, start RMAN and duplicate the database. As in step 1, it is assumed that no recovery catalog is available. If one is available, simply amend the RMAN command to include it. rman target sys@<source_database> nocatalog auxiliary / duplicate target database to ; This will restore the database and apply some archive logs. It can appear to hang at the end sometimes. Just give it time - I think it is because RMAN does a 'shutdown normal'. If you see the following error, it is probably due to the file_name_convert settings being wrong. Return to step 2 and double check the settings. RMAN-05001: auxiliary filename '%s' conflicts with a file used by the target database Once the duplicate has finished RMAN will display a message similar to this:

database opened Finished Duplicate Db at 26-FEB-05 RMAN> Exit RMAN. • • • • •

7. Create an spfile From sqlplus: create spfile from pfile; shutdown immediate startup

Now that the clone is built, we no longer need the file_name_convert settings: alter system reset db_file_name_convert scope=spfile sid='*' / alter system reset log_file_name_convert scope=spfile sid='*' / • • • • • •

8. Optionally take the clone database out of archive log mode RMAN will leave the cloned database in archive log mode. If archive log mode isn't required, run the following commands from sqlplus: shutdown immediate startup mount alter database noarchivelog; alter database open; 9. Configure TNS Add entries for new database in the listener.ora and tnsnames.ora as necessary.

Clone an Oracle Database Using Rman Duplicate Command Alejandro Vargas Oracle Israel 19/03/07 Summary of Steps.............................................................................................................................................1 1. The Rman Backup Script:..............................................................................................................................2 2. Create password file on the remote node for the instance you plan to build.................................................4 3. Create a new init.ora for the database..........................................................................................................4 4. Configure SQL Net on source and target nodes...........................................................................................6 5. Create directories for the database on the remote node..............................................................................8 6. Startup nomount the auxiliary instance on target node................................................................................9 7. Check that source database is mounted or open.........................................................................................9 8. Copy all backup files to target node, the path MUST be the same as in the source..................................10 9. Start Rman and allocate auxiliary channels to the new database, execute the Duplicate command.........11 10. Check the new database..........................................................................................................................18 End of the Procedure......................................................................................................................................19

Summary of Steps 1. Prepare an Rman backup script 2. Create an Oracle Password File for the Auxiliary Instance 3. Create a new init.ora for the database 4. Configure SQL Net for the new database on remote node 5. Create the required directories for the new database on the remote node 6. Startup nomount the auxiliary instance on target node 7. Check that source database is mounted or open 8. Copy all backup files to target node 9. Start Rman, allocate auxiliary channels to the new database, execute Duplicate command 10. Check the new database

1. The Rman Backup Script: {oracle} /crmdbtst/app01/oracle/scripts/av/scripts [crmdbtst1] > cat rman_backup #!/bin/ksh set -x # Script : backup_to_fs # Alejandro Vargas # March 6, 2007 # This script backup a database and the archived logs required to recover it on File System # This version is for a single instance database. # Environment Variables # --------------------export ORACLE_SID=r2tape export ORACLE_BASE=/crmdbtst/app01/oracle export ORACLE_HOME=/crmdbtst/app01/oracle/product/10gDB

# Path Variables # -------------export BASE_PATH=/crmdbtst/app01/oracle/scripts/general:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bi n:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin:/crmdbtst/app01/oracle/scripts:/usr/local/maint/oracle:/crmdb/ app01/oracle/product/db_scripts/RAC:/crmdb/app01/oracle/product/db_scripts export PATH=${ORACLE_HOME}/bin:${BASE_PATH} # Script Variables # ---------------# v_logvw = set the name of dynamic view v$log to variable v_logvw to be used inside a command. # v_scni1 = initial scn thread1 # v_scnf1 = final scn thread1 # v_bdest = backup destination v_logvw=gv\$log v_scni1=`echo "select 'xX '||min(sequence#) from $v_logvw where thread#=1;" | sqlplus -s '/as sysdba' | grep "xX" | awk '{print $2}'` v_bdest=/crmdbtst/oa01/rman/r2tape # Backup database # --------------rman target / nocatalog <<EOF run { sql "alter system archive log current"; allocate channel backup_disk1 type disk format '$v_bdest/%U'; allocate channel backup_disk2 type disk format '$v_bdest/%U'; allocate channel backup_disk3 type disk format '$v_bdest/%U'; allocate channel backup_disk4 type disk format '$v_bdest/%U'; backup database include current controlfile; release channel backup_disk1; release channel backup_disk2; release channel backup_disk3; release channel backup_disk4; sql "alter system archive log current"; } exit EOF v_scnf1=`echo "select 'xX '||max(sequence#) from $v_logvw where thread#=1;" | sqlplus -s '/as sysdba' | grep "xX" | awk '{print $2}'` rman target / nocatalog <<EOF run { allocate channel backup_disk1 type disk format '$v_bdest/%U'; backup archivelog sequence between $v_scni1 and $v_scnf1 thread 1 delete input; release channel backup_disk1 ; } exit EOF rman target / nocatalog log=$v_bdest/backuset_info.log <<EOF list backupset; exit EOF # EOF backup_to_fs

2. Create password file on the remote node for the instance you plan to build [dbtst2] > orapwd file=orapwr2dupl password=oracle entries=10

[dbtst2] > ls -ltr orapwr2dupl -rw-r----- 1 oracle dba 2560 Mar 19 10:52 orapwr2dupl

3. Create a new init.ora for the database Create a copy of source database spfile and edit it. SQL> create pfile='/dbtst/app01/oracle/product/10gDB/dbs/initr2dupl.ora' from pfile='/dbtst/app01/oracle/product/10gDB/dbs/spfiler2tape.ora' / File created. r2dupl.__shared_pool_size=146800640 *.audit_file_dest='/dbtst/app01/oracle/admin/r2dupl/adump' *.background_dump_dest='/dbtst/app01/oracle/admin/r2dupl/bdump' *.compatible='10.2.0.3.0' *.control_files='/dbtst/oa01/r2dupl/files1/R2TAPE/controlfile/o1_mf_2kqh1rj1_.ctl', '/dbtst/oa01/r2dupl/files2/R2TAPE/controlfile/o1_mf_2kqh1rly_.ctl' *.core_dump_dest='/dbtst/app01/oracle/admin/r2dupl/cdump' *.db_block_size=8192 *.db_cache_size=114572800 *.db_create_file_dest='/dbtst/oa01/r2dupl' *.db_create_online_log_dest_1='/dbtst/oa01/r2dupl/files1' *.db_create_online_log_dest_2='/dbtst/oa01/r2dupl/files2' *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='r2dupl' *.dispatchers='(PROTOCOL=TCP) (SERVICE=r2duplXDB)' *.java_pool_size=25165824 *.job_queue_processes=10 *.large_pool_size=0 *.log_archive_format='%t_%s_%r.dbf' *.open_cursors=300 *.pga_aggregate_target=1624244224 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.shared_pool_size=838860800 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/dbtst/app01/oracle/admin/r2dupl/udump'

4. Configure SQL Net on source and target nodes On Source Node add to tnsnames.ora an entry to get to the database r2dupl = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = crmdbtst2)(Port = 1521)) ) (CONNECT_DATA = (SID = r2dupl) ) ) On Target Node add to tnsnames.ora and listener.ora respective entries. On Tnsnames.ora add: r2dupl = (DESCRIPTION =

(ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = crmdbtst2)(Port = 1521)) ) (CONNECT_DATA = (SID = r2dupl) ) ) On Listener.ora add: (SID_DESC = (GLOBAL_DBNAME = r2dupl) (ORACLE_HOME = /crmdbtst/app01/oracle/product/10gDB) (SID_NAME = r2dupl) ) Check Connectivity from source and target nodes SOURCE: [dbtst1] > tnsping r2dupl TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 19-MAR-2007 11:50:13 Copyright (c) 1997, 2006, Oracle. All rights reserved. Used parameter files: /dbtst/app01/oracle/product/10gDB/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = crmdbtst2)(Port = 1521))) (CONNECT_DATA = (SID = r2dupl))) OK (10 msec) TARGET: [dbtst2] > tnsping r2dupl TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 19-MAR-2007 11:48:25 Copyright (c) 1997, 2006, Oracle. All rights reserved. Used parameter files: /dbtst/app01/oracle/product/10gDB/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = dbtst2)(Port = 1521))) (CONNECT_DATA = (SID = r2dupl))) OK (0 msec)

5. Create directories for the database on the remote node [dbtst2] > mkdir -p /dbtst/app01/oracle/admin/r2dupl/udump [dbtst2] > mkdir -p /dbtst/app01/oracle/admin/r2dupl/cdump [dbtst2] > mkdir -p /dbtst/app01/oracle/admin/r2dupl/bdump [dbtst2] > mkdir -p /dbtst/app01/oracle/admin/r2dupl/adump [dbtst2] > mkdir -p /dbtst/oa01/r2dupl/files1/R2TAPE/controlfile/ [dbtst2] > mkdir -p /dbtst/oa01/r2dupl/files2/R2TAPE/controlfile/

6. Startup nomount the auxiliary instance on target node [dbtst2] > setenv ORACLE_SID r2dupl [dbtst2] > sql SQL*Plus: Release 10.2.0.3.0 - Production on Mon Mar 19 11:52:50 2007 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 989855744 bytes Fixed Size 2077552 bytes Variable Size 864029840 bytes

Database Buffers 117440512 bytes Redo Buffers 6307840 bytes

7. Check that source database is mounted or open [dbtst1] > sts r2tape [dbtst1] > sql SQL*Plus: Release 10.2.0.3.0 - Production on Mon Mar 19 11:56:43 2007 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options SQL> select status from v$instance; STATUS -----------------------------------OPEN SQL>

8. Copy all backup files to target node, the path MUST be the same as in the source. [dbtst1] > ls -ltr total 1799072 -rw-r----- 1 oracle dba 16400384 Mar 22 10:40 1cid8b5e_1_1 -rw-r----- 1 oracle dba 242335744 Mar 22 10:41 1bid8b5e_1_1 -rw-r----- 1 oracle dba 393609216 Mar 22 10:41 1aid8b5e_1_1 -rw-r----- 1 oracle dba 1138081792 Mar 22 10:41 19id8b5e_1_1 -rw-r----- 1 oracle dba 207360 Mar 22 10:42 1fid8b7t_1_1 -rw-r----- 1 oracle dba 7192576 Mar 22 10:42 snapcf_r2tape.f -rw-r----- 1 oracle dba 7274496 Mar 22 10:42 c-2373435176-20070322-02 -rw-r--r-- 1 oracle dba 7938 Mar 22 10:42 backuset_info.log [dbtst1] > scp * dbtst2:/dbtst/oa01/rman/r2tape [dbtst1] > scp * dbtst2:/dbtst/oa01/rman/r2tape 19id8b5e_1_1 100% 1085MB 8.0MB/s 02:15 1aid8b5e_1_1 100% 375MB 8.2MB/s 00:46 1bid8b5e_1_1 100% 231MB 7.5MB/s 00:31 1cid8b5e_1_1 100% 16MB 7.8MB/s 00:02 1fid8b7t_1_1 100% 203KB 202.5KB/s 00:00 backuset_info.log 100% 7938 7.8KB/s 00:00 c-2373435176-20070322-02 100% 7104KB 6.9MB/s 00:01 snapcf_r2tape.f 100% 7024KB 6.9MB/s 00:01

9. Start Rman and allocate auxiliary channels to the new database, execute the Duplicate command Highlighted all the input required to execute the duplicate: [dbtst1] > rman target / nocatalog auxiliary sys/oracle@r2dupl <<<<<< invoke RMAN Recovery Manager: Release 10.2.0.3.0 - Production on Thu Mar 22 10:59:18 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: R2TAPE (DBID=2373435176) using target database control file instead of recovery catalog connected to auxiliary database: R2DUPL (not mounted) RMAN> run {ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK; <<<<<< execute the Duplicate Command DUPLICATE TARGET DATABASE TO r2dupl;

} allocated channel: aux1 channel aux1: sid=153 devtype=DISK Starting Duplicate Db at 22/03/2007 11:00:05 contents of Memory Script: { set until scn 3739135100301;

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; restore check readonly clone database ; } executing Memory Script executing command: SET until clause executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 22/03/2007 11:00:05 channel aux1: starting datafile backupset restore channel aux1: specifying datafile(s) to restore from backup set restoring datafile 00002 to /crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_undotbs1_%u_.dbf channel aux1: reading from backup piece /crmdbtst/oa01/rman/r2tape/1cid8b5e_1_1 channel aux1: restored backup piece 1 piece handle=/crmdbtst/oa01/rman/r2tape/1cid8b5e_1_1 tag=TAG20070322T104045 channel aux1: restore complete, elapsed time: 00:00:03 channel aux1: starting datafile backupset restore channel aux1: specifying datafile(s) to restore from backup set restoring datafile 00003 to /crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_sysaux_%u_.dbf channel aux1: reading from backup piece /crmdbtst/oa01/rman/r2tape/1bid8b5e_1_1 channel aux1: restored backup piece 1 piece handle=/crmdbtst/oa01/rman/r2tape/1bid8b5e_1_1 tag=TAG20070322T104045 channel aux1: restore complete, elapsed time: 00:00:25 channel aux1: starting datafile backupset restore channel aux1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_system_%u_.dbf channel aux1: reading from backup piece /crmdbtst/oa01/rman/r2tape/1aid8b5e_1_1 channel aux1: restored backup piece 1 piece handle=/crmdbtst/oa01/rman/r2tape/1aid8b5e_1_1 tag=TAG20070322T104045 channel aux1: restore complete, elapsed time: 00:00:45 channel aux1: starting datafile backupset restore channel aux1: specifying datafile(s) to restore from backup set restoring datafile 00004 to /crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_users_%u_.dbf channel aux1: reading from backup piece /crmdbtst/oa01/rman/r2tape/19id8b5e_1_1 channel aux1: restored backup piece 1 piece handle=/crmdbtst/oa01/rman/r2tape/19id8b5e_1_1 tag=TAG20070322T104045 channel aux1: restore complete, elapsed time: 00:01:25 Finished restore at 22/03/2007 11:02:43 sql statement: CREATE CONTROLFILE REUSE SET DATABASE "R2DUPL" 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 '/crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_system_304kfl61_.dbf' CHARACTER SET WE8ISO8859P1 contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 2 switched to datafile copy input datafile copy recid=1 stamp=617886165 filename=/crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_undotbs1_304kdoxf_.dbf datafile 3 switched to datafile copy input datafile copy recid=2 stamp=617886165 filename=/crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_sysaux_304kdrwx_.dbf datafile 4 switched to datafile copy input datafile copy recid=3 stamp=617886165 filename=/crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_users_304kgyyt_.dbf contents of Memory Script: { set until scn 3739135100301; recover clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 22/03/2007 11:02:45 starting media recovery channel aux1: starting archive log restore to default destination channel aux1: restoring archive log archive log thread=1 sequence=139 channel aux1: reading from backup piece /crmdbtst/oa01/rman/r2tape/1fid8b7t_1_1 channel aux1: restored backup piece 1 piece handle=/crmdbtst/oa01/rman/r2tape/1fid8b7t_1_1 tag=TAG20070322T104205 channel aux1: restore complete, elapsed time: 00:00:02 archive log filename=/crmdbtst/app01/oracle/product/10gDB/dbs/arch1_139_602349163.dbf thread=1 sequence=139 channel clone_default: deleting archive log(s) archive log filename=/crmdbtst/app01/oracle/product/10gDB/dbs/arch1_139_602349163.dbf recid=1 stamp=617886167 media recovery complete, elapsed time: 00:00:01 Finished recover at 22/03/2007 11:02:49 contents of Memory Script: { shutdown clone; startup clone nomount ; } executing Memory Script database dismounted Oracle instance shut down connected to auxiliary database (not started)

Oracle instance started Total System Global Area 989855744 bytes Fixed Size 2077552 bytes Variable Size 864029840 bytes Database Buffers 117440512 bytes Redo Buffers 6307840 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "R2DUPL" 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 '/crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_system_304kfl61_.dbf' CHARACTER SET WE8ISO8859P1 contents of Memory Script: { set newname for clone tempfile 1 to new; switch clone tempfile all; catalog clone datafilecopy "/crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_undotbs1_304kdoxf_.dbf"; catalog clone datafilecopy "/crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_sysaux_304kdrwx_.dbf"; catalog clone datafilecopy "/crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_users_304kgyyt_.dbf"; switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed temporary file 1 to /crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_temp_%u_.tmp in control file cataloged datafile copy datafile copy filename=/crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_undotbs1_304kdoxf_.dbf recid=1 stamp=617886180 cataloged datafile copy datafile copy filename=/crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_sysaux_304kdrwx_.dbf recid=2 stamp=617886181 cataloged datafile copy datafile copy filename=/crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_users_304kgyyt_.dbf recid=3 stamp=617886181 datafile 2 switched to datafile copy input datafile copy recid=1 stamp=617886180 filename=/crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_undotbs1_304kdoxf_.dbf datafile 3 switched to datafile copy input datafile copy recid=2 stamp=617886181 filename=/crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_sysaux_304kdrwx_.dbf datafile 4 switched to datafile copy input datafile copy recid=3 stamp=617886181 filename=/crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_users_304kgyyt_.dbf

contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script database opened Finished Duplicate Db at 22/03/2007 11:03:19 RMAN>

10. Check the new database {oracle} /crmdbtst/oa01/rman/r2tape [crmdbtst2] > sql SQL*Plus: Release 10.2.0.3.0 - Production on Thu Mar 22 11:15:38 2007 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options SQL> select name from v$database; NAME --------R2DUPL SQL> select bytes||' '||file_name||' .-' from dba_data_files order by bytes; BYTES||''||FILE_NAME||'.-' -------------------------------------------------------------------------------267386880 /crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_undotbs1_304kdoxf_.dbf .346030080 /crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_sysaux_304kdrwx_.dbf .524288000 /crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_system_304kfl61_.dbf .1156055040 /crmdbtst/oa01/r2dupl/R2DUPL/datafile/o1_mf_users_304kgyyt_.dbf .SQL>

End of Procedure