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.