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

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:36

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以上,可以分配更多的物理内存给数据库使用。

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】