Re: is problem on remote or local database?

From: Peter J. Schoenster <peter_at_schoenster.com>
Date: Mon, 13 Nov 2000 14:03:07 -0700
Message-Id: <10679.121929@fatcity.com>

On 13 Nov 2000, at 21:10, Oliver Artelt wrote:

> Hi, in my opinion mysql and oracle aren't comparable in this case.If you'll
> find your problem -and tnsping works - try to connect via sqlplus on the
> client and the server and with the net service name in the connection string.
> I think your suggestions are wrong -Probably it could really not resolve the
> net service name because your tnsnames.ora are wrong. If you do tnsnames
> check the FQN of the net service name. Man, that's not mysql!!!

:) ... certainly mysql is not oracle. My point was that mysql tells me where the problem is, but oracle does not. Oracle's error message appears too generic. I find it odd (but then I'm ignorant) that oracle can resolve the name when using tnsping but not when using sqlpus.

SID =
(DESCRIPTION =
(ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)
      (HOST = hostname)
      (PORT = 1521))

    )
    (CONNECT_DATA =
      (SERVICE_NAME = SID)
    )
  )

I took out SID and hostname of course. When I do tnsping

> Sun Microsystems Inc. SunOS 5.8 Generic February 2000
> > tnsping SID
>
> TNS Ping Utility for Intel SVR4 UNIX: Version 8.1.5.0.0 - Production
> on 13-NOV-00 12:24:17
>
> (c) Copyright 1997 Oracle Corporation. All rights reserved.
>
> Attempting to contact
> (ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521)) OK (800
> msec) >

(please note, SID and hostname represent real values). So it resolves the hostname when I use tnsping. When I do ... Wait, wait ... here must be the answer ...

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor

TNS:listener must be on the remote oracle server ... and SERVICE_NAME is the SID ... it is telling me that there is not database called SID (actually the value I use for SID).

TNS:listener is not on my client machine ... it is on the remote machine.

I think this must be it. If I had used the wrong hostname for HOST then even tnsping would not have worked (or by accident ... perhaps I found some other oracle server that responded:) but because I do have a good hostname it responds ... but my SID does not correspond to a known database on the remote server ...

Peter

"Reality is that which, when you stop believing in it, doesn't go Received on Mon Nov 13 2000 - 15:03:07 CST