Re: tnsping fails, yet sqlplus connects

From: EdStevens <quetico_man_at_yahoo.com>
Date: 29 Jun 2006 11:38:49 -0700
Message-ID: <1151606329.200395.10150@b68g2000cwa.googlegroups.com>

replies embedded:

hpuxrac wrote:
> EdStevens wrote:
> > Oracle 10.2.0.1.0 on Windows 2003 server
> >
> > While chasing down a problem with a db link, I ran across this bit of
> > strangeness. Our temp hired gun -- who is far better than me -- can't
> > explain it either, beyond a vague comment about differences in
> > Windows....
> >
> > I've solved my orginal problem, but this bit leaves me with unanswered
> > questions. Here is a short, concise demo, copied straight from the
> > command window. Only the userid and password have been masked. Nothing
> > else has been cut or ortherwise edited.
> >
> > <==== begin quote ====>
> >
> > C:\>tnsping epspd
> >
> > TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on
> > 29-JUN-2
> > 006 09:10:21
> >
> > Copyright (c) 1997, 2005, Oracle. All rights reserved.
> >
> > Used parameter files:
> >
> > TNS-03505: Failed to resolve name
> >
> > C:\>sqlplus myuser/*****@epspd
> >
> > SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 29 09:10:41 2006
> >
> > Copyright (c) 1982, 2005, Oracle. All rights reserved.
> >
> >
> > Connected to:
> > Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
> > Production
> > With the Partitioning, OLAP and Data Mining options
> >
> > SQL>
> > <==== end quote ====>
> >
> > FWIW, here's the TNSNAMES entry, witht the IP masked.
> >
> > EPSPD =
> > (DESCRIPTION =
> > (ADDRESS_LIST =
> > (ADDRESS = (PROTOCOL = TCP)(HOST = 10.xxx.xxx.xxx)(PORT = 1521))
> > )
> > (CONNECT_DATA =
> > (SERVICE_NAME = EPSPD)
> > )
> > )
> >
> >
> > My expectation is that if TNSPING fails, so should an actual connection
> > attempt from the same envrionment.

>

> Are you running the tnsping and sqlplus from the same machine ( server
> ) as the database instance is on or from a different machine?
Different machines. The session I quoted was on the windows box referenced in my environment statement at the beginning of this thread.  It is a db server box, but we were trying to connect to a db on a different (Solaris) box.

>Are

> there differences in the domain name suffixes if applicable?
>
Not sure.

> What was "the original problem" that you fixed but don't mention in
> this posting?

A db_link that was returning an error - ora-0219 - connection description for remote database not found. The examples I posted were from the box with the db where the link is defined and the use of that link was throwing the error. I eventually traced that to a difference in the 'domain' portion of the db_link name - difference between a link that was working and an otherwise identical one that wasn't. This is the first time I've seen that part of the link name actually make a difference.

>
> Static IP address for the server?

Yes

>
> Did you check if patchset for 10.2 is available in for 2003 server?
No.

>
> Did you start the listener before the database instance came up?
Yes.

>

> What output does a lsnrctl service show you?

>

> What does your listener.ora look like? How about sqlnet.ora?

>

> What tracing options have you used for tnsping?
>
Ok, those last 3, esp. the tnsping trace, give me an action plan .... ;-) I'll try to run with it from there. Thanks for the pointers.

> Have fun and remember you can always submit the output from a tnsping
> trace if you wish to oracle support for assistance.
>
I should have some fun digging some more myself, but probably not with oracle support. I've got two other open issues with them right now, and my estimation of them is dropping like a stone. The latest ticket looks something like this:

June 19 - customer opened service request and uploads rda report
June 29 - customer asks why he hasn't heard anything yet
June 29 - oracle asks customer to install RDA and upload the results.

> Personally, I haven't found a lot ( ok any ) circumstances where
> tnsping has helped me. I would recommend always using a "real" client
> connection such as what sqlplus does but I realize other people do
> sometimes want to use tnsping.

I haven't either, other than demonstrating to non-oracle people that the problem isn't in sqlplus (really!).

In spite of all of the above, my mind keeps coming back to my original assumption that if tnsping fails, all else should fail as well. Here's a direct quote from Note:1017954.6:

"If are using the TNSPing to connect and receiving the TNS-03505 error, connecting with another tool such as SQL*Plus __will__ (emphasis mine) give you an error message that will help narrow down the exact connection problem." Received on Thu Jun 29 2006 - 13:38:49 CDT