Duplicate database for cloning

This post is aimed at providing an understanding of how to clone a PROD database to a TEST/DEV database using DUPLICATE DATABASE command. The following are the environmental specifications to aid in understanding the setup/implementation. This is implemented on a single Windows host.

Target DB (TDB) - The PROD database which is cloned to form the TEST/DEV database.
Auxiliary DB (ADB) - The TEST/DEV database which is cloned from the PROD database.

The above acronyms will be widely used in the entire post.

Before we move any further, make an entry for both the Target Database (TDB) and the Auxiliary Database (ADB) in the tnsnames.ora file, also, make sure the listener is running and that both the services of TDB and ADB are registered with the listener.


1. Start the ADB (Duplicate Database) in nomount mode and exit out of the SQL propmt since the instance goes down in the process of creating a cloned DB. The existing init.ora parameter file for the ADB could be used to start it in the nomount mode.

C:\Documents and Settings\individual\Desktop>set ORACLE_SID=ADB

C:\Documents and Settings\individual\Desktop>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 17 16:32:29 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 1248428 bytes
Variable Size 83886932 bytes
Database Buffers 171966464 bytes
Redo Buffers 7139328 bytes
SQL>
SQL>


2. Connect to the Target Database (TDB) using RMAN as sys or any user who has SYSDBA privileges from another session of command prompt and take a full backup of the TDB along with the archive logs, this is the backup which will be used to create the ADB.


C:\Documents and Settings\individual\Desktop>rman target sys/manager@TDB

TDBvery Manager: Release 10.2.0.1.0 - Production on Wed Sep 17 16:29:13 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: TDB (DBID=1449103016)

RMAN>
RMAN>
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> backup format 'C:\oracle\rman_bkup\TDB_duplicate_database.bkup' database;
5> backup format 'C:\oracle\rman_bkup\TDB_duplicate_archives.bkup' archivelog all;
6> release channel c1;
7> }


3. Once the backup is complete, connect to the auxiliary instance from the same RMAN session.


RMAN> connect auxiliary sys/sys@ADB

connected to auxiliary database: ADB (not mounted)

RMAN>
RMAN>

It is to be noted that the password used for the sys account is actually an imaginary one and the server will anyway let you in. Any text/password could be used to connect to the auxiliary instance.

4. Now we are going to manually rename every file from the TDB to a valid location in the ADB. This is where we actually clone the database with a simple "DUPLICATE TARGET DATABASE TO" command.

This step is like the "CREATE CONTROLFILE" step of the conventional cloning process using a physical backup copy, which indeed gets executed in the background. It also involves giving names of the logfiles for the ADB as shown below, ignoring which would end in an error.


RMAN> run
2> {
3> allocate auxiliary channel aux_c1 device type disk;
4> set newname for datafile 'C:\ORACLE\TDB\SYSTEM01.DBF' to 'C:\ORACLE\ADB\SYSTEM01.DBF';
5> set newname for datafile 'C:\ORACLE\TDB\UNDOTS.DBF' to 'C:\ORACLE\ADB\UNDOTS.DBF';
6> set newname for datafile 'C:\ORACLE\TDB\SYSAUX.DBF' to 'C:\ORACLE\ADB\SYSAUX.DBF';
7> set newname for datafile 'C:\ORACLE\TDB\TOOLS.DBF' to 'C:\ORACLE\ADB\TOOLS.DBF';
8> set newname for datafile 'C:\ORACLE\TDB\USERS.DBF' to 'C:\ORACLE\ADB\USERS.DBF';
9> set newname for tempfile 'C:\ORACLE\TDB\TEMP.DBF' to 'C:\ORACLE\ADB\TEMP.DBF';
10> duplicate target database to ADB
11> logfile
12> group 1('C:\ORACLE\ADB\LOG1A.RDO') size 5M,
13> group 2('C:\ORACLE\ADB\LOG2A.RDO') size 5M;
14> }


After hitting return key at the 14th line, a lot of scripts are seen executing on the screen. Capturing each and every snap of it would make this post a real lengthy one so only a gist of the scripts are shown below to give users a brief snapshot of what actually happens.

allocated channel: aux_c1
channel aux_c1: sid=36 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting Duplicate Db at 17-SEP-08


contents of Memory Script:
{
set until scn 271346;
set newname for datafile 1 to
"C:\ORACLE\ADB\SYSTEM01.DBF";
set newname for datafile 2 to
.
.
}


sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ADB" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1752
LOGFILE
.
.
.

}


contents of Memory Script:
{
set until scn 271346;
TDBver
clone database
delete archivelog
;
}


database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started


renamed temporary file 1 to C:\ORACLE\ADB\TEMP.DBF in control file

cataloged datafile copy
datafile copy filename=C:\ORACLE\ADB\UNDOTS.DBF recid=1 stamp=665686637


contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 17-SEP-08

RMAN>

RMAN>

A few memory scripts are run in the background as part of the DUPLICATE DATABASE creation. The cloned TEST/DEV database is now ready to be used by the Testers/Developers. Now a comparison between the TDB and ADB will show different values for DBID.


SQL> select DBID,NAME,CREATED,OPEN_MODE,LOG_MODE from v$database;

DBID NAME CREATED OPEN_MODE LOG_MODE
---------- --------- --------- ---------- ------------
1449103016 TDB 08-SEP-08 READ WRITE ARCHIVELOG



SQL> select DBID,NAME,CREATED,OPEN_MODE,LOG_MODE from v$database;

DBID NAME CREATED OPEN_MODE LOG_MODE
---------- --------- --------- ---------- ------------
4023825067 ADB 17-SEP-08 READ WRITE ARCHIVELOG