How To Automate Cleanup Of Dead Connections And INACTIVE 【如何自动清理ORACLE中的死连接和非活动会话】




这篇文章解释了死连接(dead connection)和 v$session中的 非活动会话(inactive)

范围 & 应用
这篇文是为想自动清理死连接和/或 非活动会话的DBA准备的

Difference between INACTIVE sessions and Dead Connections
Dead connections and INACTIVE sessions are different issues. Oracle
provides separate mechanisms to automate the cleanup of each.

(1) Dead connections:

    These are previously valid connections with the database but the
    connection between the client and server processes has terminated

   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.

    用Net8(其实就是SQL*Net)里面的DCD(Dead Connection Detection)特性可以自动清理这样的会话。
    In these cases, the shadow process running on the server and the
    session in the database may not terminate. To automate the cleanup
    of these sessions, you can use the Dead Connection Detection (DCD)
    feature of Net8.

     When DCD is enabled, Net8 (server-side) sends a packet to the client.
    If the client is active, the packet is discarded. If the client has
    terminated, the server will receive an error and Net8 (server-side)
    will end that session.

    Refer toNote:151972.1: Dead Connection Detection (DCD) Explained,
    for details regarding DCD.

(2) INACTIVE Sessions:
    有些仍然与数据库相连接的会话在 v$session 里的status 显示为INACTIVE。
    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.

    详细的方法可以参考metalink上的文章 Note:159978.1
    To automate cleanup of INACTIVE sessions you can create a profile
    with an appropriate IDLE_TIME setting and assign that profile to
    the users.Note:159978.1: How To Automate Disconnection of Idle Sessions,
    outlines the steps to setup IDLE_TIME for this.


情况(1): 编辑 sqlnet.ora文件,新增expire_time=x   (单位分钟)

情况(2): create profile idleprofile limit idle_time=10;
alter user testuser profile  idelprofile ;
alter system set RESOURCE_LIMIT= true;

========================另外2篇额外介绍INACTIVE SESSION的文章==========================

A   sniped   session   occurs   when   a   user   has   exceeded   his   idle   time.   The   situation   has   been   noted   in   the   database,   and   the   user’s   actual   database   session   has   been   suspended.   However,   the   user   is   still   consuming   a   dedicated   server   resource   that   has   not   yet   been   allocated   to   someone   else.   This   situation   can   have   an   adverse   effect   on   overall   database   performance.  

 When IDLE_TIME is set in the users’ profiles or the default profile. This will kill the sessions in the database (status in v$session now becomes SNIPED) and they will eventually disconnect. It does not always clean up the Unix session (LOCAL=NO sessions). At this time alloracleresources are released but the shadow processes remains and OS resources are not released. This shadow process is still counted towards the parameters of init.ora.

This process is killed and entry from v$session is released only when user again tries to do something. Another way of forcing disconnect (if your users come in via SQL*Net) is to put the file sqlnet.ora on every client machine and include the parameter “SQLNET.EXPIRE_TIME” in it to force the close of the SQL*Net session.

A status of SNIPED in v$session for a session indicates that the session has been terminated by Oracle, because it exceeded the IDLE_TIME set in the profile assigned to the user. For a session to be terminated in such a manner, the RESOURCE_LIMIT parameter should be set to true. All locks and
resources held by the session get released during this SNIPE.
The problem with this encountered by yours truly in the past, is that, this session does not get deleted from v$sesssion. This could build up until you get a Number of sessions exceeded Oracle error, in which case the only solution is to bounce the database. This entry will get deleted from v$session only if Oracle responds back to the user saying his or her session has been terminated, which in most cases, happens the next morning


Inactive sessions in the database.

Posted on
September 13, 2011
by Gary

Following a conversation regarding inactive sessions and the process limitation on the database, this client was having connection failures to the database. What should be done regarding this ?

There are several things that can be done to help this.

First off, check what the sessions are doing. There are many session which remain marked as inactive, but are they executing lots of small pieces of code, and your query is finding only when they are inactive.

For that, look to the LAST_CALL_ET column of v$session which will indicate the last time the session was active. If the sessions have remained inactive for a long period of time, then these are indeed not being dropped. From that you can refer to metalink note 206007.1 (How To Automate Cleanup Of Dead Connections And INACTIVE Sessions).

Check how the connections are made, for example and application server may be creating new connections for each and every connection coming in through the server. If this is the case then you should be enabling connection pooling on the server to prevent the overload of the number of sessions connected to the database. My experience would be that with a high number of connections being maintained by an application server would normally bring this system down.

You can configure systems to have a max set of connections, and a minimum level, whereby the server can open for example 20 sessions in the pool, and obtain more as required up to a defined upper limit.

Some applications may not be killing sessions created to the database and simply creating more as required. This would be a bug in the application level, and should be investigated.

Ensure also that if the sessions parameter has been increased, that the processes parameter is also incremented, whilst bearing in mind that the database requries a restart each time this has to be changed. You will require more processes than sessions to cater for the background processes running on the database (Check v$process for these item).

As a last resort, you can implement a process which kills the inactive sessions, but beware that the session is not part of a pool of connections which you are killing. Many application require to be restarted to re-establish the pool of connections. Ensure you are looking at sessions which are not just idle, but have not communicated with the database is a reasonable length of time.

******************************************************************eygle大大的一篇关于KILL SESSION的文章********************************************************************