[20210115]sqlnet.ora設定sqlnet.expire_time與tcp_keepalive_time關係以及一些總結

[20210115]sqlnet.ora設定sqlnet.expire_time與tcp_keepalive_time關係以及一些總結.txt

--//最近一直在做分院資料庫的最佳化,我發現下午總是非常容易出現連線斷開的情況.上午也偶爾會出現.
--//主要原因資料庫在外網,如果連線長期不用,網路就會自動斷開.我感覺自己以前做了許多測試,有必要做一些總結.

1.一般設定的方法有2種:

--//方法1.修改/etc/sysctl.conf:
# grep "^net.ipv4.tcp" /etc/sysctl.conf  
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_keepalive_time =100
net.ipv4.tcp_keepalive_intvl = 10
net.ipv4.tcp_keepalive_probes = 4

--//方法2.修改$ORACLE_HOME/network/admin/sqlnet.ora:
sqlnet.expire_time=N
--//N單位是分鐘,注意實際上發包間隔2*N.我在一些文章有論述.
--//另外一點12c開始設定sqlnet.expire_time實際上開始TCP KEEPALIVE套接字選項來檢查連線是否仍然可用。
--//2*N的時間間隔不再有效,變成N時間間隔.可以參考連結:
--// http://blog.itpub.net/267265/viewspace-2662867/ =>http://blog.itpub.net/267265/viewspace-2662867/

2.如果在兩者設定的情況下,那個優先呢?

--//實際上是設定sqlnet.expire_time=N優先.比如設定如下:

# grep "^net.ipv4.tcp" /etc/sysctl.conf  ; sysctl -p > /dev/null
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_keepalive_time =100
net.ipv4.tcp_keepalive_intvl = 10
net.ipv4.tcp_keepalive_probes = 4

--//如果不配置/etc/sysctl.conf,預設設定如下:
# echo /proc/sys/net/ipv4/tcp_keepalive* | xargs   -n 1  strings -1 -f
/proc/sys/net/ipv4/tcp_keepalive_intvl: 75
/proc/sys/net/ipv4/tcp_keepalive_probes: 9
/proc/sys/net/ipv4/tcp_keepalive_time: 7200

$ grep sqlnet.expire_time $ORACLE_HOME/network/admin/sqlnet.ora
sqlnet.expire_time=1

--//服務端執行:
#  tcpdump -i eth0 host 192.168.31.8 -nnn
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 96 bytes
...
09:10:44.212970 IP 192.168.31.8.1521 > 192.168.100.78.6326: P 2113:2130(17) ack 2410 win 101 <nop,nop,timestamp 2834317875 3519004814>
09:10:44.213056 IP 192.168.100.78.6326 > 192.168.31.8.1521: P 2410:2423(13) ack 2130 win 166 <nop,nop,timestamp 3519004814 2834317875>
09:10:44.213510 IP 192.168.31.8.1521 > 192.168.100.78.6326: P 2130:2147(17) ack 2423 win 101 <nop,nop,timestamp 2834317876 3519004814>
09:10:44.253173 IP 192.168.100.78.6326 > 192.168.31.8.1521: . ack 2147 win 166 <nop,nop,timestamp 3519004855 2834317876>

# seq 100000 | xargs -IQ bash -c "netstat -tonp | grep 192.168.100.78:|ts.awk;sleep 1"
[2021-01-15 09:10:44] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  off (0.00/0/0)
[2021-01-15 09:10:45] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  off (0.00/0/0)
[2021-01-15 09:10:46] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  off (0.00/0/0)
[2021-01-15 09:10:47] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  off (0.00/0/0)
[2021-01-15 09:10:49] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  off (0.00/0/0)
[2021-01-15 09:10:50] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  off (0.00/0/0)
--//注意最好1個欄位off表示不使用TCP KEEPALIVE.
--//注: netstat 使用-o 引數可以看到 networking timers 資訊.

--//客戶端登入伺服器:
$ rlsql sys/XXX@192.168.31.8:1521/hrp430 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 15 09:10:44 2021
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
..

