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

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)

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

About the Author Atul Kumar

Leave a Comment:

22 comments

Atul says June 17, 2008

Thanks Subbu for useful post

Can you please explain little bit internals (which in my opinion can be useful to understand oracle database listener)
1. Why and in what scenarios child listener process is spawned ?
2. What is meaning of setting SUBSCRIBE_FOR_NODE_DOWN_EVENT_=OFF
3. Is there any use as of 10g for database to register with ONS ?

I know even Oracle Notes don’t cover things in such details and knowing such things will really help us in understanding basics.

SubbaRao says June 17, 2008

Thanks Atul,

I understood and will edit the post with the relevant information soon.

Regards,
Subbu

Asad says June 17, 2008

i had same experience with listener sometime ago. I noticed in my system there were a ton of spawned tcp (TIME_WAIT) connections.

My system, however, was non-rac, 10.2.0.3 so such problem may occur even in 10.2.

I had installed 10g grid control/ (app server),and database on same box.

my solution was to rename ons.config file and bounce the listener.

nitmal_gm says June 17, 2008

Hi Subbu,

When I was installed Oracle OEM Grid control, I faced similar issue, which is 4 listener process was running. Then I renamed the ons.config file and bounced the listener.

Now I understood better.

good work.

Nirmal.

Atul says June 17, 2008

Hi Subbu, Much appreciated and its made me to think on use of ONS in database (Though in 10g AS ONS is used for interprocess notufication for fault tolerance and part of OPMN)
I’ll gather more information and try to cover ONS use in Database .

Good post specially Why & What questions.

SubbaRao says June 18, 2008

Thank you all.

Regards,
Subbu

Geetha M says June 27, 2008

Subbu!!

Highly appreciable for sharing your real time problems in database and providing their fixes.

Rgds
Geetha M

SubbaRao says June 28, 2008

Thanks geetha.

Regards,
Subbu

jose says August 26, 2009

Hi Subbu,
I have same problem in a 10.2.0.2 database..
I might that problem was in SAP (related to own process)..

Thanks a lot for workaround and solution!
(I find this solution before metalink)

Regards,
jose

SubbaRao says September 1, 2009

Thank you Jose

Regards,
Subbu

yung says March 9, 2010

Thank You.

I found problem in 10.1.0.5.0.

Thanks a lot for workaround and solution!

best regard,
Yung

Ather Hussain says March 15, 2010

Dear Subbu

what to do if stop/start and listener accidently deleted in 11i or R12.

Thanks & Best regards,
Ather Hussain
atherhussain9@yahoo.com

karthick says August 19, 2010

Hi Subbu

Its worthable information
Txs alot..

Karthick
knkarthick5@gmail.com

Jayakumar says September 30, 2010

Hi,

I have installed Oracle EBS 11i at machine 1.
I migrated the database to oracle 10g(using imp/exp) which is at machine 2,now i need to reconfigure my database tire with Oracle 10g.
(Oracle Application Manager is running with oracle 9i as database tire ,i need to reconfigure the Oracle Application Manager with oracle 10g)

I have mentioned the scenario below.

Machine 1
OS : HP-ux (64 bit)
Database : Oracle 9i

Machine 2
OS : HP-ux (64 bit)
Database : Oracle 10g

Sreedhar says January 3, 2011

Great work Subbu.It’s really wnderful information.

Thanks,
Sreedhr
sreedhar.sai@gmail.com

karthic says May 11, 2011

This was an outstanding blog which u have shared… really appreciated and looking forward to much more and other issues solutions like this.

chirdeep says December 18, 2011

Hi All,

Thanks for the inputs. In one of my environments as well we are getting the Listener Spawning issues very frequently.

But unfortunately, the version of Oracle is 9.2.0.8. Hence let me know if anything can be done for the Child listener forking issues in 9I databases as well.

That would be of a great help.

Regards,
Chirdeep

guest says May 3, 2012

listener hangs or is very slow to shutdown on 11g (11.1.0.7) plus latest pacthset update.
Same issue. Using the details here fixed the problem. (oracle is just full of old bugs that never seem to get pacthed .. and we pay money for such a product)

pankaj says October 21, 2012

As per oracle document this issue comes before 10.2.0.2 but i got the same issue on release 4 either need to update patch…
Highly appreciate the solution it work….

Regards
Pankaj Sharma

Atul Kumar says October 23, 2012

Hi Pankaj,

Can you please share the OS details…

OS has a major part to play in this.. and even need information about whether database and OS is a 32bit / 64bit.

Regards,
Chirdeep

Atul Kumar says October 23, 2012

Kindly share the information of below query as well

SQL> select * from v$version;
SQL> show parameter compatible;

Regards,
Chirdeep

Atul Kumar says October 23, 2012

Cause of the ISSUE: High load on the server… This might lead to listener Spawning…
We can confirm this using the TOP command for high utilization PIDs…

Affected Versions: 10.2.0.1& 10.2.0.2… INfact even 9.2.0.8 is prone for these kind of issues.

Resolution: Apply the patch: 4518443 for solution. But its always better to upgrade the database to higer version…

Regards,
Chirdeep

Add Your Reply