二、诊断情况
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、到现场查看服务器具体环境(服务器配置情况,数据库版本等数据库相关信息)
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超时现象。