[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)
--//现在还原了。