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 called 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.
