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)
操作系统: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博客 ” ,链接:http://blog.itpub.net/25373498/viewspace-1763377/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25373498/viewspace-1763377/