使用 SQLNET.EXPIRE_TIME 清除僵死连接

使用 SQLNET.EXPIRE_TIME 清除僵死连接

数据库连接的客户端异常断开后，其占有的相应并没有被释放，如从v$session视图中依旧可以看到对应的session处于inactive，且对应的服务器进程也没有释放，导致资源长时间地被占用，对于这种情形开该如何处理呢？SQLNET.EXPIRE_TIME对于这个问题我们提供了解决方案，专门用于清理那些异常断开的情形，如网络异常中断，客户端异常掉电，异常重启等。本文描述了设置SQLNET.EXPIRE_TIME参数以及演示死连接以及资源被释放的情形。

1、理解SQLNET.EXPIRE_TIME参数
  Use parameter SQLNET.EXPIRE_TIME to specify a the time interval, in minutes, to send a probe to verify that client/server
  connections are active.
  Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination.
  If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the
  server process to exit.
  This parameter is primarily intended for the database server,which typically handles multiple connections at any one time.
 
  通过设定参数为非零值(分钟)来发送探测包以检查客户端的异常断开。一旦探测包找到了异常的连接将返回错误，清除对应的server process
  下面是参数使用的一些限制。(缺省值为0，最小值0，建议值10。SQLNET.EXPIRE_TIME=10)
  Limitations on using this terminated connection detection feature are:
 
      It is not allowed on bequeathed connections.
      Though very small, a probe packet generates additional traffic that may downgrade network performance.
      Depending on which operating system is in use, the server may need to perform additional processing to distinguish
      the connection probing event from other events that occur. This can also result in degraded network performance.

2、Dead Connection Detection (DCD)与Inactive Sessions

Dead connections:
  These are previously valid connections with the database but the connection between the client and server processes has
  terminated abnormally.
  Examples of a dead connection:
  - A user reboots/turns-off their machine without logging off or disconnecting from the database.
  - A network problem prevents communication between the client and the server.
 
  In these cases, the shadow process running on the server and the session in the database may not terminate.
 
  Implemented by
        * adding SQLNET.EXPIRE_TIME = <MINUTES> to the sqlnet.ora file
 
  With DCD is enabled, the Server-side process sends a small 10-byte packet to the client process after the duration of
  the time interval specified in minutes by the SQLNET.EXPIRE_TIME parameter.
 
  If the client side connection is still connected and responsive, the client sends a response packet back to the database
  server, resetting the timer..and another packet will be sent when next interval expires (assuming no other activity on
  the connection).
 
  If the client fails to respond to the DCD probe packet
        * the Server side process is marked as a dead connection and
        * PMON performs the clean up of the database processes / resources
        * The client OS processes are terminated
 
  NOTE: SQLNET.RECV_TIMEOUT can be set on the SERVER side sqlnet.ora file. This will set a timeout for the server process
        to wait for data from the client process.

Inactive Sessions:
  These are sessions that remain connected to the database with a status in v$session of INACTIVE.
  Example of an INACTIVE session:
  - A user starts a program/session, then leaves it running and idle for an extended period of time.

3、配置SQLNET.EXPIRE_TIME

#对于SQLNET.EXPIRE_TIME的配置，需要修改sqlnet.ora，然后添加SQLNET.EXPIRE_TIME项
[Oracle@orasrv admin]$ more sqlnet.ora
sqlnet.expire_time = 1    #仅仅需要配置此项，后面的各项仅仅是为了生成跟踪日志，可省略
TRACE_LEVEL_SERVER = 16
TRACE_FILE_SERVER = SERVER
TRACE_DIRECTORY_SERVER= /u01/app/oracle/network/trace
TRACE_TIMESTAMP_ SERVER = ON
TRACE_UNIQUE_SERVER = ON
DIAG_ADR_ENABLED=OFF