1. 首页
  2. IT资讯

Fatal NI connect error 12170 TNS-12535 TNS-00505

                  Fatal NI connect error 12170 TNS-12535 TNS-00505

今天一位朋友遇到这个错误,每2个小时长时间运行的存储过程就断开,一开始怀疑PROFILE或者RESOURCE PLAN限制。
但是大家都明白,一般很少用这些,特别是资源计划,拿到报错后如下:
Fatal NI connect error 12170.

  VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.1.0 – Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.1.0 – Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.1.0 – Production
  Time: 27-1月 -2015 15:43:45
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: TNS: 操作超时
    ns secondary err code: 12560
    nt main err code: 505
    TNS-00505: 操作超时
    nt secondary err code: 60
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.47.98.172)(PORT=60611))
  这里的
  nt secondary err code: 60 可能随着系统的不同而不同
Linux x86 or Linux x86-64: “nt secondary err code: 110”
HP-UX : “nt secondary err code: 238”
AIX: “nt secondary err code: 78”
Solaris: “nt secondary err code: 145”

对于这样的错误在11G以前是不计入alert日志的,但是11G后 Automatic Diagnostic Repository (ADR)记录,其实可以通过如下方法进行屏蔽掉:
To revert to Oracle Net Server tracing/logging, set following parameter in the server’s sqlnet.ora :
DIAG_ADR_ENABLED = OFF
Also, to back out the ADR diag for the Listener component, set following parameter in the server’s listener.ora:
DIAG_ADR_ENABLED_ = OFF
   – Where the would be replaced with the actual name of the configured listener(s) in the listener.ora configuration file.  For example, if the listener name is ‘LISTENER’, the parameter would read:
DIAG_ADR_ENABLED_LISTENER = OFF

上述报错原因如下:
This issue can arise during a long running query or when using JDBC Thin connection pooling. 
If there is no data ‘on the wire’ for lengthy,This would indicate an issue with a firewall 
here a maximum idle time setting is in place.
The alert.log message indicates that a connection was terminated AFTER it was established to the instance.  
In this case, it was terminated 2 hours and 3 minutes after the listener handed the connection to the database. 
 也就是他出现在长期的操作或者使用连接池的时候,并且设置了防火墙,并且2小时会进行中断一次。
解决办法如下:
The non-Oracle solution would be to remove or increase the firewall setting for maximum idle time.  
In cases where this is not feasible, Oracle offers the following suggestion:
The following parameter, set at the **RDBMS_HOME/network/admin/sqlnet.ora, can resolve this kind of problem.  
DCD or SQLNET.EXPIRE_TIME can mimic data transmission between the server and the client during long periods of idle time. 
SQLNET.EXPIRE_TIME=n  Where is a non-zero value set in minutes.  
在ORACLE级别可以设置SQLNET.EXPIRE_TIME参数,如果设置为2,可以2分钟发起一次探测,发送最小的探测包来保证连接之间有流量,
而不至于被防火墙中断。当然也可以设置防火墙的最大IDLE TIME。

参考:
Fatal NI Connect Error 12170, ‘TNS-12535: TNS:operation timed out’ Reported in 11g Alert Log (文档 ID 1286376.1)
Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out (文档 ID 1628949.1)

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

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183796.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code