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

来源：这里教程网时间：2026-03-03 16:21:28 作者：

[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

编辑推荐：

返回列表

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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推