[20200218]ENABLE=BROKEN在连接串中.txt
--//昨天看https://blog.dbi-services.com/sqlnet-expire_time-and-enablebroken/
--//转抄:
Those parameters, SQLNET.EXPIRE_TIME in sqlnet.ora and ENABLE=BROKEN in a connection description exist for a long time
but may have changed in behavior. They are both related to detecting dead TCP connections with keep-alive probes. The
former from the server, and the latter from the client.
--//这些参数，SQLNET。sqlnet.or a中的EXIRE_TIME和连接描述中的ENABLE=BROKEN存在很长一段时间但可能在行为上发生了变化。它们
--//都与用保持活的探针检测死TCP连接有关。前者来自服务器，后者来自客户端。
--//我曾经提到，链接：http://blog.itpub.net/267265/viewspace-2662867/=>[20191106]12c DCD SQLNET.EXPIRE_TIME.txt
--//12之前DCD使用TNS包来"ping"客户端，并且依赖底层TCP堆栈，这有时可能需要更长的时间。现在，在12c中，这种情况已经改变，
--//DCD探针由TCP Stack实现。DCD探测现在将使用TCP KEEPALIVE套接字选项来检查连接是否仍然可用。
--//我个人认为12c的这种改进更加科学.不会出现11g的情况,如果11g实际上最大间隔是2*EXPIRE_TIME.
--//换一句话讲12c自动将sqlnet.ora中的SQLNET.EXPIRE_TIME设置为探测之间的时间量(以分钟为单位)。
--//我个人从来没有在连接串中设置ENABLE=BROKEN,我仔细看链接：https://blog.dbi-services.com/sqlnet-expire_time-and-enablebroken/
--//重复对方测试：
1.环境：
在linux下实际上使用如下参数:
$ 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: 20
$ cat $ORACLE_HOME/network/admin/sqlnet.ora | grep SQLNET.EXPIRE_TIME
# SQLNET.EXPIRE_TIME = 1
--//注意：注解SQLNET.EXPIRE_TIME = 1
2.测试无ENABLE=BROKEN的情况:
$ rlwrap sqlplus scott/book@"(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=book)(SERVER = DEDICATED))(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))"
--//注意我增加(SERVER = DEDICATED)，保证使用DEDICATED模式连接：
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//注意我的服务端是11g。
SCOTT@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
296 319 46346 DEDICATED 46348 21 129 alter system kill session '296,319' immediate;
$ netstat -np | grep sqlplus
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:1417 127.0.0.1:1521 ESTABLISHED 46346/sqlplus
$ netstat -np | grep 1417
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:1417 127.0.0.1:1521 ESTABLISHED 46346/sqlplus
tcp 0 0 127.0.0.1:1521 127.0.0.1:1417 ESTABLISHED 46348/oraclebook
# tcpdump -vvnni lo port 1417
tcpdump: listening on lo, link-type EN10MB (Ethernet), capture size 96 bytes
09:19:47.120266 IP (tos 0x0, ttl 64, id 35513, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.1417: ., cksum 0xfe28 (incorrect (-> 0x843e), 3700102688:3700102688(0) ack 1995699216 win 385
09:19:47.120432 IP (tos 0x0, ttl 64, id 43674, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1417 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0x20dd), 1:1(0) ack 1 win 385
SCOTT@book> set time on escape on
09:20:09 SCOTT@book>
# tcpdump -vvnni lo port 1417
tcpdump: listening on lo, link-type EN10MB (Ethernet), capture size 96 bytes
--//不执行任何命令等观察：
09:19:47.120266 IP (tos 0x0, ttl 64, id 35513, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.1417: ., cksum 0xfe28 (incorrect (-> 0x843e), 3700102688:3700102688(0) ack 1995699216 win 385
09:19:47.120432 IP (tos 0x0, ttl 64, id 43674, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1417 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0x20dd), 1:1(0) ack 1 win 385
~~~~~~~~~~~~~~~
09:20:07.152745 IP (tos 0x0, ttl 64, id 35514, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.1417: ., cksum 0xfe28 (incorrect (-> 0xe7bd), 0:0(0) ack 1 win 385
09:20:07.152765 IP (tos 0x0, ttl 64, id 43675, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1417 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0xd29c), 1:1(0) ack 1 win 385
~~~~~~~~~~~~~~~~
09:20:27.185370 IP (tos 0x0, ttl 64, id 35515, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.1417: ., cksum 0xfe28 (incorrect (-> 0x4b3c), 0:0(0) ack 1 win 385
09:20:27.185388 IP (tos 0x0, ttl 64, id 43676, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1417 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0x845b), 1:1(0) ack 1 win 385
09:20:47.217453 IP (tos 0x0, ttl 64, id 35516, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.1417: ., cksum 0xfe28 (incorrect (-> 0xaeba), 0:0(0) ack 1 win 385
09:20:47.217471 IP (tos 0x0, ttl 64, id 43677, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1417 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0x361b), 1:1(0) ack 1 win 385
09:21:07.248171 IP (tos 0x0, ttl 64, id 35517, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.1417: ., cksum 0xfe28 (incorrect (-> 0x123b), 0:0(0) ack 1 win 385
09:21:07.248193 IP (tos 0x0, ttl 64, id 43678, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1417 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0xe7db), 1:1(0) ack 1 win 385
--//间隔20秒发送探测包，检查网络是否正常，从服务段发起。与内核参数/proc/sys/net/ipv4/tcp_keepalive_time: 20有关。
--//注意看下划线之间内容，从服务端发起，客户端接收。
# tcpdump -vvnni lo port 1417
...
09:24:07.536211 IP (tos 0x0, ttl 64, id 35526, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.1417: ., cksum 0xfe28 (incorrect (-> 0x91b6), 0:0(0) ack 1 win 385
09:24:07.536232 IP (tos 0x0, ttl 64, id 43687, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1417 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0x2799), 1:1(0) ack 1 win 385
09:24:27.568219 IP (tos 0x0, ttl 64, id 35527, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.1417: ., cksum 0xfe28 (incorrect (-> 0xf535), 0:0(0) ack 1 win 385
09:24:27.568238 IP (tos 0x0, ttl 64, id 43688, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1417 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0xd958), 1:1(0) ack 1 win 385
--//这里设置防火墙规则,阻塞服务端到client的连接。
# iptables -I INPUT 1 -p tcp --dport 1417 -j DROP
09:24:47.600219 IP (tos 0x0, ttl 64, id 35528, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.1417: ., cksum 0xfe28 (incorrect (-> 0x58b5), 0:0(0) ack 1 win 385
09:24:57.616220 IP (tos 0x0, ttl 64, id 35529, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.1417: ., cksum 0xfe28 (incorrect (-> 0x3195), 0:0(0) ack 1 win 385
09:25:07.632221 IP (tos 0x0, ttl 64, id 35530, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.1417: ., cksum 0xfe28 (incorrect (-> 0x0a75), 0:0(0) ack 1 win 385
09:25:17.648218 IP (tos 0x0, ttl 64, id 35531, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.1417: ., cksum 0xfe28 (incorrect (-> 0xe354), 0:0(0) ack 1 win 385
09:25:27.664230 IP (tos 0x0, ttl 64, id 35532, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.1417: R, cksum 0xfe28 (incorrect (-> 0xbc2f), 1:1(0) ack 1 win 385
--//间隔变成了10秒，发送探测包,探测4次。因为这时网络已经不通。
$ 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: 20
$ netstat -npo | grep 1417
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:1417 127.0.0.1:1521 ESTABLISHED 46346/sqlplus off (0.00/0/0)
--//你可以发现现在服务端进程已经消失
$ ps -ef | grep 4634[8]
--//如果这时client有命令执行，看看发生什么情况呢？
09:20:09 SCOTT@book> select sysdate from dual
09:31:29 2 ;
# tcpdump -vvnni lo port 1417
...
09:31:30.969789 IP (tos 0x0, ttl 64, id 43689, offset 0, flags [DF], proto: TCP (6), length: 358) 127.0.0.1.1417 > 127.0.0.1.1521: P 1:307(306) ack 1 win 385
09:31:30.969818 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto: TCP (6), length: 40) 127.0.0.1.1521 > 127.0.0.1.1417: R, cksum 0xb3b7 (correct), 3700102689:3700102689(0) win 0
09:31:31.171207 IP (tos 0x0, ttl 64, id 43690, offset 0, flags [DF], proto: TCP (6), length: 358) 127.0.0.1.1417 > 127.0.0.1.1521: P 1:307(306) ack 1 win 385
09:31:31.171235 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto: TCP (6), length: 40) 127.0.0.1.1521 > 127.0.0.1.1417: R, cksum 0xb3b7 (correct), 3700102689:3700102689(0) win 0
09:31:31.576202 IP (tos 0x0, ttl 64, id 43691, offset 0, flags [DF], proto: TCP (6), length: 358) 127.0.0.1.1417 > 127.0.0.1.1521: P 1:307(306) ack 1 win 385
09:31:31.576229 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto: TCP (6), length: 40) 127.0.0.1.1521 > 127.0.0.1.1417: R, cksum 0xb3b7 (correct), 3700102689:3700102689(0) win 0
09:31:32.386179 IP (tos 0x0, ttl 64, id 43692, offset 0, flags [DF], proto: TCP (6), length: 358) 127.0.0.1.1417 > 127.0.0.1.1521: P 1:307(306) ack 1 win 385
09:31:32.386206 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto: TCP (6), length: 40) 127.0.0.1.1521 > 127.0.0.1.1417: R, cksum 0xb3b7 (correct), 3700102689:3700102689(0) win 0
09:31:34.008203 IP (tos 0x0, ttl 64, id 43693, offset 0, flags [DF], proto: TCP (6), length: 358) 127.0.0.1.1417 > 127.0.0.1.1521: P 1:307(306) ack 1 win 385
09:31:34.008252 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto: TCP (6), length: 40) 127.0.0.1.1521 > 127.0.0.1.1417: R, cksum 0xb3b7 (correct), 3700102689:3700102689(0) win 0
09:31:37.248243 IP (tos 0x0, ttl 64, id 43694, offset 0, flags [DF], proto: TCP (6), length: 358) 127.0.0.1.1417 > 127.0.0.1.1521: P 1:307(306) ack 1 win 385
09:31:37.248272 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto: TCP (6), length: 40) 127.0.0.1.1521 > 127.0.0.1.1417: R, cksum 0xb3b7 (correct), 3700102689:3700102689(0) win 0
09:31:43.728164 IP (tos 0x0, ttl 64, id 43695, offset 0, flags [DF], proto: TCP (6), length: 358) 127.0.0.1.1417 > 127.0.0.1.1521: P 1:307(306) ack 1 win 385
09:31:43.728189 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto: TCP (6), length: 40) 127.0.0.1.1521 > 127.0.0.1.1417: R, cksum 0xb3b7 (correct), 3700102689:3700102689(0) win 0
09:31:56.688182 IP (tos 0x0, ttl 64, id 43696, offset 0, flags [DF], proto: TCP (6), length: 358) 127.0.0.1.1417 > 127.0.0.1.1521: P 1:307(306) ack 1 win 385
09:31:56.688210 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto: TCP (6), length: 40) 127.0.0.1.1521 > 127.0.0.1.1417: R, cksum 0xb3b7 (correct), 3700102689:3700102689(0) win 0
09:32:22.576293 IP (tos 0x0, ttl 64, id 43697, offset 0, flags [DF], proto: TCP (6), length: 358) 127.0.0.1.1417 > 127.0.0.1.1521: P 1:307(306) ack 1 win 385
09:32:22.576326 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto: TCP (6), length: 40) 127.0.0.1.1521 > 127.0.0.1.1417: R, cksum 0xb3b7 (correct), 3700102689:3700102689(0) win 0
09:33:14.416169 IP (tos 0x0, ttl 64, id 43698, offset 0, flags [DF], proto: TCP (6), length: 358) 127.0.0.1.1417 > 127.0.0.1.1521: P 1:307(306) ack 1 win 385
09:33:14.416218 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto: TCP (6), length: 40) 127.0.0.1.1521 > 127.0.0.1.1417: R, cksum 0xb3b7 (correct), 3700102689:3700102689(0) win 0
09:34:57.968188 IP (tos 0x0, ttl 64, id 43699, offset 0, flags [DF], proto: TCP (6), length: 358) 127.0.0.1.1417 > 127.0.0.1.1521: P 1:307(306) ack 1 win 385
09:34:57.968221 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto: TCP (6), length: 40) 127.0.0.1.1521 > 127.0.0.1.1417: R, cksum 0xb3b7 (correct), 3700102689:3700102689(0) win 0
09:36:58.288221 IP (tos 0x0, ttl 64, id 43700, offset 0, flags [DF], proto: TCP (6), length: 358) 127.0.0.1.1417 > 127.0.0.1.1521: P 1:307(306) ack 1 win 385
09:36:58.288256 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto: TCP (6), length: 40) 127.0.0.1.1521 > 127.0.0.1.1417: R, cksum 0xb3b7 (correct), 3700102689:3700102689(0) win 0
09:38:58.608216 IP (tos 0x0, ttl 64, id 43701, offset 0, flags [DF], proto: TCP (6), length: 358) 127.0.0.1.1417 > 127.0.0.1.1521: P 1:307(306) ack 1 win 385
09:38:58.608250 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto: TCP (6), length: 40) 127.0.0.1.1521 > 127.0.0.1.1417: R, cksum 0xb3b7 (correct), 3700102689:3700102689(0) win 0
09:40:58.928247 IP (tos 0x0, ttl 64, id 43702, offset 0, flags [DF], proto: TCP (6), length: 358) 127.0.0.1.1417 > 127.0.0.1.1521: P 1:307(306) ack 1 win 385
09:40:58.928302 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto: TCP (6), length: 40) 127.0.0.1.1521 > 127.0.0.1.1417: R, cksum 0xb3b7 (correct), 3700102689:3700102689(0) win 0
09:42:59.248255 IP (tos 0x0, ttl 64, id 43703, offset 0, flags [DF], proto: TCP (6), length: 358) 127.0.0.1.1417 > 127.0.0.1.1521: P 1:307(306) ack 1 win 385
09:42:59.248290 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto: TCP (6), length: 40) 127.0.0.1.1521 > 127.0.0.1.1417: R, cksum 0xb3b7 (correct), 3700102689:3700102689(0) win 0
09:44:59.568184 IP (tos 0x0, ttl 64, id 43704, offset 0, flags [DF], proto: TCP (6), length: 358) 127.0.0.1.1417 > 127.0.0.1.1521: P 1:307(306) ack 1 win 385
09:44:59.568217 IP (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto: TCP (6), length: 40) 127.0.0.1.1521 > 127.0.0.1.1417: R, cksum 0xb3b7 (correct), 3700102689:3700102689(0) win 0
--//16次。(44-31)*60+59-30 = 809秒。另外可以注意时间间隔会越来越大。最后1次120秒。
$ netstat -npo | grep 1417
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 306 127.0.0.1:1417 127.0.0.1:1521 ESTABLISHED 46346/sqlplus on (37.99/8/0)
$ netstat -npo | grep 1417
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 306 127.0.0.1:1417 127.0.0.1:1521 ESTABLISHED 46346/sqlplus on (22.63/8/0)
$ netstat -npo | grep 1417
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 306 127.0.0.1:1417 127.0.0.1:1521 ESTABLISHED 46346/sqlplus on (20.36/9/0)
$ netstat -npo | grep 1417
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 306 127.0.0.1:1417 127.0.0.1:1521 ESTABLISHED 46346/sqlplus on (11.55/10/0)
$ netstat -npo | grep 1417
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 306 127.0.0.1:1417 127.0.0.1:1521 ESTABLISHED 46346/sqlplus on (78.83/14/0)
$ netstat -npo | grep 1417
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
--//on后面的数字（第1个表示探测的时间间隔，如果不断执行可以发现不断减少，第2个表示探测次数）
--//我在链接http://blog.itpub.net/267265/viewspace-2150555/有描述这个现象。大约需要927秒，客户段报错。
09:20:09 SCOTT@book> select sysdate from dual
09:31:29 2 ;
select sysdate from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 46348
Session ID: 296 Serial number: 319
09:46:59 SCOTT@book>
--//提示时间是09:46:59，前面少算120秒，809+120 = 929，基本与我前面测试927秒接近。
--//(46-31)*60+59-29 = 930.
09:46:59 SCOTT@book> select sysdate from dual ;
ERROR:
ORA-03114: not connected to ORACLE
--//连接已经断开。
3.测试ENABLE=BROKEN的情况:
# iptables -D INPUT 1
rlwrap sqlplus scott/book@"(DESCRIPTION=(ENABLE=BROKEN)(CONNECT_DATA=(SERVICE_NAME=book)(SERVER = DEDICATED))(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))"
SCOTT@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
58 585 46995 DEDICATED 46997 28 245 alter system kill session '58,585' immediate;
$ netstat -np | grep sqlplus
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:2560 127.0.0.1:1521 ESTABLISHED 46995/sqlplus
$ netstat -npo | grep 2560
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:2560 127.0.0.1:1521 ESTABLISHED 46995/sqlplus keepalive (9.48/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:2560 ESTABLISHED 46997/oraclebook keepalive (9.51/0/0)
# tcpdump -vvnni lo port 2560
tcpdump: listening on lo, link-type EN10MB (Ethernet), capture size 96 bytes
SCOTT@book> set time on escape on
10:55:03 SCOTT@book>
# tcpdump -vvnni lo port 2560
tcpdump: listening on lo, link-type EN10MB (Ethernet), capture size 96 bytes
10:54:54.992573 IP (tos 0x0, ttl 64, id 63732, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.2560 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0x3a5f), 2007468150:2007468150(0) ack 4186535938 win 385
10:54:54.992575 IP (tos 0x0, ttl 64, id 52212, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.2560: ., cksum 0xfe28 (incorrect (-> 0x3a3e), 1:1(0) ack 1 win 385
10:54:55.024174 IP (tos 0x0, ttl 64, id 52213, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.2560: ., cksum 0xfe28 (incorrect (-> 0x3a1f), 0:0(0) ack 1 win 385
10:54:55.024193 IP (tos 0x0, ttl 64, id 63733, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.2560 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0xebfd), 1:1(0) ack 1 win 385
~~~~~~~~~~~~~~~~~~~~~~~~~~
10:55:15.024332 IP (tos 0x0, ttl 64, id 63734, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.2560 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0x9dde), 0:0(0) ack 1 win 385
10:55:15.024368 IP (tos 0x0, ttl 64, id 52214, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.2560: ., cksum 0xfe28 (incorrect (-> 0x9dbd), 1:1(0) ack 1 win 385
10:55:15.056201 IP (tos 0x0, ttl 64, id 52215, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.2560: ., cksum 0xfe28 (incorrect (-> 0x9d9e), 0:0(0) ack 1 win 385
10:55:15.056219 IP (tos 0x0, ttl 64, id 63735, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.2560 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0x4f7d), 1:1(0) ack 1 win 385
~~~~~~~~~~~~~~~~~~~~~~~~~~
10:55:35.056497 IP (tos 0x0, ttl 64, id 63736, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.2560 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0x015e), 0:0(0) ack 1 win 385
10:55:35.056572 IP (tos 0x0, ttl 64, id 52216, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.2560: ., cksum 0xfe28 (incorrect (-> 0x013d), 1:1(0) ack 1 win 385
10:55:35.088172 IP (tos 0x0, ttl 64, id 52217, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.2560: ., cksum 0xfe28 (incorrect (-> 0x011e), 0:0(0) ack 1 win 385
10:55:35.088192 IP (tos 0x0, ttl 64, id 63737, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.2560 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0xb2fc), 1:1(0) ack 1 win 385
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
10:55:55.088507 IP (tos 0x0, ttl 64, id 63738, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.2560 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0x64dd), 0:0(0) ack 1 win 385
10:55:55.088582 IP (tos 0x0, ttl 64, id 52218, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.2560: ., cksum 0xfe28 (incorrect (-> 0x64bc), 1:1(0) ack 1 win 385
10:55:55.120174 IP (tos 0x0, ttl 64, id 52219, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.2560: ., cksum 0xfe28 (incorrect (-> 0x649d), 0:0(0) ack 1 win 385
10:55:55.120193 IP (tos 0x0, ttl 64, id 63739, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.2560 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0x167c), 1:1(0) ack 1 win 385
--//间隔20秒。如果你仔细看相同时间的有4个包，看下划线部分。1组是客户端发起，1组是服务端发起，。讲的通俗一点就是双向检查。
# iptables -I INPUT 1 -p tcp --dport 2560 -j DROP
...
10:56:15.120166 IP (tos 0x0, ttl 64, id 63740, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.2560 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0xc85c), 0:0(0) ack 1 win 385
10:56:15.120293 IP (tos 0x0, ttl 64, id 52220, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.2560: ., cksum 0xfe28 (incorrect (-> 0xc83b), 1:1(0) ack 1 win 385
10:56:15.152207 IP (tos 0x0, ttl 64, id 52221, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.2560: ., cksum 0xfe28 (incorrect (-> 0xc81c), 0:0(0) ack 1 win 385
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
10:56:25.136167 IP (tos 0x0, ttl 64, id 63741, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.2560 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0xa13c), 0:0(0) ack 1 win 385
10:56:25.136256 IP (tos 0x0, ttl 64, id 52222, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.2560: ., cksum 0xfe28 (incorrect (-> 0xa11b), 1:1(0) ack 1 win 385
10:56:25.168204 IP (tos 0x0, ttl 64, id 52223, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.2560: ., cksum 0xfe28 (incorrect (-> 0xa0fc), 0:0(0) ack 1 win 385
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//注意看下划线之间内容，第3个包没有回来的包（iptables已经drop了）。
10:56:35.152178 IP (tos 0x0, ttl 64, id 63742, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.2560 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0x7a1c), 0:0(0) ack 1 win 385
10:56:35.152268 IP (tos 0x0, ttl 64, id 52224, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.2560: ., cksum 0xfe28 (incorrect (-> 0x79fb), 1:1(0) ack 1 win 385
10:56:35.184215 IP (tos 0x0, ttl 64, id 52225, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.2560: ., cksum 0xfe28 (incorrect (-> 0x79dc), 0:0(0) ack 1 win 385
10:56:45.168173 IP (tos 0x0, ttl 64, id 63743, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.2560 > 127.0.0.1.1521: ., cksum 0xfe28 (incorrect (-> 0x52fc), 0:0(0) ack 1 win 385
10:56:45.168261 IP (tos 0x0, ttl 64, id 52226, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.2560: ., cksum 0xfe28 (incorrect (-> 0x52db), 1:1(0) ack 1 win 385
10:56:45.200215 IP (tos 0x0, ttl 64, id 52227, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.1521 > 127.0.0.1.2560: ., cksum 0xfe28 (incorrect (-> 0x52bc), 0:0(0) ack 1 win 385
10:56:55.184173 IP (tos 0x0, ttl 64, id 63744, offset 0, flags [DF], proto: TCP (6), length: 52) 127.0.0.1.2560 > 127.0.0.1.1521: R, cksum 0xfe28 (incorrect (-> 0x2bd7), 1:1(0) ack 1 win 385
~~~~~~~~~~~~~~
--//看到的是客户端，服务器的包。
--//也就是这样仅仅40秒，探测4次，网络已经断开。
$ netstat -npo | grep 2560
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:1521 127.0.0.1:2560 ESTABLISHED 46997/oraclebook keepalive (7.69/0/1)
tcp 0 0 127.0.0.1:2560 127.0.0.1:1521 ESTABLISHED 46995/sqlplus keepalive (7.66/0/1)
$ netstat -npo | grep 2560
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:1521 127.0.0.1:2560 ESTABLISHED 46997/oraclebook keepalive (6.99/0/1)
tcp 0 0 127.0.0.1:2560 127.0.0.1:1521 ESTABLISHED 46995/sqlplus keepalive (6.95/0/1)
$ netstat -npo | grep 2560
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:1521 127.0.0.1:2560 ESTABLISHED 46997/oraclebook keepalive (1.45/0/3)
tcp 0 0 127.0.0.1:2560 127.0.0.1:1521 ESTABLISHED 46995/sqlplus keepalive (1.42/0/3)
$ netstat -npo | grep 2560
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:1521 127.0.0.1:2560 ESTABLISHED 46997/oraclebook keepalive (0.83/0/3)
tcp 0 0 127.0.0.1:2560 127.0.0.1:1521 ESTABLISHED 46995/sqlplus keepalive (0.80/0/3)
$ netstat -npo | grep 2560
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:1521 127.0.0.1:2560 ESTABLISHED 46997/oraclebook keepalive (0.16/0/3)
tcp 0 0 127.0.0.1:2560 127.0.0.1:1521 ESTABLISHED 46995/sqlplus keepalive (0.12/0/3)
..
$ netstat -npo | grep 2560
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:1521 127.0.0.1:2560 ESTABLISHED 46997/oraclebook keepalive (2.32/0/4)
tcp 0 0 127.0.0.1:2560 127.0.0.1:1521 ESTABLISHED 46995/sqlplus keepalive (2.28/0/4)
$ netstat -npo | grep 2560
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:1521 127.0.0.1:2560 ESTABLISHED 46997/oraclebook keepalive (1.60/0/4)
tcp 0 0 127.0.0.1:2560 127.0.0.1:1521 ESTABLISHED 46995/sqlplus keepalive (1.56/0/4)
$ netstat -npo | grep 2560
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:1521 127.0.0.1:2560 ESTABLISHED 46997/oraclebook keepalive (0.93/0/4)
tcp 0 0 127.0.0.1:2560 127.0.0.1:1521 ESTABLISHED 46995/sqlplus keepalive (0.89/0/4)
[oracle@gxqyydg4 IP=100.78 ~/hrp430/testx ] $ netstat -npo | grep 2560
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
--//第3个参数4。
$ netstat -npo | grep 2560
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
--//连接已经断开。
10:55:03 SCOTT@book> select sysdate from dual ;
select sysdate from dual
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 46997
Session ID: 58 Serial number: 585
--//直接断开。
总结：
--//https://blog.dbi-services.com/sqlnet-expire_time-and-enablebroken/
In summary:
On the server, the keep-alive is always enabled and SQLNET.EXPIRE_TIME is used to reduce the tcp_keepalive_time defined
by the system, because it is probably too long.
--//在服务器上，始终启用keep-alive和SQLNET.EXPIRE_TIME是用来减少tcp_keepalive_time定义的通过这个系统，因为它可能太长了。
On the client, the keep-alive is enabled only when (ENABLE=BROKEN) is in the connection description, and uses the
tcp_keepalive_time from the system. Without it, the broken connection will be detected only when attempting a user call.
--//在客户端上，只有在连接描述中(ENABLE=BROKEN)并使用来自系统的tcp_keepalive_time。没有它，只有在尝试用户调用时才会检测
--//到中断的连接。
Setting SQLNET.EXPIRE_TIME to a few minutes (like 10) is a good idea because you don't want to keep resources and locks
on the server when a simple ping can ensure that the connection is lost and we have to rollback. If we don't, then the
dead connections may disappear only after 2 hours and 12 minutes (the idle time + the probes). On the client-side, it is
also a good idea to add (ENABLE=BROKEN) so that idle sessions that have lost contact have a chance to know it before
trying to use them. This is a performance gain if it helps to avoid sending a "select 1 from dual" each time you grab a
connection from the pool
--//设置SQLNET.EXPIRE_TIME。把时间设置到几分钟(比如10分钟)是个好主意，因为你不想保留资源和锁在服务器上，当一个简单的ping
--//可以确保连接丢失时，我们必须回滚。如果我们没有，那么只有在2小时12分钟后(探针的空闲时间)，死连接才可能消失。在客户端
--//，它是还有一个好主意是添加(Enable=Broken)，这样那些失去联系的空闲会话就有机会在之前了解它试图使用它们。这是一个性能
--//增益，如果它有助于避免发送一个"select 1 from dual"，每次你抓住一个从连接池.
And, most important: the documentation is imprecise, which means that the behavior can change without notification. This
is a test on specific OS, specific driver, specific version,… Do not take the results from this post, but now you know
how to check in your environment.
--//而且，最重要的是：文档是不精确的，这意味着行为可以在没有通知的情况下改变。这个是对特定操作系统、特定驱动程序、特定版
--//本的测试，...不要接受这篇文章的结果，但现在你知道了如何检查你的环境。
--//换一句话设置SQLNET.EXPIRE_TIME 和在连接串中加入(Enable=Broken)，是一个不错的方法。
--//写的有点乱。许多概念不是很清楚，不知道别人能看懂吗？^_^ 。
--//补充测试：
--//如果没有(ENABLE=BROKEN).
$ seq 100 | xargs -IQ bash -c "netstat -npo 2>/dev/null | grep 6162| egrep 'oraclebook|sqlplus' ;sleep 1"
tcp 0 0 127.0.0.1:6162 127.0.0.1:1521 ESTABLISHED 49543/sqlplus off (0.00/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6162 ESTABLISHED 49545/oraclebook keepalive (8.11/0/0)
tcp 0 0 127.0.0.1:6162 127.0.0.1:1521 ESTABLISHED 49543/sqlplus off (0.00/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6162 ESTABLISHED 49545/oraclebook keepalive (7.08/0/0)
tcp 0 0 127.0.0.1:6162 127.0.0.1:1521 ESTABLISHED 49543/sqlplus off (0.00/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6162 ESTABLISHED 49545/oraclebook keepalive (6.06/0/0)
tcp 0 0 127.0.0.1:6162 127.0.0.1:1521 ESTABLISHED 49543/sqlplus off (0.00/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6162 ESTABLISHED 49545/oraclebook keepalive (5.04/0/0)
tcp 0 0 127.0.0.1:6162 127.0.0.1:1521 ESTABLISHED 49543/sqlplus off (0.00/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6162 ESTABLISHED 49545/oraclebook keepalive (4.02/0/0)
tcp 0 0 127.0.0.1:6162 127.0.0.1:1521 ESTABLISHED 49543/sqlplus off (0.00/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6162 ESTABLISHED 49545/oraclebook keepalive (2.99/0/0)
^C
--//主意看最后一列。sqlplus那行 显示的是off。
--//如果设置(ENABLE=BROKEN).
$ seq 100 | xargs -IQ bash -c "netstat -npo 2>/dev/null | grep 6079| egrep 'oraclebook|sqlplus' ;sleep 1"
tcp 0 0 127.0.0.1:6079 127.0.0.1:1521 ESTABLISHED 48858/sqlplus keepalive (3.29/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6079 ESTABLISHED 48860/oraclebook keepalive (3.35/0/0)
tcp 0 0 127.0.0.1:6079 127.0.0.1:1521 ESTABLISHED 48858/sqlplus keepalive (2.27/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6079 ESTABLISHED 48860/oraclebook keepalive (2.33/0/0)
tcp 0 0 127.0.0.1:6079 127.0.0.1:1521 ESTABLISHED 48858/sqlplus keepalive (1.24/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6079 ESTABLISHED 48860/oraclebook keepalive (1.31/0/0)
tcp 0 0 127.0.0.1:6079 127.0.0.1:1521 ESTABLISHED 48858/sqlplus keepalive (0.22/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6079 ESTABLISHED 48860/oraclebook keepalive (0.29/0/0)
tcp 0 0 127.0.0.1:6079 127.0.0.1:1521 ESTABLISHED 48858/sqlplus keepalive (9.21/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6079 ESTABLISHED 48860/oraclebook keepalive (9.28/0/0)
tcp 0 0 127.0.0.1:6079 127.0.0.1:1521 ESTABLISHED 48858/sqlplus keepalive (8.19/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6079 ESTABLISHED 48860/oraclebook keepalive (8.26/0/0)
tcp 0 0 127.0.0.1:6079 127.0.0.1:1521 ESTABLISHED 48858/sqlplus keepalive (7.17/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6079 ESTABLISHED 48860/oraclebook keepalive (7.23/0/0)
tcp 0 0 127.0.0.1:6079 127.0.0.1:1521 ESTABLISHED 48858/sqlplus keepalive (6.15/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6079 ESTABLISHED 48860/oraclebook keepalive (6.21/0/0)
tcp 0 0 127.0.0.1:6079 127.0.0.1:1521 ESTABLISHED 48858/sqlplus keepalive (5.13/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6079 ESTABLISHED 48860/oraclebook keepalive (5.19/0/0)
tcp 0 0 127.0.0.1:6079 127.0.0.1:1521 ESTABLISHED 48858/sqlplus keepalive (4.10/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6079 ESTABLISHED 48860/oraclebook keepalive (4.17/0/0)
tcp 0 0 127.0.0.1:6079 127.0.0.1:1521 ESTABLISHED 48858/sqlplus keepalive (3.08/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6079 ESTABLISHED 48860/oraclebook keepalive (3.14/0/0)
tcp 0 0 127.0.0.1:6079 127.0.0.1:1521 ESTABLISHED 48858/sqlplus keepalive (2.06/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6079 ESTABLISHED 48860/oraclebook keepalive (2.12/0/0)
tcp 0 0 127.0.0.1:6079 127.0.0.1:1521 ESTABLISHED 48858/sqlplus keepalive (1.04/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6079 ESTABLISHED 48860/oraclebook keepalive (1.10/0/0)
tcp 0 0 127.0.0.1:6079 127.0.0.1:1521 ESTABLISHED 48858/sqlplus keepalive (0.01/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:6079 ESTABLISHED 48860/oraclebook keepalive (0.08/0/0)
^C
[20200218]ENABLE=BROKEN在连接串中.txt
-
lfree
2020-02-18 16:06:22 -
Oracle
- 原创