mercredi 10 août 2011

Oracle11g: How to configure a database to register dynamically a service in a listener that not use the default port

First you need to define the listener with a port that is not the default port by registering your listener configuration in the listener.ora file.
# 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)(PORT = 1523))
  )

ADR_BASE_LISTENER = /opt/oracle

Run the listener using the following command:
>lsnrctl start
or using the srvctl command:
>srvctl start listener

Then you can update your database parameter in order to define the address of the listener:
Using sql ;
SQL>ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOl=TCP)(HOST=myhost)(PORT=1523))' SCOPE=BOTH;

If you listener is running on a different host then the database, you need to update the REMOTE_LISTER database parameter. This parameter will reference a network alias that must be declared in the tnsnames.ora file of the database.
For example, you can write in your tnsnames.ora file where is located your database :
# tnsnames.ora Network Configuration File: /opt/oracle/11.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

MY_REMOTE_ALIAS =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myhost2)(PORT = 1523))
  )

and update the remote_listener database parameter in this way :
SQL>ALTER SYSTEM SET REMOTE_LISTENER='MY_REMOTE_ALIAS' SCOPE=BOTH;
The database will try to connect on host2 and port number 1523 to register dynamically all its services.

Remark:
Check the following situation :
  1. Define a listener that has a name different than the default one (for example MY_LISTNER  instead of  LISTENER)
    # listener.ora Network Configuration File: /opt/oracle/11.2.0/network/admin/listener.ora
    # Generated by Oracle configuration tools.
    
    MY_LISTENER =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1523))
      )
    
    ADR_BASE_LISTENER = /opt/oracle
  2. start the listener
    >lsnrctl start MY_LISTENER
    or
    >srvctl start listener -l MY_LISTNER
  3. Set the LOCAL_LISTENER parameter to its initial value (an empty string)
    SQL> ALTER SYSTEM SET LOCAL_LISTENER='' SCOPE=BOTH;
    SQL> show parameter local_listener
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    local_listener                       string
    
  4. Start the default listener
    >lsnrctl start

Then, you will see that the database register its configuration under the listener MY_LISTENER listening on port 1523 but also in the default listener LISTENER listening on port 1521. This is due to the fact that we do not update the LOCAL_LISTNER value !