In my previous article, ORA-12514: TNS:listener does not currently know of service, I demonstrated the relationships between a client requesting a connection to a database, and the database services known to the listener. In that article I focused only on the immediate cause of the error, deferring the discussion of exactly how databases get registered with the listener. In this article we will take up that discussion and try to clarify the means by which databases come to be known by the listener.
While the examples and documentation references in this article are using Oracle 12c, the same information applies equally to all versions of Oracle at least back to 9i.
The Oracle database listener
The Oracle listener is a process that runs on the database host server – the same machine that hosts the database itself. It serves as a connection broker, and all connection requests that come across the network must first be handled by the listener. If the listener is not up, or if it is up but does not know about the requested database, the connection request will be rejected by the listener without getting anywhere near an actual database.
Before going any further, let me make one point crystal clear. Please burn this into your brain:
One single listener, with the default name of LISTENER, using the default port of 1521, is quite capable of — indeed, was designed to — service multiple databases of multiple versions running from multiple homes.
I often see people trying to create and manage a separate listener for each database on a server. This does nothing to increase performance or security. It only serves to complicate administration and troubleshooting.
You also need to very clearly understand that the database instance and the listener are two completely separate and distinct processes. Creating a database does not create a listener. Neither one (database nor listener) is a sub-process of the other. Just yesterday I had to explain this to a developer in my shop.
Whew! Glad to get that off my chest! Let’s get back to the business at hand.
Database registration
When the listener knows about a database, it is said that the database is “registered” with the listener. Think of the listener as the front desk clerk at a hotel, taking messages for guests. There are two ways he can know who he needs to take messages for. First, he can be given a list of guests. He has no idea how accurate the list is. He doesn’t know if the listed guests actually exist or if they are in the rooms listed. All he can do is say “ok, if someone asks to contact a guest on this list I’ll do my best to connect them.” The other method is for the guests themselves to tell the clerk who they are and what room they are in.
Likewise, there are two methods by which databases are registered with the listener – “static” and “dynamic”. We’ll discuss each in turn.
Static registration
Static registration is like our desk clerk being given a list of guests. In the case of the listener, this “guest list” is the SID_LIST section of the listener.ora file. The listing for each individual, specific SID begins with the SID_DESC identifier under the SID_LIST section.
# 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))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=myfubardb)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = fubar)
)
(SID_DESC =
(GLOBAL_DBNAME=tulsa)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = tulsa)
)
)
Fig. 1
In Fig. 1 we see two SIDs listed: “fubar” and “tulsa” (lines 17 and 22). Checking the status of the listener, we see
oracle:tulsa$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-JAN-2016 18:09:42 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 21-JAN-2016 18:09:22 Uptime 0 days 0 hr. 0 min. 19 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 "myfubardb" has 1 instance(s). Instance "fubar", status UNKNOWN, has 1 handler(s) for this service... Service "tulsa" has 2 instance(s). Instance "tulsa", status UNKNOWN, has 1 handler(s) for this service... 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
Fig. 2
Notice the entry for service “myfubardb” (Fig. 2, line 23) maps back to “GLOBAL_DBNAME=myfubardb” in the listener.ora file (Fig. 1, line 15) and is related to instance “fubar”, which maps to “SID_NAME=fubar” in listener.ora (fig, 1, line 17). 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 does not make a check to see if there really is an instance named “fubar” broadcasting a service name of “myfubardb”. The listener, like our imaginary hotel clerk, 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 “tulsa” has two instances, one UNKNOWN and one READY (Fig.2, lines 26 and 27). Like myfubardb, the UNKNOWN tulsa comes from listener.ora (Fig. 1, line 22); the READY instance comes from the database having registered itself with the listener. This is known as “dynamic registration”, and we will delve into that in just a minute.
Again, for our current discussion, we can ignore the service tulsaXDB. This has special internal use 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:tulsa$ pwd /u01/app/oracle/product/12.1.0/dbhome_1/network/admin oracle:tulsa$ mv listener.ora listener.save oracle:tulsa$ ls listener.ora ls: cannot access listener.ora: No such file or directory oracle:tulsa$ lsnrctl stop LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-JAN-2016 18:21:55 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) The command completed successfully oracle:tulsa$ lsnrctl start LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-JAN-2016 18:22:26 Copyright (c) 1991, 2014, Oracle. All rights reserved. Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.2.0 - Production Log messages written to /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521))) Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 21-JAN-2016 18:22:27 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF 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))) The listener supports no services The command completed successfully oracle:tulsa$
Fig. 3
With no listener.ora file, the listener will start with all default values and support no services (Fig. 3, line 43) until a database process registers itself. Obviously there is no static registration without listener.ora.
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. In our hotel clerk analogy, this is a guest periodically dropping by the front desk to inform the clerk of his availability.
There are three database initialization parameters that affect what service name(s) pmon will register with the listener:
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:
- Alter an initialization parameter.
- Bounce the database (Some of the parameters require it. To keep things clean and consistent, I’ll do it for all of them.)
- Restart the listener. (To flush the old registrations.)
- Force a new registration. (The database will register on its own, but that may take up to a minute. Here we force it simply to avoid the wait.)
- Show the listener status, with the results of the new registration.
- Show the values of all three parameters, for comparison.
On the first iteration I will show the entire sequence. On subsequent iterations I will show only the key parts.
(Note that in all of the demonstrations, I take the convenience of stopping and starting the listener from within my sqlplus session. If you are not familiar with it, sqlplus has the ability to have the host operating system execute commands. This is done with the ‘host’ directive, implemented with the ‘!’ symbol. See the SQL*Plus User’s Guide and Reference for more information.)
oracle:tulsa$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 23 13:24:14 2016 Copyright (c) 1982, 2014, 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 options SQL> alter system set service_names='' scope=spfile; System altered. SQL> alter system set db_domain='' scope=spfile; System altered. SQL> startup force; ORACLE instance started. Total System Global Area 713031680 bytes Fixed Size 2928488 bytes Variable Size 520093848 bytes Database Buffers 184549376 bytes Redo Buffers 5459968 bytes Database mounted. Database opened. SQL> !lsnrctl stop LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 13:25:57 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) The command completed successfully SQL> !lsnrctl start LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 13:26:03 Copyright (c) 1991, 2014, Oracle. All rights reserved. Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.2.0 - Production Log messages written to /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521))) Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 23-JAN-2016 13:26:03 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF 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))) The listener supports no services The command completed successfully SQL> alter system register; System altered. SQL> !lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 13:26:25 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 23-JAN-2016 13:26:03 Uptime 0 days 0 hr. 0 min. 21 sec Trace Level off Security ON: Local OS Authentication SNMP OFF 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))) 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 SQL> show parameter db_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string tulsa SQL> show parameter service_names; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string SQL> show parameter db_domain; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_domain string SQL>
Fig. 4
In Fig. 4, at lines 91 and 93, we see two service names. Both are associated with the instance “tulsa” 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> startup force ORACLE instance started. SQL> alter system register; System altered. SQL> !lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 13:34:25 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 23-JAN-2016 13:33:59 Uptime 0 days 0 hr. 0 min. 26 sec Trace Level off Security ON: Local OS Authentication SNMP OFF 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))) Services Summary... Service "edstevens" has 1 instance(s). Instance "tulsa", status READY, has 1 handler(s) for this service... 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 SQL> show parameter db_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string tulsa SQL> show parameter service_names; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string edstevens SQL> show pra arameter db_domain; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_domain string SQL>
Fig. 5
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. 5, lines 34 and 51).
Next we set db_domain
SQL> alter system set db_domain='acme.com' scope=spfile; System altered. SQL> startup force ORACLE instance started. SQL> alter system register; System altered. SQL> !lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 13:39:17 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 23-JAN-2016 13:39:17 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF 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))) Services Summary... Service "edstevens.acme.com" has 1 instance(s). Instance "tulsa", status READY, has 1 handler(s) for this service... Service "tulsa.acme.com" has 1 instance(s). Instance "tulsa", status READY, has 1 handler(s) for this service... Service "tulsaXDB.acme.com" has 1 instance(s). Instance "tulsa", status READY, has 1 handler(s) for this service... The command completed successfully SQL> show parameter db_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string tulsa 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. 6
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 (Fig. 6, lines 34, 36, 38, and 56).
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> startup force ORACLE instance started. SQL> alter system register; System altered. SQL> !lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 13:42:21 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 23-JAN-2016 13:42:20 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF 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))) Services Summary... Service "edstevens.acme.com" has 1 instance(s). Instance "tulsa", status READY, has 1 handler(s) for this service... Service "tulsa.acme.com" has 1 instance(s). Instance "tulsa", status READY, has 1 handler(s) for this service... Service "tulsaXDB.acme.com" has 1 instance(s). Instance "tulsa", status READY, has 1 handler(s) for this service... Service "wiley.coyote.com" has 1 instance(s). Instance "tulsa", status READY, has 1 handler(s) for this service... The command completed successfully SQL> show parameter db_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string tulsa 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. 7
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. 7, line 40) from the second value supplied from service_names (line 53). Since this was fully qualified in the service_names initialization parameter, 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.
There is one question I’ve not addressed but hopefully is bouncing around in the back of your mind. Every process (like a database instance) that needs to communicate with another process (like a listener) must have some means of locating that process and delivering the message. Have you asked yourself how it is the database instance actually locates the listener? Especially since the listener is specifically expecting to receive messages from the network? I will take up that issue in my next article, discussing the LOCAL_LISTENER initialization parameter.
Film at eleven …
Ed Stevens, DBA 