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.
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.