Cold cloning oracle database on the same server

Cold cloning oracle database on the same server

Source database SID : ora10gdb01
Clone database SID  : clone1

First connect to the database which you are planning to clone

[oracle@db10g ~]$ . oraenv
ORACLE_SID = [ora10gdb01] ? ora10gdb01

[oracle@db10g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Fri Jun 20 21:25:06 2014

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

Connected to an idle instance.

SYS@ora10gdb01>startup
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size                  2020352 bytes
Variable Size             318770176 bytes
Database Buffers          872415232 bytes
Redo Buffers               14753792 bytes
Database mounted.
Database opened.
SYS@ora10gdb01>

SYS@ora10gdb01>select name from v$datafile;

NAME
——————————————————————————–
/u01/app/oracle/product/10.2.0.1/db01/oradata/ora10gdb01/system01.dbf
/u01/app/oracle/product/10.2.0.1/db01/oradata/ora10gdb01/undotbs01.dbf
/u01/app/oracle/product/10.2.0.1/db01/oradata/ora10gdb01/sysaux01.dbf
/u01/app/oracle/product/10.2.0.1/db01/oradata/ora10gdb01/users01.dbf
/u01/app/oracle/product/10.2.0.1/db01/oradata/ora10gdb01/users02.dbf

SYS@ora10gdb01>create pfile=’$ORACLE_HOME/dbs/initclone1.ora’ from spfile;

File created.

SYS@ora10gdb01>alter database backup controlfile to trace as ‘/tmp/control_script.sql’;

Database altered.

edit pfile for clone1 database

[oracle@db10g ~]$ vi $ORACLE_HOME/dbs/initclone1.ora

contents before change
ora10gdb01.__db_cache_size=872415232
ora10gdb01.__java_pool_size=16777216
ora10gdb01.__large_pool_size=16777216
ora10gdb01.__shared_pool_size=285212672
ora10gdb01.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/product/10.2.0.1/db01/admin/ora10gdb01/adump’
*.background_dump_dest=’/u01/app/oracle/product/10.2.0.1/db01/admin/ora10gdb01/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u01/app/oracle/product/10.2.0.1/db01/oradata/ora10gdb01/control01.ctl’,’/u01/app/oracle/product/10.2.0.1/db01/oradata/ora10gdb01/control02.ctl’,’/u01/app/oracle/product/10.2.0.1/db01/oradata/ora10gdb01/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/product/10.2.0.1/db01/admin/ora10gdb01/cdump’
*.db_block_size=8192
*.db_create_file_dest=’/u01/app/oracle/product/10.2.0.1/db01/oradata/ora10gdb01′
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’ora10gdb’
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest=’/u03/ora10gdb01/flash_recovery_area’
*.db_unique_name=’ora10gdb01′
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora10gdb01XDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=402653184
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=1207959552
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/oracle/product/10.2.0.1/db01/admin/ora10gdb01/udump’

contents after change
clone1.__db_cache_size=872415232
clone1.__java_pool_size=16777216
clone1.__large_pool_size=16777216
clone1.__shared_pool_size=285212672
clone1.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/product/10.2.0.1/db01/admin/clone1/adump’
*.background_dump_dest=’/u01/app/oracle/product/10.2.0.1/db01/admin/clone1/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u01/app/oracle/product/10.2.0.1/db01/oradata/clone1/control01.ctl’,’/u01/app/oracle/product/10.2.0.1/db01/oradata/clone1/control02.ctl’,’/u01/app/oracle/product/10.2.0.1/db01/oradata/clone1/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/product/10.2.0.1/db01/admin/clone1/cdump’
*.db_block_size=8192
*.db_create_file_dest=’/u01/app/oracle/product/10.2.0.1/db01/oradata/clone1′
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’ora10gdb’
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest=’/u03/clone1/flash_recovery_area’
*.db_unique_name=’clone1′
*.dispatchers='(PROTOCOL=TCP) (SERVICE=clone1XDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=402653184
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=1207959552
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/oracle/product/10.2.0.1/db01/admin/clone1/udump’

make changes to the controlfile the contents should be

vi /tmp/control_script.sql
CREATE CONTROLFILE SET  DATABASE  “clone1” RESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u01/app/oracle/product/10.2.0.1/db01/oradata/clone1/redo01.log’  SIZE 50M,
GROUP 2 ‘/u01/app/oracle/product/10.2.0.1/db01/oradata/clone1/redo02.log’  SIZE 50M,
GROUP 3 ‘/u01/app/oracle/product/10.2.0.1/db01/oradata/clone1/redo03.log’  SIZE 50M
DATAFILE
‘/u01/app/oracle/product/10.2.0.1/db01/oradata/clone1/system01.dbf’,
‘/u01/app/oracle/product/10.2.0.1/db01/oradata/clone1/undotbs01.dbf’,
‘/u01/app/oracle/product/10.2.0.1/db01/oradata/clone1/sysaux01.dbf’,
‘/u01/app/oracle/product/10.2.0.1/db01/oradata/clone1/users01.dbf’,
‘/u01/app/oracle/product/10.2.0.1/db01/oradata/clone1/users02.dbf’
CHARACTER SET WE8ISO8859P1
;

create directories as referred in pfile
[oracle@db10g ~]$ mkdir -p /u01/app/oracle/product/10.2.0.1/db01/admin/clone1/adump
[oracle@db10g ~]$ mkdir -p /u01/app/oracle/product/10.2.0.1/db01/admin/clone1/bdump
[oracle@db10g ~]$ mkdir -p /u01/app/oracle/product/10.2.0.1/db01/admin/clone1/cdump
[oracle@db10g ~]$ mkdir -p /u01/app/oracle/product/10.2.0.1/db01/admin/clone1/udump
[oracle@db10g ~]$ mkdir -p /u01/app/oracle/product/10.2.0.1/db01/oradata/clone1
[oracle@db10g ~]$ mkdir -p /u03/clone1/flash_recovery_area

copy all datafile to clone directory
[oracle@db10g ~]$ cd /u01/app/oracle/product/10.2.0.1/db01/oradata/ora10gdb01
[oracle@db10g ora10gdb01]$ ll
total 1373152
-rw-r—–  1 oracle oinstall   7061504 Jun 20 21:30 control01.ctl
-rw-r—–  1 oracle oinstall   7061504 Jun 20 21:30 control02.ctl
-rw-r—–  1 oracle oinstall   7061504 Jun 20 21:30 control03.ctl
drwxr-x—  3 oracle oinstall      4096 Jun  4 20:16 ORA10GDB01
-rw-r—–  1 oracle oinstall  52429312 Jun 20 21:30 redo01.log
-rw-r—–  1 oracle oinstall  52429312 Jun 20 21:25 redo02.log
-rw-r—–  1 oracle oinstall  52429312 Jun 20 21:25 redo03.log
-rw-r—–  1 oracle oinstall 272637952 Jun 20 21:30 sysaux01.dbf
-rw-r—–  1 oracle oinstall 503324672 Jun 20 21:30 system01.dbf
-rw-r—–  1 oracle oinstall  27271168 Jun 14 06:00 temp01.dbf
-rw-r—–  1 oracle oinstall 429924352 Jun 20 21:30 undotbs01.dbf
-rw-r—–  1 oracle oinstall   6561792 Jun 20 21:30 users01.dbf
-rw-r—–  1 oracle oinstall  10493952 Jun 20 21:30 users02.dbf

[oracle@db10g ora10gdb01]$ cp -v *.dbf $ORACLE_HOME/oradata/clone1

[oracle@db10g ora10gdb01]$ export ORACLE_SID=clone1
[oracle@db10g ora10gdb01]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0.1/db01

[oracle@db10g ora10gdb01]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Fri Jun 20 21:47:18 2014

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

Connected to an idle instance.

SYS@clone1>startup nomount
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size                  2020352 bytes
Variable Size             318770176 bytes
Database Buffers          872415232 bytes
Redo Buffers               14753792 bytes
SYS@clone1>

SYS@clone1>@/tmp/control_script.sql

Control file created.

SYS@clone1>alter database open resetlogs;

Database altered.

SYS@clone1>select name from v$database;

NAME
———
CLONE1

Edit the oratab file for clone database

[oracle@db10g dbs]$ vi /etc/oratab

[oracle@db10g dbs]$ cat /etc/oratab
#
# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.

# A colon, ‘:’, is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, ‘#’, are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , “Y”, or should not,
# “N”, be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
ora10gdb01:/u01/app/oracle/product/10.2.0.1/db01:N
10gdb02:/u01/app/oracle/product/10.2.0.1/db01:N
clone1:/u01/app/oracle/product/10.2.0.1/db01:N

for any queries write to me at sunil@sunilthetechfreak.com