Home » Server Options » Replication » A question about CREATE PUBLIC DATABASE LINK and ORA-12154 error
icon5.gif  A question about CREATE PUBLIC DATABASE LINK and ORA-12154 error [message #431485] Tue, 17 November 2009 10:46 Go to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Dear all,


I have a problem about public database link creation and I would appreciate if you could kindly give me a hand. I have the following connection parameters in my tnsnames.ora file:

...
DGPAPROD.WORLD = 
	(DESCRIPTION = 
	  (ADDRESS_LIST = 
	   (ADDRESS = (COMMUNITY=tcp.world)
		(PROTOCOL=TCP)(HOST=ORASR001)(PORT=1521)
	    ) 
	  )
       (CONNECT_DATA = (SID = DGPAPROD))
    )
...


Having the above mentioned parameters I can connect to this remote database directly in a SQL*Plus shell:
$ sqlplus username/password@DGPAPROD.WORLD


This works pretty well and the connection is established without any problem.

Now, what I would like to do is to create a public database link to this remote database in order to avoid the user/connection switching for viewing the content of this database. I proceeded according to the syntax indicated in the Oracle online documentation:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_5005.htm#SQLRF01205

Therefore I run the following in order to create a public database link
CREATE PUBLIC DATABASE LINK SR001_dblink CONNECT TO user IDENTIFIED BY password USING 'DGPAPROD.WORLD';



Apparently there is no error and the link is created successfully. However it cannot resolve the remote host and whenever I run the following query (myenterprise is the name of a table in that remote database)
SELECT *
FROM myenterprise@SR001_dblink

ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified



What causes this problem?

Thanks in advance,


Kind Regards,
Dariyoosh
Re: A question about CREATE PUBLIC DATABASE LINK and ORA-12154 error [message #431487 is a reply to message #431485] Tue, 17 November 2009 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The tnsnames.ora pointed by the environment of the user that started the instance must contain the service name.

Regards
Michel
Re: A question about CREATE PUBLIC DATABASE LINK and ORA-12154 error [message #431604 is a reply to message #431487] Wed, 18 November 2009 08:03 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Michel Cadot wrote on Tue, 17 November 2009 17:50
The tnsnames.ora pointed by the environment of the user that started the instance must contain the service name.

Regards
Michel



Thanks a lot for your help. In fact this was the problem. Now it works!


Kind Regards,
Dariyoosh
Re: A question about CREATE PUBLIC DATABASE LINK and ORA-12154 error [message #434418 is a reply to message #431604] Wed, 09 December 2009 03:48 Go to previous messageGo to next message
mrbaddy@rediffmail.com
Messages: 5
Registered: December 2009
Junior Member
Hi Dariyoosh,

Can you post the modified TNSNAMES.ORA so that I can notice the change you made?

I am also facing a similar kind of issue.

Regards,
Badarinath
Re: A question about CREATE PUBLIC DATABASE LINK and ORA-12154 error [message #434422 is a reply to message #434418] Wed, 09 December 2009 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think he didn't modify the tnsnames.ora but pointed to the correct one (or copy the correct one to the one he was pointed to).

Regards
Michel
Re: A question about CREATE PUBLIC DATABASE LINK and ORA-12154 error [message #434990 is a reply to message #434418] Sun, 13 December 2009 03:35 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
mrbaddy@rediffmail.com wrote on Wed, 09 December 2009 10:48
Hi Dariyoosh,

Can you post the modified TNSNAMES.ORA so that I can notice the change you made?

I am also facing a similar kind of issue.

Regards,
Badarinath




Hello there,

Sorry for the delay to answer your question, I just saw your post this morning. As dear Michel explained, all that has to be done is that you refer to the correct tnsnames.ora on the right server. In order to better clarify this I'm going to describe my situation. I have the oracle 10g client on my system which of course has a TNSNAMES.ORA file allowing me to connect to an oracle server which I name it here srvA.

What I wanted to do was to be able to access the data on the srvB while connecting to the srvA without needing to change the user connection. Therefore, I had to create a database link from srvA to srvB. But the mistake that I made was that I forgot the fact that the TNSNAMES.ORA of srvA didn't include the proper entry. This is important because when you create a database link from srvA to srvB, it is in fact srvA that becomes client (in this context) of the srvB so if there is a TNSNAMES.ORA file that has to be checked (or modified if needed) it is the one of the srvA and surely not the one installed on your PC used for connecting to srvA.

However, if for some reason it is not possible to change the TNSNAMES.ORA of the srvA (for example if the administrator is not currently available), you can create the database link directly with its whole content instead of using the tns entry in the tnsnames.ora file. So in my situation the link is created in the following way:

CREATE PUBLIC DATABASE LINK SR001_dblink CONNECT TO user IDENTIFIED BY password USING 
          '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY=tcp.world)
		(PROTOCOL=TCP)(HOST=here I write the IP address of srvB)(PORT=1521)
	    ) 
	  )
       (CONNECT_DATA = (SID = DGPAPROD))
    )';


And this works pretty well. However I noticed that if you create the database link in this way, you have to specify the IP address of the destination server and not its hostname (even if you access the server by using its hostname) and I have no explanation for that!

However using directly the IP address of the server has a drawback: if one day for some reason the IP address of the server is changed your database link will not work until it has updated with the new IP address.


I hope this can help.


Kind Regards,
Dariyoosh
Re: A question about CREATE PUBLIC DATABASE LINK and ORA-12154 error [message #435241 is a reply to message #434990] Tue, 15 December 2009 05:52 Go to previous message
mrbaddy@rediffmail.com
Messages: 5
Registered: December 2009
Junior Member
Thanks Dariyoosh.
Previous Topic: materialized view replication
Next Topic: which edition is better EE vs SE vs SE1
Goto Forum:
  


Current Time: Thu Mar 28 03:33:21 CDT 2024