Wednesday, October 5, 2011

How to Register Listener in the Database ?

The  listener is a separate process that runs on the database server computer. It  receives incoming client connection requests and manages the traffic of these requests to the database server. There are two methods by which a listener comes to know of a database instance. In Oracle terminology, this is referred  as “Registering with the Listener” .  The two methods are 

1.) Static Instance Registration
2.) Dynamic Instance Registration

First we will discuss about the Static Instance Listener  :
This is the very basic method to register listener .We can either add the entries in $ORACLE_HOME\NETWORK\ADMIN\listener.ora file or by using the GUI i.e, through Net Manager. The configuration inside the listener.ora file looks like : 

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = noida)
      (ORACLE_HOME = C:\app\neerajs\product\11.2.0\dbhome_1)
      (SID_NAME = noida)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = hyd)
      (ORACLE_HOME = C:\app\neerajs\product\11.2.0\dbhome_1)
      (SID_NAME = hyd)
    )
  )
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tech-199)(PORT = 1521))
  )

and when we check the registration , it shows the status of UNKNOWN :

C:\>lsnrctl
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-OCT-2011 15:26:27
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tech-199)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                28-SEP-2011 15:03:39
Uptime                    7 days 0 hr. 22 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\neerajs\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         c:\app\neerajs\diag\tnslsnr\tech-199\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tech-199)(PORT=1521)))
Services Summary...
Service "hyd" has 1 instance(s).
  Instance "hyd", status UNKNOWN, has 1 handler(s) for this service...
Service "noida" has 1 instance(s).
  Instance "noida", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

The status is unknown because there is no mechanism to guarantee that the specified status even exists.Here the listener assumes that instance will be there whenever there will be any request. It donot have inforamtion about the status of the Current Instance. 

Now, we will check the Dynamic Instance Listener :

Dynamic Instance Registration :  This dynamic registration feature is called service registration. The registration is performed by the PMON process  an instance background process   of each database instance that has the necessary configuration in the database initialization parameter file. Dynamic service registration does not require any configuration in the listener.ora file.

Service registration offers the following benefits :

1.) Simplified configuration  :  Service registration reduces the need for the SID_LIST_listener_name parameter setting, which specifies information about the databases served by the listener, in the listener.ora file.

Note :  The SID_LIST_listener_name parameter is still required if we are using Oracle Enterprise Manager to manage the database.

2.) Connect-time failover  : Because the listener always knows the state of the instances, service registration facilitates automatic failover of the client connect request to a different instance if one instance is down.
In a static configuration model, a listener would start a dedicated server upon receiving a client request. The server would later find out that the instance is not up, causing an "Oracle not available" error message.

3.) Connection load balancing : Service registration enables the listener to forward client connect requests to the least loaded instance and dispatcher or dedicated server. Service registration balances the load across the service handlers and nodes.  To ensure service registration works properly, the initialization parameter file should contain the following parameters:

SERVICE_NAMES for the database service name
INSTANCE_NAME for the instance name
For example:
SERVICE_NAMES=noida.TECH-199
INSTANCE_NAME=noida

Let's have a Demo of Dynamic Listener.

The listener is quite capable of running without a listner.ora file at all. It will simply start and run with all default values.Here i have rename the listener.ora file and stop and start the listener and find that listener supports no services.Check the below: 

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tech-199)(PORT=1521)))
The command completed successfully.

Now start the listener

LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Log messages written to c:\app\neerajs\diag\tnslsnr\tech-199\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tech-199)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tech-199)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                05-OCT-2011 16:21:30
Uptime                    0 days 0 hr. 0 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         c:\app\neerajs\diag\tnslsnr\tech-199\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tech-199)(PORT=1521)))
The listener supports no services
The command completed successfully

Here, we find that listener donot support any services.Since it doesnot found the listener.ora file ,and  if we try to connect to the Instance then it will  throws the error i.e, ORA-12514 :

C:\> tnsping noida
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-OCT-2011 16:23:03
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
C:\app\neerajs\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.100.0.112)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = noida)))
OK (40 msec)

Now, we try to connect with Instance "NOIDA"

