DBlink and TNS could not resolve connect identifier specified

I had a strange problem yesterday

We were upgrading Oracle Servers and migrating and back and forth .We need to establish a DB link to move data from one db to one another

Oracle 9.2.0.6 on Solaris 10 =DBNAME=PRD
Oracle 10.2.0.3 on solaris 10=DBNAME=UAT

Steps we followed

1.Connect admin/*******@UAT
Connected

2.create database link PRD connect to admin identified by **** using 'PRD'

where PRD is the service name..

Database link created;


select * from hrm_employee@prd

ora-12514 TNS could not resolve connect identifier specified

PRD service entry is present in the TNSNAMES.ora in the client side..

connect admin/*******@PRD

connectes:

so we tried all the option including replace the 'servicename' in the create DB link stmt with the

'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = *********)(PORT = 1521)) ) (CONNECT_DATA = (SID = PRD) ) )'

But still it didnt work out

Then i realised that DBlink uses the Server TNSNAMES.ora but not the Cleint TNSNAMES.ora which i am connecting from

We added the PRD entry in the TNSNAMES.ora and the DBlink worked


Pretty hard day...

New things are learned everyday..

Cheers maran

More Over

If the GLOBAL_NAMES=TRUE then the DBlink name should be same as the SID name..Or else you will hit the ora-2085 error.