Hot clone oracle database on same server

Hot cloning oracle database on the same server

source database : ora10gdb01
clone  database : clone2

database is up and running
[oracle@db10g /]$ ps -ef | grep pmon
oracle    6603     1  0 Jun20 ?        00:00:02 ora_pmon_clone1
oracle   10339     1  0 16:28 ?        00:00:00 ora_pmon_ora10gdb01
oracle   10484 10300  0 16:36 pts/4    00:00:00 grep pmon

[oracle@db10g ~]$ . oraenv
ORACLE_SID = [oracle] ? ora10gdb01
[oracle@db10g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Sat Jun 21 16:44:16 2014

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

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

SYS@ora10gdb01>select name from v$database;

NAME
———
ORA10GDB

Check the datafiles in your database
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

create pfile so you can edit for your clone2 database
SYS@ora10gdb01>create pfile=’/$ORACLE_HOME/dbs/initclone2.ora’ from spfile;

File created.

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

contents after changing initclone2.ora
[oracle@db10g ~]$ cat $ORACLE_HOME/dbs/initclone2.ora
clone2.__db_cache_size=872415232
clone2.__java_pool_size=16777216
clone2.__large_pool_size=16777216
clone2.__shared_pool_size=285212672
clone2.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/product/10.2.0.1/db01/admin/clone2/adump’
*.background_dump_dest=’/u01/app/oracle/product/10.2.0.1/db01/admin/clone2/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u01/app/oracle/product/10.2.0.1/db01/oradata/clone2/control01.ctl’,’/u01/app/oracle/product/10.2.0.1/db01/oradata/clone2/control02.ctl’,’/u01/app/oracle/product/10.2.0.1/db01/oradata/clone2/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/product/10.2.0.1/db01/admin/clone2/cdump’
*.db_block_size=8192
*.db_create_file_dest=’/u01/app/oracle/product/10.2.0.1/db01/oradata/clone2′
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’clone2′
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest=’/u03/clone2/flash_recovery_area’
*.db_unique_name=’clone2′
*.dispatchers='(PROTOCOL=TCP) (SERVICE=clone2XDB)’
*.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/clone2/udump’
[oracle@db10g ~]$

create required directories
[oracle@db10g tmp]$ mkdir -p /u01/app/oracle/product/10.2.0.1/db01/admin/clone2/adump
[oracle@db10g tmp]$ mkdir -p /u01/app/oracle/product/10.2.0.1/db01/admin/clone2/bdump
[oracle@db10g tmp]$ mkdir -p /u01/app/oracle/product/10.2.0.1/db01/admin/clone2/udump
[oracle@db10g tmp]$ mkdir -p /u01/app/oracle/product/10.2.0.1/db01/admin/clone2/cdump
[oracle@db10g tmp]$ mkdir -p /u01/app/oracle/product/10.2.0.1/db01/oradata/clone2/
[oracle@db10g tmp]$ mkdir -p /u03/clone2/flash_recovery_area

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

Database altered.

SYS@ora10gdb01>alter system switch logfile;

System altered.

SYS@ora10gdb01>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     45
Next log sequence to archive   47
Current log sequence           47

SYS@ora10gdb01>alter database begin backup;

Database altered.

SYS@ora10gdb01>!
[oracle@db10g ~]$ cp /u01/app/oracle/product/10.2.0.1/db01/oradata/ora10gdb01/*.dbf $ORACLE_HOME/oradata/clone2/

updated few data
SUNIL@ora10gdb01>update emp set sal=&sal where empno=&empno
2  /
Enter value for sal: 1500
Enter value for empno: 7934
old   1: update emp set sal=&sal where empno=&empno
new   1: update emp set sal=1500 where empno=7934

1 row updated.

SUNIL@ora10gdb01>/
Enter value for sal: 7500
Enter value for empno: 7839
old   1: update emp set sal=&sal where empno=&empno
new   1: update emp set sal=7500 where empno=7839

1 row updated.

SUNIL@ora10gdb01>/
Enter value for sal: 2000
Enter value for empno: 7900
old   1: update emp set sal=&sal where empno=&empno
new   1: update emp set sal=2000 where empno=7900

1 row updated.

SUNIL@ora10gdb01>commit;

Commit complete.

SYS@ora10gdb01>alter database end backup;

Database altered.

SYS@ora10gdb01>alter system switch logfile;

System altered.

SYS@ora10gdb01>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     47
Next log sequence to archive   49
Current log sequence           49
SYS@ora10gdb01>
SYS@ora10gdb01>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

edit the controlfile
[oracle@db10g ~]$ vi /tmp/control_script.sql
CREATE CONTROLFILE SET DATABASE “clone2” 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/clone2/redo01.log’  SIZE 50M,
GROUP 2 ‘/u01/app/oracle/product/10.2.0.1/db01/oradata/clone2/redo02.log’  SIZE 50M,
GROUP 3 ‘/u01/app/oracle/product/10.2.0.1/db01/oradata/clone2/redo03.log’  SIZE 50M
DATAFILE
‘/u01/app/oracle/product/10.2.0.1/db01/oradata/clone2/system01.dbf’,
‘/u01/app/oracle/product/10.2.0.1/db01/oradata/clone2/undotbs01.dbf’,
‘/u01/app/oracle/product/10.2.0.1/db01/oradata/clone2/sysaux01.dbf’,
‘/u01/app/oracle/product/10.2.0.1/db01/oradata/clone2/users01.dbf’,
‘/u01/app/oracle/product/10.2.0.1/db01/oradata/clone2/users02.dbf’
CHARACTER SET WE8ISO8859P1
;

[oracle@db10g ~]$ export ORACLE_SID=clone2
[oracle@db10g ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0.1/db01
[oracle@db10g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Sat Jun 21 17:45:14 2014

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

Connected to an idle instance.

SYS@clone2>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@clone2>@/tmp/control_script.sql

Control file created.

SYS@clone2>

Now recover the database using backup control file

SYS@clone2>recover database using backup controlfile until cancel;

you will be prompted for archive log files. Specify the full path and file name from your source database as you have not copied the
archive log files on the target because we are cloning on the same server. you have to keep specifying the archive log till you cross the
last old sequence then type cancel to end the media recovery

SYS@clone2>alter database open resetlogs;

Also update the /etc/oratab file for new cloned database

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