Table of Contents
What is Listener in Oracle 19c ?
Listener is service which runs on a specific port and used to connect to oracle database.
In Oracle, listener.ora is used to configure the listener service on Oracle Database Server. The listerner.ora file has network configuration parameters. It comes into picture when you want to connect to the database over network. For instance here, This is default listener, which is listening on 1521 TCP port on my here. My oracle database hostname is oracledbworld. And My oracle database instance is dbworld
Location of listene.ora file -> $ORACLE_HOME/network/admin
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledbworld )(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =dbworld)
(ORACLE_HOME = /u01/app/oracle/product/19c/dbhome)
(SID_NAME = dbworld)
)
)
How to start and Stop Listener in Single Instance database
$ lsnrctl stop listener_name $ lsnrctl start listener_name
How to Start and Stop Listener in Oracle Restart and Oracle RAC
If listener is managed by grid infrastructure, then you need to use following command to stop and start the listener service –
$srvctl stop listener -l listener_name $srvctl start listener -l listener_name
How to register a database service to Listener And their types
There are two ways to register a service to listener
Static Registration to a Listener
In this, we use SID_LIST_ to explicitly register database with the listener.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledbworld )(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =dbworld)
(ORACLE_HOME = /u01/app/oracle/product/19c/dbhome)
(SID_NAME = dbworld)
)
)
Doesn’t matter database is up or down, listener status will always show the service registered to it if you have used Static registration to a listener.
Dynamic Registration to a Listener
In this, we have listener configured without SID_LIST and we make use of local_listener parameter and let PMON to do it for us.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledbworld )(PORT = 1521))
)
)
When you check the status of the listener – It will show no service registered here. The moment you set the local_listener parameter, you will see that db is registered with the listener.
[oracle admin]$ lsnrctl status listener LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 03-JAN-2022 11:46:17 Copyright (c) 1991, 2020, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracledbworld)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 03-JAN-2022 11:45:58 Uptime 0 days 0 hr. 0 min. 18 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19c/dbhome/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/oracledbworld/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracledbworld)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) The listener supports no services The command completed successfully [oracle admin]$
Once you register the db with listener with following commands
export ORACLE_SID=dbworld sqlplus -s / as sysdba show parameter local_listener alter system set local_listener='(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST=oracledbworld )(PORT = 1521))" scope=both;
Now lets check the listener Status –
[oracle admin]$ lsnrctl status LISTENER LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 03-JAN-2022 11:45:32 Copyright (c) 1991, 2020, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracledbworld)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 03-JAN-2022 12:45:58 Uptime 0 days 0 hr. 0 min. 18 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19c/dbhome/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/oracledbworld/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracledbworld)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "dbworld" has 1 instance(s). Instance "dbworld", status READY, has 1 handler(s) for this service... Service "dbworldXDB" has 1 instance(s). Instance "dbworld", status READY, has 1 handler(s) for this service... The command completed successfully [oracle admin]$
Static Registration of Multiple database to a Listener in Oracle 19c
If there are multiple database instances on the server, you can add multiple SID_DESC entries inside the SID_LIST section.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledbworld )(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =dbworld)
(ORACLE_HOME = /u01/app/oracle/product/19c/dbhome)
(SID_NAME = dbworld)
)
(SID_DESC =
(GLOBAL_DBNAME =dbworld1)
(ORACLE_HOME = /u01/app/oracle/product/19c/dbhome)
(SID_NAME = dbworld1)
)
(SID_DESC =
(GLOBAL_DBNAME =dbworld2)
(ORACLE_HOME = /u01/app/oracle/product/19c/dbhome)
(SID_NAME = dbworld2)
)
(SID_DESC =
(GLOBAL_DBNAME =dbworld3)
(ORACLE_HOME = /u01/app/oracle/product/19c/dbhome)
(SID_NAME = dbworld3)
)
)
Once you have made changes you need to reload the listener to reflect the listener. or You can go ahead with start stop of the listener service. Please note you should do this in lean or = downtime period.
lsnrctl reload listener or lsnrctl stop listener lsnrctl start listener
If you don’t have downtime for the listener, create a new listener on new port and release the system to the application team.
Why you need Static Registration over Dynamic Registration to a Listener
If you want to remotely startup the database, you need to have a statically registered listener. If you have dynamic registered listener, then it will deregister automatically once database is down. And it will not get registered until you database is up. Hence if you wnt to start a database remotely you need to have a listener where database is registered in listener configuration file ( $ORACLE_HOME/network/admin/listener.ora)
On other hand, if you look at managing multiple databases and if you want to register multiple instances to single listener on same port it will require listener reload/start-stop to reflect the changes in case of static registered listener. Where as dynamic listener doesn’t require listener reload. You just need to set local_listener in new database and service will get registered to the listener.