Manually Cloning a Database (Hot Backup Cloning)

Source database name = prod
Target database name = clone

1) On the target(clone) location, create all the directories for the various files:-

2)Copy Oracle home from source(prod) database machine to target(clone) database machine:-

$tar -czvf home.tgz db_1
$scp -r hmoe.tgz oracle@192.168.0.12:/u01
$tar -xzvf home.tgz

3)Create new Init.ora file for Target database (clone):-
* New changes in init.ora file:-

db_name= clone

* DB_FILE_NAME_CONVERT: This parameter transforms the target data file names to the duplicate database data file names.
*LOG_FILE_NAME_CONVERT: This parameter converts the target database redo log file names to the duplicate database redo log file names.

*.Db_file_name_convert='/oradata/oracle/datafiles/prod/','/oradata/datafiles'

*If you have multiple datafile locations at Source database then

*.Db_file_name_convert='/oradata/oracle/datafiles/prod/','/oradata/datafiles','/u01/datafiles/prod','/oradata/datafiles'
*.log_file_name_convert='/oradata/oracle/datafiles/prod/','/oradata/datafiles'

4)Take a Hot backup of Source(prod) datafile to Target(clone):-

sql> alter system switch logfile;
       System altered.
sql> alter database begin backup;
        Database altered.
sql> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/oracle/datafiles/prod/system01.dbf
/oradata/oracle/datafiles/prod/undotbs01.dbf
/oradata/oracle/datafiles/prod/sysaux01.dbf
/oradata/oracle/datafiles/prod/users01.dbf
/oradata/oracle/datafiles/prod/test01.dbf
/oradata/oracle/datafiles/prod/test03.dbf
/oradata/oracle/datafiles/prod/zee01.dbf
/oradata/oracle/datafiles/prod/zee02.dbf
/oradata/oracle/datafiles/prod/zee03.dbf

9 rows selected.

SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata/oracle/datafiles/prod/redo03.log
/oradata/oracle/datafiles/prod/redo02.log
/oradata/oracle/datafiles/prod/redo01.log

SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oradata/oracle/datafiles/prod/control01.ctl
/oradata/oracle/datafiles/prod/control02.ctl
/oradata/oracle/datafiles/prod/control03.ctl

$ cd /oradata/oracle/datafiles/prod/
* Copy the following four sets of files from the Source (prod) database to the target database(clone):-
   control files, data files, and redo log files.

$scp -r * oracle@192.168.0.12:/u01/datafiles           (all datafiles, controlfiles, redolog files)

sql> alter database end backup;
       Database altered.

sql> alter system switch logfile;
       System altered.
5) Take a trace of control file:-

SQL> alter database backup controlfile to trace;
          Database altered.
* Go to udump directory of the source database.

$ ls –ltr

* Copy the last trace file with another name to Target(clone) database and edit it:-

$ cp prod_ora_22305.trc /u01/bkpctrl.sql

$vi bkpctrl.sql
* And change
CREATE CONTROLFILE REUSE DATABASE "CATALOG" RESETLOGS ARCHIVELOG
                                                           to
CREATE CONTROLFILE REUSE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG

* Change all the logfiles locations according to your directories:

GROUP 1 '/oradata/oracle/datafiles/catalog/redo01.log' SIZE 50M,
                                              to
GROUP 1 '/u01/datafiles/redo01.log' SIZE 50M,

* Change all the datafiles locations according to your directories:

'/oradata/oracle/datafiles/catalog/system01.dbf'
                              to
'/u01/datafiles/system01.dbf',

6) Take Backup of all Archivelogs from source(prod) to target(clone):-

7)On Target database:-

$ export ORACLE_HOME=/oradata/oracle/oracle/product/10.2.0/db_1
$ export ORACLE_SID=clone
* Login as a sysdba

* Start database in nomount stage

SQL> startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
* Now run edited controlfiles trace:

SQL> @bkpctrl.sql

         Control file created.

8) Recover database and open with resetlogs:-

Sql>recover database using backup controlfile until cancel;
(apply all archives and specify all redo log files if required)

SQL> alter database open resetlogs;
           Database altered.

SQL> select status from v$instance;
STATUS
------------
OPEN