C:\> sqlplus sys/xxxx@noida as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 5 16:23:45 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Since the tnsping proves that our tnsnames.ora resolution is correct, but it throws the error while connecting to database because the listener doesnot knows anything about the services "NOIDA" . Let's start the instance and check again : 

C:\> set ORACLE_SID=noida
SQL> startup 
ORACLE instance started.

Total System Global Area  263639040 bytes
Fixed Size                  1373964 bytes
Variable Size             222300404 bytes
Database Buffers           33554432 bytes
Redo Buffers                6410240 bytes
Database mounted.
Database opened.

Now check the listener status again :

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tech-199)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                05-OCT-2011 16:21:30
Uptime                    0 days 0 hr. 19 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         c:\app\neerajs\diag\tnslsnr\tech-199\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tech-199)(PORT=1521)))
Services Summary...
Service "noida.TECH-199" has 1 instance(s).
  Instance "noida", status READY, has 1 handler(s) for this service...
Service "noidaXDB.TECH-199" has 1 instance(s).
  Instance "noida", status READY, has 1 handler(s) for this service...
Service "noida_DGB.TECH-199" has 1 instance(s).
  Instance "noida", status READY, has 1 handler(s) for this service...
The command completed successfully

Here we observe that once the instance is started , when we re-check the listener now knows of service “NOIDA”, with a status of READY . This obviously did not come from listener.ora as the file is renamed. Notice also that, unlike the static registration, this time the status is READY. The listener knows the instance is ready because the instance itself told the listener it was ready. 

Now agian connecting to the Instance :

C:\>sqlplus sys/xxxx@noida as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 4 18:14:28 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Here by default, the PMON process registers service information with its local listener on the default local address of TCP/IP, port 1521. As long as the listener configuration is synchronized with the database configuration, PMON can register service information with a nondefault local listener or a remote listener on another node. During service registration PMON provides listener with the following information: 

- Name of the associated instance
- Current load and maximum load on instance
- Names of DB services provided by database.
- Information about dedicated servers and dispatchers (depends on database server mode i.e dedicated/shared server mode) .

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: 
SQL> ALTER SYSTEM REGISTER; 

Reference ::  Click Here


Enjoy      :-) 


29 comments:

djay said...

Thanks for so detailed information.

NEERAJ VISHEN said...

you are welcome djay ...

Enjoy and have fun :-)

Akanksha Kaul said...

Hi Neeraj,

The entry in my listener.ora is :

# listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)


)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 01hw054895)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

While in the listener status, the o/p is:

LSNRCTL> stat
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
Start Date 09-AUG-2012 19:01:42
Uptime 0 days 0 hr. 0 min. 57 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oracle\product\10.2.0\db_1\network\admin\listener.o
ra
Listener Log File D:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=01hw054895.corp.tatacapital.com)(POR
T=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "TESTANIXDB" has 1 instance(s).
Instance "testani", status READY, has 1 handler(s) for this service...
Service "TESTANI_XPT" has 1 instance(s).
Instance "testani", status READY, has 1 handler(s) for this service...
Service "testani" has 1 instance(s).
Instance "testani", status READY, has 1 handler(s) for this service...
The command completed successfully


Plz sugegst how is my instance dynamically registered when there is no entry in listener.ora

NEERAJ VISHEN said...

Hi Akanksha ,

By default, the PMON process registers service information with its local listener on the default local address of TCP/IP, port 1521. As long as the listener configuration is synchronized with the database configuration, PMON can register service information with a nondefault local listener or a remote listener on another node. During service registration PMON provides listener with the following information:

- Name of the associated instance
- Current load and maximum load on instance
- Names of DB services provided by database.
- Information about dedicated servers and dispatchers (depends on database server mode i.e dedicated/shared server mode) .

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.

Hope this help you :-)

--Neeraj

Akanksha Kaul said...

Thanks for the reply Neeraj :)

This following statement has really cleared my doubt :)

"PMON process wakes up at every 60 seconds and provide information to the listener"

NEERAJ VISHEN said...

you are welocome ....

Enjoy :-)

Anonymous said...

This saved my bacon big time! Many thanks.

