To add a New Listener - other than the default LISTENER

The oracle listener process is a background process which runs on the database server and handles incoming client connection requests. There can be one or more listeners listening for a database server. When a connection request is made to a database listener, the listener will pass this request on to a dedicated server process or a dispatcher process, and sends a redirection message to the client process with the address of the server process. The client process then proceeds to communicate with the server process, while the listener continues to listen for further connection requests from client processes. By default the PMON process registers service information with a listener on the local address and port specified in listener.ora using the specified protocol. This registered information is then used by the listener process to forward client connection requests to the appropriate service handlers. Below is an example to configure to new listener for a database.

To configure a new listener

Sometimes there are multiple database instances created on the same server each with its own unique SID.

Generally the listener for all these databases is the same i.e. - the default LISTENER.

There may be a requirement where you will want a database to have its own listener.

example - a test/development database where the dba may want to frequently bring the listener up and down. 

In such a case you will not want the other database instances to be affected. 

For this you can create a separate listener for the test database.

Below are the detailed steps for creating a new listener.

If there are multiple Oracle Homes, see which is the base ORACLE_HOME being used for listener.ora and tnsnames.ora

One way to do this is to run the command 'lsnrctl status' on the command prompt.

This will give the path for the listener.ora file under the parameter 'Listener Parameter File'

This will also give the path for the listener log file for troubleshooting errors due to listener not starting or not configured correctly.

You can find the status of the listener as follows from the cmd prompt

cmd>lsnrctl
lsnrctl>status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=01HW2038611)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date                14-JAN-2023 08:32:37
Uptime                    0 days 3 hr. 7 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\ora_home\network\admin\listener.ora
Listener Log File         D:\app\oracle\Administrator\diag\tnslsnr\01HW2038611\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=01HW2038611)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=01HW2038611)(PORT=5501))(Security=(my_wallet_directory=D:\APP\ORACLE\ADMINISTRATOR\admin\alpha\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...

Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
  
The command completed successfully

Another way to find the path of your listener.ora file on the server is to do a 'tnsping ' at the command prompt. This will give the directory of sqlnet.ora file under Used parameter files This directory is the same where the listener.ora file being used will be residing. You can always try to access it from $ORACLE_HOME/network/admin directory, but if there are multiple ORACLE_HOME it will be a problem. Once the path for the listener.ora file is located, you can make the enry for the new listener say - LISTENER_ORCL as follows. Make entries in listener.ora and tnsnames.ora files for the new listener and tns entry. 

Entry in listener.ora

SID_LIST_LISTENER_ORCL =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u02/app/oracle/product/10.2.0/db_2)
      (PROGRAM = extproc)
    )
  )

LISTENER_ORCL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbserverorcl)(PORT = 1522))
    )
  )

Note the port used for the new listener is 1522 and not the default port 1521. Since the port 1521 will already be used by the existing listener 'LISTENER' you can assign a different port to this new listener.

Entry in tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserverorcl)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

LISTENER_ORCL =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserverorcl)(PORT = 1522))
  )  

Make a corresponding entry in initora file also for the new listener

CMD> sqlplus / as sysdba
SQL>create pfile from spfile;

initORCL.ora file will be created in $ORACLE_HOME/dbs directory. 

Add the below line to the initORCL.ora file

*.local_listener='LISTENER_ORCL'  

Shutdown the database with 'shutdown immediate' or 'shutdown abort' 

Now for the new listener changes to reflect in the init ora file you can recreate the spfile as follows

cmd> sqlplus / as sysdba
create the spfile from the pfile thus modified.
SQL> create spfile from pfile;

Once all these changes are done, restart the database for the new listener to be registered with the ORCL database.

SQL> startup
SQL>show parameter listener
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      LISTENER_ORCL
remote_listener                      string

This will show the new listener thus created. You can start and stop the new listener from the command prompt as follows

cmd> lsnrctl start LISTENER_ORCL

cmd> lsnrctl stop LISTENER_ORCL  

When starting the second listener you may get the following error

LSNRCTL> start LISTENER_ORCL
TNS-01106: Listener using listener name LISTENER_ALPHA has already been started

I do not know the solution to this. But to work around this problem,  you can reverse the order in which the listeners are started. 

Stop LISTENER_ALPHA 

Now start the listeners in the following sequence 

start LISTENER_ORCL 

start LISTENER_ALPHA 

 References

https://alekciss.com/create-a-listener-in-oracle-database-19c/
https://dba.stackexchange.com/questions/269484/how-to-add-a-new-listener-on-a-new-port-and-restrict-it-to-one-instance-only
https://docs.oracle.com/en/database/oracle/oracle-database/19/netag/configuring-and-administering-oracle-net-listener.html#GUID-0E7C39E3-4627-403A-AE69-E9AA2C7E4C57

Thought For the Day

Not all of us can do great things. But we can do small things with great love.

--Mother Theresa