f Oracle SQLNET.EXPIRE_TIME=10 side effect ~ 迪貝之家

Pages

Oracle SQLNET.EXPIRE_TIME=10 side effect

一直被complain 連線中斷,也解釋過Oracle 如果沒在user profile設定,是不會主動砍user session,像鴨子聽雷一樣,這個case搞到最後火很大,接下來被要求設上SQLNET.EXPIRE_TIME這個參數,然後斷線的問題就解決了.....那到底會是誰的問題?

Oracle 官方的解說 :

A SQLNET.EXPIRE_TIME=10 solution is to make this backend "not so" passive, using the DCD (dead connection detection) to figure out if the communication is still possible.
Simply, set in the $ORACLE_HOME/network/admin/sqlnet.ora, in the server side SQLNET.EXPIRE_TIME=10 (10 minutes, for example). With this parameter in place, after 10 minutes of inactivity, the server send a small 10 bytes probe packet to the client. If this packet is not acknowledge, the connection will be closed and the associated resources will be released.
There are two benefits with this DCD
1. If the SQLNET.EXPIRE_TIME is less than the FW connection idle time-out, then the firewall *may* consider this packet as activity, and the idle time-out (firewall blackout) will never happen until both the client and the server processes are alive.
2. If the SQLNET.EXPIRE_TIME (let's say a little bit higher) than the FW idle limit, then , as soon as the blackout happens , the RDBMS will know and will close the connection.
The first case is recommended when the connection comes from another application server , and the second makes sense for client applications.
DCD works at the application level and also works on top of TCP/IP protocol. If you have set the SQLNET.EXPIRE_TIME=10 then do not expect that the connections will be flagged as dead exactly after 10 minutes of the blackout or network outage. Please see Note:151972.1 "Dead Connection Detection (DCD) Explained" for details on DCD. The TCP timeout and TCP retransmission values also adds to this time.
PLEASE NOTE:
DCD was never designed to be used as a "virtual traffic generator" as we are wanting to use it for here. This is merely a useful side-effect of the feature.
In fact, some later firewalls and updated firewall firmware may not see DCD packets as a valid traffic possibly because the packets that DCD sends are actually empty packets. Therefore, DCD may not work as expected and the firewall / switch may still terminate TCP sockets that are idle for the period monitored, even when DCD is enabled and working.
In such cases, the firewall timeout should be increased or users should not leave the application idle for longer than the idle time out configured on the firewall.


今天是2019/12/23,user又跟我反應了相同的狀況,因為昨天凌晨才又從士林搬了一套10g的資料庫到板橋。查了一下10g相關的文件,原來10g就有這個參數了。
順便去查了一下Internet其他人的case,如果你的連線是重建的,參數設上sqlnet.ora之後,不需要重開任何的服務。