ORA-02291: integrity constraint (RMAN.RLH_F1) violated

On one of our boxes, we start to get the error stack below when we want to do a crosscheck with catalog database in use.

 

RMAN>crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=357 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of crosscheck command on ORA_DISK_1 channel at 11/26/2009 15:08:05
ORA-19633: control file record 8403 is out of sync with recovery catalog

RMAN>resync catalog; 

starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03008: error while performing automatic resync of recovery catalog
ORA-02291: integrity constraint (RMAN.RLH_F1) violated - parent key not found

I unregistered the DB and register it but after it tries to resync the catalog during registration, error came up like below .

RMAN>unregister database;

database unregistered from the recovery catalog

RMAN>register database
database registered in recovery catalog
starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03008: error while performing automatic resync of recovery catalog
ORA-02291: integrity constraint (RMAN.RLH_F1) violated - parent key not found

 

I could not find a metalink note, but a working solution from forums.oracle.com

Basically, you need to run unregister it by running undocumented internal (Update: I did not check metalink it is documented in 1058332.6)!!!!! DBMS_RCVCAT.UNREGISTERDATABASE procedure on RMAN catalog DB. (ask Oracle support before you start doing something)

 

 

SQL> select 'EXEC DBMS_RCVCAT.UNREGISTERDATABASE('||DB_KEY||','||DBID||');'
3   command, resetlogs_time
4  from rc_database
5  where name = '&db_name';
Enter value for db_name: XXXXXX

COMMAND                                                      RESETLOGS_TIME
------------------------------------------------------------ -----------------
EXEC DBMS_RCVCAT.UNREGISTERDATABASE(5626305,201074392);      20071130 11:57:12

 

 

Run the command if the resetlogs time is right and it will unregister your database from catalog.

Re-register your DB ( RMAN>register database) , if you are lucky as me it will register and sync the catalog without a problem.

Disk backup to Network Drive on Windows with RMAN

We are currently working on moving our databases to different geographic locations and try to look for the best approach. One of the suggested ideas was mapping the target server drive and backup directly to mapped network drive on the target server and get rid of copy step after the backup.

After the initial trials, every time RMAN failed to write to the network drive with the error stack below.

ORA-19504: failed to create file "X:\DB_T686079212_S20_P1"
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.


or


ORA-19504: failed to create file "\\SERVERNAME\DB_T686079212_S20_P1"
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.


Metalink note 145843.1 came up with a solution

Basically all you need to do is update Oracle Services (both listener and Database) to run with Domain Account which has access on both servers with Administrator Privilege.

After you update the services ;
1- Restart the services

2-Use UNC naming instead of using drive letter on your script. (UNC naming part is not mentioned in the note)

3- Run the RMAN backup

Worked fine on my case.

rman behaviour for default device type

Today I got the error below when I try to run crosscheck command for one of our databases

It was weird to get ORA-19554: error allocating device, device type: SBT_TAPE, device name: error for this operation because it says device_type=disk and I was pretty sure that default device type is configured to disk because we are not doing tape backups. when I check with show all the default device type was disk as shown in the output below.

What was the reason. At first, I was thinking having default compressed backup set on disk channels causes this problem but After a search I found this thread on Oracle Forums I saw that The line which says

CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ MAXPIECESIZE 4 G;

causes this problem even if you set default device type. RMAN configuration file was updated through enterprise manager. When you set 4GB backup piece through manager it updates configuration file for both.

When you remove this configuration with the command below it will let you to do maintenance over disk channels.

CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ CLEAR;

Crosscheck command now works through disk channel

Edited: If you dont want to remove this parameter and still want to do crosscheck with disk channels you should try the following command.  (if you doing this over gui you have to write this command instead of the one produced by EM)

ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;

CROSSCHECK BACKUPSET;

Thanks to Michael T. Dihn for his comment.

Database Version is 10.2.0.2

When you lost your controlfile backups

While searching through the metalink I saw an article Doc ID: 372996.1 “Using RMAN to Restore and Recover a Database When the Repository and Spfile/Init.ora Files Are Also Lost”. I wondered, how I can do it, but it only references to an internal article DocID 60545.1 “How to Extract Controlfiles, Datafiles, and Archived Logs from RMAN Backupsets.” with an advice of raising a server request.