--//客戶端不執行任何sql語句,等...
# seq 100000 | xargs -IQ bash -c "netstat -tonp | grep 192.168.100.78:|ts.awk;sleep 1"
...
[2021-01-15 09:12:41] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  off (0.00/0/0)
[2021-01-15 09:12:42] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  off (0.00/0/0)
[2021-01-15 09:12:43] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  off (0.00/0/0)
[2021-01-15 09:12:44] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (0.27/1/0)
~~~~~~~~~~~~~~~~~~~~~
[2021-01-15 09:12:45] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (1.63/3/0)
[2021-01-15 09:12:46] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (0.50/3/0)
[2021-01-15 09:12:47] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (2.69/4/0)
[2021-01-15 09:12:49] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (1.57/4/0)
[2021-01-15 09:12:50] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (0.44/4/0)
[2021-01-15 09:12:51] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (5.94/5/0)
[2021-01-15 09:12:52] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (4.82/5/0)
[2021-01-15 09:12:53] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (3.70/5/0)
[2021-01-15 09:12:54] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (2.57/5/0)
[2021-01-15 09:12:55] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (1.45/5/0)
[2021-01-15 09:12:56] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (0.32/5/0)
[2021-01-15 09:12:58] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (12.45/6/0)
[2021-01-15 09:12:59] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (11.34/6/0)
[2021-01-15 09:13:00] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (10.22/6/0)
[2021-01-15 09:13:01] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (9.10/6/0)
[2021-01-15 09:13:02] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (7.98/6/0)
[2021-01-15 09:13:03] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (6.87/6/0)
[2021-01-15 09:13:04] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (5.75/6/0)
[2021-01-15 09:13:05] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (4.63/6/0)
[2021-01-15 09:13:07] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (3.51/6/0)
[2021-01-15 09:13:08] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (2.39/6/0)
[2021-01-15 09:13:09] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (1.27/6/0)
[2021-01-15 09:13:10] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (0.16/6/0)
[2021-01-15 09:13:11] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (25.53/7/0)
[2021-01-15 09:13:12] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (24.42/7/0)
[2021-01-15 09:13:13] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (23.30/7/0)
[2021-01-15 09:13:14] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (22.18/7/0)
[2021-01-15 09:13:15] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (21.06/7/0)
[2021-01-15 09:13:17] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (19.94/7/0)
[2021-01-15 09:13:18] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (18.83/7/0)
[2021-01-15 09:13:19] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (17.71/7/0)
[2021-01-15 09:13:20] tcp        0     10 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:6326  ESTABLISHED 17772/oraclehrp430  on (16.59/7/0)
--//我這裡的測試已經斷開,你可以發現2分鐘就已經斷開. 登入 09:10:44,09:12:44網路狀態變成on,也就是服務端傳送探測包時網路已經斷開.
--//這個on狀態oracle採用自己獨特的探測方式,我以前做過測試,間隔逐步增加,探測15次
--//1 1 2 4 7 13 25 50 100  120 120 120 120 120 120  
--//1+1+2+4+7+13+25+50+100+120+120+120+120+120+120 = 923.
--//關於這方面的測試,我記得以前貼出過blog,現在找不到了,我重新寫一篇說明.

3.sqlnet.ora格式問題:
--//sqlnet.expire_time前面不能有空格或者tab鍵,這是我這次遇到的問題.
$ grep sqlnet.expire_time $ORACLE_HOME/network/admin/sqlnet.ora
        sqlnet.expire_time=1

# grep "^net.ipv4.tcp" /etc/sysctl.conf  ; sysctl -p > /dev/null
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_keepalive_time =100
net.ipv4.tcp_keepalive_intvl = 10
net.ipv4.tcp_keepalive_probes = 4

--//重複前面的測試.

# seq 100000 | xargs -IQ bash -c "netstat -tonp | grep 192.168.100.78:|ts.awk;sleep 1"
[2021-01-15 09:42:57] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:7102  ESTABLISHED 20910/oraclehrp430  keepalive (99.65/0/0)
[2021-01-15 09:42:58] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:7102  ESTABLISHED 20910/oraclehrp430  keepalive (98.52/0/0)
[2021-01-15 09:42:59] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:7102  ESTABLISHED 20910/oraclehrp430  keepalive (97.40/0/0)
[2021-01-15 09:43:00] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:7102  ESTABLISHED 20910/oraclehrp430  keepalive (96.28/0/0)
[2021-01-15 09:43:02] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:7102  ESTABLISHED 20910/oraclehrp430  keepalive (95.16/0/0)
[2021-01-15 09:43:03] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:7102  ESTABLISHED 20910/oraclehrp430  keepalive (94.03/0/0)
[2021-01-15 09:43:04] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:7102  ESTABLISHED 20910/oraclehrp430  keepalive (92.91/0/0)
[2021-01-15 09:43:05] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:7102  ESTABLISHED 20910/oraclehrp430  keepalive (91.79/0/0)
[2021-01-15 09:43:06] tcp        0      0 ::ffff:192.168.31.8:1521    ::ffff:192.168.100.78:7102  ESTABLISHED 20910/oraclehrp430  keepalive (90.67/0/0)
--//注意最好1個欄位keepalive 表示使用TCP KEEPALIVE.括號裡面表示時間.

4.我個人建議採用TCP KEEPALIVE套接字選項來檢查連線是否仍然可用.

--//我現在設定間隔時間很短.對效能影響應該不大.
# grep "^net.ipv4.tcp" /etc/sysctl.conf  ; sysctl -p > /dev/null
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_keepalive_time = 60
net.ipv4.tcp_keepalive_intvl = 10
net.ipv4.tcp_keepalive_probes = 4