使用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
[python]
#对于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
4、模拟及测试DCD连接
[sql]
C:\Users\robinson.cheng>sqlplus scott/tiger@ora11g --->从windows客户端发起连接
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 25 09:57:59 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
---Issued the sql to hold a lock
SQL> update emp set sal=sal*1.1 where deptno=20;
5 rows updated.
--disabled the network adapter in VM setting
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 29522
Session ID: 15 Serial number: 447
--服务器端环境
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
--在服务器端查看session的情况，SCOTT用户的session状态为INACTIVE
SQL> @comm_sess_users;
+----------------------------------------------------+
| User Sessions (All) |
+----------------------------------------------------+
Instance SID Serial ID Status Oracle User O/S User O/S PID Session Program Terminal Machine
--------- ------ --------- --------- ----------- ------------ ------- -------------------------- ---------- -------------
ora11g 15 447 INACTIVE SCOTT Robinson.Che 29522 sqlplus.exe &
