Instance/service registration with Database listener

I have seen many times DBAs are getting confused with Static registration and dynamic registration of services/instance with listener.
As far back I remember, dynamic registration of services was introduced in Oracle 9i.

In this article, I am going to cover everything about static and dynamic service/instance registration with listener and what does it mean.

Many times you must have seen following error while making remote connection to database
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
Descriptor
Understanding of service/instance registration is essential to fix this issue.

Static Registration of instances/service – How it works

Static Instance Registration:

We all know that listener is a separate process that runs on database server and is responsible for receiving client connection and passing connection to database. After connection is handed over to database, listener is out of the picture.

Question is how does listener know what is the instance name/service name and where he should send the client connection ?
One way to do it is using static registration. This is done by including SID_LIST section in listener.ora configuration file.

Example, my listener.ora file looks like following

 

l_deo_001 =
    (description =
        (address_list =
            (address=(protocol=tcp)(host=deo-server.example.com)(port=1529))))
sid_list_l_deo_001 =
    (sid_list =
        (sid_desc =
            (sid_name = deo)
            (oracle_home = /opt/app/oracle/product/db_1))
    )

 

If you see I have a section called “SID_LIST_<listener_name>” which tells listener that SID name is “deo”.

If we check listener status we will see following

lsnrctl status l_deo_001

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-APR-2013 09:17:58

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

Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=deo-server.example.com)(port=1529)))
STATUS of the LISTENER
------------------------
Alias                     l_deo_001
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                02-APR-2013 09:02:55
Uptime                    0 days 0 hr. 15 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/oracle/product/db_1/network/admin/listener.ora
Listener Log File         /opt/app/oracle/diag/tnslsnr/db-deo-r1/l_deo_001/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deo-server.example.com)(PORT=1529)))
Services Summary...
Service "deo" has 1 instance(s).
  Instance "deo", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

“Instance “deo”, status UNKNOWN” meaning that instance DEO is registered with listener l_deo_001.

Why status is unknown is because listener does not know whether the instance is really up and available or not. There is no mechanism to guarantee that the specified instance even exists. The listener just assumes that the instance will be there when a connect request is received.

My tnsping is as below

(DESCRIPTION = (ENABLE = BROKEN) (LOAD_BALANCE = YES) (ADDRESS = (PROTOCOL = TCP)(HOST = deo-server.example.com)(PORT = 1529)) (CONNECT_DATA = (SID = deo)))

So I have “SID = deo” in my connect string. And since this SID/instance is registered with my listener, my connections goes fine with database (Offcource using correct host and port is required).

Static Service Registration:

Same is the case with service. Example if I have following TNS alias which is using SERVICE_NAME in CONNECT_DATA

(DESCRIPTION = (ENABLE = BROKEN) (LOAD_BALANCE = YES) (ADDRESS = (PROTOCOL = TCP)(HOST = deo-server.example.com)(PORT = 1529)) (CONNECT_DATA = (SERVICE_NAME = adg_deo)))

Now if you want to connect to database using above TNS alias which has SERVICE_NAME, you need to have this service registered in listener port

Way to do that using static registration is to use GLOBAL_DBNAME = <service_name> in listener.ora parameter as shown below and bounce listener for this to take affect

l_deo_001 =
    (description =
        (address_list =
            (address=(protocol=tcp)(host=deo-server.example.com)(port=1529))))

sid_list_l_deo_001 =
    (sid_list =
        (sid_desc =
            (sid_name = deo)
            (oracle_home = /opt/app/oracle/product/db_1))
    (sid_desc =
            (GLOBAL_DBNAME = adg_deo.example)
            (sid_name = deo)
            (oracle_home = /opt/app/oracle/product/db_1))
    )

 

After that listener will show adg_deo service

lsnrctl start l_deo_001

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-APR-2013 10:24:10

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

Starting /opt/app/oracle/product/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /opt/app/oracle/product/db_1/network/admin/listener.ora
Log messages written to /opt/app/oracle/diag/tnslsnr/db-deo-r1/l_deo_001/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deo-server.example.com)(PORT=1529)))

Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=deo-server.example.com)(port=1529)))
STATUS of the LISTENER
------------------------
Alias                     l_deo_001
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                02-APR-2013 10:24:10
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/oracle/product/db_1/network/admin/listener.ora
Listener Log File         /opt/app/oracle/diag/tnslsnr/db-deo-r1/l_deo_001/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deo-server.example.com)(PORT=1529)))
Services Summary...
Service "adg_deo.example" has 1 instance(s).
  Instance "deo", status UNKNOWN, has 1 handler(s) for this service...
Service "deo" has 1 instance(s).
  Instance "deo", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

 

After that you will be able to connect using SERVICE_NAME = adg_deo.example

