Registering the Oracle database with the listener

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:

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 parameter.
  2. Bounce the database (Some of the parameters 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. (The database will register on its own, but that may take up to a minute.  Here we force it simply to avoid the wait.)
  5. Show the listener status, with the results of the new registration.
  6. 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.

<snip repetitive lines>

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.

<snip repetitive lines>

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.

<snip repetitive lines>

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 …

 

Please like us:

Leave a Reply

Your email address will not be published. Required fields are marked *