ORA-12514: TNS:listener does not currently know of service

In previous posts 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 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 “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

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.

C:\>sqlplus scott/tiger@vlnxora1

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 15 18:47:26 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:
C:\>

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”. 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.

C:\>type C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
vlnxora1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = fubar)
    )
  )

C:\>

Fig. 3

In Fig. 3, line 8 we see that we should be requesting SERVICE_NAME = fubar. 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.

C:\>tnsping vlnxora1

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 15-MAR-2
011 19:02:39

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

Used parameter files:
C:\oracle\product\10.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 = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = fubar)))
OK (0 msec)

C:\>

Fig. 4

Fig. 4, line 13 shows that we are requesting a connection to service ‘fubar’. Don’t be fooled by the good return code. As we saw in “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:

[oracle@vmlnx01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 15-MAR-2011 19:11:49

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                15-MAR-2011 18:45:24
Uptime                    0 days 0 hr. 26 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))
Services Summary...
Service "vlnxora1" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
Service "vlnxora1XDB" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
Service "vlnxora1_XPT" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@vmlnx01 ~]$

Fig. 5

This listener knows about three service names (vlnxora1, vlnxora1XDB, and vlnxora1_XPT), all associated with the instance ‘vlnxora1’. (The XDB and XTP services are for special use – we can ignore them 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 ‘vlnxora1’ instead of ‘fubar’. Since that request comes from the client, we have to fix tnsnames.ora

C:\>type C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
vlnxora1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = vlnxora1)
    )
  )

C:\>sqlplus scott/tiger@vlnxora1

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 15 19:14:41 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Fig. 6

We see in Fig. 6, line 12 that we have made the necessary correction in tnsnames.ora. The subsequent connection request is successful (fig. 6, line 18), thus validating our analysis and corrective action.

Registering the service with the listener

So how did the listener come to know about service “vlnxora1” 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.

[oracle@vmlnx01 admin]$ cat listener.ora
# listener.ora Network Configuration File: /ora00/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /ora00/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME=myfubardb)
      (ORACLE_HOME = /ora00/app/oracle/product/10.2.0/db_1)
      (SID_NAME = fubar)
    )
    (SID_DESC =
      (GLOBAL_DBNAME=vlnxora1)
      (ORACLE_HOME = /ora00/app/oracle/product/10.2.0/db_1)
      (SID_NAME = vlnxora1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01.vmdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

[oracle@vmlnx01 admin]$

Fig. 7

In Fig. 7 we see three SIDs listed: PLSExtProc, fubar, and vlnxora1 (lines 7,14, and 19). Checking the status of the listener, we get

[oracle@vmlnx01 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 18:27:25

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vmlnx01.vmdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                16-MAR-2011 18:25:56
Uptime                    0 days 0 hr. 1 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ora00/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "myfubardb" has 1 instance(s).
  Instance "fubar", status UNKNOWN, has 1 handler(s) for this service...
Service "vlnxora1" has 2 instance(s).
  Instance "vlnxora1", status UNKNOWN, has 1 handler(s) for this service...
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
Service "vlnxora1XDB" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
Service "vlnxora1_XPT" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@vmlnx01 admin]$

Fig. 8

Notice the entry for service “myfubardb” (fig. 8, line 25) maps back to “(GLOBAL_DBNAME=myfubardb)” (fig 7, line 12) and is related to instance “fubar”, which maps to fig 7, line 14). 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 “fubar” broadcasting a service name of “myfubardb”. The listener is just saying “if you ask for a connection to “myfubardb”, I’ll see what I can do to service it.” In fact, I have no database named “fubar” or “myfubardb”.

Notice also that service “vlnxora1” has two instances, one unknown, and one READY. Like myfubardb, the UNKNOWN vlnxora1 comes from listener.ora (fig. 7, line 19); the READY instance comes from the database having registered with the listener (dynamic registration).

Again, for our current discussion, we can ignore the services vlnxora1XDB and vlnxora1_XTP. These have 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:

[oracle@vmlnx01 admin]$ rm listener.ora

[oracle@vmlnx01 admin]$ ls -l listener.ora
ls: listener.ora: No such file or directory

[oracle@vmlnx01 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 18:44:43

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
[oracle@vmlnx01 admin]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 18:44:49

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /ora00/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Log messages written to /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                16-MAR-2011 18:44:49
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@vmlnx01 admin]$

Fig. 9

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 parms 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. (Click on the links) 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)

