Re: tnsping works but not sqlplus

I've seen this problem before on 8.1.6.0. It was fixed in 8.1.6.2. Don't know about 8.1.5, but here goes:

There is a bug in some versions of the Net8 listener that will affect client connections as they are redirected to an MTS dispatcher or dedicated server. When the listener provides the client with the redirection information, the server host's operating system name (The one found in uname, Etc.) is returned to form the new connection string.

What does this mean? It means that if you specify a server by IP address (because you're not running DNS) or official DNS name (which let's say is different than the box's actual system name) in the connect string that it will get to the listener and get redirected to a name that the client cannot resolve.

Some things that you can try to eliminate your problem:

• Try using an 8.1.6.2.x listener.
• Make sure that your client is able to resolve the server's operating system name BY ITSELF. If your server's name is "biff" then your client had better be able to resolve the name "biff" without a domain suffix or anything else. This might mean that you have to twiddle with your hosts file or possibly your client's default domain settings to get this to work properly.

Hope this helps,

--Roby

"Peter J. Schoenster" wrote:

> Hi,
>
> Thanks to yosi who told me my tnsnames.ora file was okay and
> that I should use the SID and not IP number I tried again. I then
> discovered that I have 2 instances of tnsnames.ora within the
> ORACLE_HOME :) So I changed the one here:
>
> /oracle/app/oracle/network/admin
>
> to be like the one here:
>
> /oracle/app/oracle
>
> And then when I ran this:
>
> tnsping SID
>
> it resolved the name and came back with an OK.
>
> > tnsping SID
>
> TNS Ping Utility for Intel SVR4 UNIX: Version 8.1.5.0.0 -
> Production on 08-NOV-00 15:19:59
>
> (c) Copyright 1997 Oracle Corporation. All rights reserved.
>
> Attempting to contact
> (ADDRESS=(PROTOCOL=TCP)(HOST=IP#)(PORT=1521))
> OK (560 msec)
>
> But when I tried it with sqlplus I get this:
>
> ERROR:
> ORA-12514: TNS:listener could not resolve SERVICE_NAME given
> in connect descriptor
>
> Odd ... I thought if tnsping worked, so should sqlplus. I thought I
> had it :) Anyone with any more clues?
>
> Peter
>
> ---------------------------
> "Reality is that which, when you stop believing in it, doesn't go
> away".
> -- Philip K. Dick
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Peter J. Schoenster
> INET: peter_at_schoenster.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Wed Nov 08 2000 - 18:40:35 CST