Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1010977
  • 博文数量: 584
  • 博客积分: 2293
  • 博客等级: 大尉
  • 技术积分: 3045
  • 用 户 组: 普通用户
  • 注册时间: 2006-03-28 11:15
文章分类

全部博文(584)

文章存档

2012年(532)

2011年(47)

2009年(5)

我的朋友

分类: Oracle

2012-01-03 21:29:34

http://space.itpub.net/12272958/viewspace-686518

2011年1月24日,有一个生产数据库,客户端连接服务器出现连接超时情况,到了现场,首先描述一下现场情况:

一、问题描述:

  1.1、

二、诊断情况
2.1判断出现位置(服务器端还是客户端)
    让现场工程师更换多个客户端,tnsping数据库服务器,返回的结果都是timed out

2.2查看listener.log


WARNING: Subscription for node down event still pending

24-JAN-2011 21:36:02 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=p550db_01)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0

24-JAN-2011 21:36:05 * service_register * mahanso * 0

24-JAN-2011 21:36:10 * (CONNECT_DATA=(SERVICE_NAME=mahanso)(CID=(PROGRAM=d:\Program Files\TE52\bin\zxserver.exe)(HOST=PTSS2-85)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.31.240.85)(PORT=1540)) * establish * mahanso * 0

24-JAN-2011 21:36:16 * (CONNECT_DATA=(SERVICE_NAME=mahanso)(CID=(PROGRAM=d:\Program Files\TE52\bin\zxserver.exe)(HOST=PTSS2-85)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.31.240.85)(PORT=1539)) * establish * mahanso * 0

24-JAN-2011 21:36:17 * service_update * mahanso * 0

24-JAN-2011 21:36:23 * (CONNECT_DATA=(SERVICE_NAME=mahanso)(CID=(PROGRAM=d:\Program Files\TE52\bin\zxserver.exe)(HOST=PTSS2-85)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.31.240.85)(PORT=1542)) * establish * mahanso * 0

24-JAN-2011 21:36:23 * service_update * mahanso * 0

24-JAN-2011 21:36:26 * service_update * mahanso * 0

24-JAN-2011 21:36:29 * (CONNECT_DATA=(SERVICE_NAME=mahanso)(CID=(PROGRAM=d:\Program Files\TE52\bin\zxserver.exe)(HOST=PTSS2-85)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.31.240.85)(PORT=1541)) * establish * mahanso * 0

24-JAN-2011 21:36:31 * (CONNECT_DATA=(SERVICE_NAME=mahanso)(CID=(PROGRAM=d:\Program Files\TE52\bin\zxserver.exe)(HOST=PTSS2-85)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.31.240.85)(PORT=1545)) * establish * mahanso * 12518

TNS-12518: TNS:listener could not hand off client connection

 TNS-12547: TNS:lost contact

  TNS-12560: TNS:protocol adapter error

   TNS-00517: Lost contact

    IBM/AIX RISC System/6000 Error: 32: Broken pipe

24-JAN-2011 21:36:31 * (CONNECT_DATA=(SERVICE_NAME=mahanso)(CID=(PROGRAM=d:\Program Files\TE52\bin\zxserver.exe)(HOST=PTSS2-85)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.31.240.85)(PORT=1544)) * establish * mahanso * 12518

TNS-12518: TNS:listener could not hand off client connection

 TNS-12547: TNS:lost contact

  TNS-12560: TNS:protocol adapter error

   TNS-00517: Lost contact

    IBM/AIX RISC System/6000 Error: 32: Broken pipe

24-JAN-2011 21:36:31 * (CONNECT_DATA=(SERVICE_NAME=mahanso)(CID=(PROGRAM=d:\Program Files\TE52\bin\zxserver.exe)(HOST=PTSS2-85)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.31.240.85)(PORT=1549)) * establish * mahanso * 12518

TNS-12518: TNS:listener could not hand off client connection

 TNS-12547: TNS:lost contact

  TNS-12560: TNS:protocol adapter error

   TNS-00517: Lost contact

    IBM/AIX RISC System/6000 Error: 32: Broken pipe

24-JAN-2011 21:36:31 * (CONNECT_DATA=(SERVICE_NAME=mahanso)(CID=(PROGRAM=d:\Program Files\TE52\bin\zxserver.exe)(HOST=PTSS2-85)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.31.240.85)(PORT=1548)) * establish * mahanso * 12518

