Cloning Database on Same Machine on Windows without rman clone or rman restore

Yesterday I tried to clone a database instance to the same windows machine. I used rman copy datafile command for this process. The primary database is 10.2.0.1 EE with default installation database having ORACLE_SID=OCP . What I want was to create a clone database with ORACLE_SID=OCPDENE

Here is step by step what I did and what i faced

First of all I created the file hierarchy for datafiles and logfiles then I created a pfile of OCP database and backed the controlfile to trace.

sys@OCP> create pfile from spfile; File created.sys@OCP> alter database backup controlfile to trace; Database altered.

Then I spool a datafile copy command from directory structure of OCP to directory structure of OCPDENE database.

sys@OCP> select ‘COPY DATAFILE ‘||FILE_ID||’ TO ”D:\oracle\product\10.2.0\oradata\ocpdene\’||SUBSTR(FILE_NAME,38,50)||”’;’ from dba_data_files 2 ;’COPYDATAFILE’||FILE_ID||’TO”D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\’||SUBSTR(FILE_NAME,20,50)||’ —————————————————————————————————- COPY DATAFILE 4 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\USERS01.DBF’; COPY DATAFILE 3 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\SYSAUX01.DBF’; COPY DATAFILE 2 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\UNDOTBS01.DBF’; COPY DATAFILE 1 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\SYSTEM01.DBF’; COPY DATAFILE 5 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\EXAMPLE01.DBF’;

Right after the spooling I modified the initocpdene.ora with the new datafile,logfile, dumpfile flash recovery area destinations and new SID.

Next modification was on listener.ora for adding listener_OCPDENE service.

The last modification was for controlfile creation script. I chosed the set database and resetlogs options like below

STARTUP NOMOUNTCREATE CONTROLFILE SET DATABASE “OCPDENE” RESETLOGS ARCHIVELOGMAXLOGFILES 16 MAXLOG MEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\REDO01.LOG’ SIZE 50M, GROUP 2 ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\REDO02.LOG’ SIZE 50M, GROUP 3 ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\REDO03.LOG’ SIZE 50M — STANDBY LOGFILE DATAFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\SYSTEM01.DBF’, ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\UNDOTBS01.DBF’, ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\SYSAUX01.DBF’, ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\USERS01.DBF’, ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\EXAMPLE01.DBF’ CHARACTER SET TR8MSWIN1254 ; RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE ALTER DATABASE OPEN RESETLOGS; ALTER TABLESPACE TEMP ADD TEMPFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\TEMP01.DBF’ SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Everything was ready I connected to RMAN and start the command below

RMAN>run1> { 2> allocate channel ch1 type disk; 3> allocate channel ch2 type disk; 4> COPY DATAFILE 4 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\USERS01.DBF’; 5> COPY DATAFILE 3 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\SYSAUX01.DBF’; 6> COPY DATAFILE 2 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\UNDOTBS01.DBF’; 7> COPY DATAFILE 1 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\SYSTEM01.DBF’; 8> COPY DATAFILE 5 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\EXAMPLE01.DBF’; 9> release channel ch1; 10> release channel ch2; 11> }; … .. ..

it was time to connect to database OCPDENE but nothing could go this smooth for an Oracle Database creation:)

C:\Documents and Settings\admin>set ORACLE_SID=OCPDENE C:\Documents and Settings\admin>sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 – Production on Fri Apr 20 15:27:03 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. ERROR:ORA-12560: TNS: protocol adapter error

What was this ? I must take a connected to an idle instance warning not ORA-12560.

Ohhhh I forgot I was on Windows everything could go different with oracle.

The problem was that I was trying to connect a database which did not have a service defined for it. So windows behaved crazy as if it was connecting through a listener. By this cloning operation i learned the tool oradim utility. Shortly Oradim is the tool to administer database related service on windows platforms.

I created the service by the command below and this service created OracleServiceocpdene OracleJobSchedulerocpdene and OracleOraDb10g_home1TNSListenerlistener_ocpdene service.

C:\Documents and Settings\admin> oradim -NEW -SID ocpdene -SRVC OracleServiceOCPDENE -STARTMODE manual -PFILE D:\oracle\product\10.2.0\db_1\database\initocpdene.oraInstance Created

After the service creations I started the service and connect the database and startup nomount the OCPDENE database. Run the control file creation script and script wanted the archive logs which were produced during datafile copy operation. I applied them and press cancel and open the database with resetlogs option (ALTER DATABASE OPEN RESETLOGS)

Now I have a clone database on the same machine different names and directory hierarch with same names.