【listener hangs】监听hangs，导致新的连接无法连接数据库

查阅MOS发现如下BUG：

Oracle Net Services - Version 10.1.0.3.0 to 10.2.0.2.0 [Release 10.1 to 10.2]

Information in this document applies to any platform.
All new connections via TNS listener hang, no errors reported
在10.1到10.2的任何平台的数据库版本中，新的TNS方式连接无法连接到数据库，而且没有任何报错

DESCRIPTION

Intermittently the TNS listener hangs and new connections to the database are not possible. 

监听间接性的hangs导致无法连接到数据库

OCCURRENCE

The issue is that the TNS listener can hang under load if a second spawned listener process is not closed (remains persistent). Secondary listener processes are not unusual, depending on traffic as well as when the OS grep snapshot is taken. However, a persistent secondary process (longer than say 5 second) is not normal and may be a result of this referenced problem.

TNS listener can hang at any time and effect standalone or RAC systems

其原因是监听上的数据量过大导致监听进程产生一个从属的监听进程，从属监听进程的存在导致了监听的Hangs。单实例和RAC上都可能产生这个问题。

SYMPTOMS

Listener process can also consume high amount of CPU 
Child TNS listener process is seen when doing a ps on the listener process, eg.:

$ ps -ef | grep tnslsnr  

ora10g 8909 1 0 Sep 15 ? 902:44 /u05/10GHOME/DBHOME/bin/tnslsnr sales -inherit  
ora10g 22685 8909 0 14:19:23 ? 0:00 /u05/10GHOME/DBHOME/bin/tnslsnr sales -inherit 

Killing the child process allows new connections to work until the problem reoccurs 
监听进程也可能会大量消耗CPU资源，可以通过grep命令来查看从属监听进程，然后将其kill。

WORKAROUND

Issue is fixed in 10.2.0.3 Patch Set
Oracle Support recommends patching to 10.2.0.5 as this the lastest release
- OR - 
Apply Patch 4518443 for the problem (if a patch is available) 

这是oracle的一个BUG，需要打补丁4518443
- OR - 
As a workaround, two steps should be done:
1. The following parameter can be added to listener.ora

SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF

Where <listener_name> should be replaced with the actual listener name configured in the LISTENER.ORA file. This parameter is to be placed by itself on an empty line / at the end of file. 

For example, if the listener name is LISTENER (default), the parameter would be:

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

2. Locate the ons.config file in the 10g(rdbms) home and rename it to something else. 
For example: 
cd $ORACLE_HOME/opmn/conf 
mv ons.config ons.config.orig 
The listener needs to be restarted after these changes.

This will both prevent the listener from registering against ONS (Oracle Notification Services), which is the area affected by bug:4518443, as well as disable ONS itself. For more information on ONS, please refer to the specific Oracle documentation, for example, for 10.2, see the Oracle10g Release 2 Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide. 

Please note, that adding the SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name> to listener.ora file on RAC and disabling the ONS file, will mean that FAN (fast application notification) will not be possible. See Note 220970.1RAC: Frequently Asked Questions for further information on FAN. Therefore, if you have a RAC configuration, then apply the patch and do not disable ONS or FAN. 

Also, please note that this might happen with ANY 10g installation, whether it is RAC related or not, and whether there is an Oracle Application installation or not. 
如果在生产环境中，可以通过在listener.ora文件中添加参数SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF来解决这个问题。但这个参数会在RAC环境中造成一些副作用，所以只建议在单机环境中使用

ballontt
2013/11/07

---The End---

微博：weibo.com/ballontt

如需转载，请标明出处和链接，谢谢！