[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/,如需转载,请注明出处,否则将追究法律责任。