Oracle 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.

Overview

Server 1: prod.dbagenesis.com

We have a prod database running on Oracle Linux 7. We will be cloning the prod database from prod server to dev server using cold cloning method.

Server 2: dev.dbagenesis.com

This is our dev (development) server where we are going to create a copy of prod database from prod server.

Note: we will only shutdown the prod database while copying the data files and redo log files. Rest of the time, prod 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

We need to create certain directories on target (dev) server. If the directories are not created, oracle will throw error when we take the clone database to nomount stage.

Create pfile from spfile and check all the directory locations that you must create on target server

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

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
;

Also, change REUSE to SET and NORESETLOGS to RESETLOGS. Finally, your file must look like below. Save and close

CREATE CONTROLFILE SET DATABASE "PROD" RESETLOGS  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 from above script 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!