Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted December 19, 2016

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

Oracle’s Automatic Listener Registration

By David Fitzjarrell

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM