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/，如需转载，请注明出处，否则将追究法律责任。