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

1,957 total views, 1 views today



断开原因可能是会话空闲,一直没有数据通信,最终连接时间是超过了防火墙设置的最大空闲时长(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。


2、在sqlnet.ora中增加 sqlnet.expire_time 参数(单位 分钟) 。当连接长时间空闲时,定时模仿客户端与数据库实例之间的通信,确保不会被防火墙断开。




Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out (文档 ID 1628949.1)

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.

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