Static Registration and the Oracle 12.2 Listener

In earlier versions of the Listener, Oracle has been more forgiving with static registrations; with 12.2 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, 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.

Static 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 generation will be a mystery.

The solution to this is fairly simple and has been available in listener configurations for many releases. Taking the configuration shown above it can be modified to provide the ‘missing’ information the listener needs by adding the ENVS configuration parameter for each static database registration:


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')
    )
)

A simple change but it may be unfamiliar to some DBAs who have never configured a listener outside of NETCA. 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 (it may be 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.]

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.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles