ANBOB™

前几日发现有套数据库的连接数有些异常，查看当时的session时发现还存在大量的”killed”状态的会话存在v$session 视图中，确认几个小时前有从数据库做过alter system kill session（没有带immediate，如果带了immediate实际调用的OS CALL类似从操作系统 kill -9 ）操作，已经几个小时不太可能是pmon没有清理过， 以前写过一篇笔记《How to release still “killed“ status session in v$session? (释放killed的session)（一）》 时也遇到过，各种方式尝试过这次使用lock user的方式也是无法成功释放，下面再记录下这个情况的处理方法。

查看非INACTIVE 的会话，ase.sql脚本会附在下面。

SQL> @ase

USERNAME          SID EVENT                MACHINE    PROGRAM        STATUS   LAST_CALL_ET HASH_VALUE SQL_ID      WAI_SECINW BS         SQLTEXT                CH#
---------- ---------- -------------------- ---------- -------------- -------- ------------ ---------- ----------- ---------- ---------- --------------- ----------
...
ANBOB              1164 SQL*Net message from qdyyc1     oracle@qdyyc1  KILLED          21269                        -1:21270   :
ANBOB              2344 SQL*Net message from qdyyb1     oracle@qdyyb1  KILLED          21646                        -1:21646   :
ANBOB               672 SQL*Net message from qdyyb1     oracle@qdyyb1  KILLED          70256                        -1:70256   :
ANBOB              1126 SQL*Net message from qdyyd1     oracle@qdyyd1  KILLED          74873                        -1:74873   :
ANBOB               648 SQL*Net message from qdyyc2     oracle@qdyyc2  KILLED          77898                        -1:77898   :
ANBOB               613 SQL*Net message from qdyyc2     oracle@qdyyc2  KILLED          78700                        -1:78700   :
ANBOB               379 SQL*Net message from qdyyd2     oracle@qdyyd2  KILLED          78824                        -1:78824   :
ANBOB               541 SQL*Net message from qdyyc2     oracle@qdyyc2  KILLED          78839                        -1:78839   :
ANBOB               571 SQL*Net message from qdyyc2     oracle@qdyyc2  KILLED          78847                        -1:78848   :
ANBOB              2681 SQL*Net message from qdyyc2     oracle@qdyyc2  KILLED          78891                        -1:78891   :
...
ANBOB              1357 SQL*Net message from qdyyd2     oracle@qdyyd2  KILLED         543989                        -1:543989  :
ANBOB               165 SQL*Net message from qdyyd2     oracle@qdyyd2  KILLED         597098                        -1:597097  :
ANBOB              2514 SQL*Net message from qdyyc2     oracle@qdyyc2  KILLED         600604                        -1:600604  :
ANBOB              2125 SQL*Net message from qdyyd2     oracle@qdyyd2  KILLED         603601                        -1:603601  :
ANBOB              2415 SQL*Net message from qdyyd2     oracle@qdyyd2  KILLED         603601                        -1:603601  :
ANBOB              1024 SQL*Net message from qdyyc1     oracle@qdyyc1  KILLED         604838                        -1:604838  :
ANBOB              2316 SQL*Net message from qdyyc1     oracle@qdyyc1  KILLED         604840                        -1:604841  :
ANBOB              2750 SQL*Net message from qdyyd1     oracle@qdyyd1  KILLED         606690                        -1:606690  :
ANBOB              2701 SQL*Net message from qdyyd1     oracle@qdyyd1  KILLED         606717                        -1:606717  :
ANBOB              2366 SQL*Net message from qdyyd1     oracle@qdyyd1  KILLED         606738                        -1:606738  :
ANBOB              2606 SQL*Net message from qdyyd1     oracle@qdyyd1  KILLED         606759                        -1:606759  :

206 rows selected.

SQL> 
CURRENT_TIME
-----------------
20151104 15:18:27

SQL> select sid ,paddr,logon_time from v$session where status='KILLED';

       SID PADDR            LOGON_TIME
