Oracle database cloning in 11g R2

In this post how to clone an Oracle Database is shown with 11g R2 Version on Windows platform. This post is just for learning purpose. Don't directly try on Production databases till you get the insight of it.

Old database -  test007
New cloned database - clonetest007

Step 1: Bring the old database test007 in Archivelog Mode.

SET ORACLE_SID=test007
SQLPLUS / as sysdba;
SQL> Archive log List

The output can be ARCHIVELOG or NOARCHIVELOG. We will go with ARCHIVELOG mode so following is the step for it.

SQL> Alter System Set LOG_ARCHIVE_DEST_1='Location=E:\oradata\test007\Archived_logs' scope=SPFILE;

Step 2: Now we will shutdown the database and start it in Archivelog Mode;

SQL> Shutdown Immediate;

SQL> startup mount;

SQL> Alter database Archivelog;

SQL> Alter database Open;

With this query let's check if our old database test007 is now in Archivelog mode or not.

SQL> select log_mode from v$database; OR SQL> Archive log list;

Step 3: Now we will create a new database with the clone of old one.

First we will create new folders for our new database.

We will change our directory on command prompt till the oradata where the data files exists.

e:> cd e:\app\product\11.2.0\dbhome_1\oradata\
e:\app\product\11.2.0\dbhome_1\oradata> mkdir clonetest007
e:\app\product\11.2.0\dbhome_1\oradata> cd clonetest007

e:\app\product\11.2.0\dbhome_1\oradata\cd clonetest007> mkdir Archived_Logs

Step 4: Now we will create the PFILE parameter file for the new database clonetest007

Lets create as "initCLONETEST007.ora" in ORACLE_HOME\database with parameter

db_name=clonetest007

Save the file.

Step 5: Now we will create the window service for this oracle database with the shipped "oradim" utility.

oradim -new -sid clonetest007

Instance Created.

We can also start this window service if it's not started with the below command.

net start OracleServiceclonetest007

Step 6: Let's now take the backup of old database test007

set ORACLE_SID=test007

SQLPLUS / AS SYSDBa;

SQL> select log_mode from v$database; ( Archivelog)

SQL> select * from v$backup;

If status is NOT ACTIVE then make it ACTIVE

SQL> Alter database begin backup;

SQL> quit;

Step 7: Now copy all the datafiles *.dbf to the oradata/clonetest007 folder which we created in above steps.

SQL> Alter database end backup;

SQL> Alter system archive log current;

SQL> Alter database backup control file to trace as 'e:\clonetest007.sql';

Open the trace file & make following changes:

REUSE - SET
NORESETLOGS - RESETLOGS
test007 - clonetest007

Remove comments and extra spaces. Keep the text till character set else delete and save it.

Step 8: Now lets start the new database clonetest007

set ORACLE_SID=clonetest007

SQLPLUS / AS SYSDBA;

Connected to an Idle Instance.

Run the trace file now created in above step.

SQL> e:\clonetest007.sql;

Control File Created;

Step 9: Now we will take the current archive log of old database for recovery of new database.

set ORACLE_SID=test007

SQLPLUS / AS SYSDBA;

SQL> Alter system archive log current;

SQL> /

SQL> /

SQL> quit;

Step 10: Recover and start the new database.

set ORACLE_SID=clonetest007

 SQLPLUS / AS SYSDBA;

SQL> Set logsource 'e:\oradata\test007\Archived_logs';

Press Enter four times then Cancel Type.

SQL> Alter database open resetlogs;


************************************** Done ************************************



 

Cannot open the Windows NT Service Control Manager.

This is the most common error that we face in using oracle "oradim" utility from command line:

DIM-00014: Cannot open the Windows NT Service Control Manager.

O/S-Error: (OS 5) Access is denied.

Solution:

This is just an administrator priviledge/right's issue.

Start the command line "cmd.exe" by right click and Run as Administrator.