Skip to content

How to Set Multiple Listeners in Database

  • by

Multiple Listeners in LOCAL_LISTENER

Theoretically, you don't have to configure parameters related to listener in database, neither LOCAL_LISTENER nor REMOTE_LISTENER. A background process Listener Registration (LREG) of each instance will register services with the default local and remote listener automatically.

For a new listener which uses non-local IP address or non-default port number, you need to explicitly tell LREG to register database services with numerous listeners. Which requires LOCAL_LISTENER to accept more listener addresses in the setting.

In this case, we create a backup listener to listen a non-default port 1522 for redundancy.

Create New Listener

Edit listener.ora

To add a new listener which listens a different port, we need to modify listener.ora.

[oracle@test ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
LISTENER2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.111)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

For adding a listener in a RAC database, you may refer to: How Srvctl Add Listener to Default Network of Grid.

lsnrctl start

We start it by lsnrctl start command.

[oracle@test ~]$ lsnrctl start listener2

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 13-OCT-2022 04:36:46

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

Starting /u01/app/oracle/product/19.3.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/test/listener2/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.111)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.111)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     listener2
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                13-OCT-2022 04:36:46
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/test/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.111)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
The listener supports no services
The command completed successfully

For starting a listener in a RAC database, you may refer to: How Srvctl Add Listener to Default Network of Grid.

We are good to go.

Alter System Set LOCAL_LISTENER

Suppose you have already created a new backup listener which listens port 1522. Let's see how we configure multiple entries in LOCAL_LISTENER.

SQL> alter system set local_listener='(ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.111)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.111)(PORT = 1522)))' scope=both;

System altered.

ADDRESS_LIST vs ADDRESS

The key is to use one ADDRESS_LIST to accept multiple ADDRESS in the parameter. So the relationship between ADDRESS_LIST and each ADDRESS can be considered as parent-child one.

Since the parameter can be dynamically changed, we don't have to restart the instance(s). So now, we can make sure that the parameter is configured correctly.

SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS_LIST=(ADDRESS = (PROT
                                                 OCOL = TCP)(HOST = 192.168.0.1
                                                 11)(PORT = 1521))(ADDRESS = (P
                                                 ROTOCOL = TCP)(HOST = 192.168.
                                                 0.111)(PORT = 1522)))

Alter System Register

Database services will be registered with both listeners in 60 seconds, or you can make it faster.

SQL> alter system register;

System altered.

The SQL command should be done in each instance.

Except for a backup listener, there're more scenarios that may use multiple entries in LOCAL_LISTENER.

Multiple Listeners with Same Port?

No, you can't create a listener to listen to the same IP with same port which has already taken by other one. You can't even start it. Let's see the error.

[oracle@test ~]$ lsnrctl start listener2
...
TNS-01106: Listener using listener name LISTENER has already been started

Just don't listen to the same port for your backup listener, choose another port. Conventionally, port 1522 is acceptable.

Leave a Reply

Your email address will not be published. Required fields are marked *