NyQuest Consulting, LLC

Cloning an Oracle Database

Cloning" an Oracle Database simply allows the DBA to create another database w/o using the traditional IMPORT/EXPORT route.

Exporting and then importing of a database generally takes a long time, and after the import the DBA must go through numerous checks of the new database to make sure that all objects imported successfully, and are in a valid state.

Cloning of an Oracle database produces a exact snapshot of the source database taken at the time of the clone.

Cloning works on both 8 & 9 versions of the RDBMS, but produces an unexpected side-effect. For more information about this, click here.

It is helpful to have a working knowledge of Oracle before doing this, although the steps are fairly straightforward.

Prior to cloning an Oracle Database, decide on a location for the new database's datafiles, controlfiles, and redo logfiles. Placing these files in the same location as the source database files is a bad idea, since you WILL get confused over which is which.

You will also need to decide on the name (SID) of the new database and to create an associated parameter file to define the Oracle instance. Remember, the parameter file MUST reside in $ORACLE_HOME/dbs and have an init<$ORACLE_SID>.ora filename. For example, if the name of the new database is to be PROD2, then the associated parameter file must be c alled initPROD2.ora for the database. When you create the parameter file, be sure to change the parameter DB_NAME to contain the new name of the database, and change CONTROL_FILES to contain the location of the new associated Oracle controlfiles.

1. Invoke Oracle Server Manager or SQL*DBA and connect as internal to the source database. The issue the following command:

SVRMGR> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

This command creates a text-based copy of the Database controlfile in the Database trace files.

2. Cleanly shutdown the source database. Typically, this is done by invoking SERVER MANAGER or SQL*DBA and issuing a 'SHUTDOWN IMMEDIATE' statement while connected as "internal'

SVRMGR> connect internal
Connected.
SVRMGR> shutdown immediate

3. Now comes the hard part. You will have to find the text-based copy of your controlfile which will be saved in the trace directory. This directory will be contained in the directory pointed to by USER_DUMP_DEST in the database initialization parameter file. Typically, this is the $ORACLE_HOME/rdbms/log directory.

All the trace files in this directory will be named with an obscure PID label and '.trc' extension. The trace file that is a backup copy of the database controlfile will have the word 'CONTROLFILE' in it, so search all trace files for this string.

On Unix systems, position yourself into the USER_DUMP_DEST dir and type the following:

grep -i CONTROLFILE *.trc

Once you find a match, take a look at it, it should similar look like the one below. Be SURE that it contains the keywords 'STARTUP NOMOUNT' and 'CREATE CONTROLFILE'

Dump file /usr/local/oracle/product/733/rdbms/log/ora_245.trc
Oracle7 Server Release 7.3.3.0.0 - Production Release
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.3.0.0 - Production
ORACLE_HOME = /usr/local/oracle/product/733

Node name:
Release:
Version: 1
Machine:
Instance name:
Redo thread mounted by this instance: 1
Oracle process number: 7
Unix process pid: 245, image: oracle

Thu Jul 23 22:04:39 1998
Thu Jul 23 22:04:39 1998
*** SESSION ID:(6.1) 1998.07.23.22.04.39.084
# The following commands will create a new control file and use it
# to open the database.
# No data other than log history will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SEED" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE
GROUP 1 '/oradata/SEED/log_SEED_01.rdo' SIZE 1M,
GROUP 2 '/oradata/SEED/log_SEED_02.rdo' SIZE 1M
DATAFILE
'/oradata/SEED/system_SEED_01.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;

You'll have to edit this file and perform the following changes

(a) Delete all lines at the top of the trace file up to but NOT INCLUDING STARTUP NOMOUNT

(b) Delete all lines that start with '#', which are comments

(c) On the the that starts with CREATE CONTROLFILE, change 'REUSE' to 'SET' Change the old database name to the new one, and change 'NORESETLOGS' to 'RESETLOGS'

(d) Change the locations of all logfiles and datafiles to the location of the datafiles and logfiles for the new database.

(e) Delete the line 'RECOVER DATABASE'

(f) Change the line 'ALTER DATABASE OPEN' to 'ALTER DATABASE OPEN RESETLOGS'

Save the trace file with a more managable filename, like clone.sql

Once you are done editing the above file, it should look something like the one shown below. In this example, we are copying the SEED database into a database named TEST with /oradata/TEST containing all Oracle datafiles and logfiles.

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE
GROUP 1 '/oradata/TEST/log_TEST_01.rdo' SIZE 1M,
GROUP 2 '/oradata/TEST/log_TEST_02.rdo' SIZE 1M
DATAFILE
'/oradata/TEST/system_TEST_01.dbf'
;
ALTER DATABASE OPEN RESETLOGS;

4. At this point, the source database should still be shut down. Before you start it back up, copy all logfiles + datafiles associated with this database to the new location. These files will eventually comprise your destination database. Be sure to name these files with meaningful descriptors. "1.dbf" is NOT a good file descriptor. Each datafile should include the corresponding tablespace, database name, and file #, for example, "SYSTEM_TEST_01.dbf"

5. Once you have copied all the associated datafiles, you are ready to create the new database. The actual cloning process simply creates a new set of Oracle controlfiles for the new database. Set your $ORACLE_SID to point to the name of the new database. The actual command to do this will depend on the type of shell you are using.

{for /bin/sh, /bin/ksh, /bin/bash}
ORACLE_SID={name of new database} ; export ORACLE_SID

{for /bin/csh, /bin/tcsh}
setenv ORACLE_SID {name of new database}

for our example above, we would enter the following:
$ ORACLE_SID=TEST ; export ORACLE_SID {sh, ksh, bash}
OR
% setenv ORACLE_SID TEST {csh, tsch}

6. Invoke Oracle's Server Manager (svrmgrl) or SQL*DBA. Execute a 'connect internal' to the new database and run the newly edited script.

bash-2.02$ svrmgrl
Oracle Server Manager Release 2.3.3.0.0 - Production
Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.
Oracle7 Server Release 7.3.3.0.0 - Production Release
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.3.0.0 - Production
SVRMGR> connect internal
Connected.
SVRMGR> @clone.sql
Statement processed.

7. If you have no errors, you now have another copy of your database. As a general rule, you may want to shutdown/startup this new database just to make sure there are no problems.

8. You can now go back and restart the original database. Simply reset $ORACLE_SID to point to the original database name, and invoke svrmgrl or sqldba and restart.