The only thing I want is just try the case without disturbing Oracle Support , so I asked the help of google and it helped me as it always do.

Now its time to share what I learned, but please do, what Metalink says ” Raise a Service Request with Oracle Support Services“, when you are in this worst scenario.

First of all, I took a database backup of my test server DABAK, while controlfile autobackup option of. As it is written in official documents, RMAN took controlfile and spfile backups automatically because I backup datafile 1 (SYSTEM) .

Here is the backup list.

Then I move the spfileDABAK.ora, initDABAK.ora and forced the database to start without parameter file like below.

After finishing the first part of the recovery, I moved into the second step ,which is restoring controlfile from backup pieces by calling undocumented dbms_restore package in a PL/SQL block.

restore_foundationsql.txt

DECLARE v_dev varchar2(50); — device type allocated for restore v_done boolean; — has the controlfile been fully extracted yet type t_fileTable is table of varchar2(255) index by binary_integer; v_fileTable t_fileTable; — Stores the backuppiece names v_maxPieces number:=1; — Number of backuppieces in backupsetBEGIN — Initialise the filetable & number of backup pieces in the backupset — This section of code MUST be edited to reflect the customer’s available — backupset before the procedure is compiled and run. In this example, the — backupset consists of 4 pieces:v_fileTable(1):=’C:\backup_test\ORA_DF631909845_S144_P1_C1′; –controlfile v_fileTable(2):=‘C:\backup_test\ORA_DF631909818_S143_P1_C1’; –spfile v_maxPieces:=2; —number of backup pieces — Allocate a device. If the backuppiece is on disk, specify type=>null if it is on tape then spesify ‘sbt_tape’ v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>null, ident=>’d1′); — Begin the restore conversation sys.dbms_backup_restore.restoreSetDatafile; — Specify where the controlfile is to be recreated sys.dbms_backup_restore.restoreControlfileTo(cfname=>‘D:\oracle\oradata\DABAK_TEST\control01.ctl’); –ctrlfile sys.dbms_backup_restore.restorespfileto(‘D:\oracle\oradata\DABAK_TEST\spfile’); –spfile location — Restore the controlfile FOR i IN 1..v_maxPieces LOOP sys.dbms_backup_restore.restoreBackupPiece(done=>v_done, handle=>v_fileTable(i), params=>null); IF v_done THEN GOTO all_done; END IF; END LOOP; <<all_done>> — Deallocate the device sys.dbms_backup_restore.deviceDeallocate; END; /

The only things you must change in the script are backup locations and restore locations . (I don’t know the author of the script so I am sorry for not mentioning the author). I changed them and start the script but the error stack below raised. (Script will ask you a value you can just enter 1 and continue)

I ‘m sure that, sp file is in the backup set, so I decided to comment out spfile line of dbms_restore and start the script for only controlfile.

As you see it worked without any error.
After restoring controlfile, I commented out controlfile line and restored the spfile.

Note that, Mine was just a test database and I reached my goal without any support but you must consult Oracle Support before trying this solution !!!!

For further steps of this recovery you can visit the reference site.

References Used:

Advenced Recovery Article

ORA-600 [KSFD_DECAIOPC] While Taking Image Backups

Today I faced with ORA-600 error while trying image backups. My release is 10.2.0.3 for windows. I made search on metalink and I want to share the solution for the ones who haven’t got metalink access.

The cause of this error is oracle Bug 4541506 and this bug is said to be solved with 10.2.0.4 release. It is also said this error occurs when MAXPIECESIZE is set or configured in the disk channels, nevertheless I faced with the error without any MAXPIECESIZE configuration. But the second solution which is about manual channel allocation is worked for my case.

Solution

A. When channels allocated manually then do not specify MAXPIECESIZE when
producing image copies.

B. If channels are configured with MAXPIECESIZE and image copies are being
produced there are two options:

1) Reconfigure the channels without MAXPIECESIZE

2) Allocate manual channels to produce the image copies.

If you try this solution on the RMAN session which took the ORA-600 error, you may face with RMAN-06034: at least 1 channel must be allocated to execute this command, error as me. To solve the situation shown below, just create a fresh RMAN session.

