ANBOB™

在ORACLE数据库服务器端，如果是专用模式的会话会创建对用的SERVER进程，每个进程会分配一定的内存等操作系统资源来管理进程，所以数据库会话同样会占用数据库资源，如客户端异常断开在客户端成为一个dead session会永远存在，如果客户端没有断开也没有活动就是一个idle session， 如果这个idle session做了一些修改未提交，然后下班或去吃饭、上WC、开会等，这时就会堵塞其他人对相同的数据做修改，这类会话可以叫做idle blocker session. 如果是出于保持长连接的需求，可能不希望firewall中断了网络连接（To avoid firewall dropping “inactive” sessions. ），需要保持在网络连接中有流量；如果是出于探测客户端是否存活的需求，是希望对客户端有个探测功能, 在会话无响应时可以及时关闭连接。

 DCD 2种行为 :
1. If the SQLNET.EXPIRE_TIME is less than the FW connection idle time-out, then the firewall *may* consider this packet as activity, and the idle time-out (firewall blackout) will never happen until both the client and the server processes are alive.

2. If the SQLNET.EXPIRE_TIME (let’s say a little bit higher) than the FW idle limit, then , as soon as the blackout happens , the RDBMS will know and will close the connection.

在自治数据库的时代这些session 都可以被释放或者kill / teminal 终结掉. 下面对不同的session如何被释放

A， 对于dead session

Dead connection detection（DCD）是Oracle网络功能，主要用于客户端关闭其系统电源，或客户端计算机意外崩溃，而又无法正常关闭Oracle数据库连接的环境。

如果客户端计算机因正确断开会话而突然崩溃，则这些会话锁定的资源将继续被锁定，从而导致环境性能问题。由于这种情况可以在任何环境中发生，因此Oracle网络服务提供了DCD的功能，可以在早期检测到这种情况，从而可以快速恢复锁定的资源。

为了解决这种情况并检测死连接，Oracle在网络会话（12c中的tcp）层引入了一个新概念。服务器进程将SQL * Net Probe数据包发送到客户端，以检查在sqlnet.expire_time参数指定的每个固定时间间隔内连接是否仍然可用。如果通过探测数据包的通信失败，则会返回错误，导致服务器进程退出。

如何启用DCD

要在您的环境中启用DCD。DB ORACLE_HOME目录或TNS_ADMIN 环境变量目录下,修改sqlnet.ora文件中设置SQLNET.EXPIRE_TIME参数，然后重新启动侦听器或重新装入侦听器。

其中n是时间间隔（以分钟为单位），在此间隔之后发送DCD的探测数据包。

1. For pre-9.2.0.4 oracle clients you can actually see the null packet of 10 bytes been received every x minutes after this time has elapsed.

2. Oracle Clients version > 9.2.0.4 the DCD packet is sent to the client from the respective database server. The null packets are actually written to the socket , but they are read only when the client becomes active.

3. versions 12.1 and newer The new method of DCD uses the TCP KEEPALIVE socket option.

The previous implementations of DCD uses TNS packets and relies on underlying TCP send failures.  The TCP stack would need to resend and retransmit the probes – leading to a disconnect which could take several minutes. The new method of DCD uses the TCP KEEPALIVE socket option.

There are 3 parameters associated with an operating system’s TCP keepalive (These are Linux parameters, but other operating system have similarly named parameters)

TCP_KEEPALIVE_TIME (the amount of time until the first keepalive packet is sent)
TCP_KEEPCNT(the number of probes to send)
TCP_KEEPINTVL (the interval between keepalive packets)

The sqlnet.ora parameter SQLNET.EXPIRE_TIME now sets the socket option TCP_KEEPALIVE.

If sqlnet.expire_time=1 then TCP_KEEPALIVE will be set to 60 seconds.  The other parameters KEEPINTVL and KEEPCNT get set to 6 and 10 respectively (which is very reasonable).  This means that, at minimum, DCD can only be set to 2 minutes.

LINUX平台是以上的参数单位秒， AIX平台是对应的tcp_keepidle，tcp_keepinit，tcp_keepintvl单位不同，是半秒； HPUX平台对应tcp_keepalive_detached_interval, tcp_keepalive_ext, tcp_keepalive_interval 单位毫秒

Linux

-- /etc/sysctl.conf reboot to take effect  or sysctl -p
net.ipv4.tcp_keepalive_time = 60
net.ipv4.tcp_keepalive_probes = 2
net.ipv4.tcp_keepalive_intvl = 5

1. 距离 TCP 最后一次活动后，等待 tcp_keepalive_time (60s)，之后触发 TCP keepalive 的 probe 行动
2. 如果 probe 没有得到对方回应，则每隔 tcp_keepalive_intvl (5s) 进行一次重试。一共发送 tcp_keepalive_probes (2个) probe.
Probe 实际上是向对方发送一个 ack，这个 ack 包不带实际数据。

如AIX 和HPUX默认都是2小时

AIX

/usr/sbin/no -o tcp_keepidle=120
/usr/sbin/no -o tcp_keepinit=10
/usr/sbin/no -o tcp_keepintvl=50

HPUX

