In previous articles I have explored various reasons why a client process might not be able to connect to an Oracle database. In Help! I can’t connect to my database … I presented an overview of the process. I followed that with articles 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 problems that can arise once the request actually arrives at the listener, in particular “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 Error – ORA-12514
Before we get into specifics of debugging ORA-12514, let’s create a test case and mention some general principles.
C:\>sqlplus scott/tiger@tulsa SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 18 15:28:47 2016 Copyright (c) 1982, 2010, Oracle. All rights reserved. ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Enter user-name:
Fig. 1
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@vmlnx01 admin]$ 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.
Fig. 2
The error is pretty self-explanatory: “listener does not currently know of service requested in connect descriptor”. Of course, this could be because the client incorrectly specified the service name for the database, or because the listener really doesn’t know of a service that it should know.
The description returned by oerr also outlines a course of action to resolve the problem. By following this outline, we should come to know where the actual problem lies. Let’s fill in the details on each one in turn.
Wait and try again.
One of the features of Oracle database is the ability to register itself with the listener. This is referred to as “dynamic registration”. The listener does not have to be specifically configured to know about the database instance. The database will contact the listener every several seconds and register itself. The “wait and try again” solution is predicated on the assumption that we simply haven’t waited long enough for the database to register itself. Since the database instance performs dynamic registration every sixty seconds (if not more often), the “wait and try again” solution should only work if the error occurred immediately after database startup.
Check which services are known by the listener
Many people try to answer this question by looking at the listener configuration file, listener.ora. However, this is insufficient for one big reason: dynamic registration is not dependent on any listing in listener.ora. And as a result of that, for most cases it is not even necessary to have a listener.ora file at all.
The one reliable way to tell what instances and services the listener knows about is to query it directly, using the lsnrctl utility.
oracle:tulsa$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-JAN-2016 15:03:12 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 18-JAN-2016 14:43:59 Uptime 0 days 0 hr. 19 min. 12 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "tulsa" has 1 instance(s). Instance "tulsa", status READY, has 1 handler(s) for this service... Service "tulsaXDB" has 1 instance(s). Instance "tulsa", status READY, has 1 handler(s) for this service... The command completed successfully oracle:tulsa$
Fig. 3
We see in figure 3, above, that the listener knows of two services, “tulsa”, and “tulsaXDB”, both associated with the instance “tulsa”. (The service “tulsaXDB” is for special use and is of no real concern for our present discussion.) Notice that the status is listed as “READY”. This is a definitive indication that the registration is dynamic. By contrast, look at the content of listener.ora:
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
Fig. 4
Notice in Fig. 4 that there is no mention of any service or SID at all, yet we know that the listener has registered service “tulsa” with SID “tulsa”.
At this point in your problem resolution, you must ask yourself if the listener has registered the database you are expecting. If not, you need to resolve that. If the expected database is not reported by the listener, then ORA-12514 is just an indication of a more fundamental problem, which I will discuss in more depth in a future article. In our current case, we want to connect to “tulsa”, and the listener knows about “tulsa”, so we should continue to the next possible problem.
Check the SERVICE_NAME parameter in the connect descriptor
At this point we know that the listener has a registration for “tulsa”. And didn’t we request a connection to “tulsa” in Fig. 1? Well, no, not really. We specified “tulsa” as the net service name, but that is not necessarily what gets passed in the connect descriptor.
The net service name in our connection request (see Fig. 1, line 1) is nothing more than an alias which gets resolved to the final connect descriptor. As described in my previous article Troubleshooting ORA-12154, there are several methods of resolving a net service name to a connect descriptor. In our case, we are relying on resolution via tnsnames.ora, so our request for “tulsa” was located in tnsnames, and information found there was used to construct the final connect descriptor.
Our tnsnames.ora entry for “tulsa” looks like this:
TULSA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = fubar)
)
)
Fig. 5
At Fig. 5, line 7, we see that the actual service name specified is “fubar”. Normally, one might expect the SERVICE_NAME specification to be the same as the alias, but this cannot be taken for granted and must be checked. If they do not match, you must ask yourself, “why?” There may be legitimate reasons, or it could be a simple typographical error, or it could be that the person who set it up really didn’t understand what they were doing. In the above case I deliberately made a mismatch for the express purpose of creating an error and making a point.
Of course, we could be looking at the wrong tnsnames.ora. Let’s run this to ground a bit more thoroughly.
First, let’s try a tnsping and see if the reported connect descriptor matches what we saw in our tnsnames.ora
C:\>tnsping tulsa TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 18-JAN-2 016 15:57:51 Copyright (c) 1997, 2010, Oracle. All rights reserved. Used parameter files: C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = vblnxsrv02)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = fubar))) OK (30 msec) C:\>
Fig. 6
At line 14, we see the same bogus SERVICE_NAME = fubar, pretty much confirming that we were looking at the correct tnsnames.ora.
As one final nail in the coffin, we can look at the listener log on the database server. The plain text version of this log will be located at $ORACLE_BASE/diag/tnslsnr/<hostname>/listener/trace/listener.log. Looking at my log, I find the following entry:
18-JAN-2016 15:24:20 * (CONNECT_DATA=(SERVICE_NAME=fubar)(CID=(PROGRAM=C:\app\oracle\product\11.2.0\client_1\bin\sqlplus.exe)(HOST=STEVENS-NB-04)(USER=ed))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.1)(PORT=64942)) * establish * fubar * 12514 TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
Fig. 7
You may have to scroll the above display to the right, but you can clearly see in line 1 that the listener received a request to connect to “SERVICE_NAME=fubar”. The last item on the line logging the request is the return code, which in this case is “12514”. This line is followed immediately by a line explicitly reporting the 12514. At this level, it is reported as a TNS error instead of an ORA error but that distinction is merely a matter of where, within the call stack, the error is being reported. It is still the same error.
The fix
At this point it should be obvious that the root problem is an incorrect specification of SERVICE_NAME in our tnsnames.ora file. Let’s fix that:
TULSA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tulsa)
)
)
Fig. 8
And prove our fix
C:\>sqlplus scott/tiger@tulsa SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 18 16:22:34 2016 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt ions SQL>
Fig. 9
Conclusion
In this article I have demonstrated the connection between what the client requests and what the listener expects. The next logical issue was mentioned in passing when we looked at the services registered with the listener. In my next article I will discuss the methods by which a database becomes registered with the listener.
Film at eleven …
Ed Stevens, DBA 