About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Monday, December 2, 2013

Difference between Static listener and Dynamic listener registrations

Database can be registered in two ways with a listener :-

1. Static Registration :- A static registration of database to a listener is used when we want to remotely start our database. Static registration is like hardcoding 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 later find out that instance is not up then it gives error message as "Oracle not available". 

2. Dynamic Registration :- In Dynamic registration , registration is performed by PMON process. Once a Database instance starts, its PMON process registers instance details with associated listener. Dynamic registration does not require any manual configuration in the listener.ora file where as Static configuration does.From Oracle 8i dynamic configuration introduced. 

PFB listener.ora file having static configuration :-

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = AMIT)
      (ORACLE_HOME = /oracle_home/oracle/ora11g/product)
    )
    (SID_DESC =
      (SID_NAME = TEST)
      (ORACLE_HOME = /oracle_home/oracle/ora11g/product)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.1.10)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /oracle_home/oracle

PFB listener.ora file having dynamic configuration :-

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.1.10)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /oracle_home/oracle
As we can see dynamic configuration is much easier as compared to static configuration.

PFB listener status details when using Dynamic regitration :-

bash-3.2$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 02-DEC-2013 10:51:59

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=amit)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
Start Date                01-DEC-2013 16:19:04
Uptime                    10 days 18 hr. 32 min. 56 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /oracle_home/oracle/ora11g/product/network/admin/listener.ora
Listener Log File         /oracle_home/oracle/diag/tnslsnr/new/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amit)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "amit" has 1 instance(s).
  Instance "amit", status READY, has 1 handler(s) for this service...
Service "amitXDB" has 1 instance(s).
  Instance "amit", status READY, has 1 handler(s) for this service...
The command completed successfully

Above status as "READY" resembles that listener has checked instance details and found its status as up or running to handle client request.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=amit)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
Start Date                01-DEC-2013 16:42:04
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /oracle_home/oracle/ora11g/product/network/admin/listener.ora
Listener Log File         /oracle_home/oracle/diag/tnslsnr/new/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amit)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

Above status resembles that either Instance is not up, or its not been registered with listener.

PFB commands to register database instance with listerner if its not automatically registering with listener :-

bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 2 11:43:39 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system register;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
bash-3.2$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 02-DEC-2013 11:43:51

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=amit)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
Start Date                01-DEC-2013 16:44:04
Uptime                    0 days 0 hr. 1 min. 47 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /oracle_home/oracle/ora11g/product/network/admin/listener.ora
Listener Log File         /oracle_home/oracle/diag/tnslsnr/new/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amit)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "amit" has 1 instance(s).
  Instance "amit", status READY, has 1 handler(s) for this service...
Service "amitXDB" has 1 instance(s).
  Instance "amit", status READY, has 1 handler(s) for this service...
The command completed successfully

PFB listener status details when using static regitration also :-

bash-3.2$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production on 02-DEC-2013 11:50:28

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= amit)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
Start Date                01-DEC-2013 18:10:27
Uptime                    0 days 02 hr. 40 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /oracle/oracle/ora11g/product/network/admin/listener.ora
Listener Log File         /oracle/oracle/diag/tnslsnr/pacs3/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amit)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "amit" has 2 instance(s).
  Instance "amit", status UNKNOWN, has 1 handler(s) for this service...
  Instance "amit", status READY, has 1 handler(s) for this service...
Service "amitXDB" has 1 instance(s).
  Instance "amit", status READY, has 1 handler(s) for this service...
The command completed successfully

Above status as "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.

I hope this article helped you.

Regards,
Amit Rath

11 comments: