Archive

Posts Tagged ‘listener’

Resolving Oracle networking problems – ORA-12514.

The original version of the below article was created by Ed Stevens and could be find here.
In previous posts I have explored various reasons why a client process might not be able to connect to an Oracle database. In “Resolving Oracle networking problems – general introduction” I presented an overview of the process used by sqlnet to convert a connection request to a network connection descriptor and deliver that request to a listener capable of servicing the request. I followed that with two posts going into more depth on the types of problems that can prevent the request from reaching the host server. Now I would like to look at the next possible problem in the sequence, in particular the “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”.
For this demonstration I will generate the error then go through the standard analysis and solution. After that, I will explore some of the interesting factors that flow from this.

The ORA-12514 error.
The vast majority of the time, this error results from an incorrectly specified connect descriptor in the tnsnames.ora file. Let’s look at a very typical example then diagnose and fix it. After that we will dig in to how the listener comes to know of a service name.

Of course the very first thing anyone should do when confronted with an Oracle error is to check the official description. There are many sources on the web, but I like to start with ‘oerr’ utility on the server:

[oracle@orclsvr ~]$ oerr ora 12514
12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
// *Cause:  The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a service name for a service (usually a database service)
// that either has not yet dynamically registered with the listener or has
// not been statically configured for the listener.  This may be a temporary
// condition such as after the listener has started, but before the database
// instance has registered with the listener.
// *Action:
//  - Wait a moment and try to connect a second time.
//  - Check which services are currently known by the listener by executing:
//    lsnrctl services
//  - Check that the SERVICE_NAME parameter in the connect descriptor of the
//    net service name used specifies a service known by the listener.
//  - If an easy connect naming connect identifier was used, check that
//    the service name specified is a service known by the listener.
//  - Check for an event in the listener.log file.

The error is pretty self-explanatory: “listener does not currently know of service requested in connect descriptor”. So how do we know exactly what service was “requested in connect descriptor”? First, do a sanity check by looking at the tnsnames.ora file.

# tnsnames.ora Network Configuration File: c:\oracle\11.2.0\CLIENT\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
#===========================
myorcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orclsvr)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

In line 8 we see that we should be requesting SERVICE_NAME = orcl. In our discussion of ORA-12154 I described how we might not be using the tnsnames.ora we thought we were. At this point we know what SERVICE_NAME we should be using. We can use tnsping to confirm this.

In the above screen we can see that we are requesting a connection to service ‘orcl’. Don’t be fooled by the good return code. As we saw in “Resolving Oracle networking problems – TNSPING: what it is, what it isn’t“, tnsping only goes as far as confirming there is a listener at the specified IP address and port. It says nothing about any services the listener knows about. The presence of SERVCICE_NAME in the feedback is simply the result of showing the entire connect descriptor.
Now that we know what service name was actually requested, we need to check what the listener knows about. Examining the listener configuration file, listener.ora, could give some clues but it is not the whole story. In fact, the listener can be started without any listener.ora file at all. The only sure way to tell what the listener knows about is to ask it directly, with the lsnrctl command:

This listener knows about two service names (orclnew, orclnewXDB), all associated with the instance ‘orclnew’. The XDB service is for special use – we can ignore it for general connection problems. The vast majority of the time, that’s all the debugging we need to do. We know that we should be requesting a connection to service ‘orclnew’ instead of ‘orcl’. Since that request comes from the client, we have to fix tnsnames.ora:

# tnsnames.ora Network Configuration File: c:\oracle\11.2.0\CLIENT\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
#===========================
myorcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orclsvr)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orclnew)
)
)

and make another test:

We have made the necessary correction in tnsnames.ora. The subsequent connection request is successful, thus validating our analysis and corrective action.

Registering the service with the listener.
So how did the listener come to know about service ‘orclnew’ in the first place? There are two methods by which a service is registered with the listener – ‘static’ and ‘dynamic’. We’ll discuss each in turn:
Static registration.
Static registration is accomplished by configuring the SID_LIST section of the listener.ora file (on the server side).

# listener.ora Network Configuration File: c:\oracle\11.2.0\DATABASE\network\admin\listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = c:\oracle\11.2.0\DATABASE)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = test)
      (ORACLE_HOME = c:\oracle\11.2.0\DATABASE)
      (SID_NAME = test)
    )
    (SID_DESC =
      (GLOBAL_DBNAME=orclnew)
      (ORACLE_HOME = c:\oracle\11.2.0\DATABASE)
      (SID_NAME = orclnew)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orclsvr)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

