Database Listener Hangs Intermittently
I would like to share one thing here, which i resolved recently. Problem was Database Listener hangs intermittently in 10.2.0.1 instance. It might happen in the 10g versions(10.1.0.3, 10.1.0.4, 10.1.0.4.2, 10.1.0.5, 10.2.0.1 and 10.2.0.2). This is mostly specific to Unix/Linux platforms.
The issue was
—> Users were not able to connect to database
—> There was high CPU Usage for Listener process
—> Found that, there were 2 listener processes running
We can check the listener processes by issuing the following command.
$ ps -ef | grep lsnr
oracle 3184 1 0 May 14 ? 12:28 /oradata/oracle/bin/tnslsnr LISTENER -inherit
oracle 3988 3184 0 Jun 01 ? 10:15 /oradata/oracle/bin/tnslsnr LISTENER -inherit
Second line is the child listener process (Child Listener parent process id is the process id of parentlistener).
Just kill the child process to allow new connection until problem reoccurs.
The TNS listener can hang under load if a second spawned listener process is not closed (remains persistent).
TNS listener can hang at any time and it can happen in Standalone or RAC systems.
Here is the workaround to rectify this problem
As a Tempoary workaround, the following parameter can be added to listener.ora
(a) SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF
Where <listener_name> is listener name configured in listener.ora file
If the listener name is LISTENER, the parameter will be:
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
(b) Locate the ons.config file in the 10g RDBS Oracle Home and rename it
$cd $ORACLE_HOME/opmn/conf
$mv ons.config ons.config_back
(c) Bounce the Listener after the above changes
This will prevent the listener from registering against ONS (Oracle Notification Services), which is the area affected by bug:4518443, as well as disable ONS itself.
Permanent Solution:
Apply Patch 4518443 (Patch is available, can be downloaded from Metalink)
or
This issue is fixed in 10.2.0.3 and 10.2.0.4 Patchsets
As per the Atul’s suggestion, I want to discuss little bit more regarding the following questions raised.
1. Why and in what scenarios child listener process is spawned ?
Appearing listener child process is usual, but mostly it is happening so quick, that we will not be able to see it. When the Listener got connection request from client, it will fork itself and then that new process executes the Oracle binary to create the Server process. Sometimes, if there is a bad event (or might be at high load) or OS bug, this process get stuck and leads listener to be in hung state.
for more basic information regarding Fork unix processes
please check the following link
http://en.wikipedia.org/wiki/Fork_(operating_system)
2. What is meaning of setting SUBSCRIBE_FOR_NODE_DOWN_EVENT_=OFF
It disables the Registering the Database Listener with ONS services.
Unfortunately there is no Oracle document which describes, what internally happens when you set or unset this undocumented parameter
3. Is there any use as of 10g for database to register with ONS ?
Since I am not much more experienced with Advanced features in Oracle RAC Administration, as per my understanding, Listener registration with ONS is useful in RAC systems only, where you will configure advanced features like Fast Application Nofitification, Fast Application Fail over and Fast Connection Failover which inturn uses Oracle Notification Services (ONS).
Fast Connection Failover (FCF) is a integration between Oracle Application Server middle tiers and RAC databases. Fast Connection Failover uses Oracle Notification Services(ONS) for communication between the two tiers.
For more regarding Fast Conncetion Failover and Oracle Notification Services.
Please check the following clusterware and RAC Administration guide
http://download-west.oracle.com/docs/cd/B19306_01/rac.102/b14197/votocr.htm
Reference:
Note:340091.1 Intermittent TNS Listener Hang, New Child Listener Process Forked
Note:52808.1 Listener Hanging Intermittently
Note:284602.1 10g Listener: High CPU Utilization – Listener May Hang