TNS-12535: TNS:operation timed out TNS-00505: Operation timed out

2,164 total views, 1 views today

公司一台Oracle备库alert.log中每隔一小时出现以下错误

错误原因

上述报错表示,一个会话与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分钟客户端与服务端通信一次。

加上该参数后,无需重启监听,参数对新连接生效。原有连接可能依然会出现上面的情况,直到所有原有连接遇到上面的问题断开后重新连接时,这个错误会彻底消失。


相关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

发表评论

必填项已用*标注