What is a TNS:listener in the Context of Oracle?

Question

Borderline ServerFault question, but figured I'd try here first since I've had luck with Oracle questions in the past.

I'm trying to connect to an oracle database from PHP, and I'm getting the following error.

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

This is the error that PHP reports, and the error that shows up in Oracle's listener.log.

My immediate problem is fixing this error. The larger question I'd like answered is how does Oracle connection model work?

This is in a development environment that's running on my local windows machine and has been working up until now. Unfortunately, the environment was handed to me (I didn't set it up) and the people who did set it up are unavailable to help me debug it.

If I was getting a similar error with MySQL or PostgreSQL (two systems I'm more familiar with), I'd check to ensure that a database process was running, and then attempt to connect manually to the database using the username/password/connection string. Unfortunately, I'm not familiar with the Oracle tools on windows (other than SQL Developer) and I don't know what a TNS:listener or SID are in the context of Oracle (I have vague ideas, but vague ideas rarely help when you're debugging something like this)

Any general advice would be appreciated.

Updates per Comments:

There's a number of entires in my tnsnames.ora file, the relevant entry being

OBS2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = steel-ae39650)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = OBS2)
    )
  )

This is not reflected in the list of instances when I run

    LSNRCTL> services

So I think my next question is, how do I attempt to manually start up the OBS2 instance?

Fortuitous

A TNS entry is part of the connection string to Oracle. – Tybie

Answer 1

A TNS name is like an alias to your service instance. The TNS listener service acts as a sort of lookup service for you in this regard. It will fail with that error message if the actual service you're trying to connect to via a TNS name isn't valid.

You can then test out to see if the TNS listener sees the service correctly using the command line tool:

%>lsnrctl services

Which should output something like the following:

Service "myservice" has 1 instance(s).
  Instance "myinstance", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1002 state:ready
         DISPATCHER <machine: LOCALHOST, pid: 12345>
         (ADDRESS=(PROTOCOL=tcp)(HOST=LOCALHOST)(PORT=6789))

Can you please post the relevant TNS entry (in the tnsnames.ora file)? It is located in ORAHOME\client or db\ADMIN\NETWORK. If you have both client and server, make sure both copies of the tnsnames.ora file have correct values, just to be safe.

Here's an example of a proper TNS name definition in tnsnames.ora called 'mydb':

myDbAlias =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 12345)(QUEUESIZE = 100))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = myservice)
    )
  )

Billfish

Updated question with relevant info. It looks like the relevant TNS entry isn't reflected in the listener. Is there a way to manually start a TNS instance? Also possible relevent; If I use dbca and attempt to "Confgiure Database Options" for the database/schema (right term?) in question, I get the following error: ORA-02778: Name given for the log directory is invalid. I've solved my problem by creating a new database and importing a recent dump, but I'd still be interested in any debugging techniques for this kind of thing. – Fortuitous

You said you're on Windows, so you should be able to see if the service is running in the Windows services snap in component. Start->Admin Tools->Services. Look to see if the OracleServiceOBS2 (I am guessing that name, it might be different). Then start it from there. Your TNS listener service is right there too, named something like OracleOraDb10g_home_1TNSListener. – Billfish

So, I think what's confusing me here is how can one oracle database be up, but another cannot. That is, when I listed the services I saw the other databases (OBS3, OBS4 etc.) were up. Are all the databases controlled by OracleServiceOBS2, or does each database get its own OracleServiceOBS2? (apologies if this isn't making sense, bu I don't know what I don't know here!) – Fortuitous

You're just a bit unfamiliar with Oracle's choice of terminology coming from another system. Check out this SO topic: #1087576 – Billfish

Also, this link: club-oracle.com/forums/… – Billfish

Particularly worth clearing up: an instance can have multiple tablespaces (you're calling them databases?). – Billfish

Yeah, there's clearly terminology issues going on here. In MySQL you might have a database named Foo that contains two tables named bar and baz. – Fortuitous

And in Oracle, a database is more of a blanket term for a collection of objects, some of which are happen to tables. My favorite client to do development work is the free SQL Developer. You can connect via TNS names as a test to see if they're set up correctly, or connect with raw connections as a fall back. oracle.com/technology/products/database/sql_developer/… – Billfish

Answer 2

Just wanted to add to this, as I recently had a similar connection issue that drove me nuts until I figured out what was happening.

First, the keywords SID and SERVICE_NAME are not exactly the same. This was my first wrong assumption. In many environments you can interchange SID and SERVICE_NAME, but not always, it depends.

That said, your error gives away the problem: you're specifying SID in a connection string instead of the SERVICE_NAME that tnsnames successfully uses.

So, if you are specifying the connect string in your code, try using SERVICE_NAME keyword in the connect string (*or, if you've already using SERVICE_NAME and cannot connect, try using SID keyword*).

Overly simplistic answer I know, but easy to try and may save someone some headaches.

Hope that helps.

Pratique

Answer 3

Mike Atlas' answer is fairly comprehensive, but note that you can connect to 10g (or later) DBs which don't have a published tnsname using [//]host_name[:port][/service_name]

HTH

C.

Suez