Skip to content

Alter System set Local_Listener Multiple Examples

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 by ALTER SYSTEM SET LOCAL_LISTENER. 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 in case of listener failure.

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

Since you have multiple listeners, you can set LOCAL_LISTENER to one of them or multiple listeners.

Single Listener

If you'd like to switch the working listener to the backup one, you may alter system set local listener like this.

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

System altered.

Here we use only one ADDRESS.

Multiple Listeners

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.

Here we use ADDRESS_LIST to accommodate more entries.

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 in this instance.

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 by instructing PMON to register with the listeners immediately.

SQL> alter system register;

System altered.

Please note that, the SQL command should be done in each instance if the database is a RAC one.

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 *