Home » RDBMS Server » Server Administration » URGENT: ORA 12154 on use of DB-Link
URGENT: ORA 12154 on use of DB-Link [message #58323] Thu, 14 August 2003 05:16 Go to next message
Xenofon Grigoriadis
Messages: 33
Registered: May 2002
Member
Hi there,

I try to create a db-link between two Oracle9 DBs.

Creating them goes ok.

But then using testing like this,

select * from /table_name/@/db-link/;

I get the message ORA-12154 "The TNS-Service name could not be resolved" or so (translation).

What's the problem? The service name and the TNSNAMES.ORA are OK because I can connect on both DBs. Creating the link is also ok. Only using the link in SQL produces this error.

Does anybody have a hint? Do you need the TNSNAMES.ORA? I could publish it, if neccessary.

Thanks in advance,

Xenofon Grigoriadis
Re: URGENT: ORA 12154 on use of DB-Link [message #58324 is a reply to message #58323] Thu, 14 August 2003 06:36 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
TNS-12154 TNS:could not resolve service name
Cause:    The service name specified is not defined correctly in the TNSNAMES.ORA file.
Action:    Make the following checks and correct the error:
Verify that a TNSNAMES.ORA file exists and is in the proper place and accessible. 
See the operating system specific manual for details on the required name and location.
Check to see that the service name exists in one of the TNSNAMES.ORA files and add it if necessary.
Make sure there are no syntax errors anywhere in the file. Particularly look for unmatched parentheses or stray characters. 
Any error in a TNSNAMES.ORA file makes it unusable. 
See the SQL*Net V2 Administrator's Guide. If possible, regenerate the configuration files using the Oracle Network Manager.
You're connecting with a user who has access to this DB link?

MHE
Re: URGENT: ORA 12154 on use of DB-Link [message #58325 is a reply to message #58323] Thu, 14 August 2003 06:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
TNS address is not resolved on the client, it is resolved on the server that client's SQL*Plus session is connected to.

so, make sure u have the tnsentries
in in database1 and database2 ( ie. tnsnames files of these two servers).

by convention , create db link name same as connect string.
if it is not helping you,
create db link like this
-- note: change SID to SERVICE_NAME accordingly
-- or just copy the entries from tnsnames.ora
create database link test
connect to scott identified by tiger
using '(description=(address=(protocol=TCP) 
     (host=xxx.xxx.x.xx)(port=xxxx))(connect_data=(sid=xxxx)))'; 

----------------------------------------------------------------------
 if any of these is not helping, post the tnsnames.ora, and ur session

Re: URGENT: ORA 12154 on use of DB-Link [message #58326 is a reply to message #58325] Thu, 14 August 2003 07:47 Go to previous messageGo to next message
Xenofon Grigoriadis
Messages: 33
Registered: May 2002
Member
Thanks for the reply,

here is my create-statement:

CREATE DATABASE LINK /SID/.WORLD CONNECT TO /USER/ IDENTIFIED BY /PASSWD/ USING '/SID/'

Here is my client TNSNAMES.ORA:

/SID/xxx.de =
(DESCRIPTION =
(FAILOVER=ON)
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = aaa.xxx.de)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME =/SID/)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
)
)
)

One thing I have never been quite sure of is, how Oracle handles those strings after the dot, like ".world". How can I be sure if I need that string, when using the link or when creating it? But still I think I tried all possible ways and still it did not work.

Thanks in advance,

Xenofon Grigoriadis
Re: URGENT: ORA 12154 on use of DB-Link [message #58328 is a reply to message #58326] Thu, 14 August 2003 08:37 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
as per your posting
[i] USING '/SID/' [/i]
are u usid sid or the connectstring?
you must use the connectstring not the sid

your statement should be somehting like
CREATE DATABASE LINK [b]dblinkname[/b] 
CONNECT TO USER IDENTIFIED BY PASSWD 
USING [b]'/SID/xxx.de' [/b]-- as appearing in tnsnames.ora
                          -- and this tnsnnames.ora should be in server, NOT in your client
                 

Previous Topic: test
Next Topic: what is the diff in all 3 stmts
Goto Forum:
  


Current Time: Fri Sep 20 13:32:29 CDT 2024