[20260611]SQLNET.EXPIRE_TIME的干系标题.txt
SQLNET.EXPIRE_TIME的干系标题.txt--//SQLNET.EXPIRE_TIME 是 Oracle Net 的一个参数，用于 检测死毗连(Dead Connection Detection, DCD)。它通过定期向空闲毗连发
--//送探测包，来判定毗连是否仍旧有效。
--//如果客户端无相应，服务端会主动关闭该毗连，开释干系资源,重要用于防止防火墙/负载均衡器因长时间空闲而堵截毗连，或整理已
--//瓦解客户端遗留的毗连.
--//oracle 12c之前DCD使用TNS包来"ping"客户端，而且依赖底层TCP堆栈，这偶尔大概必要更长的时间。现在，在12c中，这种环境已经
--//改变，DCD探针由TCP Stack实现。DCD探测现在将使用TCP KEEPALIVE套接字选项来查抄毗连是否仍旧可用。也就是从12c开始DCD的监
--//测使用TCP KEEPALIVE套接字选项来查抄毗连。它的设置通过相应目次的sqlnet.ora文件加入如下内容实现：
SQLNET.EXPIRE_TIME = NN
--//NN的单位是时间。实际上不设置也可以实现雷同功能。只不外缺省内核参数设置的net.ipv4.tcp_keepalive_time是7200，单位秒。
--//时间有点长，不得当许多生产体系场所。缺省设置如下：
net.ipv4.tcp_keepalive_time = 7200
net.ipv4.tcp_keepalive_intvl = 75
net.ipv4.tcp_keepalive_probes = 9
--//固然可以通过手工设置，符合业务需求，确定影响全局内核参数设置。
# grep -v "^#" /etc/sysctl.conf
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_intvl = 60
net.ipv4.tcp_keepalive_probes = 5
--//设置SQLNET.EXPIRE_TIME = NN，仅仅针对oracle的毗连使用。11g之前的方式估计使用者越来越少，我从条件过，11g下要设置
--//SQLNET.EXPIRE_TIME必须设置网络断开的时间除以2，更加安全一些。
--//但是早期oracle设置SQLNET.EXPIRE_TIME仅仅针对服务器端有效，也就是服务端主动探测客户端.如果客户端设置
--//SQLNET.EXPIRE_TIME,客户端不会有效,也就是客户端不会主动探测服务端.
--//前段时间看链接 https://www.dbi-services.com/blog/oracle-database-26ai-client-and-sqlnet-expire_time/提到26ai已经支持
--//客户端设置支持TCP KEEPALIVE特性,对方测试使用19c client还不支持该特性.当时想大概21c已经支持这个特性,一段时间变乱太多
--//忘记测试,本日恰好有空测试看看,趁便做一个概括总结.
1.环境:
SYS@book> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.测试前准备:
--//内核干系参数设置如下:
# grep keep /etc/sysctl.d/99-sysctl.conf
net.ipv4.tcp_keepalive_time = 60
net.ipv4.tcp_keepalive_intvl = 70
net.ipv4.tcp_keepalive_probes = 3
# sysctl -p /etc/sysctl.d/99-sysctl.conf
net.ipv4.tcp_keepalive_time = 60
net.ipv4.tcp_keepalive_intvl = 70
net.ipv4.tcp_keepalive_probes = 3
# strings -1 -f /proc/sys/net/ipv4/tcp_keepalive*
/proc/sys/net/ipv4/tcp_keepalive_intvl: 70
/proc/sys/net/ipv4/tcp_keepalive_probes: 3
/proc/sys/net/ipv4/tcp_keepalive_time: 60
$ grep -i "SQLNET.EXPIRE_TIME" /u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora
SQLNET.EXPIRE_TIME=5
3.测试:
--//测试前简单阐明我的测试全部在服务端举行,应该不影响测试效果.
SCOTT@book01p> @ i
USERNAME INST_NAME HOST_NAME I# SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR
-------- ------------ --------- --- ----- -------- ---------- -------- ---------- ----- ---- ---------------- ----------------
SCOTT BOOK01P-book centtest 1 313 60893 21.0.0.0.0 20260611 4224 63 4222 000000007EA17490 000000007E5AB2C0
--//记下进程号4224,sqlplus进程号对应4222(也就是client端)
# netstat -tnop | egrep "Active|Proto|4222|4224"
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:43195 127.0.0.1:1521 ESTABLISHED 4222/sqlplus keepalive (247.23/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:43195 ESTABLISHED 4224/oraclebook keepalive (247.23/0/0)
--//注:netstat要加入-p选择,以root用户实行,否则无法体现过滤进程号.本身还是不熟悉ss的使用.
--//看末了1列timer,2行都出现keepalive,阐明21c开始client也支持SQLNET.EXPIRE_TIME设置.
--//还可以看出oracle优先选择sqlnet.ora的SQLNET.EXPIRE_TIME设置.
$ grep -i "SQLNET.EXPIRE_TIME" /u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora
#SQLNET.EXPIRE_TIME=5
--//重复前面测试,过程略.
# netstat -tnop | egrep "Active|Proto|4311|4309"
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:1521 127.0.0.1:43197 ESTABLISHED 4311/oraclebook keepalive (21.56/0/0)
tcp 0 0 127.0.0.1:43197 127.0.0.1:1521 ESTABLISHED 4309/sqlplus off (0.00/0/0)
--//可以发现仅仅服务端有效,使用的是内核参数tcp_keepalive_time.而客户端不支持tcp keepalive.
3.继承:
--//修改服务端设置:
$ grep -i "SQLNET.EXPIRE_TIME" /u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora
SQLNET.EXPIRE_TIME=5
--//客户端使用windows体系,设置client 版本21c,相应sqlnet.ora加入:
SQLNET.EXPIRE_TIME=1
SCOTT@book01p> @ i
USERNAME INST_NAME HOST_NAME I# SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SCOTT BOOK01P-book centtest 1 110 33464 21.0.0.0.0 20260611 4492 61 2356:5596 000000007E80BEC8 000000007E5A86B0
# netstat -tnop | egrep "Active|Proto|4492"
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.56.101:1521 192.168.56.1:49678 ESTABLISHED 4492/oraclebook keepalive (256.07/0/0)
--//仅仅能看到服务端设置.
--//windows端netstat下令不支持检察timer.在服务端通过tcpdump观察.
# tcpdump -nni enp0s8 'port 49678 and tcp==tcp-ack'
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on enp0s8, link-type EN10MB (Ethernet), capture size 65535 bytes
16:20:46.057782 IP 192.168.56.1.49678 > 192.168.56.101.1521: Flags [.], seq 3831407993:3831407994, ack 116756250, win 8212, length 1
16:20:46.057895 IP 192.168.56.101.1521 > 192.168.56.1.49678: Flags [.], ack 1, win 514, options , length 0
16:21:46.058035 IP 192.168.56.1.49678 > 192.168.56.101.1521: Flags [.], seq 0:1, ack 1, win 8212, length 1
16:21:46.058317 IP 192.168.56.101.1521 > 192.168.56.1.49678: Flags [.], ack 1, win 514, options , length 0
--//可以发现client支持tcp keepalive.由于client设置SQLNET.EXPIRE_TIME=1,隔断60秒发送一个tcp keepalive信息,从客户端起首发出。
--//可以发现21c开始,客户端与服务端设置SQLNET.EXPIRE_TIME都支持tcp keepalive特性监测DCD.
4.疑问:
--//在我从前学习中,对客户端不支持SQLNET.EXPIRE_TIME存在疑问,监测总是从服务端发起,对于一些不支持状态防火墙会壅闭从服务器
--//发起的毗连,从而使监测失效,固然在实际过程中我从来没有遇到.
--//我曾经得到的解答就是服务器很少死机,客户端常常死机,如许有服务端发起监测才故意义,而客户端死机就不存在发送监测的大概性.
--//总之从客户端支持tcp keepalive监测至少多一个选择.别的也可以通过编程设置支持这个特性,已经超出本文的范围.
--//那么早期版本怎样实现这个特性呢,可以在毗连串中加入ENABLE=BROKEN,如许客户端具有这个特性,但是使用内核参数设置.
--//发起tcp_keepalive_time设置不要过大.
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_intvl = 60
net.ipv4.tcp_keepalive_probes = 5
--//windows版本修改注册表，
> cat keepalive.reg
REGEDIT4
"KeepAliveTime"=dword:493e0
"KeepAliveInterval"=dword:2710
--//0x493e0 = 300000 ,单位是毫秒,相当于30秒.
--//0x2710 = 10000,10秒.
--//windows似乎没有雷同keepalive_probes的参数，缺省10次。
--//简单测试毗连串中加入ENABLE=BROKEN的环境.
--//注解SQLNET.EXPIRE_TIME=5
$ grep -i "SQLNET.EXPIRE_TIME" /u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora
#SQLNET.EXPIRE_TIME=5
--//创建毗连串BOOK01PB如下,加入ENABLE=BROKEN:
$ grep -A11 ^BOOK01P /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora
BOOK01P =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = book01p)
)
)
BOOK01PB =
(DESCRIPTION =
(ENABLE=BROKEN)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = book01p)
)
)
$ sqlplus -s -l /nolog
conn scott/book@book01pb
@i
USERNAME INST_NAME HOST_NAME I# SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR
-------- ------------ --------- --- ----- -------- ---------- -------- ---------- ----- ----- ---------------- ----------------
SCOTT BOOK01P-book centtest 1 16 54397 21.0.0.0.0 20260611 5186 64 5184 000000007E719908 000000007E5AC8C8
# netstat -tnop | egrep "Active|Proto|5186|5184"
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:43205 127.0.0.1:1521 ESTABLISHED 5184/sqlplus keepalive (32.11/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:43205 ESTABLISHED 5186/oraclebook keepalive (32.11/0/0)
--//使用毗连串book01pb,client,server都支持tcp keepalive.使用内核参数tcp_keepalive_time.
conn scott/book@book01p
@i
USERNAME INST_NAME HOST_NAME I# SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR
-------- ------------ --------- --- ----- -------- ---------- -------- ---------- ----- ----- ---------------- ----------------
SCOTT BOOK01P-book centtest 1 16 65508 21.0.0.0.0 20260611 5221 64 5184 000000007E719908 000000007E5AC8C8
# netstat -tnop | egrep "Active|Proto|5221|5184"
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:43207 127.0.0.1:1521 ESTABLISHED 5184/sqlplus off (0.00/0/0)
tcp 0 0 127.0.0.1:1521 127.0.0.1:43207 ESTABLISHED 5221/oraclebook keepalive (34.89/0/0)
--//使用毗连串book01p,server支持tcp keepalive.使用内核参数tcp_keepalive_time,而client端不支持。
--//增补设置SQLNET.EXPIRE_TIME，使用book01pb毗连串的环境。
$ grep -i "SQLNET.EXPIRE_TIME" /u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora
SQLNET.EXPIRE_TIME=5
$ sqlplus -s -l /nolog
connect scott/book@book01pb
@i
USERNAME INST_NAME HOST_NAME I# SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR
-------- ------------ --------- --- ----- -------- ---------- -------- ---------- ----- ---- ---------------- ----------------
SCOTT BOOK01P-book centtest 1 228 19309 21.0.0.0.0 20260612 3712 42 3710 000000007E93C1C8 000000007E58E418
# netstat -tnop | egrep "Active|Proto|3712|3710"
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:1521 127.0.0.1:32567 ESTABLISHED 3712/oraclebook keepalive (281.71/0/0)
tcp 0 0 127.0.0.1:32567 127.0.0.1:1521 ESTABLISHED 3710/sqlplus keepalive (281.71/0/0)
--//client,server都支持tcp keepalive,可以发现优先使用SQLNET.EXPIRE_TIME的设置。
5.收尾还原干系设置.
--//过程略.
$ grep "SQLNET.EXPIRE_TIME" /u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora
SQLNET.EXPIRE_TIME=5
免责声明：如果侵犯了您的权益，请联系站长及时删除侵权内容，谢谢合作！ qidao123.com:ToB企服之家，中国第一个企服评测及软件市场,开放入驻,技术点评得现金.
页:
[1]