Step by Step Guide for Super fast Database Cloning!!

 Author: Don Burleson

Database Cloning enables you to copy the database structure to another database or location. In this article I will discuss Oracle Database Cloning Procedure that clones the Oracle database and is often the fastest way to copy an Oracle database.

Benefits of Database Cloning:

• A database cloning procedure is 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.
• Database cloning is useful when you customize a Summation database (with custom fields, forms, and tables), and want to copy the custom fields, forms, and tables (including lookup tables) to a new case or location, and want to copy the database structure only (copy the fields, forms, and tables without the coded data).
• The Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another. 

Step 1 - Trace directory:

On the old system, go into SQL*Plus, sign in 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.

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 - Closing Old Database:

In this this step shutdown the old database

Step 3 - Copying and Editing Data Files:

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 /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq
rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq 

Step 4 - Copying and Editing Control File:

Modify the control file creation script by changing by using the output syntax from Step 1.

STEP 5 – Removing Syntax:

In this step you will 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-naming Data Files:

Now re-names of the data files names that have changed.

Save as db_create_controlfile.sql.

STEP 7 – Creating Directories:

Now 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 init.ora file:

Now copy-over the old init.ora file

rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile

STEP 9 – Starting new Database:

In this step you will s tart the new database

startup nomount;
@db_create_controlfile.sql

STEP 10 - archivelog mode:

In the final step place the new database in archivelog mode

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   The Secrets of Protecting Data in your Database Tables!!
   Efficient Tracking of Exceptions in Oracle!
   DBA Tips: Limiting User Commands, Roles & Sessions!!
   The Power of Oracle Virtual Column!!