• Arun Kumar

Oracle Hot Database Cloning

Hot database cloning is more suitable for databases which are running 24X7X365 type of databases and is done using the hot backup. For hot database cloning, database has to be in archivelog mode and there no need to shutdown the database.


  • Source Database SID: RIS

  • Clone Database SID: RIS

  • Source and target files locations is same

Parameter file cloning

Find out the path and names of datafiles on the source database

SQL> select name from v$datafile;

Create pfile from spfile and copy it to target server

SQL> create pfile from spfile;

scp $ORACLE_HOME/dba/initRIS.ora oracle@clone.dbagenesis.com:$ORACLE_HOME/dbs/

On the target server, open the pfile and create locations to hold controlfiles and dump files

vi $ORACLE_HOME/dbs/initRIS.ora

In my case I had to create below two main locations

mkdir -p <control_file_locations>
mkdir -p <adump_locations>

Controlfile cloning

On the target server, take controlfile backup to trace location

SQL> alter database backup controlfile to trace as '/tmp/RIS_ctl.sql' REUSE RESETLOGS;

Copy the trace file on target server

scp /tmp/RIS_ctl.sql oracle@clone.dbagenesis.com:/tmp/

On target server, open the trace file (/tmp/RIS.ctl) and follow below steps

vi /tmp/RIS_ctl.sql
  • Delete everything before "CREATE CONTROLFILE"

  • Delete everything after ";"

And you must be left with below. Save and close the file

 GROUP 1 '/u01/db_files/RIS/redo01.log' SIZE 200M BLOCKSIZE 512,
 GROUP 2 '/u01/db_files/RIS/redo02.log' SIZE 200M BLOCKSIZE 512,
 GROUP 3 '/u01/db_files/RIS/redo03.log' SIZE 200M BLOCKSIZE 512

Create the logfile and datafiles locations on clone server. In my case, all files reside under one location

mkdir -p /u01/db_files/RIS

Our CREATE CONTROLFILE script is ready, we will use it later in the cloning process.

Datafiles cloning

Note down the oldest log sequence number on the source database

SQL> alter system switch logfile;
SQL> archive log list

Place the source database into to backup mode

SQL> alter database begin backup;

Copy all datafiles and logfiles from database to target server

scp /u01/db_files/RIS/*log /u01/db_files/RIS/*.dbf oracle@clone.dbagenesis.com:/u01/db_files/RIS/

Release the source database from backup mode

SQL> alter database end backup;

Switch the current log file on source database and note down the oldest log sequence number

SQL> alter system switch logfile;
SQL> archive log list;

Copy all archive log files generated on the source database during begin backup and the end backup mode to the target server.

Make sure to copy the archivelogs in the same location on target server as on the source server

Recover clone database

Let us create the controlfiles for the clone database and put it in mount mode

sqlplus / as sysdba

SQL> startup nomount;
SQL> @/tmp/RIS_ctl.sql
SQL> alter database mount;

Recover the clone database using backup controlfile option

SQL> recover database using backup controlfile until cancel;

You will be prompted to feed the archive log files henceforth. Hit enter, enter until all the archive logs are applied and then type CANCEL;

Open the database with resetlogs option

SQL> alter database open resetlogs;


Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

Automate RMAN Backup using Shell Script

In a real environment, off course you will not manually trigger all the Oracle database backups. You need an automated mechanism to trigger RMAN backups. In this article we will look at RMAN backup au

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis