Cloning a ORACLE Data Base

Cloning is faster way to replicate the database; basic principles are same across all oracle versions (8i, 9i, and 10g)

I will demonstrate it using my 8i database installation.

1) Choose a database to be cloned let’s say it is “prod” and we want to make a duplicate of it named “prodc01”
2) Shutdown prod (only in normal or immediate mode)
3) Startup prod in restricted mode
-------------------------------------------------------------------
SVRMGR> startup pfile=c:\OraAmresh\admin\prod\initPROD.ora
LRM-00109: could not open parameter file 'c:\OraAmresh\admin\prod\initPROD.ora'
ORA-01078: failure in processing system parameters
SVRMGR> startup pfile=c:\OraAmresh\admin\prod\initPROD.ora
LRM-00109: could not open parameter file 'c:\OraAmresh\admin\prod\initPROD.ora'
ORA-01078: failure in processing system parameters
SVRMGR> startup pfile=C:\OraAmresh\admin\prod\PFILE\initPROD.ora
ORACLE instance started.
Total System Global Area 73431068 bytes
Fixed Size 75804 bytes
Variable Size 22220800 bytes
Database Buffers 51056640 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
----------------------------------------------------------------------------------
4) Retrieve value of user_dump_dest parameter from pfile (init.ora)
5) Run ‘ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOG ; ‘
----------------------------------------------------------------------------------
SVRMGR> ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;
Statement processed.
SVRMGR>
---------------------------------------------------------------------------------
6) Retrieve file from the path obtained from step 4 and change it as given steps
a) Change the name of file to ‘clone.sql’
b) Remove everything other then ‘CREATE CONTROLFILE’ statement.
c) In ‘CREATE CONTROLFILE’ statement change every occurrence of to in our case from “prod” to “prodc01”
d) Change ‘REUSE’ to ‘SET’


6) Shut down database ‘prod’

-----------------------------------------------------------------------------------

SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> exit
Server Manager complete.
-------------------------------------------------------------------------------------
7) Shut down instance (Services) run below command in command prompt.

ORADIM -SHUTDOWN -SID prod -SHUTTYPE srvc,inst

8) Copy all parameter files, datafiles and Redo log files to new location.

8) Change path and SID information in new parameter file and rename it to init.ora
9) Create services for new instance using below command.
-------------------------------------------------------------------------------------

C:\OraAmresh\admin\prod01\UDUMP>ORADIM -NEW -SID prod01 -SRVC OracleServiceprod01 -PFILE C:\OraAmresh\admin\prod01\pfile
\initPROD01.ora

C:\OraAmresh\admin\prod01\UDUMP>

--------------------------------------------------------------------------------------

10) Log in to svrmgrl and connect to internal

---------------------------------------------------------------------------------------
SVRMGR> connect internal
Connected.
SVRMGR> startup nomount pfile=c:\OraAmresh\admin\prod01\pfile\initPROD01.ora
ORACLE instance started.
Total System Global Area 73431068 bytes
Fixed Size 75804 bytes
Variable Size 22220800 bytes
Database Buffers 51056640 bytes
Redo Buffers 77824 bytes
SVRMGR>
--------------------------------------------------------------------------------------------
11) Run clone.sql created in step 6

--------------------------------------------------------------------------------------------
SVRMGR> connect internal
Connected.
SVRMGR> @prod01.sql
Statement processed.
SVRMGR>
-------------------------------------------------------------------------------------
12) Open database in RESETLOGS mode.
-------------------------------------------------------------------------------------
SVRMGR> ALTER DATABASE OPEN RESETLOGS;
Statement processed.
SVRMGR>
-------------------------------------------------------------------------------------
13) issue shutdown normal command

14) issue startup ;; good work  you have cloned the database

-------------------------------------------------------------------------------------

SVRMGR> startup pfile=c:\OraAmresh\admin\prod01\pfile\initPROD01.ora
ORACLE instance started.
Total System Global Area 73431068 bytes
Fixed Size 75804 bytes
Variable Size 22220800 bytes
Database Buffers 51056640 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
SVRMGR> select name from V$database;
NAME
---------
PROD01
1 row selected.
-------------------------------------------------------------------------------------
Note : Cloning across OS is possible in Oracle Release 10 G