Since neither DB_DOMAIN nor SERVICE_NAMES are required parameters, let’s start with an instance with neither of those set, then start observing how service names get constructed with various settings. For each iteration I will do the following:

1) alter an initialization parm
2) bounce the database (some of the parms require it. To keep things clean and consistent, I’ll do it for all of them)
3) restart the listener, to flush the old registrations
4) force a new registration
5) show the listener status, with the results of the new registration
6) show the values of all three parms, for comparison

[oracle@vmlnx01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Mar 16 19:17:57 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set service_names='' scope=spfile;

System altered.

SQL> alter system set db_domain='' scope=spfile;

System altered.

SQL> @doit
SQL> startup force
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1267068 bytes
Variable Size             138414724 bytes
Database Buffers          142606336 bytes
Redo Buffers                2924544 bytes
Database mounted.
Database opened.
SQL> !lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 19:18:52

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully

SQL> !lsnrctl start

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 19:19:01

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /ora00/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Log messages written to /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                16-MAR-2011 19:19:01
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))
The listener supports no services
The command completed successfully

SQL> alter system register;

System altered.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 19:19:01

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                16-MAR-2011 19:19:01
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))
Services Summary...
Service "vlnxora1" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
Service "vlnxora1XDB" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
Service "vlnxora1_XPT" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> show parameter db_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      vlnxora1
SQL> show parameter service_names;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string
SQL> show parameter db_domain;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string
SQL>

Fig. 10

In Fig. 10, at lines 91,93 and 95, we see three service names. All three are associated with the instance “vlnxora1” and derived their name from the initialization parameter “db_name”.

Next, we add a simple service name. While normal practice might be to make it the same as the db_name, I’ll make it different so that we can trace it to the end result. Remember, it is quite acceptable to have multiple service_names, which we will get to in a moment.

SQL> alter system set service_names='edstevens' scope=spfile;

System altered.

SQL> @doit
---- snip repetitive commands and output ----
SQL> !lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 19:22:06

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                16-MAR-2011 19:22:06
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))
Services Summary...
Service "edstevens" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
Service "vlnxora1" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
Service "vlnxora1XDB" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
Service "vlnxora1_XPT" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> show parameter db_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      vlnxora1
SQL> show parameter service_names;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      edstevens
SQL> show parameter db_domain;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string
SQL>

Fig. 11

Now we see that all of the service names derived from db_name are still in place, but we have also added one derived from service_names (Fig. 11, line 27)

Next we set db_domain

SQL> alter system set db_domain='acme.com' scope=spfile;

System altered.

SQL> @doit
---- snip repetitive commands and output ----
SQL> !lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 19:24:01

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                16-MAR-2011 19:24:01
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))
Services Summary...
Service "edstevens.acme.com" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
Service "vlnxora1.acme.com" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
Service "vlnxora1XDB.acme.com" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
Service "vlnxora1_XPT.acme.com" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> show parameter db_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      vlnxora1
SQL> show parameter service_names;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      edstevens
SQL> show parameter db_domain;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string      acme.com
SQL>

Fig. 12

Notice that all service names – those derived from db_name as well as the one derived from service_names – have the value of db_domain appended to them.

Next we add a second service name, this one qualified with a second domain name. Not something you’d normally do, but useful for demonstrating the interaction of the parameters

SQL> alter system set service_names='edstevens,wiley.coyote.com' scope=spfile;

System altered.

SQL> @doit
---- snip repetitive commands and output ----
SQL> !lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 19:27:07

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                16-MAR-2011 19:27:07
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))
Services Summary...
Service "edstevens.acme.com" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
Service "vlnxora1.acme.com" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
Service "vlnxora1XDB.acme.com" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
Service "vlnxora1_XPT.acme.com" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
Service "wiley.coyote.com" has 1 instance(s).
  Instance "vlnxora1", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> show parameter db_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      vlnxora1
