ITPub博客

首页 > 数据库 > Oracle > [20210115]sqlnet.ora设置sqlnet.expire_time与tcp_keepalive_time关系以及一些总结

[20210115]sqlnet.ora设置sqlnet.expire_time与tcp_keepalive_time关系以及一些总结

原创 Oracle 作者:lfree 时间:2021-01-15 09:56:41 0 删除 编辑

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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2855
  • 访问量
    6643915