[20200225]觀察使用TCP keep-alive feature特性2.txt

[20200225]觀察使用TCP keep-alive feature特性2.txt

--//昨天透過netstat檢視keep alive timer引數時，遇到一點疑問，做一個記錄：

1.環境：
--//服務端設定：
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

$ 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: 33

$ netstat -ntop 2>/dev/null
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name    Timer
tcp        0      0 192.168.100.78:1521         192.168.100.78:12349        ESTABLISHED 60047/tnslsnr       keepalive (2.93/0/0)
tcp        0      0 192.168.100.78:12349        192.168.100.78:1521         ESTABLISHED 52283/ora_pmon_book off (0.00/0/0)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
tcp        0      0 192.168.100.78:22           192.168.98.6:56478          ESTABLISHED -                   keepalive (21.87/0/0)

--//可以發現ora_pmon_book程式與tnslsnr程式之間存在網路連線。實際上這個ora_pmon_book程式用來定時註冊監聽服務的。
--//但是有點奇怪是keep alive timer狀態是off，簡單探究看看。

2.首先看看間隔多久註冊監聽服務。

$ grep  "service_update" listener.log | grep "FEB-2020" | head -1
01-FEB-2020 00:00:45 * service_update * book * 0

$ grep  "service_update" listener.log | grep "^25-FEB-2020" | head -100  | awk '{print $1,$2}' >| /tmp/xx1.txt

$ cat /tmp/xx1.txt | xargs -I{} date -d "{}" "+%Y-%m-%d:%T %s" |awk 'NR==1 {a=$1;b=$2} NR>1 {print $1,"-",a,$2-b;a=$1;b=$2}'  | awk '{print $4}' | sort | uniq -c | sort -nr | head
    397 601
    384 30
    329 600
    295 3
    114 9
    111 6
    105 18
    103 12
     93 15
     87 21
--//可以看出大部分是600秒出現，也就是平時沒什麼業務的情況下間隔600秒執行service_update。

3.為什麼keep alive timer狀態是off呢？
$ rlsql scott/book@127.0.0.1:1521/book:DEDICATED
SCOTT@127.0.0.1:1521/book:DEDICATED> @ spid

       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        47        241 14657                    DEDICATED 14659       27        113 alter system kill session '47,241' immediate;

$ netstat -ntop 2>/dev/null
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name    Timer
tcp        0      0 127.0.0.1:59413             127.0.0.1:1521              ESTABLISHED 14657/sqlplus       off (0.00/0/0)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
tcp        0      0 192.168.100.78:1521         192.168.100.78:12349        ESTABLISHED 60047/tnslsnr       keepalive (3.22/0/0)
tcp        0      0 192.168.100.78:12349        192.168.100.78:1521         ESTABLISHED 52283/ora_pmon_book off (0.00/0/0)
tcp        0      0 127.0.0.1:1521              127.0.0.1:59413             ESTABLISHED 14659/oraclebook    keepalive (2.07/0/0)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
tcp        0      0 192.168.100.78:22           192.168.98.6:56478          ESTABLISHED -                   keepalive (5.14/0/0)

--//注意看下劃線，問題在於在連線串時沒有使用(ENABLE=BROKEN)引數。

4.不行無法在監聽檔案中加入引數(ENABLE=BROKEN).
--//我嘗試報錯。

$ netstat -ntop 2>/dev/null
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name    Timer
tcp        0      0 192.168.100.78:1521         192.168.100.78:13015        ESTABLISHED 15151/tnslsnr       keepalive (9.07/0/0)
tcp        0      0 192.168.100.78:22           192.168.98.6:59267          ESTABLISHED -                   keepalive (1.77/0/0)
tcp        0      0 192.168.100.78:22           192.168.98.6:56478          ESTABLISHED -                   keepalive (7.53/0/0)
tcp        0      0 192.168.100.78:13015        192.168.100.78:1521         ESTABLISHED 52283/ora_pmon_book off (0.00/0/0)

$ sqlplus scott/book@"(DESCRIPTION=(ENABLE=BROKEN)(CONNECT_DATA=(SERVICE_NAME=book)(SERVER = DEDICATED))(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))"
SCOTT@book> @ spid

       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
         1        967 15202                    DEDICATED 15204       24         74 alter system kill session '1,967' immediate;

$ netstat -ntop 2>/dev/null | egrep "15204|15202"
tcp        0      0 127.0.0.1:59852             127.0.0.1:1521              ESTABLISHED 15202/sqlplus       keepalive (11.24/0/0)
tcp        0      0 127.0.0.1:1521              127.0.0.1:59852             ESTABLISHED 15204/oraclebook    keepalive (11.31/0/0)

$ netstat -ntop 2>/dev/null
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name    Timer
tcp        0      0 192.168.100.78:1521         192.168.100.78:13015        ESTABLISHED 15151/tnslsnr       keepalive (10.08/0/0)
tcp        0      0 127.0.0.1:59852             127.0.0.1:1521              ESTABLISHED 15202/sqlplus       keepalive (9.34/0/0)
tcp        0      0 127.0.0.1:1521              127.0.0.1:59852             ESTABLISHED 15204/oraclebook    keepalive (9.40/0/0)
tcp        0      0 192.168.100.78:22           192.168.98.6:59267          ESTABLISHED -                   keepalive (17.62/0/0)
tcp        0      0 192.168.100.78:22           192.168.98.6:56478          ESTABLISHED -                   keepalive (19.80/0/0)
tcp        0      0 192.168.100.78:13015        192.168.100.78:1521         ESTABLISHED 52283/ora_pmon_book off (0.00/0/0)
--//如何實現不知道??

5.嘗試修改引數local_listener.

SYS@book> alter system set local_listener='(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))' scope=memory ;
System altered.

$ netstat -ntop 2>/dev/null | egrep "tns|ora_pmon"
tcp        0      0 127.0.0.1:60065             127.0.0.1:1521              ESTABLISHED 15437/ora_pmon_book keepalive (7.72/0/0)
tcp        0      0 127.0.0.1:1521              127.0.0.1:60065             ESTABLISHED 15406/tnslsnr       keepalive (7.72/0/0)

--//OK現在可以了。

SYS@book> alter system set local_listener='' scope=memory ;
System altered.

$ netstat -ntop 2>/dev/null | egrep "tns|ora_pmon"
tcp        0      0 192.168.100.78:13307        192.168.100.78:1521         ESTABLISHED 15437/ora_pmon_book off (0.00/0/0)
tcp        0      0 192.168.100.78:1521         192.168.100.78:13307        ESTABLISHED 15406/tnslsnr       keepalive (22.44/0/0)
--//現在還原了。