1) No-one was able to connect to the database.
2) There were two listener processes running, which was apparent from a ps-ef|grep tns
oracle 2647896 1495262 1 May 18 - 0:00 /oem/oracle/product/10.2/bin/tnslsnr LISTENER -inherit oracle 1495262 1 1 May 13 - 2:32 /oem/oracle/product/10.2/bin/tnslsnr LISTENER -inherit
(Note the different start times and the later process was definitely not started manually.)
This had happened once or twice and we found that when we killed the child listener (note that one listener's parent process ID is the process id of the other) everything worked correctly.
This time I had a good look through the listener.log and noticed these warning messages related to the ONS service, even though we're not using RAC.
WARNING: Subscription for node down event still pending
It took a while to track it down, but it turns out that this is a known, but unpublished bug number 4518443 in 10.2.0.2 (and no doubt previous versions), fixed in 10.2.0.3. For those of you with Metalink accounts, the relevant note is here. You can either apply patch 4518443 or, as a temporary workaround, disable that service by adding the following to your listener.ora configuration and restarting the listener.
Maybe the following gives a little bit more insight...
Yeah I ran into the same thing a month or two ago... even did a writeup of it.
We were also using 10.2.0.2; really caused us some problems at first until we figured out what it was!
I encountered the same issue in December 2006. I don't believe there was a patch available at that time so I had to implement the workaround. Details can be found on my blog.
Glad to hear there is a patch if you can't move to 10.2.0.3.
Once again, I forgot to mention the O/S - AIX 5.3. However, as the Metalink note says
This problem can occur on any platform.
All new connections via TNS listener hang, no errors reported
I noticed this in today's Oracle-L digest. I should point out that in our case we are not using RAC at all.
No RAC... so was something else listening on port 6101? Or was the listener configured to look for ONS on a different port, on which some different app was running? Seems like I've heard that Oracle Application Server can cause this problem; were that related to the problem?
even without RAC or iAS, if you use ASM, an ONS will be running and therefore you can face this problem.
Good point, Martin, thanks.
Because I rarely get to work with ASM at the moment, I wouldn't have thought of this.
Good Article Doug,
I ran into this problem, saw your article and helped me to resolve the issue.
Thanks for sharing the information.
Thanks much for blogging this issue and sharing the solution!
In 10.2.0.4 (EE on hp-ux, at least) it still leaves copious warning messages in non-rac listener.log.
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF (or appropriate listener name) in listener.ora fixes.
I'm not using iAS, RAC or ASM, but those messages are still annoying. It's seems the targets.xml file used by dbconsole defines which listener is used, simply using dbconsole is using enough of ONS to create these warning messages.
I ran into the same problem at yesterday
midnight on our production server and the way I fixed it was same as Doug's. I want to figure out why the second listener would come out. I search from google and get the snswer from your blog. Now I know it is not people manually started process, it is a bug in 10.2.0.2 and lower release (we are using 10.2.0.1).
Thanks for the Doug's good blog and all other people's helpful information related this issue.
Glad you got it sorted out, Pauline.
Another win for blogs
Thanks a Lot for this solutions ... It´s work very good ...
Hi This is chirdeep from India, blog is really very much helpful. We actually have a same scenario but our database version is of 18.104.22.168. Hence if some one could help it would be very much helpful.