Ram Kumar said...

Hi Neeraj,

I have three database instances running on my system.I just want that all the 3 listeners should automatically start when I start my databases. is there any configuration by which it can happen
i don not want to start the listeners using lsnrctl every time..

NEERAJ VISHEN said...

Hi Ram ,

You can write a script for this . Here are few link which will help you in writing ....

http://neeraj-dba.blogspot.in/2012/03/automatically-startingstopping-oracle.html

http://www.dbforums.com/oracle/1068135-automatic-start-database-listener-boot.html

http://www.oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux.php



Let me know if you have any issue .


Good Luck .



Ravi Garlapati said...

I struck with the same issue, service UNKNOWN (ORA-12514: TNS:listener does not currently know of service requested in connect
), was following your steps to resolve this. but couldn't understand how you jumped from c: prompt to sql prompt ?


C:\> set ORACLE_SID=noida
SQL> startup

I used the command "SQLPLUS /NOLOG" to change c: prompt to sql prompt, then when i say startup, it gives me error:
SQL> startup
ORA-01031: insufficient privileges

Please clarify. reply to rkrgarlapati@gmail.com

Ravi Garlapati said...

I struck with the same issue, service UNKNOWN (ORA-12514: TNS:listener does not currently know of service requested in connect
), was following your steps to resolve this. but couldn't understand how you jumped from c: prompt to sql prompt ?


C:\> set ORACLE_SID=noida
SQL> startup

I used the command "SQLPLUS /NOLOG" to change c: prompt to sql prompt, then when i say startup, it gives me error:
SQL> startup
ORA-01031: insufficient privileges

Please clarify. reply to rkrgarlapati@gmail.com

Anonymous said...

This is because the company' wants to give tough competition to other cheap Android phones.
The best tip for improving your golf swing is to check that your body mechanics are correct;
your position and posture must be properly
aligned with the target line. Nowadays soft phone providers ensure that
you don't have to shed lots of money on buying
costly hardware or PBX system.

Feel free to visit my web site dirt cheap iphone accessories

Priyanka Joshi said...

Hi Neeraj!

My EM was working fine till yesterday since the day of installation of oracle 11g (2 yrs.ago). I dont know what happen suddenly and i am not able to access EM from browser. Pls suggest what do I need to check.

Thanks.
Priya

Priyanka Joshi said...

Hi Neeraj!

My EM was working fine till yesterday since the day of installation of oracle 11g (2 yrs.ago). I dont know what happen suddenly and i am not able to access EM from browser. Pls suggest what do I need to check.

Thanks.
Priya

NEERAJ VISHEN said...

Hi Priya,

Information provided by you are not sufficient . what the error are you getting, OS and other info . EM may not work properly for various reason . Here are few of them .

1.) EM will not work if you have changed the hostname or Alias . So check your /etc/hosts file and make sure hostname and Alias should be same as that of before .

2.) Make sure that you are using correct environment file or environment variable and db, listener should be up and working fine .

3.) Check emca logfile and If above doesnot work then reconfigure EM . Below link may help you to configure EM .

http://neeraj-dba.blogspot.in/2012/01/how-to-reconfigure-oem-in-oracle-10g-on.html
http://neeraj-dba.blogspot.in/2012/06/configure-oem-in-oracle-11gr2-on-window.html

let me know if face an issue or you can mail me on neeraj.vishen@gmail.com

mohammed irfan said...

Hi I have created one new service now i want to register it into database ,I tried the command alter system register but it is still not showing the service name in dba_Service..

mohammed irfan said...

Hi,
I have created one new service now I want to register it into database,I tried alter system register command but it is still not showing the service name in dba_services.

Sruthi veni said...

After coming this blog, I have learned so many things. Thank you friend keep posting like this.
Oracle DBA Online Training

Anonymous said...

It's hard to come by educated people about this subject, however,
you sound like you know what you're talking about!
Thanks

Feel free to surf to my page how to build muscle fast

babu buden Gm said...

Hi Neeraj,

Hi All,