We can see three SIDs listed: PLSExtProc, test and orclnew – checking the status of the listener, we get:

Notice the entry for service “test” maps back to “(GLOBAL_DBNAME=test)” and is related to instance “test”. Further, notice that its status is UNKNOWN. This status of UNKNOWN is the indication that this registration came from the SID_LIST section of listener.ora. It is unknown because the listener doesn’t make a check to see if there is an instance named “test” broadcasting a service name of “test”. The listener is just saying “if you ask for a connection to ‘test’, I’ll see what I can do to service it”. In fact, I have no database named “test”.
Notice also that service “orclnew” has two instances, one unknown, and one READY. Like “test”, the UNKNOWN “orclnew” comes from listener.ora, the READY instance comes from the database having registered with the listener (dynamic registration).
Again, for our current discussion, we can ignore the service orclnewXDB – it has special internal uses for Oracle.
For the remainder of the discussion, I am going to completely remove listener.ora, then restart the listener so that it has no static registrations and is running with all default values:

With no static registration the listener will start with all default values and support no services until a PMON process registers itself.

Dynamic registration.
Dynamic registration is accomplished when the PMON process of the database instance contacts the listener and requests registration. This occurs at instance startup, and every few minutes during the life of the instance.

There are three initialization parameters that affect what service name(s) PMON will register with the listener:
DB_NAME
SERVICE_NAMES
DB_DOMAIN

You should look up each one in the Reference Manual and read the descriptions, notice particularly in the description of SERVICE_NAMES the following:
If you do not qualify the names in this parameter with a domain, Oracle qualifies them with the value of the DB_DOMAIN parameter. If DB_DOMAIN is not specified, then no domain will be applied to the non-qualified SERVICE_NAMES values.
There is another interaction that is not spelled out in the Reference Manual, but mentioned in the Net Services Administrator’s Guide:
The service name defaults to the global database name, a name comprising the database name (DB_NAME parameter) and domain name (DB_DOMAIN parameter).
The normal practice might be to make the SERVICE_NAMES parameter the same as the DB_NAME.
All service names – those derived from DB_NAME as well as the one derived from SERVICE_NAMES – will have the value of DB_DOMAIN appended to them (if we have the fully qualified service name in the SERVICE_NAMES initialization parameter, the value of DB_DOMAIN will not apply).

We have explored the relationship between the connect descriptor issued by the client and the services supported by the listener, as well as the factors that control what services the listener supports. In the concluding post in this series, I will discuss how the database locates the listener in order to register its services – the LOCAL_LISTENER initialization parameter.

Advertisements

Resolving Oracle networking problems – TNSPING: what it is, what it isn’t.

The original version of the below article was created by Ed Stevens and could be find here.

Before continuing our exploration of various sqlnet connection errors, let’s take a quick look at the oracle utility ‘tnsping’. We’ll see what it does, what it doesn’t do, and bust a few myths along the way.
The tnsping utility is used to determine if a service on an Oracle Net network can be reached. A complete description of its use is found in the Net Services Administrators Guide, located with the rest of the Oracle documentation at tahiti.oracle.com. TNSPING serves, for sqlnet, much the same purpose as does ‘ping’ for the OS.

Let’s take a look at a simple case. Given this entry in my tnsnames.ora:

# tnsnames.ora Network Configuration File: c:\oracle\11.2.0\CLIENT\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
#===========================
myorcl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orclsvr)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

let’s run some tests.

C:\>tnsping myorcl

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 13-SEP-2
012 11:32:22

Copyright (c) 1997,  2010, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\11.2.0\CLIENT\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = orclsvr)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (10 msec)

C:\>

