oracle 连接数过多导致系统非常慢分析总结

1 问题描述客户数据库外面客户端无法连接，这时系统非常慢，连操作系统命令ls显示也出不来,然后通过杀掉进程，重启中间件应用服务器，重启数据库后，数据库恢复正常。现把本次数据库故障分析过程总结如下。2 数据库环境aix+oracle92083 故障分析当时通过同事查连接数，LOCAL=NO的进程连接数已经达到990,而正常时只有100-150左右，查询alert.log时发现1.19号有以下报错Tue Jan 19 10:50:49 2010skgpspawn failed:category = 27142, depinfo = 17, p = fork, loc = skgpspawn5skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn3Tue Jan 19 10:52:49 2010skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn5skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn3skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn3skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn3skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn3skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn3Tue Jan 19 10:57:04 2010skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn5skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn3skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn3Tue Jan 19 11:12:04 2010skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn3skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn3skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn3skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn3skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn3skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn3通过查询文档，表示进程不能启动或者创建depinfo = 11,： is the o/s errno [EACCES] error may indicate the requested file is not available which may be an effect that the process did not start and hence its proc entries were not created.查询listener.log时从2010-1-19下午14:43时开始报TNS-12540错误,超出内存限制，外面客户端无法再连接进来。19-JAN-2010 14:43:36 * (CONNECT_DATA=(SID=ora9i)(CID=(PROGRAM=oracle)(HOST=test)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=48365)) * establish * ora9i * 12500TNS-12500: TNS:listener failed to start a dedicated server process TNS-12540: TNS:internal limit restriction exceeded TNS-12560: TNS:protocol adapter error TNS-00510: Internal limit restriction exceeded 通过检查listener.log时发现2010-1-19号0点到14点43分之间，个别时间段192.168.2.1这个ip地址每隔几秒就会建立一个连接，总共有300多个连接数，而正常时192.168.2.1这个ip只有1-2个连接数，可以看出这次连接数增多是由这个ip不断连接引起的。19-JAN-2010 10:32:38 * (CONNECT_DATA=(SID=ora9i)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.1)(PORT=1074)) * establish * ora9i* 019-JAN-2010 10:32:38 * (CONNECT_DATA=(SID=ora9i)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.1)(PORT=1076)) * establish * ora9i* 019-JAN-2010 10:32:39 * (CONNECT_DATA=(SID=ora9i)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.1)(PORT=1077)) * establish * ora9i* 019-JAN-2010 10:33:33 * (CONNECT_DATA=(SID=ora9i)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.1)(PORT=1124)) * establish * ora9i* 019-JAN-2010 10:33:34 * (CONNECT_DATA=(SID=ora9i)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.1)(PORT=1125)) * establish * ora9i* 019-JAN-2010 10:33:42 * (CONNECT_DATA=(SID=ora9i)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.1)(PORT=1135)) * establish * ora9i* 04 总结及建议建议用户检查192.168.2.1这个中间件应用服务器，看有什么异常导致连接数突然增多，目前用户数大概100-150左右，分配给数据库使用的物理内存已经够用，如果以后业务发展连接数达到1000以上，可以分配更多的物理内存给数据库使用。