[20210201]dblink建立连接串使用ENABLE=BROKEN.txt

--//我们应用建立dblink一般使用ezconnect方式,这样的有点就是不用编辑tnsnames.ora文件,缺点是克隆移植时会出现会导致一些问题.
--//而且现在很多dblink需要连接外网,经常断开,一般dcd检测需要修改sqlnet.ora文件,加入SQLNET.EXPIRE_TIME = 1都有可能断开.
--//我更主张采用设置内核参数的方式,另外我想如果在客户端连接串加入ENABLE=BROKEN,两边同时检测也许连接更不容易断开.
--//参考链接:http://blog.itpub.net/267265/viewspace-2676093/
--//自己在测试环境测试看看.

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

--//注我的服务端与客户端都注解sqlnet.ora的SQLNET.EXPIRE_TIME.采用tcp keepalive特性管理网络链接.
--//仅仅需要在/etc/sysctl.conf配置
net.ipv4.tcp_keepalive_time = 60
net.ipv4.tcp_keepalive_intvl = 10
net.ipv4.tcp_keepalive_probes = 4

2.建立dblink使用:
CREATE PUBLIC DATABASE LINK TEST040
 CONNECT TO SCOTT
 IDENTIFIED BY book
 USING '192.168.100.40:1521/bookdg';

CREATE PUBLIC DATABASE LINK test40
CONNECT TO scott
 IDENTIFIED BY book
 USING '(DESCRIPTION=(ENABLE=BROKEN)(CONNECT_DATA=(SERVICE_NAME=bookdg)(SERVER=DEDICATED))(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.40)(PORT=1521)))';

3.测试:
SCOTT@book> select sysdate from dual@test40;
SYSDATE
-------------------
2021-02-01 10:52:12

SCOTT@book> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        58       9069 30075                    DEDICATED 30076       28        150 alter system kill session '58,9069' immediate;
--//SPID=30075.

# seq 100 | xargs -IQ bash -c "netstat -tonp | grep 30076;sleep 1"
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name    Timer
tcp        0      0 192.168.100.78:64665        192.168.100.40:1521         ESTABLISHED 30076/oraclebook    keepalive (9.53/0/0)
tcp        0      0 192.168.100.78:64665        192.168.100.40:1521         ESTABLISHED 30076/oraclebook    keepalive (8.51/0/0)
tcp        0      0 192.168.100.78:64665        192.168.100.40:1521         ESTABLISHED 30076/oraclebook    keepalive (7.49/0/0)
tcp        0      0 192.168.100.78:64665        192.168.100.40:1521         ESTABLISHED 30076/oraclebook    keepalive (6.46/0/0)
tcp        0      0 192.168.100.78:64665        192.168.100.40:1521         ESTABLISHED 30076/oraclebook    keepalive (5.44/0/0)
tcp        0      0 192.168.100.78:64665        192.168.100.40:1521         ESTABLISHED 30076/oraclebook    keepalive (4.42/0/0)
tcp        0      0 192.168.100.78:64665        192.168.100.40:1521         ESTABLISHED 30076/oraclebook    keepalive (3.39/0/0)
tcp        0      0 192.168.100.78:64665        192.168.100.40:1521         ESTABLISHED 30076/oraclebook    keepalive (2.37/0/0)
^C
--//注意现在客户端连接支持tcp keepalive. 注:题头我手工加入的.
--//知道端口号192.168.100.78:64665
--//在192.168.100.40上执行.
# seq 100 | xargs -iQ bash -c "netstat -tonp | grep 192.168.100.78:64665;sleep 1"
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name    Timer
tcp        0      0 ::ffff:192.168.100.40:1521  ::ffff:192.168.100.78:64665 ESTABLISHED 32707/oraclebookdg  keepalive (4.54/0/0)
tcp        0      0 ::ffff:192.168.100.40:1521  ::ffff:192.168.100.78:64665 ESTABLISHED 32707/oraclebookdg  keepalive (3.49/0/0)
tcp        0      0 ::ffff:192.168.100.40:1521  ::ffff:192.168.100.78:64665 ESTABLISHED 32707/oraclebookdg  keepalive (2.43/0/0)
tcp        0      0 ::ffff:192.168.100.40:1521  ::ffff:192.168.100.78:64665 ESTABLISHED 32707/oraclebookdg  keepalive (1.38/0/0)
tcp        0      0 ::ffff:192.168.100.40:1521  ::ffff:192.168.100.78:64665 ESTABLISHED 32707/oraclebookdg  keepalive (0.33/0/0)
--//注这台机器linux版本太低,不支持-I选择,必须使用小写-i.

4.测试连接串没有ENABLE=BROKEN的情况:

SCOTT@book> @ spid

       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        15      13573 30215                    DEDICATED 30216       25         99 alter system kill session '15,13573' immediate;
--//SPID=30216

SCOTT@book> select sysdate from dual@test040;
SYSDATE
-------------------
2021-02-01 11:00:11

#  seq 100 | xargs -IQ bash -c "netstat -tonp | grep 30216;sleep 1"
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name    Timer
tcp        0      0 192.168.100.78:64761        192.168.100.40:1521         ESTABLISHED 30216/oraclebook    off (0.00/0/0)
tcp        0      0 192.168.100.78:64761        192.168.100.40:1521         ESTABLISHED 30216/oraclebook    off (0.00/0/0)
tcp        0      0 192.168.100.78:64761        192.168.100.40:1521         ESTABLISHED 30216/oraclebook    off (0.00/0/0)
tcp        0      0 192.168.100.78:64761        192.168.100.40:1521         ESTABLISHED 30216/oraclebook    off (0.00/0/0)
tcp        0      0 192.168.100.78:64761        192.168.100.40:1521         ESTABLISHED 30216/oraclebook    off (0.00/0/0)
tcp        0      0 192.168.100.78:64761        192.168.100.40:1521         ESTABLISHED 30216/oraclebook    off (0.00/0/0)
tcp        0      0 192.168.100.78:64761        192.168.100.40:1521         ESTABLISHED 30216/oraclebook    off (0.00/0/0)
^C
--//注意看timer=off.
--//知道端口号92.168.100.78:64761
--//在192.168.100.40上执行.
# seq 100 | xargs -iQ bash -c "netstat -tonp | grep 192.168.100.78:64761;sleep 1"
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name    Timer
tcp        0      0 ::ffff:192.168.100.40:1521  ::ffff:192.168.100.78:64761 ESTABLISHED 686/oraclebookdg    keepalive (17.64/0/0)
tcp        0      0 ::ffff:192.168.100.40:1521  ::ffff:192.168.100.78:64761 ESTABLISHED 686/oraclebookdg    keepalive (16.58/0/0)
tcp        0      0 ::ffff:192.168.100.40:1521  ::ffff:192.168.100.78:64761 ESTABLISHED 686/oraclebookdg    keepalive (15.53/0/0)
tcp        0      0 ::ffff:192.168.100.40:1521  ::ffff:192.168.100.78:64761 ESTABLISHED 686/oraclebookdg    keepalive (14.48/0/0)
tcp        0      0 ::ffff:192.168.100.40:1521  ::ffff:192.168.100.78:64761 ESTABLISHED 686/oraclebookdg    keepalive (13.42/0/0)

5.总结:
--//可以发现如果两边机器都支持TCP keepalive,dblink连接串包含ENABLE=BROKEN就可以控制链接不容易断开.而且这样的好处有时候有
--//一端我没有权限,要对方改正时间周期很长,我可以修改我这端支持采用tcp keepalive,配置连接串支持ENABLE=BROKEN.
--//相当于client端支持tcp keepalive一样能达到很好的效果.