And we think, “Aha! I can connect!” – well, maybe. Maybe not. I’ll get to that later. First, let’s look at what all we can learn from what we see here.
The first thing we learn, from line 3, is that we are running Oracle client 11.2.0.2. Note that this is the version of the client, not the database. They do not have to be the same, and this tells us only about the client.
From lines 8 and 9, we see that the network parameter file (sqlnet.ora) used by this client is at C:\oracle\11.2.0\CLIENT\network\admin. This is also a very strong indication that the tnsnames.ora file is also located in that directory. However, there are other influences on the location of tnsnames.ora – see my previous post on that subject, here.
From line 11, we see that we are using the TNSNAMES adapter to resolve the alias (the alias was ‘myorcl’). This adapter was chosen based on the value of the NAMES.DIRECTORY_PATH parameter in the sqlnet.ora file.
Lines 12 and 13 show us how sqlnet resolved the alias, or “connect identifier”, into a connect descriptor. We see that the request was routed using the tcp network protocol (PROTOCOL = TCP), to server orclsvr (HOST = orclsvr), and placed at that server on port 1521. This information was gathered from the tnsnames.ora entry for ‘myorcl’, shown above.
And from line 14 we see that the response time was 10 milliseconds. Well, not quite – according to the Net Administrators Guide, tnsping “displays an estimate of the round trip time”.

We also see that it requested a connection to (SERVICE_NAME = orcl). Or did it? What does the listener show?

Hmm – “The listener supports no services”. How can this be? Wasn’t our tnsping successful? What does a real connection request do?

Of course the listener “does not currently know of service requested”. At the moment, the listener does not know of any services at all. By the way, I made sure the listener did not know of any services. My listener relies on dynamic database registration, and I had stopped the database before starting this test. Not only does the listener not know of any services, there is no database running at all. I could just as easily specified SERVICE_NAME=fubar, or SERVICE_NAME=btzflx and received the same result.

And if the listener is not running at all? Let’s stop the listener:

then test tnsping again:

As we would expect, tnsping reports that there is no listener.

While tnsping is a very useful tool for diagnosing a variety of connection problems, we must be very clear about one important point: it tells us absolutely nothing about the state of a database instance. This is not a shortcoming of tnsping. We just need to understand that it is a tool for diagnosing sqlnet issues, and sqlnet is not the database.

Resolving Oracle networking problems – general introduction.

The original version of the below article was created by Ed Stevens and could be find here.

Some of the most frequently problems I have to deal in my work are resolving Oracle networking problems. Tracing the problem isn’t rocket science, but I often see people not paying attention to (or not trusting) specific error messages and riding off in all directions at once. A computer will always do exactly what it is told. The problem comes in that we often don’t really know everything we’re telling it. So let me try to explain a little about how Oracle handles a request to “connect me to my database” and actually locates a database running on a machine on the other side of the planet (or even on the very machine from which the request originated).

Before digging in, let’s talk about a very simple concept that an amazing number of people struggle with. For purposes of the current discussion there are two “entities”, or processes, involved. First there is the server process. Depending on one’s semantic precision and the context in which the term is used, the “server” could refer to the database, the database instance, the database server process, or the physical computer on which any of these execute. In terms of network routing it all comes back to a specific box with a specific IP address. The second process is the client process. That is the process that is requesting the connection to (and services from) the database. Again, depending on one’s semantic precision and context, the term “client” could refer to a process or a physical computer, but for our purposes it means the process. And this process could be running on any computer, including (understand this) the same computer that is acting as the server. In this case, it is still a client and the fact that it is running on the server computer is totally coincidental and irrelevant.

So let’s say you are using sqlplus. You issue this statement to start it and connect to your database:

C:\> sqlplus scott/tiger@myorcl

Of course, the first thing that will happen really has nothing to do with Oracle. First, the OS must locate an executable called ‘sqlplus’, load it, and pass it the rest of the command line (scott/tiger@myorcl) do with as it sees fit. And what sqlplus sees fit is to ask TNS to make a connection to “myorcl”, using the userid “scott” and the password “tiger” as its authentication credentials. So TNS has to figure out what is meant by “myorcl”. By default it will do this by looking in a file called tnsnames.ora. Since we are still at the client making the request, this file must be found on the client machine. By default it will be found in $ORACLE_HOME/network/admin (for example: c:\oracle\11.2.0\CLIENT\network\admin).

Let’s make it easy and suppose our tnsnames file has this entry:

myorcl =
	(DESCRIPTION =
		(ADDRESS_LIST =
			(ADDRESS = (PROTOCOL = TCP)(HOST = orclsvr)(PORT = 1521))
		)
		(CONNECT_DATA =
			(SERVICE_NAME = orcl)
		)
	)

