[20180124]測試SQLNET.EXPIRE_TIME引數3

[20180124]測試SQLNET.EXPIRE_TIME引數3.txt

--//昨天測試SQLNET.EXPIRE_TIME引數時,連結如下:
http://blog.itpub.net/267265/viewspace-2150431/
http://blog.itpub.net/267265/viewspace-2150434/

--//兩次測試發現發出網路監測包的間隔時間是862秒.997秒.按照以前的理解最大間隔是2*SQLNET.EXPIRE_TIME引數值,而我定義
--//SQLNET.EXPIRE_TIME=5,這樣最大間隔是5*2*60 = 600秒.決定對以前的測試重新驗證看看.

--//更正說明:實際上這個引數要修改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探究.
--//也就是我大概知道為什麼前面的測試實際上讀取的是oracle使用者的$ORACLE_HOME/network/admin/sqlnet.ora.

1.環境:
--//資料庫伺服器SQLNET.EXPIRE_TIME=1.注意如果修改引數,必須重啟監聽才生效.
$ grep -i SQLNET.EXPIRE_TIME sqlnet.ora
SQLNET.EXPIRE_TIME = 1

--//而我在執行sqlplus連線資料庫前,先啟動如下命令:
# tcpdump -i eth0 host 192.168.98.6 and not port 22 -nn -vv

--//在client端執行:
C:\> sqlplus scott/book@78

