Oracle’s Automatic Listener Registration

In an Oracle forum recently the question was raised regarding the auto-registration of a database with the listener. Actually, the question went on to ask how to prevent the listener from registering a database automatically. The reason given was that the database that was not to be auto-registered has the same name as the current production database but it resides on a newer server; the original poster stated that he/she wanted to eliminate confusion and prevent ‘accidental’ connections to this new production copy. If the new database connection isn’t included in the client tnsnames.ora file, then no such ‘accidental’ connections can be made. That being said there are four ways to prevent a database from auto-registering with the listener. Let’s look at those in a bit more detail.

The Oracle listener doesn’t need a listener.ora file to start and run; by default, it uses port 1521 and the name LISTENER so using lsnrctl to get the listener going is as simple as:

$ lsnrctl start

Starting /private/sales_group/sales/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.0.0 
System parameter file is /oracle/network/admin/listener.ora
Log messages written to /oracle/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sales-server)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.0.0 
Start Date                15-NOV-2008 18:02:25
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /oracle/network/admin/listener.ora
Listener Log File         /oracle/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)))
The listener supports no services
The command completed successfully

…and the listener is up and running. Since the listener is named LISTENER and is running on port 1521 every Oracle database on the server can ‘see’ that listener and auto-register with it. How can this auto-registration be prevented? The easiest method is to stop the listener; if no listener process exists then the databases have nothing to register with. This may not be the best solution, however, as other, active databases may also be on the same server and would require that a listener be running.

A different way of preventing auto-registration is to set local_listener to a non-existent port:

SQL> alter system set local_listener='(DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host.symantecexample.com)(PORT = 1577))))';

System altered.

SQL>

Since no listener is running on port 1577 the database cannot auto-register and thus no services will be available.

If setting local_listener to a non-existent value doesn’t ‘sit well’, two additional options exist. The first is to actually use a non-standard port for the listener configuration, which will require a listener.ora file. An example is shown below:

LISTENER =
  (ADDRESS_LIST =
        (ADDRESS = 
          (PROTOCOL = TCP)
          (Host = 127.0.0.1)
          (Port = 1593)
        )
  )
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = ADMIN

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host.symantecexample.com)
(PORT = 1593))
    )
  )

The listener, still using the default name of LISTENER, is now running on port 1593 and any database where local_listener isn’t configured to ‘see’ that listener won’t register automatically. If the port can’t be changed (firewall rules prevent this) then another option is to stop the default listener and simply change the name in the listener.ora file:

LSNR_BOB =
  (ADDRESS_LIST =
        (ADDRESS = 
          (PROTOCOL = TCP)
          (Host = 127.0.0.1)
          (Port = 1521)
        )
  )
STARTUP_WAIT_TIME_LSNR_BOB = 0
CONNECT_TIMEOUT_LSNR_BOB = 10
TRACE_LEVEL_LSNR_BOB = ADMIN

LSNR_BOB =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host.symantecexample.com)
(PORT = 1521))
    )
  )

Starting the listener now requires the listener name to be provided:

$ lsnrctl start lsnr_bob

Starting /private/sales_group/sales/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.0.0 
System parameter file is /oracle/network/admin/listener.ora
Log messages written to /oracle/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sales-server)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LSNR_BOB
Version                   TNSLSNR for Linux: Version 11.2.0.0.0 
Start Date                15-NOV-2008 18:02:25
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /oracle/network/admin/listener.ora
Listener Log File         /oracle/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)))
The listener supports no services
The command completed successfully

Additionally, this now requires that local_listener be set in all databases that use this listener:

SQL> alter system set local_listener='LSNR_BOB';

System altered.

SQL>

Now every database on the server where LSNR_BOB is running that is destined to use LSNR_BOB can register with it.

It isn’t difficult to thwart listener auto-registration but it does take some work and careful consideration. Not all of the above options may be available to you but at least one should work to prevent a database from registering and being visible on the network.

See all articles by David Fitzjarrell