How to clone the database using manual cold backup

Cold backup Database Cloning is simplest method to clone the database . The disadvantage with this approach is that database will be down during cold backup.

The high level steps for How to clone the database using Cold backup Database Cloning
1) Take the cold backup of the database to be cloned
2) Copy all the backup files to the destination server
3) Create the init.ora ,then startup nomount then create the control file on the new location
4) alter database open resetlogs to bring the new clone online
Let us take an example

Source Database Name: TEST
Source Database physical files path=/u001/oracle/TEST/oradata

Cloned Database Name: TEST_NEW
Target Database physical files path=/u001/oracle/TEST_NEW/oradata
1) Cold backup of Source Database

sqlplus / as sysdba
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> select name from v$controlfile;
shutdown immediate
exit

copy all the datafiles to the backup location. We dont need to copy the Temp tablespace if it is temporary files only
startup

alter database backup controlfile to trace;

This will copy the database information to the trace location

Parameter file backup.
If ‘TEST’ database is using spfile,
SQL> create pfile=’/tmp/initTEST_NEW.ora’ from spfile;
If database is using pfile, use OS command to copy the pfile to a backup location.
2) Copy all data files from backup location to the Target location. It could be different server or same server
$ mkdir /u001/oracle/TEST_NEW/oradata

cp or rcp depending the target nodes

3) On the target node

a) Copy the backup init.ora to the $ORACLE_HOME/dbs and then change the db_name,control file paths ,dump parameter and other things as neccessary

$ cd $ORACLE_HOME/dbs
$ vi initTEST_NEW.ora
db_name=TEST_NEW
control_files=/u001/oracle/TEST_NEW/oradata
….
b) Create appropriate directory structure in clone database for dumps as specified in init.ora file

c) Copy the control file trace file from SOurce database to target node and then edit it for redo log files location,datafiles location, database name

CREATE CONTROLFILE SET DATABASE “TEST_NEW” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u001/oracle/TEST_NEW/oradataredo01.log’ SIZE 200M,
GROUP 2 ‘/u001/oracle/TEST_NEW/oradataredo02.log’ SIZE 200M,

GROUP 2 ‘/u001/oracle/TEST_NEW/oradataredo02.log’ SIZE 200M,
DATAFILE
‘/u001/oracle/TEST_NEW/oradata/system01.dbf’,
‘/u001/oracle/TEST_NEW/oradata/undotbs01.dbf’,
‘/u001/oracle/TEST_NEW/oradata/sysaux01.dbf’,
‘/u001/oracle/TEST_NEW/oradata/users01.dbf’,
‘/u001/oracle/TEST_NEW/oradata/example01.dbf’
CHARACTER SET UTF8;
d) Source the target database env like ORACLE_SID,Oracle _HOME
$ export ORACLE_SID=TEST_NEW
SQL> startup nomount
SQL> @$ORACLE_HOME/dbs/cntrl.sql
Once the control file’s successfully created
4) open the database with resetlogs option.
SQL> alter database open resetlogs;

It will automatically create the temp files on opening