Oracle Hot & Cold Database Cloning

Hot and cold database cloning methods are outdated and these methods of cloning are only for good to know purpose. Currently all cloning is performed via Oracle RMAN.

Hot Database Cloning
Cold Database Cloning

Note: This is manual method of database cloning which is rarely used in real-time

Hot Database Cloning

Hot database cloning is more suitable for databases which are running 24/7 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.

Consider

Source Database SID: RIS
Clone Database SID: RIS
Source and target files locations is same

Parameter file cloning: Find out the 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

cat $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 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

CREATE CONTROLFILE REUSE DATABASE "RIS" RESETLOGS NOARCHIVELOG
 MAXLOGFILES 16
 MAXLOGMEMBERS 3
 MAXDATAFILES 100
 MAXINSTANCES 8
 MAXLOGHISTORY 292
LOGFILE
 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
-- STANDBY LOGFILE
DATAFILE
 '/u01/db_files/RIS/system01.dbf',
 '/u01/db_files/RIS/sysaux01.dbf',
 '/u01/db_files/RIS/undotbs01.dbf',
 '/u01/db_files/RIS/users01.dbf'
CHARACTER SET AL32UTF8
;

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 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;

Enjoy!

Cold Database Cloning

Oracle cold cloning is one the reliable methods that is done using the cold database backups. The drawback of this method is that the database must be shutdown while taking the cold backup.

Consider

Source PROD Database SID: RIS
Clone DEV Database SID: RIS
Source and target files locations is same

Note: we will only shutdown the source database while copying the data files and redo log files. Rest of the time source database will be up and running

Copy SPFILE: Our first goal should be to put clone database into nomount stage. For this, we need to copy the spfile from prod server to dev server

On Prod Server
==============
scp $ORACLE_HOME/dbs/spfileprod.ora oracle@dev.dbagenesis.com:$ORACLE_HOME/dbs

Create pfile from spfile and open the parameter file

On Prod Server
==============
SQL> create pfile from spfile;
exit;

cat $ORACLE_HOME/dbs/initprod.ora

Let us create audit_file_dest, control_files (only directory path), db_recovery_file_dest and diagnostic_dest on target (dev) server.

Note: In case you see any other directories in the pfile, just create those directories on target (dev) server too.

On Dev Server
=============
mkdir -p /u01/app/oracle/admin/prod/adump
mkdir -p /u01/app/db_files/prod
mkdir -p /u01/app/FRA/prod
mkdir -p /u01/app/FRA
mkdir -p /u01/app/oracle

Copy Control Files: To take the clone database from nomount to mount stage, we need to copy control files from source to target server. On source database, take controlfile trace backup under /tmp/prod_ctl.sql file

On Prod Server
==============
SQL> alter database backup controlfile to trace as '/tmp/prod_ctl.sql';

Open the /tmp/prod_ctl.sql file and only keep CREATE CONTROLFILE statement. Delete any other lines that you may see. After deleting all the other lines, the file must look like below.

Change REUSE to SET and NORESETLOGS to RESETLOGS
Save & close

CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/prod/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/prod/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/prod/redo03.log'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/prod/system01.dbf',
  '/u01/app/oracle/oradata/prod/sysaux01.dbf',
  '/u01/app/oracle/oradata/prod/undotbs01.dbf',
  '/u01/app/oracle/oradata/prod/users01.dbf'
CHARACTER SET AL32UTF8
;

Copy the /tmp/prod_ctl.sql file from prod to dev server

On Prod Server
==============
scp /tmp/prod_ctl.sql oracle@dev.dbagenesis.com:/tmp/

Copy Data Files & Redo Log Files: It's time to copy data files and redo log files from source to target server. Let us check the data files and redo log files on source (prod) database

Note: for cold cloning activity, we must shutdown the database before copying the data files and redo log files.

On Prod Server
==============
SQL> select name from v$datafile;

name
--------------
/u01/app/oracle/oradata/prod/system01.dbf
/u01/app/oracle/oradata/prod/sysaux01.dbf
/u01/app/oracle/oradata/prod/undotbs01.dbf
/u01/app/oracle/oradata/prod/users01.dbf

SQL> select member from v$logfile;

member
-----------------
/u01/app/oracle/oradata/prod/redo01.log
/u01/app/oracle/oradata/prod/redo03.log
/u01/app/oracle/oradata/prod/redo03.log

SQL> select name from v$tempfile;

name
--------------------
/u01/app/oracle/oradata/prod/temp01.dbf

Create directory locations for data files, redo logs and temp files on target (dev) server

On Dev Server
=============
mkdir -p /u01/app/oracle/oradata/prod/

Shutdown the prod server and start copying the data files, redolog files and the temp file from source to target server

On Prod Server
==============
SQL> shut immediate;
exit;

cd /u01/app/oracle/oradata/prod/

scp *.dbf *.log oracle@dev.dbagenesis.com:/u01/app/oracle/oradata/prod/

Start Clone Server: We have everything to start the clone database on target (dev) server

On Dev Server
=============
export ORACLE_SID=prod

sqlplus / as sysdba

SQL> startup nomount;
SQL> @/tmp/prod_ctl.sql
SQL> alter database open resetlogs;

Done!