[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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2750171/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210115]sqlnet.ora設定sqlnet.expire_time斷開時oracle如何探測.txtSQLOracle
- MYSQL order by排序與索引關係總結MySql排序索引
- [20200220]關於SQLNET.EXPIRE_TIME and ENABLE=BROKEN的總結.txtSQL
- [20191108]核心引數tcp_keepalive與sqlnet.ora expire_time的一些總結.txtTCPSQL
- Elasticsearch 一些命令彙總 以及學習總結Elasticsearch
- C#與C++型別對應關係總結C#C++型別
- AndroidStudio基本設定，以及一些使用小技巧Android
- 【物件導向依賴關係概念總結】物件導向程式設計的五種依賴關係物件程式設計
- DDD興起的原因以及與微服務的關係微服務
- Laravel 模型間關係設定分表方法Laravel模型
- 關於Mysql使用的一些總結MySql
- MySql關於鎖的一些總結MySql
- conda 源設定方法總結
- 交叉熵、KL 散度 | 定義與相互關係熵
- 關於Code Review的一些思考總結View
- spring cloud alibaba 元件版本關係 以及 畢業版本依賴關係SpringCloud元件
- iOS RunLoop 總結以及相關面試題解答iOSOOP面試題
- 關於Redis資料型別以及應用場景的分析與總結Redis資料型別
- iOS 設定代理（Proxy）方案總結iOS
- HBase 的結構與表的對應關係
- ReactJS &Flux &Redux 的設計思想與關係ReactJSRedux
- 總結關於CPU的一些基本知識
- 關於 JOIN 耐心總結，學不會你打我係列
- 基礎安全產品相關係統設計的一些思考
- 關於學習 Linux 系統結構的一些總結Linux
- 【BASIS】關於Hana的一些資源設定
- Hadoop與Spark關係HadoopSpark
- Cookie與Session 關係CookieSession
- CSS2中盒模型與佈局的一些概念關係CSS模型
- 對ES6中類class以及例項物件、原型物件、原型鏈之間關係的詳細總結物件原型
- 關於DDD和COLA的一些總結和思考
- 關於查詢最佳化的一些總結
- 關於 變址影像（indexed image） 的一些總結Index
- 一些關係（離散數學中的）的程式設計思想程式設計
- 關於學習-Linux-系統結構的一些總結Linux
- DDD與團隊拓撲以及微服務之間的關係圖 - aleixmorgadas微服務
- 關於es6 let var const 以及Symbol的總結Symbol
- 移動端H5混合開發設定覆盤與總結H5