“Hear ye, hear ye!”

"Have you ever heard the wonderful silence just before the dawn? Or the quiet
and calm just as a storm ends? Or perhaps you know the silence when you haven't
the answer to a question you've been asked, or the hush of a country road at
night, or the expectant pause of a room full of people when someone is just
about to speak, or, most beautiful of all, the moment after the door closes
and you're alone in the whole house? Each one is different, you know, and all
very beautiful if you listen carefully."
-- Norton Juster, The Phantom Tollbooth

In versions of Oracle prior to 12.2 the Listener has been more forgiving with static registrations; with 12.2 and later releases that seems to have changed as static registrations, if not configured completely, can cause the listener to dump a core file in the directory where the listener process was started. What can make this even more confusing is that dynamic registrations are not affected. And to add to all of this interesting behavior having databases down when starting the listener doesn’t ‘fix’ the problem because the listener isn’t connecting to a running database, it’s trying to resolve library paths for static declarations. Let’s look at how static registrations can be written to avoid this (and allow one to keep his or her hair and sanity in the process).

Statis registrations for TCP connections are the ‘problem’ with the 12.2 listener (from personal experience) when they stop short of configuring the library path for the given database and home. A typical static TCP registration can look like this:

SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = smerma)
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = alpoz)
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = plorg)
    )
)

Even up to release 12.1.0.2 the listener would appear to ‘pick up’ the necessary environment settings and find the associated libraries in the given ORACLE_HOME; in 12.2.0.1 this seems to have changed which causes the core file to be generated for Oracle databases not using the same home as the running listener. This can be a problem if your site has a dedicated home for the listener to run from since no databases will be running from that home. And, not knowing that static registrations are at fault the core file geenration will be a mystery.

The solution to this is fairly simple and has been available in listener configurations for many releases; of course having a dedicated listener home is not a standard configuration and, as such, this issue may never rear its ugly head. Modifying the configuration shown above to provide the ‘missing’ information the listener needs is merely a matter of adding the ENVS configuration parameter for each static database registration, as shown below:

SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = smerma)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/lib')
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = alpoz)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/lib')
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = plorg)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/lib')
    )
)

It is a simple change that may be unfamiliar to some DBAs who have never configured a listener outside of NETCA, and if static registrations are not required at a site the DBA may not have had the opportunity to even see a listener.ora [the listener will run just fine without a listener.ora file, but it will use the default port of 1521]. Of course simply changing the listener.ora doesn’t fix anything in a currently running listener as it will need to be reloaded to make the changes effective:

$ lsnrctl reload listener

LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 07-JAN-2018 14:39:24

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.mydomain.com)(PORT=1522)))
The command completed successfully
$

If your listener is named LISTENER (the default naming provided by NETCA) then a simple:

lsnrctl reload

is all that’s necessary. It is good practice, however, to supply the listener name every time one performs maintenance on a listener so the correct listener is affected. Not doing so may result in no actual reload taking place as the current lsnrctl executable won’t ‘know’ which LISTENER to reload on systems where multiple Oracle installations exist. In situations where LISTENER is not the name of the running listener (creativity may run amok and it may be named LISTENER_BOB, LISTENER_TURNIP or even LISTENER_POODLE) there won’t be any listener to act upon.

Every static registration can have its own ENVS setting; in installations involving multiple Oracle versions all using a single listener it’s perfectly legal to have each ENVS entry specify a different lib location:

SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = smerma)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/lib')
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle/12)
      (SID_NAME = alpoz)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/12/lib')
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle/10)
      (SID_NAME = plorg)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/10/lib')
    )
)

[This may not be obvious to DBAs not familiar with static configurations created by writing or editing a listener.ora file. If standby databases are not in use or if database backups are executed locally with RMAN then static registrations may not be necessary.]

For any DBA not familiar with static database registrations they exist so that the database can be succesfully connected to even when the database is not running. Static registrations are indicated by UNKNOWN when the listener status is generated:

[oracle@e3c57ee3dc64 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 04-JUN-2019 18:05:55

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=e3c57ee3dc64)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                04-JUN-2019 14:08:31
Uptime                    0 days 9 hr. 57 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/e3c57ee3dc64/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=e3c57ee3dc64)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=e3c57ee3dc64)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl" has 2 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@e3c57ee3dc64 ~]$

The UNKNOWN status is not an error; it simply indicates that the connection is manually configured and the listener cannot possibly know if the database is up or down, nor does it care. Having such a configuration makes it possible to run remote ‘cold’ backups and to run Data Guard databases by allowing remote startup and switchover of the database and standby.

Sometimes the most confusing issues can have the simplest of solutions; fortunately for listener static configurations this is the case as it only requires the addition of the ENVS parameter to provide the information the listener process was missing. This is, of course, covered in the Oracle on-line documentation (for several releases) which makes a good reason to have such documentation bookmarked in the browser.

You are listening?