We had running( it was working fine) oracle 11g. For some reason i had stopped services of database (OracleServiceORCL,OracleOraDb11g_home1TNSListener ). Started again the DB services But listener not working.
Not able to login into Database
c:\>tnsping orcl showed no listener
ORA-12541: TNS:no listener

and example Security kernal log failed to connect database.
Error:OCI0000374 - Failed to connect to the database instance
OCI0000375 - Error - ORA-12541: TNS:no listener
OCI0000064 - Unable to create database server connection
OCI0000367 - Unable to connect to Oracle ORA-12541: TNS:no listener

Tried below things still Listener not working.

1.Edited sqlnet file
tcp.validnode_checking = yes
tcp.invited_nodes = (localhost, depser,webser)

2. starting Listener from cmd
from location D:\oracle\product\11.2.0\dbhome_1\BIN

3. executed command "services listener" has following output. looks listener output not showing the database service name.

LSNRCTL> services listener
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC15 21)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully

Please suggest if you came across this type Listener problem.

Thank you in advance.

Regards,
babu

babu buden Gm said...

Hi All,

We had running( it was working fine) oracle 11g. It was installed on enterprise server. For some reason i had stopped services of database (OracleServiceORCL,OracleOraDb11g_home1TNSListener ) and Started again the DB services But listener not working.
Not able to login into Database
c:\>tnsping orcl showed no listener
ORA-12541: TNS:no listener

and example Security kernal log failed to connect database.
Error:OCI0000374 - Failed to connect to the database instance
OCI0000375 - Error - ORA-12541: TNS:no listener
OCI0000064 - Unable to create database server connection
OCI0000367 - Unable to connect to Oracle ORA-12541: TNS:no listener

Tried below things still Listener not working.

1.Edited sqlnet file
tcp.validnode_checking = yes
tcp.invited_nodes = (localhost, depser,webser)

2. starting Listener from cmd
from location D:\oracle\product\11.2.0\dbhome_1\BIN

3. executed command "services listener" has following output. looks listener output not showing the database service name.

LSNRCTL> services listener
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC15 21)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully

Please suggest if anyone came across this type Listener problem.

Thank you in advance.

Regards,
babu

Anonymous said...

You, sir, saved my life. Thank you so much!

Anonymous said...

I have the similar issue in my newly installed oracle 11gr2.0.4 2 node rac on RHEL 6.7.

[grid@myhost-db300 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-JUN-2016 23:22:56

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 28-JUN-2016 12:51:12
Uptime 1 days 10 hr. 31 min. 44 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/myhost-db300/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
The listener supports no services
The command completed successfully


When I login to the database and issue the same command I get the following error

[oracle@myhost-db300 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-JUN-2016 23:32:50

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 2: No such file or directory

I have tried to register the service manually by issuing "alter system register" but no luck. This is only happening to the first node. The second node is fine as shown;

[oracle@myhost-db301 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-JUN-2016 23:34:34

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 28-JUN-2016 12:51:12
Uptime 1 days 10 hr. 43 min. 22 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/myhost-db301/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.162.20.25)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.162.20.33)(PORT=1521)))
Services Summary...
Service "ORADEV" has 1 instance(s).
Instance "EDWADEV2", status READY, has 1 handler(s) for this service...
Service "ORADEVXDB" has 1 instance(s).
Instance "ORADEV2", status READY, has 1 handler(s) for this service...
The command completed successfully

Any help will be appreciated.

Thanks

vijender reddy said...

Hi Neeraj,

Nice explanation, please invite me as part your discussions.

Thanks,
Vijender
Vijender60@gmail.com

Ankita Tiwari said...

Sagacity Software is the best software company in india for Database Software Development, Database Services, Database Migration.

Blogger said...

Bluehost is the best hosting company for any hosting services you might require.

Anonymous said...

Nice Blog and Thanks for sharing.

I found lot of good info in this link. http://bit.ly/Si0i5v

kenneth ndegwa said...

THANK YOU NEERAJ,
This is a very nice blog. pointed me in the right direction

Karthika Shree said...


Somebody necessarily help to make severely posts I might state. This is the first time I frequented your website page and to this point? I surprised with the research you made to create this particular post extraordinary. Well done admin..
Oracle Training in Chennai