TNS will look in your tnsnames.ora for an entry called ‘myorcl’. Finding it, a request is sent through the normal OS network stack to (PORT = 1521) on (HOST = orclsvr) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = orcl). Notice where it got this information from the entry in the tnsnames file (this entry is known as the “connect identifier”). Also notice that what is going on here is the resolution of an alias “myorcl” to an actual destination “orcl”. In this respect the tnsnames.ora file serves the same purpose for sqlnet as the OS’s “hosts” file serves for the standard network stack.

Where is (HOST = orclsvr) on the network? When the request gets passed from TNS to the standard network stack, the name ‘orclsvr’ will get resolved to an IP address, either via a local “hosts” file, via DNS, or possibly other less used mechanisms. You can also hard-code the IP address (HOST = 192.168.111.10) in the tnsnames.ora.

Once the Ip address is determined, the standard networking process delivers the message to the designated port (PORT = 1521) on the designated host/IP address. Hopefully, there is an Oracle database listener on “orclsvr” configured to listen on the specified port, and that listener knows about SERVICE_NAME = orcl. If so, the listener will spawn a server process to act as the intermediary between your client and the database instance. Communication to that server process will be on a different port, selected by the listener. At that point the listener is out of the process and continues to await other connection requests coming in on its configured port.

Before running sqlplus (or the other application which connects to the database), you could test communication between the client and the listener. We will use tnsping to complete this step. It’s a common misconception that tnsping tests connectivity to the instance or database. In actual fact, it only tests connectivity to the listener. Here, we will use it to prove that:
a) the tnsnames.ora has the correct hostname and port
b) that there is a listener listening on the specified host and port.

You can issue tnsping like this:

C:\> tnsping myorcl

If it is successful you will see something like this:

If not, here are some common errors, and some suggestions for fixing them:
First, there may not be an entry for ‘myorcl’ in your tnsnames. In that case you get “ORA-12154: TNS:could not resolve the connect identifier specified“. I’ll expand on the various reasons ‘myorcl’ may not have been found at a later date, but make no mistake, if you receive a ORA-12154, it is an absolute certainty your request never got past this point. You are wasting your time trying to solve this by looking at your listener. If you can’t place a telephone call because you don’t know the number (can’t find your telephone directory – aka “tnsnames.ora” – or can’t find the party you are looking for listed in it – no entry for orcl) you don’t look for problems at the telephone switchboard.

Maybe the entry for ‘myorcl’ was found, but ‘orclsvr’ couldn’t be resolved to an IP address (say there was no entry for ‘orclsvr’ in the local hosts file). This will result in “ORA-12545: Connect failed because target host or object does not exist“.

Maybe there was an entry for “orclsvr” in the local hosts file, but it specified a bad IP address. This will result in “ORA-12545: Connect failed because target host or object does not exist“.

Maybe the IP was good, but there is no listener running: “ORA-12541: TNS:no listener“.

Maybe the IP was good, there is a listener at orclsvr, but it is listening on a different port => “ORA-12560: TNS:protocol adapter error“.

Maybe the IP was good, there is a listener at orclsvr, it is listening on the specified port, but doesn’t know about SERVICE_NAME = orcl => “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor“.

Maybe the IP was good, there is a listener at orclsvr, it is listening on the specified port, knows about SERVICE_NAME = orcl, but you have other application running on the same port as the listener. In that case you don’t get any errors – tnsping just hangs for a long time…

Ok, that is how we get *from* the client connection request *to* the listener. What about the listener’s part of all this?

The listener is very simple. It runs on the server (not the client) and it’s job is to listen for connection requests and make the connection (server process) between the client and the database instance. Once that connection is made, the listener is out of the picture. If you were to kill the listener, all existing connections would continue.

The listener is configured with the listener.ora file, but if that file doesn’t exist, the listener is quite capable of starting up with all default values. One common mistake with the listener configuration is to specify “HOST=localhost” or “HOST=127.0.01”. This is a NONROUTABLE ip address. LOCALHOST and IP address 127.0.0.1 always mean “this machine on which I am sitting”. So, *all* computers are known as “localhost” or “127.0.0.1”. If you specify this address in your listener configuration, the listener will only be capable of receiving requests from the machine on which it is running. If you specified that address in your tnsnames file, the request would be routed to the machine on which the requesting client resides. Probably not what you want.

From here I have a few ideas for future posts, each focusing on potential complications at each step of the process.