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)
今天一位朋友遇到这个错误，每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)