--//server端檢查:
# tcpdump -i eth0 host 192.168.98.6 and not port 22 -nn -vv
tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 96 bytes
15:16:22.398597 IP (tos 0x0, ttl 127, id 18198, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.98.6.52445 > 192.168.100.78.1521: S, cksum 0x2eb3 (correct), 2963559754:2963559754(0) win 8192
15:16:22.398793 IP (tos 0x0, ttl  64, id 0, offset 0, flags [DF], proto: TCP (6), length: 52) 192.168.100.78.1521 > 192.168.98.6.52445: S, cksum 0x47cc (incorrect (-> 0xa7a8), 266362380:266362380(0) ack 2963559755 win 14600
--//....client 執行sqlplus 連線發出的包. 太長截斷...
15:16:22.495949 IP (tos 0x0, ttl  64, id 17953, offset 0, flags [DF], proto: TCP (6), length: 57) 192.168.100.78.1521 > 192.168.98.6.52445: P, cksum 0x47d1 (incorrect (-> 0xd829), 6608:6625(17) ack 7936 win 330
15:16:22.692196 IP (tos 0x0, ttl 127, id 18244, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.52445 > 192.168.100.78.1521: ., cksum 0xa8f0 (correct), 7936:7936(0) ack 6625 win 16307
--//不執行任何命令.等.....

15:18:22.442904 IP (tos 0x0, ttl  64, id 17954, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.78.1521 > 192.168.98.6.52445: P, cksum 0x47ca (incorrect (-> 0xe12d), 6625:6635(10) ack 7936 win 330
15:18:22.642793 IP (tos 0x0, ttl 127, id 20169, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.52445 > 192.168.100.78.1521: ., cksum 0xa8e9 (correct), 7936:7936(0) ack 6635 win 16304
15:19:22.453071 IP (tos 0x0, ttl  64, id 17955, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.78.1521 > 192.168.98.6.52445: P, cksum 0x47ca (incorrect (-> 0xe123), 6635:6645(10) ack 7936 win 330
15:19:22.653064 IP (tos 0x0, ttl 127, id 21172, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.52445 > 192.168.100.78.1521: ., cksum 0xa8e1 (correct), 7936:7936(0) ack 6645 win 16302
15:20:22.463420 IP (tos 0x0, ttl  64, id 17956, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.78.1521 > 192.168.98.6.52445: P, cksum 0x47ca (incorrect (-> 0xe119), 6645:6655(10) ack 7936 win 330
15:20:22.663614 IP (tos 0x0, ttl 127, id 22084, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.52445 > 192.168.100.78.1521: ., cksum 0xa8da (correct), 7936:7936(0) ack 6655 win 16299
15:21:22.473592 IP (tos 0x0, ttl  64, id 17957, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.78.1521 > 192.168.98.6.52445: P, cksum 0x47ca (incorrect (-> 0xe10f), 6655:6665(10) ack 7936 win 330
15:21:22.673418 IP (tos 0x0, ttl 127, id 24477, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.52445 > 192.168.100.78.1521: ., cksum 0xa8d2 (correct), 7936:7936(0) ack 6665 win 16297

--//可以發現第一次間隔2分鐘,以後間隔1分鐘. 突然我明白為什麼第1次間隔2分鐘.透過下面的測試說明問題:

2.繼續測試:
# tcpdump -i eth0 host 192.168.98.6 and not port 22 -nn -vv
..
15:29:22.545029 IP (tos 0x0, ttl  64, id 17965, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.78.1521 > 192.168.98.6.52445: P, cksum 0x47ca (incorrect (-> 0xe0bf), 6735:6745(10) ack 7936 win 330
15:29:22.748454 IP (tos 0x0, ttl 127, id 32187, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.52445 > 192.168.100.78.1521: ., cksum 0xa896 (correct), 7936:7936(0) ack 6745 win 16277
15:30:22.555223 IP (tos 0x0, ttl  64, id 17966, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.78.1521 > 192.168.98.6.52445: P, cksum 0x47ca (incorrect (-> 0xe0b5), 6745:6755(10) ack 7936 win 330
15:30:22.758630 IP (tos 0x0, ttl 127, id 328, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.52445 > 192.168.100.78.1521: ., cksum 0xa88f (correct), 7936:7936(0) ack 6755 win 16274
--//等待出現監測包出現時client端執行如下:
SCOTT@78> select sysdate from dual ;
SYSDATE
-------------------
2018-01-24 15:30:26
--//下面5個是執行select sysdate from dual ;相關的包.時間也是可以對上的.
15:30:26.372134 IP (tos 0x0, ttl 127, id 388, offset 0, flags [DF], proto: TCP (6), length: 303) 192.168.98.6.52445 > 192.168.100.78.1521: P 7936:8199(263) ack 6755 win 16274
15:30:26.372644 IP (tos 0x0, ttl  64, id 17967, offset 0, flags [DF], proto: TCP (6), length: 308) 192.168.100.78.1521 > 192.168.98.6.52445: P 6755:7023(268) ack 8199 win 330
15:30:26.373110 IP (tos 0x0, ttl 127, id 389, offset 0, flags [DF], proto: TCP (6), length: 61) 192.168.98.6.52445 > 192.168.100.78.1521: P, cksum 0x86be (correct), 8199:8220(21) ack 7023 win 16207
15:30:26.373228 IP (tos 0x0, ttl  64, id 17968, offset 0, flags [DF], proto: TCP (6), length: 142) 192.168.100.78.1521 > 192.168.98.6.52445: P 7023:7125(102) ack 8220 win 330
15:30:26.577899 IP (tos 0x0, ttl 127, id 396, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.52445 > 192.168.100.78.1521: ., cksum 0xa65d (correct), 8220:8220(0) ack 7125 win 16182
--//繼續等...
15:32:22.575500 IP (tos 0x0, ttl  64, id 17969, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.78.1521 > 192.168.98.6.52445: P, cksum 0x47ca (incorrect (-> 0xde1d), 7125:7135(10) ack 8220 win 330
15:32:22.773247 IP (tos 0x0, ttl 127, id 4182, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.52445 > 192.168.100.78.1521: ., cksum 0xa656 (correct), 8220:8220(0) ack 7135 win 16179
15:33:22.585665 IP (tos 0x0, ttl  64, id 17970, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.78.1521 > 192.168.98.6.52445: P, cksum 0x47ca (incorrect (-> 0xde13), 7135:7145(10) ack 8220 win 330
15:33:22.788446 IP (tos 0x0, ttl 127, id 5189, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.52445 > 192.168.100.78.1521: ., cksum 0xa64e (correct), 8220:8220(0) ack 7145 win 16177

--//可以看出規律,測試環境是SQLNET.EXPIRE_TIME=1.
--//也就是在15:30:22 - 15:31:22 (1分鐘時間內如果client端發起連線,通俗講執行sql語句),這個1分鐘內不會發出從伺服器端發監測包.
--//這樣到下1分鐘(15:31:22 - 15:32:22),如果client沒有發起連線,再發出監測包.這樣看到的間隔就是接近2分鐘.

--//再通俗講如果在間隔時間內如果有client端發出資料包,服務端的監測包就不會發,這樣看到的情況最大間隔就是
--//2 * SQLNET.EXPIRE_TIME.實際上我以前一直以為最大間隙是僅僅出現在開始第1次實際上可以出現任何時間段.

--//自己總算理解為什麼SQLNET.EXPIRE_TIME=N,最大間隔是2 * N 分鐘.
--//也就是如果網路在沒有任何發包的情況下20分鐘斷開,你必須設定SQLNET.EXPIRE_TIME<=10的原因.

--//再補充例子:
# tcpdump -i eth0 host 192.168.98.6 and not port 22 -nn -vv
tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 96 bytes
..

15:50:22.738630 IP (tos 0x0, ttl  64, id 17989, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.78.1521 > 192.168.98.6.52445: P, cksum 0x47ca (incorrect (-> 0xd83b), 8025:8035(10) ack 8826 win 330
15:50:22.941540 IP (tos 0x0, ttl 127, id 25143, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.52445 > 192.168.100.78.1521: ., cksum 0x9fe7 (correct), 8826:8826(0) ack 8035 win 16320

--//在client在2018-01-24 15:51:17執行如下:
SCOTT@78> select sysdate from dual ;
SYSDATE
-------------------
2018-01-24 15:51:17
----

15:51:17.164205 IP (tos 0x0, ttl 127, id 25908, offset 0, flags [DF], proto: TCP (6), length: 322) 192.168.98.6.52445 > 192.168.100.78.1521: P 8826:9108(282) ack 8035 win 16320
15:51:17.164643 IP (tos 0x0, ttl  64, id 17990, offset 0, flags [DF], proto: TCP (6), length: 308) 192.168.100.78.1521 > 192.168.98.6.52445: P 8035:8303(268) ack 9108 win 330
15:51:17.165043 IP (tos 0x0, ttl 127, id 25909, offset 0, flags [DF], proto: TCP (6), length: 61) 192.168.98.6.52445 > 192.168.100.78.1521: P, cksum 0x7503 (correct), 9108:9129(21) ack 8303 win 16253
15:51:17.165165 IP (tos 0x0, ttl  64, id 17991, offset 0, flags [DF], proto: TCP (6), length: 142) 192.168.100.78.1521 > 192.168.98.6.52445: P 8303:8405(102) ack 9129 win 330
15:51:17.368482 IP (tos 0x0, ttl 127, id 25913, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.52445 > 192.168.100.78.1521: ., cksum 0x9da3 (correct), 9129:9129(0) ack 8405 win 16227
--//等...可以發現15:51:22服務端發出的監測包被忽略..如果到15:52:22時間client沒有發包,服務端再次發出監測包.
15:52:22.758917 IP (tos 0x0, ttl  64, id 17992, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.78.1521 > 192.168.98.6.52445: P, cksum 0x47ca (incorrect (-> 0xd590), 8405:8415(10) ack 9129 win 330
15:52:22.958995 IP (tos 0x0, ttl 127, id 28030, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.52445 > 192.168.100.78.1521: ., cksum 0x9d9b (correct), 9129:9129(0) ack 8415 win 16225
15:53:22.759044 IP (tos 0x0, ttl  64, id 17993, offset 0, flags [DF], proto: TCP (6), length: 50) 192.168.100.78.1521 > 192.168.98.6.52445: P, cksum 0x47ca (incorrect (-> 0xd586), 8415:8425(10) ack 9129 win 330
15:53:22.959345 IP (tos 0x0, ttl 127, id 29625, offset 0, flags [DF], proto: TCP (6), length: 40) 192.168.98.6.52445 > 192.168.100.78.1521: ., cksum 0x9d94 (correct), 9129:9129(0) ack 8425 win 16222

--//但是僅僅理解間隔最大2 * SQLNET.EXPIRE_TIME的情況,還是沒法解析我前面遇到的問題.....