首页 » ORACLE » How to release still “killed“ status session in v$session?(释放killed的session)

How to release still “killed“ status session in v$session?(释放killed的session)

最近在一套生产库上发现了几个已经killed的会话一直保持在v$session 会话中,会话是user type的连接,而且已经killed了很久,通过SPID 发现操作系统层面已不存在该进程, 下面是我多次尝试后最终释放,这里简单的记录一下。

SQL>   SELECT ses.username,
         ses.sid,
         ses.event,
         ses.machine,
         ses.program,
         ses.status,
         ses.last_call_et,
         sql.hash_value,
         ses.sql_id,
         wait_time || ':' || SECONDS_IN_WAIT wai_secinwait,
         blocking_instance || ':' || blocking_session bs,
         -- SUBSTR (sql.sql_text, 1, 30) sqltext,
         sql_child_number ch#
    FROM    v$session ses
         LEFT JOIN
            v$sql sql
         ON     ses.sql_hash_value = sql.hash_value
            AND ses.sql_child_number = sql.child_number
   WHERE ses.TYPE = 'USER' AND ses.status <> 'INACTIVE' -- and sql_text like 'select t.subsid,s.servnumber,t%'
ORDER BY SECONDS_IN_WAIT, last_call_et, 4;
 
USERNAME          SID EVENT                MACHINE    PROGRAM        STATUS   LAST_CALL_ET HASH_VALUE SQL_ID               WAI_SECINW BS                CH#
---------- ---------- -------------------- ---------- -------------- -------- ------------ ---------- -------------------- ---------- ---------- ----------
...
ANBOB             8362 cursor: pin S wait o qdyyd1     oracle@qdyyd1  ACTIVE           1589            2ad1tj9ph0799        0:238      1:6083
ANBOB             5675 cursor: pin S wait o qdyyd1     oracle@qdyyd1  ACTIVE        4233911            2ad1tj9ph0799        0:271      1:6083
ANBOB            11136 cursor: pin S wait o qdyyd1     oracle@qdyyd1  ACTIVE        2752356            2ad1tj9ph0799        0:272      1:6083
ANBOB             5699 cursor: pin S wait o qdyyd1     oracle@qdyyd1  ACTIVE        4227707            2ad1tj9ph0799        0:272      1:6083
ANBOB            10373 cursor: pin S wait o qdyyd1     oracle@qdyyd1  ACTIVE        2752406            2ad1tj9ph0799        0:278      1:6083
ANBOB             1559 cursor: pin S wait o qdyyd1     oracle@qdyyd1  ACTIVE        4233819            2ad1tj9ph0799        0:278      1:6083
ANBOB             1001 cursor: pin S wait o qdyyd1     oracle@qdyyd1  ACTIVE        4233977            2ad1tj9ph0799        0:278      1:6083
ANBOB              766 cursor: pin S wait o qdyyd1     oracle@qdyyd1  ACTIVE        4234014            2ad1tj9ph0799        0:278      1:6083
ANBOB             6276 cursor: pin S wait o qdyyd1     oracle@qdyyd1  ACTIVE        2408703            2ad1tj9ph0799        0:279      1:6083
TBCS            11709 cursor: pin S wait o qdyya1     oracle@qdyya1  ACTIVE        3608489            2ad1tj9ph0799        0:279      1:6083
ANBOB             6083 SQL*Net message from qdyyd1     oracle@qdyyd1  ACTIVE        4227719            2ad1tj9ph0799        0:1589     :                   1
ANBOB             2091 SQL*Net message from HBYD\WIN-L plsqldev.exe   KILLED        2408703            6wd9zqzvd1mhv        0:2408684  :                   0                                          
ANBOB             8857 SQL*Net message from HBYD\WIN-L plsqldev.exe   ACTIVE        2752356            25zw3ur494zrk        0:2752335  :                   0                                         
ANBOB             4727 SQL*Net message from HBYD\WIN-L plsqldev.exe   KILLED        2752406            5um4j8rzxb7m4        0:2752385  :                   0                                          
ANBOB             4772 SQL*Net message from HBYD\WIN-L plsqldev.exe   ACTIVE        4227707            g4kg17x0kdp2a        0:4227676  :                   0                                          
ANBOB             5092 SQL*Net message from HBYD\WIN-L plsqldev.exe   KILLED        4227719            5q4pm9fvgk1v2        0:4227688  :                   0                                          
ANBOB             6212 SQL*Net message from HBYD\WIN-L plsqldev.exe   KILLED        4233821            gnswv87thknxh        0:4233789  :                   0                                          
ANBOB             9651 SQL*Net message from HBYD\WIN-L plsqldev.exe   KILLED        4233911            3vsj9d74szj76        0:4233880  :                   0                                          
ANBOB              797 SQL*Net message from HBYD\WIN-L plsqldev.exe   KILLED        4233977            5q4pm9fvgk1v2        0:4233946  :                   0                                          
ANBOB             4561 SQL*Net message from qdyyd2     oracle@qdyyd2  KILLED        4234015            2ad1tj9ph0799        0:4233984  :                   0

SQL> select * from v$sql where sql_id='2ad1tj9ph0799';
no rows selected