Remember that in static registration you need to have instance name in SID_LIST section in listener.ora as specified by (sid_name = deo). If you have some service_name configured in TNS alias, you need to make sure that in case of static registration those service_names should be part of listener.ora file as specified by (GLOBAL_DBNAME = adg_deo.example).

Now, if I remove SID_LIST section from listener.ora, listener still works. Here is the change

My listener.ora now looks as below

l_deo_001 =
    (description =
        (address_list =
            (address=(protocol=tcp)(host=deo-server.example.com)(port=1529))))

 

If I check listener status
lsnrctl start l_deo_001

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-APR-2013 09:01:25

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

Starting /opt/app/oracle/product/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /opt/app/oracle/product/db_1/network/admin/listener.ora
Log messages written to /opt/app/oracle/diag/tnslsnr/db-deo-r1/l_deo_001/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deo-server.example.com)(PORT=1529)))

Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=deo-server.example.com)(port=1529)))
STATUS of the LISTENER
------------------------
Alias                     l_deo_001
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                02-APR-2013 09:01:25
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/oracle/product/db_1/network/admin/listener.ora
Listener Log File         /opt/app/oracle/diag/tnslsnr/db-deo-r1/l_deo_001/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deo-server.example.com)(PORT=1529)))
The listener supports no services
The command completed successfully

It says “The listener supports no services”. If you try connecting now, you will hit following error

 

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
Descriptor

Why? because your instance is not registered with listener. So l_deo_001 is just a standalone listener process running on the host with no instances registered with it. It doesnt transfer connections to any database. So connecting to “deo” database using this listener will not work.

Dynamic Registration of instances/service – How it works

Dynamic Instance Registration:

Do we need to have SID_LIST always in listener.ora file ? The answer is NO. This requirement is gone since Oracle 9i when dynamic registration was introduced. In dynamic registration database automatically register the instances/services to listener ports.

Then how does instance gets registered with listener ?

To answer that, I have to explain the concept of local_listener parameter.

If you are using default port (1521) for listener, then database will automatically register the instance with listener. To test this out, lets start another listener which will have default name and port (1521)

lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-APR-2013 09:57:44

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

Starting /opt/app/oracle/product/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /opt/app/oracle/product/db_1/network/admin/listener.ora
Log messages written to /opt/app/oracle/diag/tnslsnr/db-deo-r1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deo-server.example.com)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                02-APR-2013 09:57:44
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/oracle/product/db_1/network/admin/listener.ora
Listener Log File         /opt/app/oracle/diag/tnslsnr/db-deo-r1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deo-server.example.com)(PORT=1521)))
The listener supports no services
The command completed successfully

 

It still says that “The listener supports no services”. To have database register the instance, you need to bounce the database so that during the start of DB, it will register the instance name with default listener

Now if you check the listener status, you will see service is registered automatically with default listener.

lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-APR-2013 10:03:13

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                02-APR-2013 09:57:44
Uptime                    0 days 0 hr. 5 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/oracle/product/db_1/network/admin/listener.ora
Listener Log File         /opt/app/oracle/diag/tnslsnr/db-deo-r1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deo-server.example.com)(PORT=1521)))
Services Summary...
Service "deo_a.example" has 1 instance(s).
  Instance "deo", status READY, has 1 handler(s) for this service...
Service "deo_a_DGB.example" has 1 instance(s).
  Instance "deo", status READY, has 1 handler(s) for this service...
The command completed successfully

 

But what if you are not using default listener port (1521). Instead, you are using some other port – Say 1529. Oracle doesnt do dyamic registration on non-default listener port automatically.

So how do we tell Oracle what ports our listener is running on ? Answer to this question is local_listener parameter.

Lets stop default listener and start our original listener l_deo_001 on 1529 port. Also, I am not having SID_LIST section in my listener.ora file as I am going for dynamic registration

l_deo_001 =
    (description =
        (address_list =
            (address=(protocol=tcp)(host=deo-server.example.com)(port=1529))))
lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-APR-2013 10:08:42

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully

lsnrctl start l_deo_001

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-APR-2013 10:10:49

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

Starting /opt/app/oracle/product/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /opt/app/oracle/product/db_1/network/admin/listener.ora
Log messages written to /opt/app/oracle/diag/tnslsnr/db-deo-r1/l_deo_001/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deo-server.example.com)(PORT=1529)))

Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=deo-server.example.com)(port=1529)))
STATUS of the LISTENER
------------------------
Alias                     l_deo_001
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                02-APR-2013 10:10:49
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/oracle/product/db_1/network/admin/listener.ora
Listener Log File         /opt/app/oracle/diag/tnslsnr/db-deo-r1/l_deo_001/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deo-server.example.com)(PORT=1529)))
The listener supports no services
The command completed successfully

