How to Clone the Oracle Database using Cold Backup

STEP 1:Create a control file from source instance env

SQL > alter database backup controlfile to trace;

Control File Script:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "qbay11g" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 240
    MAXINSTANCES 1
    MAXLOGHISTORY 113
LOGFILE
  GROUP 1 '/u03/oracle11g/oracle11g/oradata/qbay11g/redo01.log'  SIZE 50M,
  GROUP 2 '/u03/oracle11g/oracle11g/oradata/qbay11g/redo02.log'  SIZE 50M,
  GROUP 3 '/u03/oracle11g/oracle11g/oradata/qbay11g/redo03.log'  SIZE 50M
DATAFILE
  '/u03/oracle11g/oracle11g/oradata/qbay11g/system01.dbf',
  '/u03/oracle11g/oracle11g/oradata/qbay11g/undotbs01.dbf',
  '/u03/oracle11g/oracle11g/oradata/qbay11g/sysaux01.dbf',
  '/u03/oracle11g/oracle11g/oradata/qbay11g/users01.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

STEP 2: Shutdown the Source database

SQL>shutdown immediate;

STEP 3: Copy all data files into the new directories on the new server.

You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server

Example:

scp -r  /u01/oracle/oradata  oracle@targetdatabse.com:/u01/oracle/oradata

STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

Old:

CREATE CONTROLFILE REUSE DATABASE "qbay11g" NORESETLOGS

New:

CREATE CONTROLFILE SET DATABASE "qborcl" RESETLOGS

STEP 5: Remove the "recover database" and "alter database open" syntax

# Recovery is required if any of the datafiles are restored

# backups, or if the last shutdown was not normal or immediate.

RECOVER DATABASE

# Database can now be opened normally.

ALTER DATABASE OPEN;

STEP 6: Re-names of the data files names that have changed

Save as db_create_controlfile.sql.

Old:

DATAFILE

  '/u03/oracle11g/oracle11g/oradata/qbay11g/system01.dbf',

  '/u03/oracle11g/oracle11g/oradata/qbay11g/undotbs01.dbf',

  '/u03/oracle11g/oracle11g/oradata/qbay11g/sysaux01.dbf',

  '/u03/oracle11g/oracle11g/oradata/qbay11g/users01.dbf'

New:

DATAFILE

  '/u02/oracle11g/oracle11g/oradata/qborcl/system01.dbf',

  '/u02/oracle11g/oracle11g/oradata/qborcl/undotbs01.dbf',

  '/u02/oracle11g/oracle11g/oradata/qborcl/sysaux01.dbf',

  '/u02/oracle11g/oracle11g/oradata/qborcl/users01.dbf'

STEP 7: Create the bdump, udump and cdump directories

cd $DBA/admin

mkdir newlsq

cd newlsq

mkdir bdump

mkdir udump

mkdir cdump

mkdir pfile

STEP 8: Copy-over the old init.ora file

rcp $DBA/admin/source/pfile/*.ora targethost:/u01/oracle/admin/target/pfile

STEP 9: Start the new database

SQL> startup mount pfile=initqborcl.ora;

SQL>@db_create_controlfile.sql

STEP 10: Open the new Cloned database

SQL> alter database open resetlogs;

                    ****ALL THE BEST****