Oracle11g: Dealing with dynamic service registration

Oracle11g provides a dynamic service registration.
Unlike static service registration, dynamic registration need less information in listerner.ora file and more configuration in database parameter.

listener.ora configuration
The listener.ora file will contain only the adress of the listener :

# listener.ora Network Configuration File: /opt/oracle/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.gemalto.com)(PORT = 1521))
  )

ADR_BASE_LISTENER = /opt/oracle

Database configuration
The SERVICE_NAMES database parameter contains a string that list all services that the database will register automaticatlly in the default listener.
This parameter goes along with the DB_DOMAIN parameter.

For example, if you define this parameters like here under :

• SERVICE_NAMES = 'MY_BASE'

• DB_DOMAIN = 'gemalto.com'

then the database will register the service 'MY_BASE.gemalto.com' in the default listener.

if DB_DOMAIN is defined using an empty string then the service will be 'MY_BASE'.

To define more then one services in the listener then you can write the following

• SERVICE_NAMES='DB1, DB2,DB3'
• DB_DOMAIN = 'gemalto.com'

in this way, three services will be registered dynamically in the listener : DB1.gemalto.com, DB2.gemalto.com and DB3.gemalto.com

To set the parameters you can use the following SQL syntax:

SQL>ALTER SYSTEM SET SERVICE_NAMES='MY_BASE,MY_PROD' scope=BOTH;

scope=BOTH indicates that the memory of the database will be altered and the spfile will be updated in the same time.

The DB_DOMAIN name is a static parameter and could not be modified dynamically.
You can try the following syntax

SQL>ALTER SYSTEM SET SERVICE_NAMES='MY_BASE,MY_PROD' scope=SPFILE DEFERRED

or modify your init<SID>.ora initialization file.
To be taken into account, restart your database.

Checking the listener

>lsnrctl status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.gemalto.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                10-AUG-2011 17:32:49
Uptime                    0 days 0 hr. 36 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/11.2.0/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/myhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.gemalto.com)(PORT=1521)))
Services Summary...
Service "MY_BASE" has 1 instance(s).
  Instance "INSTANCE", status READY, has 1 handler(s) for this service...
Service "MY_PROD" has 1 instance(s).
  Instance "INSTANCE", status READY, has 1 handler(s) for this service...
The command completed successfully