# ndd -h supported|grep keep
    tcp_keepalive_detached_interval - Send keepalive probes for detached TCP
    tcp_keepalive_ext          - Type of keepalive probe behavior for XTI
    tcp_keepalive_interval     - Interval for sending keepalive probes
# ndd -get /dev/tcp tcp_keepalive_detached_interval
120000
# ndd -get /dev/tcp tcp_keepalive_ext
1
# ndd -get /dev/tcp tcp_keepalive_interval
7200000 

注意事项

1, 如果平台不支持socket options for keepalive可以使用老的DCD方法
2，如果需要使用老的DCD方法( sending 10 bytes of TNS data), 需要在sqlnet.ora文件中增加USE_NS_PROBES_FOR_DCD=true
3，需要对于某些平台需要在OCI的TNS连接串或JDBC Thin中增加ENABLE=BROKEN才可以启用keepalives.

TRACE DEBUG
Review an OS trace (e.g. strace) of the Oracle shadow process.  The tcp socket options will clearly show the TCP Idle Time is set.  This is a Linux example:
setsockopt(19, SOL_TCP, TCP_KEEPALIVE_TIME, [60])
Review a TCP trace file (e.g. tcpdump or snoop)
If being read in Wireshark, simply filter for: tcp.analysis.keep_alive
Oracle Net server trace: search for the string “ntconbrok” and examine messages

B. 对于 idel session

SQL> @i

USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS                  PDB1-anbob19c        oel7db1                     1 390   34170    19.0.0.0.0 20200611 3874       33    3783            000000006AC81028 000000006B9069A8

SQL> alter system set max_idle_time=1;
SQL> @pd max_id
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                                                     VALUE                          DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
       480   1E0 max_idle_time                                            1                              maximum session idle time in minutes
       481   1E1 max_idle_blocker_time                                    0                              maximum idle time for a blocking session in minutes


SQL> set time on
19:46:51 SQL>
19:46:51 SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4298
Session ID: 449 Serial number: 3375

19:49:37 SQL>

C.  对于idle blocker session

如果一个idle session阻塞了其他会话，在19c 20c中可以使用参数max_idle_blocker_time 自动终止blocker, 当会话持有其他会话所需的资源时，该会话被视为阻塞会话. 如

1. 该会话持有另一个会话所需的锁。
2。 该会话是并行操作，并且其使用者组，PDB或数据库已达到其最大并行服务器限制或已排队的并行操作。
3.会话的PDB或数据库实例即将达到其SESSIONS或PROCESSES限制。
此参数与MAX_IDLE_TIME参数的不同之处在于，MAX_IDLE_TIME适用于所有会话（阻塞和非阻塞），而MAX_IDLE_BLOCKING_TIME仅适用于阻塞会话。  因此，为了使MAX_IDLE_BLOCKING_TIME有效，其限制必须小于MAX_IDLE_TIME限制。

SQL> @pd max_id
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                                                     VALUE                          DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
       480   1E0 max_idle_time                                            0                              maximum session idle time in minutes
       481   1E1 max_idle_blocker_time                                    1                              maximum idle time for a blocking session in minutes

-- connect new session1
USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
ANBOB                PDB1-anbob19c        oel7db1                     1 456   25738    19.0.0.0.0 20200611 5466       52    5464            000000006AD259F8 000000006B91FFC8


SQL> set sqlp session1
session1set sqlp session1>
session1>
session1>create table t1(id int);
Table created.

session1>insert into t1 values(1);
1 row created.

session1>commit;
Commit complete.

session1>update t1 set id=10 where id=1;
1 row updated.

session1>

-- connect new session2
USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
ANBOB                PDB1-anbob19c        oel7db1                     1 465   44769    19.0.0.0.0 20200611 5605       54    5603            000000006AD3C120 000000006B922A88


SQL> set sqlp session2>
session2>delete t where id=1;

0 rows deleted.

session2>delete t1 where id=1;
-- hang 

-- session 3
SQL> select * from dba_blockers;

HOLDING_SESSION     CON_ID
--------------- ----------
            456          3

SQL> select * from dba_waiters;

WAITING_SESSION WAITING_CON_ID HOLDING_SESSION HOLDING_CON_ID LOCK_TYPE                  MODE_HELD                                MODE_REQUESTED                             LOCK_ID1   LOCK_ID2
--------------- -------------- --------------- -------------- -------------------------- ---------------------------------------- ---------------------------------------- ---------- ----------
            465              3             456              3 Transaction                Exclusive                                Exclusive                                    589824        893


-- One minute later

# session 2 feedback delete complated

session2>delete t1 where id=1;
1 row deleted.

# session 1
session1>
session1>select * from t1;
select * from t1
       *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 5466
Session ID: 456 Serial number: 25738

当然可以使用resource manager 精准指定组或用户群配置idle session或idle blocker session.

其它

其它还有一些原因数据库会自动KILL会话， 如hang manager 自动解锁blocker session. 或一些锁误如ORA-600 7445等错误。

19c后配置pga_aggregate_limit， 会话使用PGA超过限制时出现ora-4036错误。

References Oracle Net 12c: Changes to the Functionality of Dead Connection Detection (DCD) (Doc ID 1591874.1)
— over —

打赏

微信扫一扫，打赏作者吧～