Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 804
Alan Storm Avatar asked Jan 25 '10 18:01

Alan Storm


People also ask

What is a TNS listener Oracle?

The Listener may also be referred to as the "Oracle Net Listener" or the "Oracle TNS Listener". Transparent Network Substrate (TNS) is the network protocol used by Oracle for connectivity to Oracle Databases.

What is a TNS entry in Oracle?

What is a TNS? It is an alias. Like a hostname is an alias for an IP address, a TNS is an alias for an OCI (Oracle Call Interface) connection string. This string identifies the database server and database instance to connect to.

What are the types of listener in Oracle?

Oracle - The Types of Oracle Listeners These include SQL*Net version 1 listeners, SQL*Net version 2 listeners, and Net8 listeners. In addition, a listener can be defined as a dedicated listener or a multi-threaded server listener.


1 Answers

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)
    )
  )
like image 98
Mike Atlas Avatar answered Oct 15 '22 01:10

Mike Atlas