In local_listener parameter we can specify non-default ports on which listener is running and Oracle will register services on those ports.

 

Example in my case, I am setting local_listener to following value

alter system set local_listener = '(address_list=(address=(protocol=tcp)(host=deo-server.example.com)(port=1529)))';

As soon as I set the value of local_listener, PMON will know the port number on which listener is running and it will dynamically register the instance with listener.

lsnrctl status l_deo_001

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-APR-2013 10:12:35

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

Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=deo-server.example.com)(port=1529)))
STATUS of the LISTENER
------------------------
Alias                     l_deo_001
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                02-APR-2013 10:10:49
Uptime                    0 days 0 hr. 1 min. 45 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/oracle/product/db_1/network/admin/listener.ora
Listener Log File         /opt/app/oracle/diag/tnslsnr/db-deo-r1/l_deo_001/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deo-server.example.com)(PORT=1529)))
Services Summary...
Service "deo_a.example" has 1 instance(s).
  Instance "deo", status READY, has 1 handler(s) for this service...
Service "deo_a_DGB.example" has 1 instance(s).
  Instance "deo", status READY, has 1 handler(s) for this service...
The command completed successfully

 

So by setting LOCAL_LISTENER to the values appropriate for the listener, Oracle PMON process is able to contact the listener and register its instance.

 

Dynamic Service Registration:

We have seen dynamic instance registration which needs LOCAL_LISTENER database parameter to be set if we are not running the listener on default port (1521). What if we are using SERVICE_NAME in our TNS connect string as shown below

(DESCRIPTION = (ENABLE = BROKEN) (LOAD_BALANCE = YES) (ADDRESS = (PROTOCOL = TCP)(HOST = deo-server.example.com)(PORT = 1529)) (CONNECT_DATA = (SERVICE_NAME = adg_deo)))

If we want to register the service name dynamically in listener, we need to set service_names parameter in database

Currently we dont have ADG_DEO service in our listener

lsnrctl start l_deo_001

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-APR-2013 06:56:24

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

Starting /opt/app/oracle/product/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /opt/app/oracle/product/db_1/network/admin/listener.ora
Log messages written to /opt/app/oracle/diag/tnslsnr/db-deo-r1/l_deo_001/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deo-server.example.com)(PORT=1529)))

Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=deo-server.example.com)(port=1529)))
STATUS of the LISTENER
------------------------
Alias                     l_deo_001
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                03-APR-2013 06:56:24
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/oracle/product/db_1/network/admin/listener.ora
Listener Log File         /opt/app/oracle/diag/tnslsnr/db-deo-r1/l_deo_001/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deo-server.example.com)(PORT=1529)))
Services Summary...
Service "deo" has 1 instance(s).
  Instance "deo", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

 

I have local_listener value set as below

 

SYS.DEO>show parameters local_listener

NAME_COL_PLUS_SHOW_PARAM                             TYPE
-------------------------------------------------------------------------------- -----------
VALUE_COL_PLUS_SHOW_PARAM
-------------------------------------------------------------------------------------
local_listener                                     string
(address_list=(address=(protocol=tcp)(host=deo-server.example.com)(port=1529)))

Now I will set service_names parameter as below

SYS.DEO>alter system set service_names = 'adg_deo.example';

System altered.

SYS.DEO>

 

If you check the status of listener now, it will have that service name shown in the list

 

lsnrctl status l_deo_001

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-APR-2013 06:58:04

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

Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=deo-server.example.com)(port=1529)))
STATUS of the LISTENER
------------------------
Alias                     l_deo_001
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                03-APR-2013 06:56:24
Uptime                    0 days 0 hr. 1 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/oracle/product/db_1/network/admin/listener.ora
Listener Log File         /opt/app/oracle/diag/tnslsnr/db-deo-r1/l_deo_001/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=deo-server.example.com)(PORT=1529)))
Services Summary...
Service "adg_deo.example" has 1 instance(s).
  Instance "deo", status READY, has 1 handler(s) for this service...
Service "deo" has 1 instance(s).
  Instance "deo", status UNKNOWN, has 1 handler(s) for this service...
Service "deo_a.example" has 1 instance(s).
  Instance "deo", status READY, has 1 handler(s) for this service...
Service "deo_a_DGB.example" has 1 instance(s).
  Instance "deo", status READY, has 1 handler(s) for this service...
The command completed successfully

So basically all the services which are listed in service_names database parameter will be registered by PMON in listener ports which are mentioned in local_listener parameter.

I hope I am able to clearify how static and dynamic instnace/service registration works.

 

About these ads

7 thoughts on “Instance/service registration with Database listener

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