/*RMAN SESSION WHICH TOOK ORA-600*/ RMAN> run { 2> allocate channel ch1 type disk; 3> backup as copy database; 4> release channel ch1; 5> } RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of allocate command at 08/10/2007 10:44:15 RMAN-06012: channel: ORA_DISK_1 not allocated RMAN> exit Recovery Manager complete. D:\oracle\product\10.2.0\asm\database>rman target /     /*FRESH RMAN SESSION*/ Recovery Manager: Release 10.2.0.3.0 – Production on ¤. ªÕ­ 10 10:47:15 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: DABAK (DBID=3241464088) RMAN> run { 2> allocate channel ch1 type disk; 3> backup as copy database; 4> release channel d1; 5> } using target database control file instead of recovery catalog allocated channel: d1 channel d1: sid=146 devtype=DISK Starting backup at 10.08.2007 file 3 is excluded from whole database backup file 4 is excluded from whole database backup channel d1: starting datafile copy input datafile fno=00001 name=D:\ORACLE\ORADATA\DABAK\SYSTEM01.DBF … … … ..

References Used.

Metalink Note: Note:401681.1

Guaranteed Restore Points

Fantastic feature called guaranteed restore point came with Oracle 10G R2. By this feature, you can set a restore point which will remain until you drop it. The best part of this feature is, it uses db_file_recovery_dest to keep the changed blocks, even if flashback logging is not enabled for your database. The only thing you must do is setting db_file_recovery_dest parameter and create a guaranteed restore point (for the first restore point you must be in mount mode). Before image of changed blocks, will be kept on flashback logs after you created the guaranteed restore point. Flashback logs of guaranteed restore points are not big because of keeping before image of changed blocks for only 1 time after first change.

To create first guaranteed restore point when flashback off, you first start the database in mount state after a consistent shut down. After opening in mount state you create your first guaranteed restore point like below.

SQL>CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Subsequent guaranteed restore points can be created when database is open. When you want to see the available guaranteed restore points you can use the select below

SQL>SELECT NAME, SCN, TIME, DATABASE_INCARNATION# DI,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE=’YES’; NAME SCN TIME DI GUA STORAGE_SIZE ——————– – ——— —————————— ———- — ———— BEFORE_UPGRADE 1011771 07.08.2007 14:14:56,0000 5 YES 0 BEFORE_TRUNCATE 1013509 07.08.2007 14:20:42,0000 5 YES 8192000

When you want to flashback to the guaranteed restore point you must run the command below from rman when DB is in mount mode.

RMAN> FLASHBACK DATABASE TO RESTORE POINT ‘BEFORE_UPGRADE’;

You can drop the restore point if you don’t need it anymore. Then you drop the guaranteed restore point ,the relevant logs will be deleted too.

SQL> drop restore point before_upgrade; Restore point dropped.

What you have to consider is, to keep the flashback logs for the restore point. No file in the flash recovery area is eligible for deletion, if it is required to satisfy the
guarantee. To monitor the db_file_recovery_area usage you can use the query below.

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES ——————– ———– ———- —————– ————— D:\oracle\oradata\fl 1098907648 500303872 0 12 ash_recover_area

If you loose flashback logs or db_file_recovery_dest is full, you should face instance termination. For instance I face with, ORA-38760: This database instance failed to turn on flashback database, error in the middle of 10.2.0.3 upgrade (I had to remove some of flashback logs in db_file_recovery_area during upgrade operation because of space lack) .

The database was not opening because of missed flashback logs. The word guarantee was working quite perfect 🙂 When I drop the guaranteed restore point, database logged the lines below in the alert log and let me open the database smoothly at last.

Deleted Oracle managed file D:\ORACLE\ORADATA\FLASH_RECOVER_AREA\DABAK\FLASHBACK\O1_MF_3C674SNO_.FLB

The moral of story can be, as it will be advised you by OUI, do not enable archive log process when you are doing an upgrade, if you don’t have any standby as Yasin mentioned as a comment below:)

References Used:

Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2)

Flaschback Query on Dropped Table

Necessity of using Flashback Query on dropped tables is disputable, though I found it worthy to tell 🙂 In my opinion, Recycle bin and Flashback Query are most useful features of 10G’s Flashback Concept. Below you will see how to use them together. Please don’t ask me why to use them together, because I couldn’t conjure up an almost real case yet 🙂 Your cases will be welcome as a comment.

