[20210201]dblink建立連線串使用ENABLE=BROKEN.txt

[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一樣能達到很好的效果.