Cloning with Cold Backup (offline/closed)

Cloning is a process that can be used to create a copy of the existing Oracle database. Sometimes DBA’s need to clone databases to test backup, development environments and Relocating an database to another machine, and  recovery strategies or export a table that was dropped from the production database and import it back into the production database. Cloning can be done on separate hosts or on the same host and is different from standby database. But Oracle suggests do not clone the database on same host with same database name.

Be sure you are doing this type of cloning on same platform with at least same Oracle version.

From Oracle 12c onwards, you can move datafiles online if you need to re-locate datafile.

The following ways Cloning can be done:
1.Cold backup Cloning
2.Hot backup Cloning
3.RMAN Cloning

Cold backup (offline/closed) Cloning:

Let see how to clone with cold (offline) backups from Host Test to Test1.

Cold backup Cloning the database with “CREATE Control file” script let us see the below steps:

Source Database Name: UAT (HostName: Test)
Clone database Name: DEV (HostName: Test1)

Step1:
On target database (jicdb) host Test:
$export ORACLE_SID=UAT
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> select name from v$controlfile;

On target side, create pfile for clone database:

SQL> create pfile=’/u01/backup/dev/initdev.ora’ from spfile;

Make available created pfile (initdev.ora) to clone database side:
On clone side, change the value of "db_name" from “UAT” to “DEV”. Keep in mind that following parameters may also need to change:
---------------------------------------------------------------------------------------------------------
*._keep_remote_column_size=TRUE
*.audit_file_dest='/oracle/app/admin/dev/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u02/oracle/oradata/dev/control01.ctl','/u02/oracle/oradata/dev/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dev'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=devXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_date_format='DD-MON-RRRR HH24:MI:SS'
*.open_cursors=300
*.pga_aggregate_target=1G
*.processes=600
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sessions=665
*.sga_target=6G
*.undo_tablespace='UNDOTBS1'

---------------------------------------------------------------------------------------------------------
Create the script that will re-create the control file:

SQL> alter database backup controlfile to trace as '/u01/control_preprd.trc';

Note:
The above statement will put a text copy of the control file in the /u01 directory.

After finding the correct trace file, rename it to PREPROD_cr8control.sql and edit it as follows:
Remove everything up to the "START NOMOUNT" statement and everything after the semicolon at the end of the "CREATE CONTROLFILE" statement.

Edit the line starting with "CREATE CONTROLFILE" and replace the word "REUSE" with the word "SET" right before the keyword DATABASE.

On the same line, modify the database name changing it from UAT to PREPROD.
On the same line, change the keyword NORESETLOGS to RESETLOGS.

If the clone (PREPROD) database files directory locations are different with target database file location, change the paths for data files and redo logs in this script.

Ex:
My source database location: “/u02/oracle/oradata/UAT/”
Clone/target database location: “/u02/oracle/oradata/dev/”

Script should now read:
Edited file dev_cr8control.sql
---------------------------------------------------------------------------
CREATE CONTROLFILE SET DATABASE "dev" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 1168
LOGFILE
  GROUP 1 '/u02/oracle/oradata/dev/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u02/oracle/oradata/dev/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u02/oracle/oradata/dev/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u02/oracle/oradata/dev/system01.dbf',
  '/u02/oracle/oradata/dev/sysaux01.dbf',
  '/u02/oracle/oradata/dev/undotbs01.dbf',
  '/u02/oracle/oradata/dev/users01.dbf',
  '/u02/oracle/oradata/dev/HR01.dbf',
  '/u02/oracle/oradata/dev/payroll01.dbf'
CHARACTER SET AL32UTF8
;

Note: As you are changing database name, the "SET" is used to create control file. "REUSE" used when to keep same database name but relocate C/R/D files etc.
---------------------------------------------------------------------------------------------------------

Step2:
On source database(UAT) Host Test:

Shutdown the database “UAT” and backup copy all datafiles only (do not copy redo logs, control files) with “CP” command at operating system level, why we are not coping redo,

control files because the redo log files and control files are automatically created by oracle when the re-create control file script was run on clone database (dev).

Let see the following procedure:
SQL>SHUTDOWN

$cp *.dbf  /u01/bkp/UAT

Transfer all copied datafiles to clone database Host Test1:
$scp  *.dbf  oracle@Test1:/u02/oracle/oradata/dev

Step3:
On clone database (dev) Host Test1:
$export ORACLE_SID=dev
SQL> STARTUP NOMOUNT PFILE=’/u01/app/oracle/db_1/dbs/initdev.ora’
Started..

Step4:
Run the re-created control file text on dev clone database:
SQL> @dev_cr8control.sql
Control file created…

Step5:
Now try to open the database "dev" with resetlog option:
SQL> alter database open resetlogs;
Database altered.
The database "dev" successfully opened.

Note:  
Here we have to keep in mind, we just copied only all data files (.dbf) to clone side host Test1, and the redo logs, control file are automatically created by oracle, when

“dev_cr8control.ctl” re-create control file script was run.

Step6:
Now check the cloned database "dev":

SQL> select name from v$database;
NAME
---------
DEV
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------
/u02/oracle/oradata/dev/system01.dbf
/u02/oracle/oradata/dev/undotbs01.dbf
/u02/oracle/oradata/dev/sysaux01.dbf
/u02/oracle/oradata/dev/users01.dbf
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------
/u02/oracle/oradata/dev/redo03.log
/u02/oracle/oradata/dev/redo01.log
/u02/oracle/oradata/dev/redo02.log
/u02/oracle/oradata/dev/redo03.log

SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------------      
/u02/oracle/oradata/dev/control01.ctl
/u02/oracle/oradata/dev/control02.ctl


Thanks !


Issues may come:

SQL> @create_ctl.sql
CREATE CONTROLFILE SET DATABASE "dev" RESETLOGS  NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01130: database file version 11.2.0.4.0 incompatible with ORACLE version
11.2.0.0.0
ORA-01110: data file 1: '/u02/oracle/oradata/dev/system01.dbf'


SQL>

Fix:

Check .compatible parameter in your parameter file.
In my case I did like below:

*.compatible='11.2.0.0.0'

But my rdbms version is 11.2.0.4. So I changed this to :

*.compatible='11.2.0.4.0'

Then It I re-created the file and worked file.