How to configure multiple Oracle listeners

How to configure multiple Oracle listeners

It happened to me few times during the stress tests that Oracle listener became a bottleneck and not be able to handle the required workload. It was resolved by creating  multiple  listeners which appeared to be a quick solution.  Here is a short step-by-step procedure to configure multiple Oracle listeners  on Solaris for standalone Oracle 9i and 10g environment.

1)  First of all, add additional NIC and cables. They can be on separate subnetwork or the same. In the later, make sure to set static route if needed.   

2) Assume that we are going to configure two listeners,  LISTENER and LISTENER2

Modify listener.ora and tnsnames.ora as following:

Here is a sample of  listener.ora

 LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.3)(PORT = 1521))
    )
  )

LISTENER2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.2)(PORT = 1525))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/my10g/orcl)
      (PROGRAM = extproc)
    )
  )

Here is sample of  tnsnames.ora

LISTENER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

LISTENER2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.2)(PORT = 1525))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  ) 

3)   To change database registry

Without changing the registration,  when starting the Oracle 10g database would cause the database to register itself with the listener running on port 1521 (the default listener). This is not what  I wanted. It should register itself to two listeners, LISTENER & LISTENER2, defined on port 1521 & 1525. For this to happen we have to add an extra line in the database parameter file init{$SID}.ora.  The parameter used by oracle is LOCAL_LISTENER. The reference for this parameter in the Oracle's  Database Reference Guide says: LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners (that is, listeners that are running on the same machine as this instance). The address or address list is specified in the TNSNAMES.ORA file or other address repository as configured for your system. With default value: (ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))  where hostname is the network name of the local host. See sample below:

    LOCAL_LISTENER=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.2)(PORT=1525))

If you don't use a database parameter file (or the above overwrite the previous definiation on 10.6.142.145), but use the spfile construction, then you can alter/set this setting via a SQL statement in eg. SQL\*Plus and an account with the correct privileges:

    Before change:
    SQL> show parameter LISTENER
    NAME                                 TYPE        VALUE
    ----------------------------------- --------- -----------------------------
    local_listener                       string
    remote_listener                      string
    SQL>

To change: (Do not put it in a single line which is "TOO LONG").
    SQL> alter system set LOCAL_LISTENER="(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.6.142.145)(PORT=1521))
      2  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.2)(PORT=1525)))" scope=BOTH;
    System altered.
    SQL>

After change
    SQL> show parameter LISTENER
    NAME                      TYPE        VALUE
    ----------------------   --------  -----------------------------
    local_listener            string      (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.6.142.145)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.2)(PORT=1525))) log_archive_local_first   boolean     TRUE
    SQL>

    SQL>
    SQL> show parameter DISPATCHERS
    NAME                      TYPE        VALUE
     -----------------------  -------   ----------------------------
    dispatchers               string      (PROTOCOL=TCP) (SERVICE=orclXDB)
    max_dispatchers           integer
    SQL>

4) Restart the listeners:

     lsnrctl stop

     lsnrctl start  LISTENER

     lsnrctl start  LISTENER2

5)  Check both listeners status and should report the same thing except on different IP:

     lsnrctl stat

     lsnrctl stat LISTENER1

     lsnrctl stat LISTENER2

     ps -ef |grep -i tns                      <=== should see two listeners running 

6) Should spread out your connections among different listeners and here are some samples of how to connect to  a particular listener. i.e
     sqlplus system/oracle@//192.168.100.3:1521/orcl
     sqlplus system/oracle@//192.168.100.2:1522/orcl

dont forget to comment !!

thanks

anand gupta