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.
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.
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.