Re: TNS Resolution

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Wed, 18 Jan 2006 11:24:35 -0500
Message-Id: <1137601475l.4691l.5l@medo.noip.com>

On 01/18/2006 10:50:53 AM, Jonathan Knight wrote:
> I have a strange (to me anyway) problem with TNS.

The problem is not strange. It is a configuration problem.

>
> Connecting with SQL*Plus, SQL*Navigator, TOAD: I get
> "ORA-12154: TNS:could not resolve service name"

You should kiss the toad and fix your tnsnames.ora

>
> But, tnsping returns in 100-130 msecs. ?!?!?!

The tnsping contacts the listener. If the listener is there, TNSPING is successful. Service name, on the other hand, is a property of an instance, not listener.

>
> I'm confused. Any thoughts?

Jonathan, your TNS descriptor should look something like this: O10G =

(DESCRIPTION =

     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = oracle)
       (SEVER = DEDICATED)
     )

)

To be able to connect, your database must have service name defined like this: SQL> show parameter service_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      oracle

SQL> Your problem is that the database doesn't have the same service name as your tnsnames.ora. What you need to do is either to change SERVICE_NAME into SID and then put the appropriate SID or set SERVICE_NAME to the value exported by the instance. If your database isn't RAC, in which case several instances may have the same value for the service_name both methods are equivalent. From the aesthetic point of view service name is strongly preferred, at least by me.

-- 
Mladen Gogala
http://www.mgogala.com
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 18 2006 - 10:25:02 CST