TNS-12535: TNS:operation timed out TNS-00505: Operation timed out
2,118 total views, 1 views today
公司一台Oracle备库alert.log中每隔一小时出现以下错误
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Mon Nov 16 13:41:19 2020 ********************************************************************** Fatal NI connect error 12170. VERSION INFORMATION: TNS for Linux: Version 11.2.0.4.0 - Production Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production Time: 16-NOV-2020 13:41: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: 110 nt OS err code: 0 Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.128.100)(PORT=20233)) |
错误原因
上述报错表示,一个会话与Oracle实例建立连接之后断开了。
断开原因可能是会话空闲,一直没有数据通信,最终连接时间是超过了防火墙设置的最大空闲时长(idle time),最终断开连接。
“nt secondary err code” 表示网络传输超时(TCP/IP)。
“nt secondary err code” 也指出了操作系统类型。110是Linux x86 或者 Linux x86-64;238是HP-UX;78是AIX;145是Solaris。
解决办法
1、增大防火墙的空闲时间。网络一般不大可能修改。
2、在sqlnet.ora中增加 sqlnet.expire_time 参数(单位 分钟) 。当连接长时间空闲时,定时模仿客户端与数据库实例之间的通信,确保不会被防火墙断开。
1 2 |
$ cat $ORACLE_HOME/network/admin/sqlnet.ora SQLNET.EXPIRE_TIME=1 |
表示空闲连接1分钟客户端与服务端通信一次。
加上该参数后,无需重启监听,参数对新连接生效。原有连接可能依然会出现上面的情况,直到所有原有连接遇到上面的问题断开后重新连接时,这个错误会彻底消失。
相关MOS
Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out (文档 ID 1628949.1)
CAUSE
The ‘nt secondary err code’ identifies the underlying network transport, such as (TCP/IP) timeout limit. In the current case 60 identifies Windows underlying transport layer.
The “nt secondary err code” will be different based on the operating system:
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”
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.
This would indicate an issue with a firewall where a maximum idle time setting is in place.
The connection would not necessarily be “idle”. This issue can arise during a long running query or when using JDBC Thin connection pooling.
If there is no data ‘on the wirte for lengthy periods of time for any reason, the firewall might terminate the connection.
SOLUTION
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.
Once this change is in place, there is NO need to restart the listener or the database.
The change will be in place for all newly spawned server processes following the change.
Be aware that connections that were established prior to this setting would not be affected by this change. Therefore, you may continue to experience some timeouts until all remote
connection are established with this setting in place.
See the following : Note 257650.1 Resolving Problems with Connection Idle Timeout With Firewall
参考:
https://www.cnblogs.com/leohahah/p/7754763.html