SQL> @usid 4561
USERNAME                SID                 AUDSID OSUSER           MACHINE            PROGRAM              SPID             OPID CPID                     SQL_ID                HASH_VALUE   LASTCALL STATUS   SADDR            PADDR            TADDR            LOGON_TIME
----------------------- -------------- ----------- ---------------- ------------------ -------------------- -------------- ------ ------------------------ -------------------- ----------- ---------- -------- ---------------- ---------------- ---------------- -----------------
DBMT                     '4561,54839'     76175883 grid             qdyyd2             (TNS V1-V3)          28443             664 26551                    2ad1tj9ph0799         1795169577    4234026 KILLED   C0000015C2F43C00 C0000015B2369670 C00000159933E970 20150629 14:46:56

SQL> ho ps -ef|grep 28443
  oracle 29899 27782  1 14:54:16 pts/3     0:00 grep 28443

Note:
OS 级进程已经不存在,但是v$session 视图中一直存在,该会话已经kill好几天,手动有尝试oradebug wakeup pmon进程,也是无法清理。而且sql_id为2ad1tj9ph0799但是没有sql hash_value,而且也没有在v$sql中查到,

SQL> @usid 4727

USERNAME                SID                 AUDSID OSUSER           MACHINE            PROGRAM              SPID             OPID CPID                     SQL_ID                HASH_VALUE   LASTCALL STATUS   SADDR            PADDR            TADDR            LOGON_TIME
----------------------- -------------- ----------- ---------------- ------------------ -------------------- -------------- ------ ------------------------ -------------------- ----------- ---------- -------- ---------------- ---------------- ---------------- -----------------
DBMT                     '4727,4920'      94210564 59497A78D31F6009 \WIN-LTQ5KT3M57E   plsqldev.exe         18504            1625 52780:39456              5um4j8rzxb7m4         4292189796    2759538 KILLED   C0000015C2F9D2A0 C0000016023DC588 C000001572A4F488 20150716 18:20:25

SQL> host ps -ef|grep 18504
  oracle  2428  3625  2 16:53:01 pts/2     0:00 grep 18504

SQL> alter system kill session  '4727,4920' immediate;
alter system kill session  '4727,4920' immediate
*
ERROR at line 1:
ORA-00030: User session ID does not exist.


SQL> alter system disconnect session  '4727,4920'  immediate;
alter system disconnect session  '4727,4920'  immediate
*
ERROR at line 1:
ORA-00030: User session ID does not exist.

SQL> oradebug setorapid 1625 force
Oracle pid: 1625, Unix process pid: 18504, image: oracle@qdyyd1
SQL> oradebug short_stack
ORA-00072: process "Unix process pid: 18504, image: oracle@qdyyd1" is not active

SQL> oradebug close_trace
ORA-00072: process "Unix process pid: 18504, image: oracle@qdyyd1" is not active

SQL> oradebug event immediate crash;
ORA-00072: process "Unix process pid: 18504, image: oracle@qdyyd1" is not active

SQL> select saddr from v$session where sid=4727;
SADDR
----------------
C0000015C2F9D2A0

SQL> select * from v$transaction where ses_addr='C0000015C2F9D2A0'
ADDR                 XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS           START_TIME           START_SCNB START_SCNW START_UEXT START_UBAFIL START_UBABLK START_UBASQN START_UBAREC SES_ADDR               FLAG SPA REC NOU PTX NAME                                            PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN PTX_XIDSLT PTX_XIDSQN     DSCN-B     DSCN-W  USED_UBLK  USED_UREC     LOG_IO     PHY_IO     CR_GET  CR_CHANGE START_DATE         DSCN_BASE  DSCN_WRAP  START_SCN DEPENDENT_SCN XID              PRV_XID          PTX_XID
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- ---------- ---------- ---------- ------------ ------------ ------------ ------------ ---------------- ---------- --- --- --- --- ----------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------- ---------- ---------- ---------- ------------- ---------------- ---------------- ----------------
C000001572A4F488       1193         24      15385          0          0          0          0 ACTIVE           07/16/15 18:20:25    2681908016       3366          7          561       167090         2993           47 C0000015C2F9D2A0    4331011 NO  NO  NO  NO                                                           0          0          0          0          0          0          0          0          1          1          3          0          0          0 20150716 18:20:25          0          0 1.4460E+13             0 04A9001800003C19 0000000000000000 0000000000000000


SQL> select * from v$lock where sid=4727;
ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
C000001572A4F488 C000001572A4F500       4727 TX   78184472      15385          6          0    2818353          2

SQL> select * from v$lock where id1=78184472 and id2=15385;
ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
C000001572A4F488 C000001572A4F500       4727 TX   78184472      15385          6          0    2822991          2

SQL> @sw 4727
    SID STATE   EVENT                                SEQ# SEC_IN_WAIT P1                  P2                  P3                  P1TRANSL
------- ------- ------------------------------ ---------- ----------- ------------------- ------------------- ------------------- -----------
   4727 WAITING SQL*Net message from dblink            52     2819299 driver id=          #bytes= 1           0
                                                                                0x0000000054435000

Note:
可以看到会话在等待dblink响应,而且一直在执行tx lock 未释放,用oradebug的手段也没有结束会话, 而且上面我们看到在会话执行SQL_ID 2ad1tj9ph0799也会也是wait在解析阶段, 因为无法确认SQL文件,无法从数据库对象下手,不过他们有一个共性就是使用的都是ANBOB这样的我们定义管理数据库用户,数据库应用没有使用该用户,想通过lock 该用户,重新解锁的方式尝试释放该会话。

alter user anbob account lock;
alter user anbob account unlock;

再次查询发现僵了好几天的killed 状态的会话终于释放,且未在出现。

打赏

对不起,这篇文章暂时关闭评论。