[20180123]测试SQLNET.EXPIRE_TIME参数2.txt
--//曾经写过一篇linux内核网络参数测试tcp_keepalive,链接http://blog.itpub.net/267265/viewspace-2138391/
--//测试服务端会定时发起连接监测与client的连接状态.
参数解析:
/proc/sys/net/ipv4/tcp_keepalive_time 当keepalive起用的时候，TCP发送keepalive消息的频度。默认是2小时。
/proc/sys/net/ipv4/tcp_keepalive_intvl 当探测没有确认时，keepalive探测包的发送间隔。缺省是75秒。
/proc/sys/net/ipv4/tcp_keepalive_probes 如果对方不予应答，keepalive探测包的发送次数。缺省值是9。
--//我发现我遗漏的一种情况的测试,使用SQLNET.EXPIRE_TIME参数,这个参数与前面的内核参数意思差不多.定时监测服务端与客户端连
--//接情况.但是注意这种测试是有服务端发起,一些状态防火墙不允许这样的包通过,我们的分院与总院正好有这样的环境,不知道是否网管
--//配置错误,正好利用这个环境测试看看.主要目的测试设置SQLNET.EXPIRE_TIME情况下,如果无法连上client,是否探测多次,间隔时间是多少.
--//知道这些数值对于理解我遇到的一个问题帮助很大,另发文写一篇blog.
1.环境:
--//服务端IP:192.168.100.106
--//客户端IP:192.168.90.14
--//服务端:
# 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
--//客户端
# echo /proc/sys/net/ipv4/tcp_keepalive* | xargs -n 1 strings -1 -f
/proc/sys/net/ipv4/tcp_keepalive_intvl: 10
/proc/sys/net/ipv4/tcp_keepalive_probes: 4
/proc/sys/net/ipv4/tcp_keepalive_time: 200
--//在sqlnet.ora文件中配置
sqlnet.expire_time = 5
--//更正说明:实际上这个参数要修改oracle用户的$ORACLE_HOME/network/admin/sqlnet.ora,而里面的定义如下:
$ cat $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.EXPIRE_TIME=10
--//这样结果基本一致.加上我当时执行一些命令查看端口号,消耗一点时间.注意为什么不是读取grid用户的
--//$ORACLE_HOME/network/admin/sqlnet.ora文件的SQLNET.EXPIRE_TIME = 5参数,另外写blog探究.
--//我前面测试服务端发起测试连接,开始间隔0.20秒,以后间隔2倍递增.到110秒后,按照120秒间隔,测试16次.
--//以下测试模拟如果服务端发起测试连接时,如果这时客户端执行sql语句会出现什么情况呢?
2.测试:
--//客户端使用sqlplus连接数据库:
xxxxx>
SELECT s.sid, s.serial#, s.process, s.server, p.spid, p.pid, p.serial# p_serial#,'alter system kill session
'''||s.sid||','||s.serial#||''''||' immediate;' c50
FROM v$session s, v$process p
WHERE s.sid in (
SELECT sid
FROM v$mystat
WHERE rownum = 1)
AND s.paddr = p.addr;
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
3545 28775 5617 DEDICATED 53003 1403 133 alter system kill session '3545,28775' immediate;
--//确定服务端进程号=53003
--//服务端:
# lsof -i -P -n | grep "53003 oracle"
oracle 53003 oracle 8u IPv4 3838743032 0t0 UDP 127.0.0.1:32082
oracle 53003 oracle 18u IPv4 3838743021 0t0 TCP 192.168.100.106:1521->192.168.90.14:46051 (ESTABLISHED)
--//确定client的端口号46051.
--//在服务端执行:
# tcpdump -i bondeth0 host 192.168.90.14 and port 46051 -nn -vv
tcpdump: listening on bondeth0, link-type EN10MB (Ethernet), capture size 96 bytes
--//客户端打开监测:(注意网络接口不要选择错误!!我测试环境是是bond1):
# tcpdump -i bond1 host 192.168.90.14 and port 46051 -nn -vv
tcpdump: listening on bond1, link-type EN10MB (Ethernet), capture size 65535 bytes
...
--//客户端执行一些sql语句以后:
xxxxx> select sysdate from dual ;
SYSDATE
-------------------
2018-01-23 16:04:52
--//服务端打开监测:
# tcpdump -i bondeth0 host 192.168.90.14 and port 46051 -nn -vv
tcpdump: listening on bondeth0, link-type EN10MB (Ethernet), capture size 96 bytes
16:04:52.726861 IP (tos 0x0, ttl 61, id 22784, offset 0, flags [DF], proto: TCP (6), length: 346) 192.168.90.14.46051 > 192.168.100.106.1521: P 1857268049:1857268355(306) ack 1586784823 win 405
16:04:52.727189 IP (tos 0x0, ttl 64, id 61007, offset 0, flags [DF], proto: TCP (6), length: 418) 192.168.100.106.1521 > 192.168.90.14.46051: P 1:379(378) ack 306 win 330
16:04:52.727949 IP (tos 0x0, ttl 61, id 22785, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.90.14.46051 > 192.168.100.106.1521: ., cksum 0xd112 (correct), 306:306(0) ack 379 win 423
16:04:52.729120 IP (tos 0x0, ttl 61, id 22786, offset 0, flags [DF], proto: TCP (6), length: 61) 192.168.90.14.46051 > 192.168.100.106.1521: P, cksum 0xa810 (correct), 306:327(21) ack 379 win 423
16:04:52.729193 IP (tos 0x0, ttl 64, id 61008, offset 0, flags [DF], proto: TCP (6), length: 212) 192.168.100.106.1521 > 192.168.90.14.46051: P 379:551(172) ack 327 win 330
16:04:52.769464 IP (tos 0x0, ttl 61, id 22787, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.90.14.46051 > 192.168.100.106.1521: ., cksum 0xd03f (correct), 327:327(0) ack 551 win 441
---//等待看看:
--//因为网关配置成状态防火墙的形式,从服务端发起的连接都会被拒接..等看看.
--//已经过了200秒,说明client的内核参数tcp_keepalive_*对于连接的服务端无用.也就是client不会主动发起连接测试.
16:21:29.804778 IP (tos 0x0, ttl 64, id 61009, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.106.1521 > 192.168.90.14.46051: P, cksum 0x3fee (incorrect (-> 0xca82), 551:561(10) ack 327 win 330
16:21:30.021390 IP (tos 0x0, ttl 64, id 61010, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.106.1521 > 192.168.90.14.46051: P, cksum 0x3fee (incorrect (-> 0xca82), 551:561(10) ack 327 win 330
16:21:30.456415 IP (tos 0x0, ttl 64, id 61011, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.106.1521 > 192.168.90.14.46051: P, cksum 0x3fee (incorrect (-> 0xca82), 551:561(10) ack 327 win 330
16:21:31.326401 IP (tos 0x0, ttl 64, id 61012, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.106.1521 > 192.168.90.14.46051: P, cksum 0x3fee (incorrect (-> 0xca82), 551:561(10) ack 327 win 330
16:21:33.064398 IP (tos 0x0, ttl 64, id 61013, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.106.1521 > 192.168.90.14.46051: P, cksum 0x3fee (incorrect (-> 0xca82), 551:561(10) ack 327 win 330
16:21:36.544396 IP (tos 0x0, ttl 64, id 61014, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.106.1521 > 192.168.90.14.46051: P, cksum 0x3fee (incorrect (-> 0xca82), 551:561(10) ack 327 win 330
16:21:43.512397 IP (tos 0x0, ttl 64, id 61015, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.106.1521 > 192.168.90.14.46051: P, cksum 0x3fee (incorrect (-> 0xca82), 551:561(10) ack 327 win 330
16:21:57.432393 IP (tos 0x0, ttl 64, id 61016, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.106.1521 > 192.168.90.14.46051: P, cksum 0x3fee (incorrect (-> 0xca82), 551:561(10) ack 327 win 330
--//这时在client端执行:
XXXX> select sysdate a from dual ;
--//一样挂起.
16:23:20.952395 IP (tos 0x0, ttl 64, id 61018, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.106.1521 > 192.168.90.14.46051: P, cksum 0x3fee (incorrect (-> 0xca82), 551:561(10) ack 327 win 330
16:25:12.440389 IP (tos 0x0, ttl 64, id 61019, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.106.1521 > 192.168.90.14.46051: P, cksum 0x3fee (incorrect (-> 0xca82), 551:561(10) ack 327 win 330
16:27:12.760421 IP (tos 0x0, ttl 64, id 61020, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.106.1521 > 192.168.90.14.46051: P, cksum 0x3fee (incorrect (-> 0xca82), 551:561(10) ack 327 win 330
16:29:13.080394 IP (tos 0x0, ttl 64, id 61021, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.106.1521 > 192.168.90.14.46051: P, cksum 0x3fee (incorrect (-> 0xca82), 551:561(10) ack 327 win 330
16:31:13.400398 IP (tos 0x0, ttl 64, id 61022, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.106.1521 > 192.168.90.14.46051: P, cksum 0x3fee (incorrect (-> 0xca82), 551:561(10) ack 327 win 330
16:33:13.720395 IP (tos 0x0, ttl 64, id 61023, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.106.1521 > 192.168.90.14.46051: P, cksum 0x3fee (incorrect (-> 0xca82), 551:561(10) ack 327 win 330
16:35:14.040393 IP (tos 0x0, ttl 64, id 61024, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.106.1521 > 192.168.90.14.46051: P, cksum 0x3fee (incorrect (-> 0xca82), 551:561(10) ack 327 win 330
--//在出现以上数据包过程中,在服务端执行如下:
--//服务器:
# netstat -otun | egrep 'Active|Proto|192.168.90.14:46051'
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address Foreign Address State Timer
tcp 0 10 192.168.100.106:1521 192.168.90.14:46051 ESTABLISHED on (38.44/8/0)
--//这里有执行sql语句.
# netstat -otun | egrep 'Active|Proto|192.168.90.14:46051'
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address Foreign Address State Timer
tcp 0 308 192.168.90.14:46051 192.168.100.106:1521 ESTABLISHED on (22.87/8/0)
# netstat -otun | egrep 'Active|Proto|192.168.90.14:46051'
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address Foreign Address State Timer
tcp 0 20 192.168.100.106:1521 192.168.90.14:46051 ESTABLISHED on (54.49/14/0)
# netstat -otun | egrep 'Active|Proto|192.168.90.14:46051'
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address Foreign Address State Timer
--//说明关于netstat -o 参数的解析参考连接:http://blog.itpub.net/267265/viewspace-2138396/
--//The interpretation of field is, first field = timer countdown value, second field = no. of retransmissions,
--//third field = number of keepalive probes sent.
--//可以发现前面的第1字段逐渐减少,而第2字段增加后,第1字段重新记录.
--//客户端:
# netstat -otun | egrep 'Active|Proto|192.168.90.14:46051'
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address Foreign Address State Timer
tcp 0 308 192.168.90.14:46051 192.168.100.106:1521 ESTABLISHED on (47.36/10/0)
# netstat -otun | egrep 'Active|Proto|192.168.90.14:46051'
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address Foreign Address State Timer
tcp 0 308 192.168.90.14:46051 192.168.100.106:1521 ESTABLISHED on (36.55/13/0)
# netstat -otun | egrep 'Active|Proto|192.168.90.14:46051'
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address Foreign Address State Timer
--//这时客户端也在做相同的测试.这时客户端报如下错误:
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 53003
Session ID: 3545 Serial number: 28775
--//客户端,在客户端监测:
# tcpdump -i bond1 host 192.168.90.14 and port 46051 -nn -vv
tcpdump: listening on bond1, link-type EN10MB (Ethernet), capture size 65535 bytes
...
16:04:52.726417 IP (tos 0x0, ttl 64, id 22784, offset 0, flags [DF], proto TCP (6), length 346) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x4116 (incorrect -> 0x3643), seq 327:633, ack 551, win 405, length 306
16:04:52.728591 IP (tos 0x0, ttl 61, id 61007, offset 0, flags [DF], proto TCP (6), length 418) 192.168.100.106.1521 > 192.168.90.14.46051: Flags [P.], cksum 0x60ae (correct), seq 551:929, ack 633, win 330, length 378
16:04:52.728623 IP (tos 0x0, ttl 64, id 22785, offset 0, flags [DF], proto TCP (6), length 40) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [.], cksum 0xd112 (correct), seq 633, ack 929, win 423, length 0
16:04:52.728800 IP (tos 0x0, ttl 64, id 22786, offset 0, flags [DF], proto TCP (6), length 61) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x3ff9 (incorrect -> 0xa810), seq 633:654, ack 929, win 423, length 21
16:04:52.730494 IP (tos 0x0, ttl 61, id 61008, offset 0, flags [DF], proto TCP (6), length 212) 192.168.100.106.1521 > 192.168.90.14.46051: Flags [P.], cksum 0x1558 (correct), seq 929:1101, ack 654, win 330, length 172
16:04:52.770133 IP (tos 0x0, ttl 64, id 22787, offset 0, flags [DF], proto TCP (6), length 40) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [.], cksum 0xd03f (correct), seq 654, ack 1101, win 441, length 0
--//这时在client端执行:
XXXX> select sysdate a from dual ;
--//一样挂起.
16:23:07.348516 IP (tos 0x0, ttl 64, id 22788, offset 0, flags [DF], proto TCP (6), length 348) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x4118 (incorrect -> 0xca89), seq 654:962, ack 1101, win 441, length 308
16:23:07.551178 IP (tos 0x0, ttl 64, id 22789, offset 0, flags [DF], proto TCP (6), length 348) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x4118 (incorrect -> 0xca89), seq 654:962, ack 1101, win 441, length 308
16:23:07.957168 IP (tos 0x0, ttl 64, id 22790, offset 0, flags [DF], proto TCP (6), length 348) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x4118 (incorrect -> 0xca89), seq 654:962, ack 1101, win 441, length 308
16:23:08.769142 IP (tos 0x0, ttl 64, id 22791, offset 0, flags [DF], proto TCP (6), length 348) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x4118 (incorrect -> 0xca89), seq 654:962, ack 1101, win 441, length 308
16:23:10.393167 IP (tos 0x0, ttl 64, id 22792, offset 0, flags [DF], proto TCP (6), length 348) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x4118 (incorrect -> 0xca89), seq 654:962, ack 1101, win 441, length 308
16:23:13.641396 IP (tos 0x0, ttl 64, id 22793, offset 0, flags [DF], proto TCP (6), length 348) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x4118 (incorrect -> 0xca89), seq 654:962, ack 1101, win 441, length 308
16:23:20.137163 IP (tos 0x0, ttl 64, id 22794, offset 0, flags [DF], proto TCP (6), length 348) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x4118 (incorrect -> 0xca89), seq 654:962, ack 1101, win 441, length 308
16:23:33.129182 IP (tos 0x0, ttl 64, id 22795, offset 0, flags [DF], proto TCP (6), length 348) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x4118 (incorrect -> 0xca89), seq 654:962, ack 1101, win 441, length 308
16:23:59.113166 IP (tos 0x0, ttl 64, id 22796, offset 0, flags [DF], proto TCP (6), length 348) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x4118 (incorrect -> 0xca89), seq 654:962, ack 1101, win 441, length 308
16:24:51.081162 IP (tos 0x0, ttl 64, id 22797, offset 0, flags [DF], proto TCP (6), length 348) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x4118 (incorrect -> 0xca89), seq 654:962, ack 1101, win 441, length 308
16:26:35.017169 IP (tos 0x0, ttl 64, id 22798, offset 0, flags [DF], proto TCP (6), length 348) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x4118 (incorrect -> 0xca89), seq 654:962, ack 1101, win 441, length 308
16:28:35.017411 IP (tos 0x0, ttl 64, id 22799, offset 0, flags [DF], proto TCP (6), length 348) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x4118 (incorrect -> 0xca89), seq 654:962, ack 1101, win 441, length 308
16:30:35.017170 IP (tos 0x0, ttl 64, id 22800, offset 0, flags [DF], proto TCP (6), length 348) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x4118 (incorrect -> 0xca89), seq 654:962, ack 1101, win 441, length 308
16:32:35.017141 IP (tos 0x0, ttl 64, id 22801, offset 0, flags [DF], proto TCP (6), length 348) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x4118 (incorrect -> 0xca89), seq 654:962, ack 1101, win 441, length 308
16:34:35.017167 IP (tos 0x0, ttl 64, id 22802, offset 0, flags [DF], proto TCP (6), length 348) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x4118 (incorrect -> 0xca89), seq 654:962, ack 1101, win 441, length 308
16:36:35.017165 IP (tos 0x0, ttl 64, id 22803, offset 0, flags [DF], proto TCP (6), length 348) 192.168.90.14.46051 > 192.168.100.106.1521: Flags [P.], cksum 0x4118 (incorrect -> 0xca89), seq 654:962, ack 1101, win 441, length 308
3.分析时间变化:
16:04:52.769464
16:21:29.804778
--//间隔17*60+29-52 = 997秒,为什么不是SQLNET.EXPIRE_TIME*2分钟=5*2*60 = 600秒呢?(我的服务器是rac环境,不知道跟这个有关吗)
--//时间变化不在计算,跟前面测试一样.
--//开始间隔0.21秒,以后间隔2倍递增.到110秒后,按照120秒间隔.另外一组数据测试如下,结果惊人的相似.
--//换一句话讲如果服务器发出测试连接如果不通,连接已经断开.只不过要经历这个过程回收网络连接以及资源.
--//换一句话讲如果真要使用状态防火墙不能使用SQLNET.EXPIRE_TIME参数,而且你设置太短问题可能更加严重.用户体验经常死机的感觉.
--//补充说明:
--//我少算了最后1次120秒,这样实际上大约809+120 = 929秒才会退出.
