Martins Blog

Trying to explain complex things in simple terms

Little things worth knowing-static and dynamic listener registration

Posted by Martin Bach on June 20, 2012

As part of  a recent project to remove a vulnerability in relation to CVE-2012-1675 it became apparent that there are certain misconceptions around dynamic and static listener registration which are hard to get rid of. The below is applicable for single instance Oracle only!

Now let’s start with a quiz: what does the following output imply:

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 20-JUN-2012 11:22:01

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1571))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                15-JUN-2012 11:14:27
Uptime                    5 days 0 hr. 7 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/server1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1571)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1571)))
Services Summary...
Service "ORA11202" has 1 instance(s).
  Instance "ORA11202", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Yes, that’s right: the UNKNOWN means that there is no dynamic registration.

How does the listener register with the database?

If memory serves me right then Oracle introduced dynamic listener registration with 9i. This is the reason why there is no listener.ora file required in your $ORACLE_HOME/network/admin/ directory anymore. Simply start the listener and it will use the defaults, binding to port 1521. Of course, every attacker knows about that port number.

Any running database will try to register with the listener on port 1521-completely automatically. If the listener is instructed to start with a different port though, then there won’t be dynamic registration. That is, unless you set the local_listener parameter for a non-Oracle Restart/RAC environment where this is done for you by a HAS/CRS agent.

However, you technically don’t need to allow the database to register with the listener in single instance environments. As in the days before dynamic registration, you can statically register database services with the listener. Even today that is required for any process that cycles the database. As soon as the database is down, any dynamically registered service is removed from the listener. Examples for such processes are the data guard broker controlled switchover operation as well as RMAN duplication from active database. I’m sure there are others too but those are the two that come to mind.

The different methods to configure your listener.ora file

The most basic way to configure the listener is not to configure it at all. Change to $ORACLE_HOME/network/admin and move the listener.ore file out of the way. Then start the listener. However that’s not really recommended-as you will see on many posts on the web the fix to prevent illicit listener registration with a database is to enable the Class of Secure Transport (“COST”). This isn’t part of the defaults. Also, you might want to rethink the listener binding to port 1521. So let’s discard that idea for anything outside your own laptop play area.

A better way is to create a basic listener.ora file, either manually or via netca/netmgr. It will most likely look similar to this one:

LISTENER =
  (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1571))
        (ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1571))
  )

This basic variant now has to be amended with the SECURE_REGISTER_LISTENER parameter (at least). Please refer to My Oracle Support for more information about this parameter and the values it can take.

With the above listener configuration file the listener was instructed to listen on IPC, and the client-facing Ethernet card on port 1571. Now let’s see what effect that has:

$ lsnrctl start listener
...
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1571)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1571)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1571))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                19-JUN-2012 19:46:36
Uptime                    0 days 0 hr. 0 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/server1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1571)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1571)))
The listener supports no services
The command completed successfully

Note the line “the listener supports no services”. Since the listener doesn’t listen on port 1521, this will stay this way. Trying to connect to a database will only return the infamous “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”. Oh and yes, the tnsping will be successful!

To make the listener aware of the database service, you have two options:

  1. Statically register the database service
  2. Modify the local_listener parameter of your single instance/non Oracle Restart database

Option 1 requires a change to the listener.ora to register your database. Consider this example:

LISTENER =
  (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1571))
        (ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1571)))

# this is new
SID_LIST_LISTENER=
   (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME=ORA11202)
          (SID_NAME=ORA11202)
          (ORACLE_HOME=/u01/app/oracle/product/11.2/dbhome_1/)
        )
      )

SECURE_REGISTER_LISTENER = (IPC)

Restarting the listener now shows us the following service information:

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1571))
Services Summary...
Service "ORA11202" has 1 instance(s).
  Instance "ORA11202", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

Now here is one of the things I’d like you to take away from this post: statically registered databases will report a status of UNKNOWN. However you can connect to a statically registered database just fine without the listener knowing anything about it. Consider it as a blindfolded person on a train: the person doesn’t see what its doing, but will get there in the end regardless.


$ sqlplus a/b@server1:1571/ORA11202

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 20 13:13:40 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

To prove it, the counters in the listener output is incremented:


Services Summary...
Service "ORA11202" has 1 instance(s).
  Instance "ORA11202", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0
         LOCAL SERVER
The command completed successfully

The second scenario involved telling the database’s pmon process how to connect to the listener. I’m using IPC here, all other registration attempts are blocked by the COST parameter:

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1571)))';

System altered.

SQL> alter system register;

System altered.

Keeping the static registration in the listener file, I get this:

Services Summary...
Service "ORA11202" has 2 instance(s).
  Instance "ORA11202", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0
         LOCAL SERVER
  Instance "ORA11202", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "ORA11202XDB" has 1 instance(s).
  Instance "ORA11202", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER
         (ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=32637))
The command completed successfully

Looking at the output you can see that the same database is now registered a number of times. The top line, with status UNKNOWN is from the static registration.

The next lines are new, and result from the registration of the services with the listener, triggered by my “alter system register” command (I’m impatient, that registration would have happened eventually). The registration can also be seen in the listener.log:

19-JUN-2012 19:19:56 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=server1)(USER=oracle))(COMMAND=services)(ARGUMENTS=64)(SERVICE=listener)(VERSION=186647040)) * services * 0
2012-06-19 19:19:24.839000 +01:00
19-JUN-2012 19:19:24 * service_register * ORA11202 * 0
2012-06-19 19:19:43.319000 +01:00
19-JUN-2012 19:19:43 * service_update * ORA11202 * 0
2012-06-19 19:19:46.320000 +01:00

Summary

So now when you see an instance with status “READY”, you know that PMON communicates with the listener. An instance with status UNKNOWN is statically registered. Static registration is perfectly sufficient, and indeed necessary when you need to (re)start the database, or cycle the database while connected remotely such as during RMAN duplication or dgmgrl during switchover operations.

About these ads

4 Responses to “Little things worth knowing-static and dynamic listener registration”

  1. Noons said

    Excellent post, Martin. Definitely into the bookmarks!

  2. pan said

    Thank you Martin. This is great.

  3. Olga said

    Great Post!!! I solved my problem!! Thanks a lot!! :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,327 other followers

%d bloggers like this: