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.
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.
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 firstname.lastname@example.org:$ORACLE_HOME/dbs/
On the target server, open the pfile and create locations to hold controlfiles and dump files
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 email@example.com:/tmp/
On target server, open the trace file and follow below steps
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 firstname.lastname@example.org:/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;
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.
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 email@example.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 firstname.lastname@example.org:/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 email@example.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;