Static service registration v/s Dynamic service registration in Oracle Database Listener.

We connect to oracle database remotely using listener. To connect the database instance through listener we need to register service for particular database under listener. There is always confusion about how the listener serve the database connections and how to register service under listener. We can register database service with listener in 2 ways. 

1. Static registration.
2. Dynamic registration.

Before we go to register any service listener must be created, if no listener is created yet we need to create one. We can create listener using GUI utility netca under $ORACLE_HOME/bin.

I recommend GUI utility because it easy to use and no chance for syntax errors.

Once you created listener you can see the listener configuration in the listener.ora file at $ORACLE_HOME/network/admin location.

GUI utility netca starts listener by default.

Now we will see static service registration. 

It is always easy to use GUI utility. Oracle has provided another GUI utility called netmgr. It is also available at $ORACLE_HOME/bin. Using netmgr we can do static registration.

cd $ORACLE_HOME/bin

./netmgr

You will get GUI. You can see listener which you created earlier under 

Oracle Net Configuration > Local > Listeners

Click on listener and select Database Services from drop down as shown below.

  Enter Oracle Home Path and SID as shown below and Save Network Configuration.

Exit from netmgr.
You need to restart the listener for changes to take effect. You can see service under listener with status UNKNOWN.

You will see that below static registration section is added to the listener.ora file.


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = prod)
      (GLOBAL_DBNAME = )
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
    )
  )



Now we will see Dynamic service registration. 

In Dynamic service registration again we have 2 scenarios depends on which port listener is running i.e either on 1521 or  any other port.

If listener is running on default port 1521 then database background process pmon dynamically register service with name defined in service_name init parameter.

Let see example.

Check the value for init parameter service_name.

You can check the value for service_name parameter as below.

SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string        

SQL>

Here you will get the value as <SID> or <SID.DOMAIN>

If the listener is running on default port 1521 then check listener status, you will get service with status READY and with name as defined in init parameter.

If the listener is running on port other than default port then pmon will not register the service under listener unless we set local_listener init parameter.

So in this case if we want pmon to register service dynamically under listener which is running on port other than default port we need to set local_listener init parameter as below.

SQL>  alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=<port>))))' scope=both;

So this local_listener init parameter let pmon register service on port defined in local_listener parameter where listener is actually running.

Service which registered statically will always have status UNKNOWN.

And Service which registered dynamically will always have status READY.