Static and Dynamic Listener Concepts in Oracle Database

admin

Static listener and  Dynamic listener

What is a Listener?

Listener is used to listen to network requests to connect and pass them on to the instance.Any Remote connection to the database instance are made by the listener.
Single listener can connect to  multiple instances, and a single instance can be connected by multiple listeners.

The information the listener needs is the SID and ORACLE_HOME of the instance with which it can connect  users to the instance

Listener parameter file

The server side configuration of Oracle net services are kept under listener.ora.
This file is located in $ORACLE_HOME/network/admin
The information in the parameter file would be like hostname/IP, port, SID, ORACLE_HOME, ORACLE_BASE


How to Register a Listener

The process in which the listener gets to know the instance with which it is connecting and  is called registration.

There are 2 types registration Static registration and Dynamic registration.

Static listener registration

Static registration is  fixing a instance details in listener.ora file. In a static registration an instance is registered with the listener whether its up or not. When a client request comes listener opens a dedicated connection , and server then check if the instance is not up then it gives error message as “Oracle not available”.

The static listener configuration is done in the listener.ora located under the
ORACLE_HOME/network/admin directory. It is divided in two parts, one starts with the listener name and has information about the addresses that listener listens on.
The other starts with SID_LIST_ which includes information about the static
registration.
Static registration cane be used when we need to start/stop the database, or
cycle the database while connected remotely such as during RMAN duplication or dgmgrl during switchover operations.

An listener with status UNKNOWN is statically registered.

Entry in Listener.ora

KRISH =
  (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=tcp)(HOST=db.lab)(PORT=1571))
        (ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1571)))


SID_LIST_KRISH=
   (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME=COREDB)
          (SID_NAME=COREDB)
          (ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
        )
      )

Checking Status of Listener:


[oracle@db Desktop]$ lsnrctl status KRISH

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=db.lab)(PORT=1571))STATUS of the LISTENER------------------------Alias                     KRISHVersion                   TNSLSNR for Linux: Version 12.1.0.2.0 - ProductionStart Date                07-APR-2019 20:46:45Uptime                    0 days 0 hr. 31 min. 27 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.oraListener Log File         /u01/app/oracle/diag/tnslsnr/db/krish/alert/log.xmlListening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db.lab)(PORT=1571)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1571)))Services Summary...Service "COREDB" has 1 instance(s).  Instance "COREDB", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully
The status “UNKNOWN” resembles that listener has not checked instance status details that whether its up or not, it has just registered the details of instance. So listener is not sure that whether instance can handle client request or not.


—————————————————————————-----------------------------------------



Dynamic listener registration

In Dynamic registration , registration is performed by PMON process using default port 1521. Once a Database instance starts, its PMON process registers instance details with associated listener. Dynamic registration does not require any manual conguration in the listener.ora file.

If we want our listener not to use the default port then we need to change the LOCAL_LISTENER parameter in the init.ora or sp le of the instance
e.g. (ADDRESS=
(PROTOCOL=TCP)(HOST=hostname)(PORT=1531))

How to change LOCAL_LISTENER parameter for dynamic registration

Method 1:

SQL>alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST=db.lab)(PORT=1531)))’;
System altered.
SQL>alter system register;
System altered.

Method 2:


We can use an alias that has those details in your tnsnames.ora. The only advantage of using the tnsnames.ora is that you can change the configuration in the SQL*Net  rather than in the database.

For example:

Tnsnames.ora entry:

ATLANTA=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db.lab)(PORT = 1531))
)
(CONNECT_DATA =
(SERVICE_NAME = COREDB)
)
)


alter system set local_listener=’ATLANTA’;
alter system register;


PMON process wakes up at every 60 seconds and provide information to the listener. If any problem arises and PMON process fails then it’s not possible to register information to listener periodically. In this case we can do ‘Manual service registration’ using command:
alter system register;

The parameter is dynamic, so we can change it with instance up, PMON will de-register from the old listener and register to the new one.
When you see an instance with status “READY”, you know that PMON communicates with the listener.


[oracle@db Desktop]$ lsnrctl status COREDBLSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-APR-2019 21:19:09Copyright (c) 1991, 2014, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db.lab)(PORT=1531)))STATUS of the LISTENER------------------------Alias                     COREDBVersion                   TNSLSNR for Linux: Version 12.1.0.2.0 - ProductionStart Date                07-APR-2019 20:29:50Uptime                    0 days 0 hr. 49 min. 19 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.oraListener Log File         /u01/app/oracle/diag/tnslsnr/db/coredb/alert/log.xmlListening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db.lab)(PORT=1531)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1531)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=db.lab)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/COREDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))Services Summary...Service "COREDB" has 1 instance(s).  Instance "COREDB", status READY, has 1 handler(s) for this service...Service "COREDBXDB" has 1 instance(s).  Instance "COREDB", status READY, has 1 handler(s) for this service...The command completed successfully

If we are using dynamic listener registration (instance registration). The instance does not register with the listener until you mount the control file. An instance must start to dynamically register with the listener.