TNS-12518: TNS:listener could not hand off client connection

 TNS-12547: TNS:lost contact

  TNS-12560: TNS:protocol adapter error

   TNS-00517: Lost contact

    IBM/AIX RISC System/6000 Error: 32: Broken pipe

24-JAN-2011 21:36:31 * (CONNECT_DATA=(SERVICE_NAME=mahanso)(CID=(PROGRAM=d:\Program Files\TE52\bin\zxserver.exe)(HOST=PTSS2-85)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.31.240.85)(PORT=1547)) * establish * mahanso * 12518

TNS-12518: TNS:listener could not hand off client connection

 TNS-12547: TNS:lost contact

  TNS-12560: TNS:protocol adapter error

   TNS-00517: Lost contact

    IBM/AIX RISC System/6000 Error: 32: Broken pipe

24-JAN-2011 21:36:31 * (CONNECT_DATA=(SERVICE_NAME=mahanso)(CID=(PROGRAM=d:\Program Files\TE52\bin\zxserver.exe)(HOST=PTSS2-85)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.31.240.85)(PORT=1546)) * establish * mahanso * 12518

TNS-12518: TNS:listener could not hand off client connection

 TNS-12547: TNS:lost contact

  TNS-12560: TNS:protocol adapter error

   TNS-00517: Lost contact

    IBM/AIX RISC System/6000 Error: 32: Broken pipe

24-JAN-2011 21:36:31 * (CONNECT_DATA=(SERVICE_NAME=mahanso)(CID=(PROGRAM=d:\Program Files\TE52\bin\zxserver.exe)(HOST=PTSS2-85)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.31.240.85)(PORT=1543)) * establish * mahanso * 12518

TNS-12518: TNS:listener could not hand off client connection

 TNS-12547: TNS:lost contact

  TNS-12560: TNS:protocol adapter error

   TNS-00517: Lost contact

    IBM/AIX RISC System/6000 Error: 32: Broken pipe

24-JAN-2011 21:36:31 * service_update * mahanso * 0

24-JAN-2011 21:36:31 * (CONNECT_DATA=(SERVICE_NAME=mahanso)(CID=(PROGRAM=d:\Program Files\TE52\bin\zxserver.exe)(HOST=PTSS2-85)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.31.240.85)(PORT=1550)) * establish * mahanso * 12518

TNS-12518: TNS:listener could not hand off client connection

 TNS-12547: TNS:lost contact

  TNS-12560: TNS:protocol adapter error

   TNS-00517: Lost contact

    IBM/AIX RISC System/6000 Error: 32: Broken pipe

24-JAN-2011 21:36:32 * service_update * mahanso * 0

WARNING: Subscription for node down event still pending

2.3查看sqlnet.log
Fatal NI connect error 12170.

  VERSION INFORMATION:
TNS for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production
  Time: 22-JAN-2011 06:10:00
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
   TNS-12535: TNS:operation timed out
    ns secondary err code: 12606
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.1)(PORT=3777))

2.4查看alert.log
WARNING: inbound connection timed out (ORA-3136)
Sat Jan 22 01:15:30 2011
WARNING: inbound connection timed out (ORA-3136)
Sat Jan 22 01:15:31 2011
WARNING: inbound connection timed out (ORA-3136)
Sat Jan 22 01:15:42 2011
WARNING: inbound connection timed out (ORA-3136)
Sat Jan 22 01:15:42 2011

大量出现warning :ORA-3136

三、处理方案
1、到现场查看服务器具体环境(服务器配置情况,数据库版本等数据库相关信息)
2、备份数据库
3、解决:ORA-3136警告(2011年1月24日晚)
    3.1、set INBOUND_CONNECT_TIMEOUT_=0 in listener.ora
例如:INBOUND_CONNECT_TIMEOUT_LISTENER=0
     3.2、set SQLNET.INBOUND_CONNECT_TIMEOUT = 0 in sqlnet.ora of server.
     3.3、Stop and start oracle server
4、解决:WARNING: Subscription for node down event still pending
set SUBSCRIBE_FOR_NODE_DOWN_EVENT_=OFF 
in listener.ora

后续跟进观察说明:

A、2011年1月24日晚上,ps -ef | grep lsnr,出现2个监听,其中一个ppid=1,另一个值很大。我kill掉了ppid=1,重启监听。并且执行“处理方案中第三步”

B、2011年1月26日中午,有出现超时现象。我执行“处理方案中第四步”后续继续观察目前还没有出现listen超时现象。

解决问题资料:
1、
2、

阅读(2220) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~