ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 解决Oracle 10g listener超时问题

解决Oracle 10g listener超时问题

原创 Linux操作系统 作者:mahanso 时间:2011-01-30 10:57:57 0 删除 编辑
2011年1月24日,有一个生产数据库,客户端连接服务器出现连接超时情况,到了现场,首先描述一下现场情况:

一、问题描述:

  1.1、Listener 超时

二、诊断情况
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超时现象。

listener.JPG

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12272958/viewspace-686518/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-10-18

  • 博文量
    160
  • 访问量
    604780