Coskan’s Approach to Oracle

April 20, 2007

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

Filed under: Backup/Recovery — coskan @ 1:22 pm

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.

About these ads

13 Comments »

  1. Çok hoş bir post olmuş.Ellerinize sağlık.

    Comment by husnu sensoy — May 7, 2007 @ 6:52 am

  2. Thank you very much for the comment
    There is One Important thing to consider;
    If you want to take backup of this new clone database with recovery catalog which is same with the primary database you have to change Database ID with DBNEWID utility

    Comment by coskan — May 7, 2007 @ 6:58 am

  3. Hi Coskan,

    Nice one, I will try this in my home as of now not in office :-)

    Comment by Sreenivasan — July 23, 2007 @ 1:36 pm

  4. Good luck to you then

    Comment by coskan — July 23, 2007 @ 1:48 pm

  5. Hi Coskan

    Just what I was looking for (haven’t tried it yet though…):

    A short and well documented description of, what should be, a moundane task. By far the best I’ve found after googling for 1 hour.

    Thank you
    Kenneth

    Comment by Kenneth — August 19, 2007 @ 10:27 am

  6. hi ,

    i want to ask that is we require to create orpwSID_NAME.ora for the clone database?

    thanks
    Arun Varma

    Comment by arun — January 9, 2008 @ 11:44 am

  7. hi coskan,

    I had serious doubts if i could clone my existing instance – your document was of great help. Good work friend.

    cheers mate!.

    Comment by vijay — April 17, 2008 @ 6:12 pm

  8. Coskan,

    Short and handy documentation

    Cheers !

    Comment by RameshSubbanna — June 13, 2008 @ 7:08 pm

  9. hi,
    Thank you very much for this topic. I have work hard to try it and now, I can do that. I very serius when error “Protocol adepter error.” I begin oracle.

    Cheers. ^_^

    Comment by Akamon — December 22, 2008 @ 2:51 am

  10. Hi dear thanks A lot it is a nice document i tried and got success can u post some more documents like this that will be halpful

    Comment by AMJAD — March 5, 2009 @ 12:05 pm

  11. can you please explain a little more about the Service Creation command:

    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

    We are creating only one service in this command, however, you have mentioned that the command will create 3 services namely – OracleServiceocpdene, OracleJobSchedulerocpdene and OracleOraDb10g_home1TNSListenerlistener_ocpdene. How does that happen. Also, is the last keyword in the command “Created” included in the command ?

    Thanks.

    Comment by muneer — April 27, 2010 @ 1:56 pm

  12. I don’t think I’ve seen this said in such a way before. You really have cleared this up for me. Thank you!

    Comment by LUISA HUMPHREY — June 1, 2010 @ 12:30 am

  13. Thanks man!
    Its realy greate article.

    Comment by Muhammad Nadeem Chaudhry — March 14, 2011 @ 7:42 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 199 other followers

%d bloggers like this: