[Oracle] 从OS上kill掉已被kill session但还没有被PMON清除的Server Process

博客首页 » Oracle 从OS上kill掉已被kill session但还没有被PMON清除的Server Process

发布于 23 Jun 2014 02:33
标签 blog
Oracle中Server Process如果已被alter system kill session 'sid,serial'切断,一般到被PMON清除为止的这段killed状态会维持很长一段时间。在这个状态下,很多资源没有被释放。下面介绍如何找到Server Process的spid并从OS上kill掉。

结论

使用下面这个SQL找到spid,并在OS上kill -9切断掉。

select spid from v$process where addr in (
   select addr from v$process p where pid <> 1
   minus
   select paddr from v$session s);

详细说明:

Oracle中可以用alter system kill session 'sid,serial'来切断一个session(以下称为kill session操作)。而这样的session经常会在v$session视图中维持很长时间,在status被标记为killed状态。而这样的没有被PMON清除的Server Process是只能被v$session查询到,而不能被select * from v$session s, v$process p where s.paddr = p.addr查询到的。这是因为kill session操作把session的paddr指到了一个特定的地址(如果有多个kill session,那么paddr的地址相同),在process发生下一个操作,或者PMON得下一次touch中才清除。在这个状态下,很多资源没有被释放。

SQL> set lin 150
SQL> set pages 9999
 
SQL> select * from v$session where osuser like 'myosusr%';
 
SADDR                   SID    SERIAL#     AUDSID PADDR                 USER# USERNAME                          COMMAND    OWNERID TADDR
---------------- ---------- ---------- ---------- ---------------- ---------- ------------------------------ ---------- ---------- ----------------
LOCKWAIT         STATUS   SERVER       SCHEMA# SCHEMANAME                     OSUSER                         PROCESS
---------------- -------- --------- ---------- ------------------------------ ------------------------------ ------------
MACHINE                                                          TERMINAL                       PROGRAM
---------------------------------------------------------------- ------------------------------ ------------------------------------------------
TYPE       SQL_ADDRESS      SQL_HASH_VALUE SQL_ID        SQL_CHILD_NUMBER PREV_SQL_ADDR    PREV_HASH_VALUE PREV_SQL_ID   PREV_CHILD_NUMBER
---------- ---------------- -------------- ------------- ---------------- ---------------- --------------- ------------- -----------------
PLSQL_ENTRY_OBJECT_ID PLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID MODULE                                           MODULE_HASH
--------------------- ------------------------- --------------- ------------------- ------------------------------------------------ -----------
ACTION                           ACTION_HASH CLIENT_INFO                                                      FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ#
-------------------------------- ----------- ---------------------------------------------------------------- -------------------- -------------
ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TI LAST_CALL_ET PDM FAILOVER_TYPE FAILOVER_M FAI RESOURCE_CONSUMER_GROUP          PDML_STA PDDL_STA
-------------- --------------- ------------- -------- ------------ --- ------------- ---------- --- -------------------------------- -------- --------
PQ_STATU CURRENT_QUEUE_DURATION CLIENT_IDENTIFIER                                                BLOCKING_SE BLOCKING_INSTANCE BLOCKING_SESSION
-------- ---------------------- ---------------------------------------------------------------- ----------- ----------------- ----------------
      SEQ#     EVENT# EVENT
---------- ---------- ----------------------------------------------------------------
P1TEXT                                                                   P1 P1RAW
---------------------------------------------------------------- ---------- ----------------
P2TEXT                                                                   P2 P2RAW
---------------------------------------------------------------- ---------- ----------------
P3TEXT                                                                   P3 P3RAW            WAIT_CLASS_ID WAIT_CLASS#
---------------------------------------------------------------- ---------- ---------------- ------------- -----------
WAIT_CLASS                                                        WAIT_TIME SECONDS_IN_WAIT STATE
---------------------------------------------------------------- ---------- --------------- -------------------
SERVICE_NAME                                                     SQL_TRAC SQL_T SQL_T
---------------------------------------------------------------- -------- ----- -----
000000015B584848        232      14572    4968481 000000015F5171B0         61 APPLDBUSER                              0 2147483644
                 KILLED   PSEUDO            61 APPLDBUSER                     myosusr-123456789              4800:9064
MY-DOMAIN\MYDBAPC                                                MYDBAPC                        ob9.exe
USER       00                            0                                000000015D2B7DB8      1382851420 23w54b996t8uw                 1
                                                                                    ob9.exe                                            736228983
                                           0                                                                             263294156            37
             1           41930             0 14-05-23       151445 NO  NONE          NONE       NO                                   DISABLED ENABLED
ENABLED                       0                                                                  NO HOLDER
       480        259 SQL*Net message from client
driver id                                                        1413697536 0000000054435000
#bytes                                                                    1 0000000000000001
                                                                          0 00                  2723168908           6
Idle                                                                      0          151445 WAITING
mydb01                                                           DISABLED FALSE FALSE
 
Elapsed: 00:00:00.02
SQL> select * from v$session s, v$process p where s.paddr = p.addr and s.osuser like 'myosusr%';
 
no rows selected
 
Elapsed: 00:00:00.01
SQL>

这时,我们可以通过outter join找到相关关系,通过寻找paddr差异的方法找到相应的paddr。

SELECT s.username,s.status,
x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP,
decode(bitand (x.ksuprflg,2),0,null,1)
FROM x$ksupr x,v$session s
WHERE s.paddr(+)=x.addr
and bitand(ksspaflg,1)!=0;
SQL> select addr from v$process p where pid <> 1
  2  minus
  3  select paddr from v$session s
  4  ;
 
ADDR
----------------
000000015A47B8A0
 
Elapsed: 00:00:00.01
SQL> select * from v$process where addr in (
  2    select addr from v$process p where pid <> 1
  3    minus
  4    select paddr from v$session s);
 
ADDR                    PID SPID         USERNAME           SERIAL# TERMINAL                       PROGRAM
---------------- ---------- ------------ --------------- ---------- ------------------------------ ------------------------------------------------
TRACEID
------------------------------------------------------------------------------------------------------------------------------------------------------
B LATCHWAIT        LATCHSPIN        PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
- ---------------- ---------------- ------------ ------------- ---------------- -----------
000000015A47B8A0        189 21891        oracle                  17 UNKNOWN                        oracle@td-ssms
 
                                          732109       2151565          1048576     8377485
 
Elapsed: 00:00:00.22

接下来就容易了。使用OS的kill清除Server Process进程。

SQL> host
$ export LANG=C
$ kill 21891
$ ps -ef | grep 21891
oracle   18867 18817  0 10:07 pts/5    00:00:00 grep 21891
oracle   21891     1  0 May23 ?        00:00:00 oraclemydb01 (LOCAL=NO)
$ kill -9 21891
$ ps -ef | grep 21891
oracle   18874 18817  0 10:08 pts/5    00:00:00 grep 21891
$ exit
exit

可以看到结果。

SQL> select * from v$session where osuser like 'myosusr%';
 
no rows selected
 
Elapsed: 00:00:00.01
SQL>

Reference:
http://www.eygle.com/faq/Kill_Session.htm


本页面的文字允许在知识共享 署名-相同方式共享 3.0协议和GNU自由文档许可证下修改和再使用,仅有一个特殊要求,请用链接方式注明文章引用出处及作者。请协助维护作者合法权益。


系列文章

文章列表

  • Oracle 从OS上kill掉已被kill session但还没有被PMON清除的Server Process

这篇文章对你有帮助吗,投个票吧?

rating: 0+x

留下你的评论

Add a New Comment
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License