- Arun Kumar
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!