/*FLASHBACK QUERY FROM NORMAL TABLE*/ 16:46:25 coskan@DABAK> SELECT * FROM T1 AS OF TIMESTAMP TO_TIMESTAMP(‘2007-08-03 16:45:04’, ‘YYYY-MM-DD HH24:MI:SS’); OBJID CREATION_D LAST_CHANG———- ———- ———-41 03.08.200742 03.08.2007 43 03.08.2007 44 03.08.2007 16:46:32 coskan@DABAK> drop table t1; Table dropped. 16:46:40 coskan@DABAK> SELECT object_name as recycle_name, original_name, type FROM recyclebin; RECYCLE_NAME ORIGINAL_NAME TYPE —————————— ——————————– ————————- BIN$riHbdXwdTB+CF2F6uHK8gg==$0 T1 TABLE BIN$EyQUrilzQLiP9E34k+xlPA==$0 T1_INSERT TRIGGER BIN$5Ysb/qadTD+hJEv3Ro3z5g==$0 T1_UPDATE TRIGGER /*QUERY FROM DROPPED TABLE*/ 16:47:00 coskan@DABAK> SELECT * FROM “BIN$riHbdXwdTB+CF2F6uHK8gg==$0”; OBJID CREATION_D LAST_CHANG ———- ———- ———- 41 03.08.2007 54 03.08.2007 03.08.2007 43 03.08.2007 44 03.08.2007 45 03.08.2007 46 03.08.2007 6 rows selected. /*FLASHBACK QUERY FROM DROPPED TABLE*/ 16:47:30 coskan@DABAK> SELECT * FROM “BIN$riHbdXwdTB+CF2F6uHK8gg==$0” AS OF TIMESTAMP 16:48:16 2 TO_TIMESTAMP(‘2007-08-03 16:45:04’, ‘YYYY-MM-DD HH24:MI:SS’); OBJID CREATION_D LAST_CHANG ———- ———- ———- 41 03.08.2007 42 03.08.2007 43 03.08.2007 44 03.08.2007

For Full Demonstration click code_listing_9

!!! Do not forget that availability of flashback query, depends on undo_retention parameter.

References Used

Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2)

Nice RMAN command for double checkers or careless ones

Suppose that, you have a RMAN command file and you are sure about the syntax of the commands you wrote. You scheduled it to run at midnight ( without testing on any test server) on production system, and when you came to office next day you saw that the script failed due to a syntax error. To avoid this kind of errors RMAN of 10G R2 has a new feature to check the syntax before you run them. The command is CHECKSYNTAX, what it does is only check the syntax of the commands you give as an input, also you do not need any target connection to run this command. Lets see how it works

Usage is simple

C:\Documents and Settings\cgundogar>rman checksyntax Recovery Manager: Release 10.2.0.1.0 – Production on Pzt Tem 30 17:28:29 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.RMAN> backup database;The command has no syntax errors RMAN> backup datafile; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found “;”: expecting one of: “double-quoted-string, integer, single-quoted-string” RMAN-01007: at line 1 column 16 file: standard input

for command files you only give the input file and take the result set;

suppose that your command file is like below

run {
allocate channel ch1 type disk;
restore archivelog ;
}

C:\Documents and Settings\cgundogar>rman checksyntax Recovery Manager: Release 10.2.0.1.0 – Production on Pzt Tem 30 17:28:29 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.RMAN> @rman.txt RMAN> run { 2> allocate channel ch1 type disk; 3> restore archivelog ; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found “;”: expecting one of: “all, from, high, like, logseq, low, scn, sequence, time, until” RMAN-01007: at line 3 column 20 file: rman.txt

When you give the correct command the output will be like below

C:\Documents and Settings\cgundogar>rman checksyntax Recovery Manager: Release 10.2.0.1.0 – Production on Pzt Tem 30 17:28:29 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.RMAN> @rman.txt RMAN> run { 2> allocate channel ch1 type disk; 3> restore archivelog all; 4> } The cmdfile has no syntax errors RMAN> RMAN> **end-of-file**

References Used:

10G R2 Documentation – Backup and Recovery Basics

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.