1，系统环境：
操作系统：Solaris 64位
数据库版本：11.2.0.4 64位

2，在检查数据库的alert日志的时候，发现大量的12170和TNS-12535的错误
***********************************************************************
Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for Solaris: Version 11.2.0.4.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.4.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.4.0 - Production
Time: 05-AUG-2015 23:09:19
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505

TNS-00505: Operation timed out
nt secondary err code: 145
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=37982))
Wed Aug 05 23:09:20 2015
***********************************************************************

3，在不同的操作系统对应不同的ns secondary err code
The 'nt secondary err code' translates to underlying network transport timeouts for the following Operating Systems:

For the Solaris system: nt secondary err code: 145:
ETIMEDOUT 145 /* Connection timed out */

For the Linux operating system: nt secondary err code: 110
ETIMEDOUT 110 Connection timed out

For the HP-UX system: nt secondary err code: 238:
ETIMEDOUT 238 /* Connection timed out */

For AIX: nt secondary err code: 78:
ETIMEDOUT 78 /* Connection timed out */

4,触发原因

一个客户端连接整个步骤：
1、客户端发起一个connection连接监听
2、监听启动一个专属进程(服务器进程，也就是我们通常说的LOCA=NO进程)用于接收这个connection
3、在专属进程启动之后，监听会将这个connection传递给这个专属进程
4、专属进程通过这个connection来跟客户端握手
5、专属进程跟客户端信息交换需要建立一个session
6、session打开

当在以上的第3步到第4步时客户端关闭，所以当专属进程尝试跟客户端联系时发现连接已关闭时，就会报出我们看到的错误！！

5,解决方案

1）在服务器端的sqlnet.ora中设置
DIAG_ADR_ENABLED = OFF
2）在服务器端的listener.ora中设置
DIAG_ADR_ENABLED_<listenername> = OFF

举例：如果监听个名字是LISTENER，参数设置如下：
DIAG_ADR_ENABLED_LISTENER = OFF

如果要生效，需重启监听或reload监听。


另外的解决方法参考：
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 <n> is a non-zero value set in minutes

进入ORACLE_HOME/network/admin目录下，添加sqlnet.ora文件，增加一行SQLNET.EXPIRE_TIME=XX

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

ITPub博客

Fatal NI connect error 12170 TNS-12535/TNS-00505 TNS:operation timed out

最新文章