Simple steps for oracle database cold cloning:

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: Prod
Source Database physical files path=/u01/oracle/prod/oradata

Cloned Database Name: Test
Target Database physical files path=/u01/oracle/test/oradata

Step 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;
SQL>shutdown immediate
SQL>exit

Copy all the datafiles to the backup location. We don't need to copy the Temp tablespace if it is temporary files only.

Start the Source Database:
SQL>startup

SQL>alter database backup controlfile to trace;

This will copy the database information to the trace location

Parameter file backup.
If ‘Prod’ database is using spfile,
SQL> create pfile=’/tmp/initTEST.ora’ from spfile;
If database is using pfile, use OS command to copy the pfile to a backup location.

Step 2) Copy all data files from backup location to the Target location. It could be different server or same server

$ mkdir /u01/oracle/test/oradata

cp or rcp depending the target nodes

Step 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 necessary.

$ cd $ORACLE_HOME/dbs
$ vi initTEST.ora
db_name=TEST
control_files=/u01/oracle/test/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” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u01/oracle/test/oradataredo01.log’ SIZE 200M,
GROUP 2 ‘/u01/oracle/test/oradataredo02.log’ SIZE 200M,
GROUP 3 ‘/u01/oracle/test/oradataredo03.log’ SIZE 200M
DATAFILE
‘/u01/oracle/test/oradata/system01.dbf’,
‘/u01/oracle/test/oradata/undotbs01.dbf’,
‘/u01/oracle/test/oradata/sysaux01.dbf’,
‘/u01/oracle/test/oradata/users01.dbf’,
‘/u01/oracle/test/oradata/example01.dbf’
CHARACTER SET UTF8;

d) Source the target database env like ORACLE_SID,Oracle _HOME
$ export ORACLE_SID=test
SQL> startup nomount
SQL> @$ORACLE_HOME/dbs/cntrl.sql
Once the control file’s successfully created

Step 4) open the database with resetlogs option

SQL> alter database open resetlogs;

It will automatically create the temp files on opening