SQL> show parameter service_names;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      edstevens,wiley.coyote.com
SQL> show parameter db_domain;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string      acme.com
SQL>

Fig. 13

As before, all of the unqualified service names derived from db_name and service_names have been qualified with the value of db_domain. But notice that we have a new service name (Fig. 13, line 35) from the second value supplied from service_names. Since this was fully qualified in the service_names initialization parm, the value of db_domain was not applied.

Conclusion

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.

Film at eleven …

About these ads

47 thoughts on “ORA-12514: TNS:listener does not currently know of service

  1. Pingback: ORA-12154: TNS:could not resolve the connect identifier specified - dBforums

  2. Ed,

    Thanks for the clear explanation on how to statically register an instance. I wish I had found that earlier. But, in any regard, I think I followed what you are saying, and I still can’t get my instance to show up in the lsnrctl status. Can you think of anything that I can do to troubleshoot? I posted this question on OTN, and it had my listener.ora.

    https://forums.oracle.com/forums/thread.jspa?messageID=10397221

    I think I need this to do a rman duplication. I want to clone another database to this one, so I need a “remote” connection to the not-yet-created database.

    Any suggestions you can think of to try would be very helpful.

    Thanks,

    Stephen

    • Stephen,

      Check my reply on OTN. You mis-spelled the header for the SID_LIST section of your listener.ora file. You have “SID_LIST_LISTNER =”, should be “SID_LIST_LISTENER”. Missing the first “E” in LISTENER.

      • In sqlplus, the ‘@’ means “read this file (default extension of .sql) and execute the statements contained in it.

        So “@doit” is telling sqlplus to execute the file ‘doit.sql’. What was actually in ‘doit.sql’ is clearly shown in the lines that follow it in the examples.

  3. Pingback: ORA-12514: TNS:listener does not currently know of service « dbtechtalk

    • Adam – “doit.sql” is just the name I use for all my one-off, throw-away, demo scripts. In this case, it’s all right there in the post. The first line of the script is “set echo on”, so the file is nothing more than the commands you see after “@doit”.

  4. Thanks Ed,

    this was very clear and very helpful (I fixed the issue, but need to spend more time reading your blogs related to Oracle)

    thanks,
    Dave

  5. Ed:
    Hi,Please “Ctrl + F” to search “Since this was fully qualified in the service_names initialization parm, the value of db_domain was not applied”.
    Why db_domain was not applied? Can you tell me clearly? Thanks advance.

    Lonion

  6. Thanks, man. This was a great refresher on how this stuff works and helped me solve an issue I’ve been messing with all day.

    • First, let’s simplify and ignore your mention of default listener.

      Then yes, as shown in the figure 10 and its associated discussion. The database will always register a service using the value of db_name. That parameter is one of the few required to actually start an instance regardless of any service registration considerations. If you set also set db_domain (optional), that value will be appended to the value of db_name to create a service name. The service_names parameter can also be used (again, in addition to db_name) to register service names that have no syntactical (is that a word?) relationship to db_name.

      Now, why did a say to set aside the consideration of ‘default listener’?

      Because the creation of service names is totally independent of what listener the instance is registering with. See my discussion of the local_listener parameter here to see how the instance finds a listener with which to register its service names.

  7. Hi Ed,

    You have written a wonderful post, but unfortunately i cant make it work. All my listeners are in status UNKNOWN

    I am a complete newbie in oracle. Kindly guide.

    What am i doing wrong.

    Configuration : Windows XP Pro Service Pack 3 / Oracle Express Edition 11g

    _____________________________________________
    D:\oraclexe\app\oracle\product\11.2.0\server\bin>lsnrctl

    LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 – Production on 29-DEC-2013 23:50
    :48

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

    Welcome to LSNRCTL, type “help” for information.

    LSNRCTL> status
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
    STATUS of the LISTENER
    ————————
    Alias LISTENER
    Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 – Produ
    ction
    Start Date 29-DEC-2013 23:17:34
    Uptime 0 days 0 hr. 33 min. 15 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Default Service XE
    Listener Parameter File D:\oraclexe\app\oracle\product\11.2.0\server\network\a
    dmin\listener.ora
    Listener Log File D:\oraclexe\app\oracle\diag\tnslsnr\localhost\listener
    \alert\log.xml
    Listening Endpoints Summary…
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.101)(PORT=1521)))
    Services Summary…
    Service “CLRExtProc” has 1 instance(s).
    Instance “CLRExtProc”, status UNKNOWN, has 1 handler(s) for this service…
    Service “PLSExtProc” has 1 instance(s).
    Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
    Service “vlnxora1″ has 1 instance(s).
    Instance “vlnxora1″, status UNKNOWN, has 1 handler(s) for this service…
    The command completed successfully
    ______________________________________________
    tnsnames.ora
    XE =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = SHARED)
    (SERVICE_NAME = XE)
    )
    )

    vlnxora1 =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))
    (CONNECT_DATA =
    (SERVICE_NAME = vlnxora1)
    )
    )
    _______________________________________
    listener.ora
    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = D:\oraclexe\app\oracle\product\11.2.0\server)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = D:\oraclexe\app\oracle\product\11.2.0\server)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = vlnxora1)
    (SID_NAME = vlnxora1)
    (ORACLE_HOME = D:\oraclexe\app\oracle\product\11.2.0\server)

    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))
    )
    )

    DEFAULT_SERVICE_LISTENER = (XE)
    ____________________________________________
    tnsping

    D:\oraclexe\app\oracle\product\11.2.0\server\bin>tnsping vlnxora1

    TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 – Production on 30-DEC-2
    013 00:02:52

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

    Used parameter files:
    D:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.
    0.101)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = vlnxora1)))
    OK (40 msec)

    • Instance status of ‘unknown’ simply means that the instance is registered with the listener due to it inclusion in the SID_LIST section of listener.ora. It is no guarantee that there is actually an instance by that name. Likewise, the successful tnsping means only that the client was able to resolve your net service name (vlnxora1) to an address, and there was a listener at that address. It proves nothing about the state (or even the existence) of that instance. See my post http://edstevensdba.wordpress.com/2011/02/27/tnsping-101/ for more information on what tnsping does and doesn’t do.

      What you haven’t shown me is the error message you get when you “can’t make it work”. Given that the listener status does not show an instance status of ‘ready’ I’d guess that you do not have a running database instance named ‘vlnxora1′.

  8. In your experience you might have skated around the most difficult issue in tnsnames.ora – that is the very poor parsing that Oracle apply to reading the text. I’ve seen, on numerous occasions, tnsnames.ora files that looked okay – but didn’t get completely parsed. My advice is always to cut and paste a working ‘part’ of the file and never to try to replicate the format by hand, because it will fail. This is a killer for those new to Oracle ( very similar to the bugs in SQLPlus where an innocuous-looking empty line causes a grammatically correct-SQL to fail.)

    • Valid point that I should have mentioned. I’ve never been able to nail it down, but early on in my experience with Oracle I had come across situations where the parsing of tnsnames seemed to be sensitive to extra spaces or other seemingly innocuous format details. I, too, am in the habit of copying a working entry or using netca to create new entries.

  9. Hi,
    When i tried tnsping localhost in the system where server is installed, i didn’t get any service name in the output. SO i was not able to proceed further.
    Please find ora files as follows:
    tnsnames.ora
    # tnsnames.ora Network Configuration File: C:\app\VIVIVERM\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
    # Generated by Oracle configuration tools.

    LISTENER_ORCL =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    ORACLR_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
    (SID = CLRExtProc)
    (PRESENTATION = RO)
    )
    )

    ORCL =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl.us.oracle.com)
    )
    )

    sqlnet.ora
    ———–
    # sqlnet.ora Network Configuration File: C:\app\VIVIVERM\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
    # Generated by Oracle configuration tools.

    # This file is actually generated by netca. But if customers choose to
    # install “Software Only”, this file wont exist and without the native
    # authentication, they will not be able to connect to the database on NT.

    SQLNET.AUTHENTICATION_SERVICES= (NTS)

    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

    listener.ora
    ————-
    # listener.ora Network Configuration File: C:\app\VIVIVERM\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
    # Generated by Oracle configuration tools.

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = C:\app\VIVIVERM\product\11.2.0\dbhome_1)
    (PROGRAM = extproc)
    (ENVS = “EXTPROC_DLLS=ONLY:C:\app\VIVIVERM\product\11.2.0\dbhome_1\bin\oraclr11.dll”)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = CLRExtProc)
    (ORACLE_HOME = C:\app\VIVIVERM\product\11.2.0\dbhome_1\BIN)
    (SID_NAME = CLRExtProc)
    )
    )

    LISTENER =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.240.101.204)(PORT = 1521))
    )

    ADR_BASE_LISTENER = C:\app\VIVIVERM

    I am not able to connect to the server using sql plus(using user@host:port:service) or sql developer. However i can connect by using connect command directly.

    Please suggest the changes in these files andwhat should be the tns details to be used .

    Thanks
    Vivek

    • Your TNS file specifies HOST=localhost. ‘localhost’ always resolves to IP address 127.0.0.1, which is a loopback address. No request to that address ever leaves the requesting machine.

      Then your listener specifies that it is listening on IP address 10.240.101.204. That is the address that should be specified in the HOST parameters in your tnsnames.ora.

      • Hi Ed Stevens,
        Thanks for the reply.
        I changed tnsnames.ora to specify host =10.240.101.204
        Now i used the following parameters to connect:
        host=10.240.101.204
        port=1521
        sid=CLRExtProc

        Now this is giving following error:
        Status : Failure -Test failed: Listener refused the connection with the following error:
        ORA-12518, TNS:listener could not hand off client connection

        Please suggest.

        Thanks
        Vivek

      • ALso the result of lsnrctl services is as follows:
        LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 – Production on 04-FEB-2014 22:37
        :31

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

        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=slc01nva.us.oracle.com)(
        PORT=1521)))
        Services Summary…
        Service “CLRExtProc” has 1 instance(s).
        Instance “CLRExtProc”, status UNKNOWN, has 2 handler(s) for this service…
        Handler(s):
        “DEDICATED” established:0 refused:0
        LOCAL SERVER
        “DEDICATED” established:0 refused:2
        LOCAL SERVER
        Service “orcl.us.oracle.com” has 1 instance(s).
        Instance “orcl”, status READY, has 1 handler(s) for this service…
        Handler(s):
        “DEDICATED” established:267 refused:0 state:ready
        LOCAL SERVER
        Service “orclXDB.us.oracle.com” has 1 instance(s).
        Instance “orcl”, status READY, has 1 handler(s) for this service…
        Handler(s):
        “D000″ established:0 refused:0 current:0 max:1022 state:ready
        DISPATCHER
        (ADDRESS=(PROTOCOL=tcp)(HOST=slc01nva.us.oracle.com)(PORT=62548))
        The command completed successfully

      • Thanks Ed Stevens for the replies. I mapped these files with a database to which i could connect and modified file accordingly. Now i am able to connect. Just i replaced service name=orcl.us.oracle.com to sid=orcl.

      • Glad you got it working, but the preferred method is to specify service_name instead of sid. I’d suggest you review my post on ORA-12514 to see how to identify the services registered to your listener, and adjust your tnsnames accordingly.

  10. Very good explanation, thanks. I’m currently troubleshooting an issue. Getting
    UDE-12520: operation generated ORACLE error 12520
    ORA-12520: TNS:listener could not find available handler for requested type of server when trying to run an export, similar using sqlplus. I have an application down to get an export.

    Listener log entries: TNS-12520: TNS:listener could not find available handler for requested type of server

    • I don’t recall ever dealing with a TNS-12520 myself, but google turned up some interesting hits. Commonly, this is caused by the db reaching max sessions and not being able to accept any more. Does ‘lsnrctl status’ report any ‘blocked’ services? What is the value of the init parm ‘sessions’, and the count of current sessions when you get this error?

      SQL> show parameter processes
      SQL> select count(*) from v$session;

      If, when you are experiencing this error, the count of v$session is at or very near the value of ‘processes’, you should get some temporary relief by increasing the value of the ‘processes’ parameter. I say ‘temporary’ because if the root cause is an ill-behaved application that is not releasing sessions, you will eventually hit the wall again. However, if the app is reasonably well designed but simply has a larger user population, the increase of ‘processes’ is appropriate and should fix your issue.

  11. Pingback: Connecting to an Oracle Database using SSIS 2008 | Sjoukje Zaal's blog

  12. HI Ed,
    Thanks for a great post. I am experiencing an odd error that I hope you can provide some guidance with. At the client end (Windows server) I have set up a new TNS connection to an 11G database located on a local government’s server (Oracle client 11.2.0 installed). (We are already successfully connecting via TNS to another database server within this local government’s network from this windows server-have been for years.) This new TNS connection fails through SQL Developer (ORA-12520) and SSIS (ORA-12514), but I can test it successfully through ORACLE NET Manager. The connection strings are very similar in format between the TNS that is working and the one that is not. The only differences being host server, port number and service_name. I have no access into the local goverment’s network, though they assure me they can connect via TNS to this new database internally. The service_name has full stops in it, but I can’t imagine that being the issue. TNSPING works, but as you mentioned, that only confirms the host name. I can’t find anything online to assist. Is there anything you can suggest?
    many thanks,
    Claudia

    PS: Format of TNS connection to server that is not working
    test =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = AB_nonProd.internal.xxxx.xxxx.xxxx)(PORT = 28300))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ‘aaa_xxx.xxxx)
    (FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))
    )

    • Claudia –
      You report both ora-12514 and ora-12520. Let’s take them one at a time.
      Your tnsnames shows that you are requesting a service name of ” ‘aaa_xxx.xxxx “. Note the leading single-quote? Is that a typo that you transcribed into your post, or is that what’s really there? In any event, whatever you specify as SERVICE_NAME in your tnsnames must match EXACTLY with a service name as reported by the listner when you issue a “lsnrctl status”. And by EXACTLY, I mean case counts. If it doesn’t match exactly, you’ll get ora-12514. To truly debug, I’d want to see the exact output of tnsping on the client machine and lsnrctl status on the db server. You say you can test “succesfully” with netmgr, but get the 12514 with SSIS. That means they are not using the same connection information. Where is SSIS getting it’s connection information? Do you possibly have two ORACLE_HOMEs on this machine?

      The ora-12520 is a bit different, and I can’t recall the last time I actually saw one. You said you get that error when connecting with SQL Dev. Depending on how you have your connection defined, you may not be using the tnsnames at all. If you define your connection as “basic”, it’s using either JDBC or Eazyconnect (honestly, I don’t know which but I think its jdbc) and so does not need tnsnames to resolve the address information. Anyway, when I googled ora-12520, it seems that it could be related to the database setting for PROCESSES. Else, you could be asking for a SHARED (as opposed to DEDICATED) connection, and have not yet configured SHARED_SERVERS and DISPATCHERS in the database.

      FWIW, I find it VERY odd that you specify to connect on port 28300. The default port for the listener is 1521. How did you arrive at 28300?

  13. Just wanted to add a quick reminder that it is very important to remove the listener.ora file completely when trying this. I tried on 12c standard windows version but didnt follow the instructions and removed any auto generated entries for my cdb and pdb’s in listener.ora but kept the CLREXTPROC entry.

    I couldnt get the listener to recognize any instances/services with that entry in place – regardless of how many times i tried to register the services. Once I remove the entry, the listener immediately recognized the running services after the db was bounced and everything worked normally through dynamic registration with no need for any manual registration.

    Just wanted to share my experience. Great article. Thanks!

    • I haven’t started playing around with 12c yet, but my guess is there is something else at play here. The only reason I deleted my listener.ora for the demo was to make it abundantly clear that there was no way a database was getting registered unless the database itself contacted the listener. I could have just as easily deleted the SID_LIST section and kept the rest.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s