Listener hangs in Oracle 10g and no new connections are allowed

Just when I was thinking how boring my job as DBA was (doing the same routine work and nothing new to implement until you convince everyone and take approvals from them!!) I was made to feel that DBA work is not at all boring if you are at the receiving end :-) . I was suddenly bombarded with mails from the users saying that they are not able to connect to the database. As usual I checked the listener status and replied to some of them saying that there is no problem with the listener as such but would get back to them with a solution. It was a production database, the pressure on me was mounting every second. I was not sure what to do? Reply to the users or solve the problem :-)

The database version was of Oracle 10.2.0.1 running on Linux RHEL 4..The listener seemed to be in hung state and moreover there were no errors in the listener log file.

I don’t know what came to my mind I thought of checking the listener process using the ps command. I was surprised to see a child process forked automatically with the same name of the current listener.

$ ps -ef |grep tns
oracle 2310 1 0 Jul 17 ? 72:00 /oracle/ora10g/db/bin/tnslsnr oprem -inherit
oracle 6573 2310 0 14:19:23 ? 0:00 /oracle/ora10g/db/bin/tnslsnr oprem –inherit

I killed the child process and then reloaded the listener. Phew!!...it worked.The users were able to connect to the database now.

Later I found that this is an Oracle bug (No.4518443).

Some of the possible solutions for the above problem are:

1. Kill the child process using kill command and then reload the listener process.

2. Add the following parameter in the listener.ora file and restart the listener process.

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER_NAME=OFF

Where listener_name is the listener name (here it would be oprem)

3. Apply Patch.

4. Rename the ons.config file and restart the listener. (path:$ORACLE_HOME/opmn/conf)

This problem is fixed in 10.2.0.3

Note: If you add the above parameter for a listener.ora in RAC setup, then Fast Application Notification (FAN) will not work.

For patch and more details on this refer to metalink note: 340091.1

Hope this was useful to you guys.