Oracle监听不定期出现异常故障处理

操作系统为：SunOS 5.10数据库版本：Oracle RAC 11.2.0.3.0该主机上有2个库，一套RAC的节点1在上面，还有另外一个库，之所以使用

环境：
操作系统为：SunOS 5.10
数据库版本：Oracle RAC 11.2.0.3.0
该主机上有2个库，一套RAC的节点1在上面，还有另外一个库，之所以使用的是DB下的监听而没用GRID下的监听，是为了避免停掉CRS时影响另外一个库的使用。

故障现象：
数据库监听不定期出现异常 ,从应用tnsping数据库，时间花费很长甚至连不上

故障分析处理过程：

从应用主机tnsping如下：

racdb1_scenemon$tnsping racdb_new
 
TNS Ping Utility for Solaris: Version 11.2.0.3.0 - Production on 19-2月 -2014 15:46:23
 
Copyright (c) 1997, 2011, Oracle. All rights reserved.
 
已使用的参数文件:

已使用 TNSNAMES 适配器来解析别名
 尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 11.111.11.1)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 11.111.11.2)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))))
 
OK (60000 毫秒)

Oracle监听之动态监听与静态监听特点

Oracle 11g RAC 环境下单实例非缺省监听及端口配置

Oracle 监听器日志配置与管理

Oracle错误- ORA-12514：TNS：无监听程序

ORA-12514 监听错误解决

Oracle监听器出现的6种连接问题及其解决方法

Oracle LISTENER 未监听到Oracle实例问题解决

从数据库服务器上查看监听状态：

$ lsnrctl status
 
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 19-FEB-2014 15:45:58
 
Copyright (c) 1991, 2011, Oracle. All rights reserved.
 
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
 

TNS-12535: TNS:operation timed out
 TNS-12560: TNS:protocol adapter error
 TNS-00505: Operation timed out   

将监听进程予以kill并重启监听：

$ ps -ef |grep tns
 oracle 18504 18468 0 15:45:15 pts/10 0:00 grep tns
 oracle 24037 1 0 Nov 28 ? 6:08 /oracle/app/asm/11.2.0/grid/bin/tnslsnr LISTENER -inherit
 oracle 9488 1 3 Jan 24 ? 4447:11 /oracle/app/db/product/11.2.0/db/bin/tnslsnr LISTENER -inherit
 $
 $ kill -9 9488
 $ lsnrctl start
 
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 19-FEB-2014 15:48:22
 
Copyright (c) 1991, 2011, Oracle. All rights reserved.
 
Starting /oracle/app/db/product/11.2.0/db/bin/tnslsnr: please wait...
 
TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
 Log messages written to /oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/racdb/listener/alert/log.xml
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racdb)(PORT=1521)))
 
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
 STATUS of the LISTENER
 ------------------------
 Alias LISTENER
 Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
 Start Date 19-FEB-2014 15:48:22
 Uptime 0 days 0 hr. 0 min. 0 sec
 Trace Level off
 Security ON: Local OS Authentication
 SNMP OFF
 Listener Log File /oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/racdb/listener/alert/log.xml
 Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racdb)(PORT=1521)))
 The listener supports no services
 The command completed successfully   

 

检查listener.log如下：

17-FEB-2014 16:02:26 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn))) * (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41603)) * establish * racdb * 0
 17-FEB-2014 16:02:26 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn))) * (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41659)) * establish * racdb * 12518
 TNS-12518: TNS:listener could not hand off client connection
 TNS-12560: TNS:protocol adapter error
 TNS-00530: Protocol adapter error
 Solaris Error: 24: Too many open files  17-FEB-2014 16:02:26 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn))) * (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41652)) * establish * racdb * 0
 17-FEB-2014 16:02:26 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn))) * (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41669)) * establish * racdb * 0
 17-FEB-2014 16:02:26 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn))) * (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41682)) * establish * racdb * 12518
 TNS-12518: TNS:listener could not hand off client connection
 TNS-12560: TNS:protocol adapter error
 TNS-00530: Protocol adapter error
 Solaris Error: 24: Too many open files

Too many open files意味着Maximum Number Of Open Files Per Process 达到了上限。因此listener hang住的原因可能是该limit设置过小，进一步查看硬件限制如下：