Clone database using cold backup

1) Create one database ‘PROD’ using dbca

[oracle@oracle10g ~]$ export ORACLE_SID=prod
[oracle@oracle10g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Fri Jul 5 19:38:02 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> select open_mode,log_mode,name from v$database;

OPEN_MODE  LOG_MODE     NAME
---------- ------------ ---------
READ WRITE ARCHIVELOG   PROD

2) Create a pfile for ‘CLONE’ database

SQL> create pfile='$ORACLE_HOME/dbs/initclone.ora from spfile;

File created.

3) Make the necessary changes in pfile of ‘CLONE’ database as shown below

clone.__db_cache_size=188743680
clone.__java_pool_size=4194304
clone.__large_pool_size=4194304
clone.__shared_pool_size=83886080
clone.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/clone/adump'
*.background_dump_dest='/u01/app/oracle/admin/clone/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/clone/control01.ctl','/u01/app/oracle/oradata/clone/control02.ctl','/u01/app/oracle/oradata/clone/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/clone/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='clone'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cloneXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/clone/udump'

4) Fire the following command to backup ‘PROD’ database controlfile to trace file.

SQL> alter database backup controlfile to trace;

After firing the above command open the latest trace file generated in udump folder.Copy the following content and paste it in a text file.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/prod/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/prod/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/prod/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/prod/system01.dbf',
  '/u01/app/oracle/oradata/prod/undotbs01.dbf',
  '/u01/app/oracle/oradata/prod/sysaux01.dbf',
  '/u01/app/oracle/oradata/prod/users01.dbf',
  '/u01/app/oracle/oradata/prod/example01.dbf'
CHARACTER SET WE8ISO8859P1
;

Change the name of the text file as control.sql.
Open the control.sql file and make the necessary changes

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "clone" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/clone/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/clone/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/clone/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/clone/system01.dbf',
  '/u01/app/oracle/oradata/clone/undotbs01.dbf',
  '/u01/app/oracle/oradata/clone/sysaux01.dbf',
  '/u01/app/oracle/oradata/clone/users01.dbf',
  '/u01/app/oracle/oradata/clone/example01.dbf'
CHARACTER SET WE8ISO8859P1
;

5) Shutdown the ‘PROD’ database

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

6) Make necessary directories for clone database

$mkdir /u01/app/oracle/oradata/clone

$mkdir /u01/app/oracle/admin/clone

$mkdir /u01/app/oracle/admin/clone/adump

$mkdir /u01/app/oracle/admin/clone/bdump

$mkdir /u01/app/oracle/admin/clone/cdump

$mkdir /u01/app/oracle/admin/clone/udump

7) Copy all the datafiles and redolog files from ‘PROD’ database directory to ‘CLONE’ database directory

8) Fire following command to connect to CLONE database

[oracle@oracle10g ~]$ export ORACLE_SID=clone
[oracle@oracle10g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Fri Jul 5 20:38:58 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

9) Run the control.sql script on clone database

SQL> @/home/oracle/Desktop/control.sql
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

Control file created.

10) Fire the following command to open the database

SQL> alter database open resetlogs;

Database altered.