---------- ---------------- -----------------
        18 0700000C18D46BF0 20151102 15:12:06
        48 0700000C18D46BF0 20151103 16:06:58
        75 0700000C18D46BF0 20151102 14:24:59
        81 0700000C18D46BF0 20151102 15:11:54
       104 0700000C18D46BF0 20151102 14:56:24
       112 0700000C18D46BF0 20151102 14:46:32
       113 0700000C18D46BF0 20151102 14:23:41
       115 0700000C18D46BF0 20151102 15:11:51
       165 0700000C18D46BF0 20151028 17:26:49
       166 0700000C18D46BF0 20151102 15:17:59
       172 0700000C18D46BF0 20151103 11:40:43
       209 0700000C18D46BF0 20151102 19:57:43
...
      2955 0700000C18D46BF0 20151102 15:12:05
      2961 0700000C18D46BF0 20151102 15:31:45
      2964 0700000C18D46BF0 20151030 11:17:32
      2988 0700000C18D46BF0 20151031 12:23:33
      2993 0700000C18D46BF0 20151029 10:16:21
      3037 0700000C18D46BF0 20151029 08:23:20
      3040 0700000C18D46BF0 20151102 14:58:58
      3045 0700000C18D46BF0 20151029 10:14:30

199 rows selected.

SQL> select * from v$process where addr='0700000C18D46BF0';

no rows selected

SQL> select logon_time,status,last_call_et from v$session where sid=2366;

LOGON_TIME        STATUS   LAST_CALL_ET
----------------- -------- ------------
20151028 14:46:08 KILLED         610024

Note:

可以看到有差不多200个session是killed 状态在v#session中， 但是根据v$session.paddr 和v$process关连是无法取到记录,随便找了一个session，从last_call_et也可以看出该session保持这个状态已经很长时间，也许是前几次kill session都没有释放。

SQL> select ADDR,PID,SPID,PNAME,USERNAME,SERIAL#,PROGRAM from v$process 
     where addr=(select creator_addr from v$session where sid=2366);

ADDR                    PID SPID                     PNAME USERNAME      SERIAL#  PROGRAM        
---------------- ---------- ------------------------ ----- ---------- ----------  -------------- 
0700000BF8C95888        881 8716360                        grid               55  oracle@kdfk1   

SQL> ! ps -ef|grep 8716360|grep -v grep
    grid  8716360        1   0   Oct 28      -  0:00 oraclesvp1 (LOCAL=NO)

SQL> ! kill -9 8716360

SQL> ! ps -ef|grep 8716360|grep -v grep

SQL> select ADDR,PID,SPID,PNAME,USERNAME,SERIAL#,PROGRAM  from v$process 
     where addr=(select creator_addr from v$session where sid=2366);

ADDR                    PID SPID                     PNAME USERNAME      SERIAL#  PROGRAM        
---------------- ---------- ------------------------ ----- ---------- ----------  -------------- 
0700000BF8C95888        881 2688084                        grid               59  oracle@kdfk1   

TIP:
对于killed的session可以使用creator_addr字段关联，然后通过操作系统KILL, 对于CREATOR_ADDR字段的解释下如：

additional columns have been added to V$SESSION from 11g on:
V$SESSION
CREATOR_ADDR – state object address of creating process
CREATOR_SERIAL# – serial number of creating process
CREATOR_ADDR is the column that can be joined with the ADDR column in V$PROCESS to uniquely identify the killed process corresponding to the former session.

下面我们就可以拼脚本KILL会话了,先确认一下进程是LOCAL=NO

select 'ps -ef|grep '||spid||'|grep -v grep' from  v$process 
where program='oracle@kdfk1' and  addr in(select creator_addr from v$session where status='KILLED');

select 'kill -9 '||spid from  v$process where program='oracle@kdfk1' and  addr in(select creator_addr from v$session where status='KILLED');

附使用脚本

================= ase.sql =================
-- file: ase.sql
-- author: weejar(anbob.com)
-- Desc. To Display all sessions of not "inactive"

set pages 1000 lines 200
col username for a10 
col machine for a10 
col program for a14 trunc 
col event for a20 trunc 
col sqltext for a30 
col sql_id for a20 
col  wai_secinwait for a10 
col bs for a10 
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; 
 select  sysdate current_time from dual;