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.

Considerations

• 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

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

Enjoy!