[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
这篇文章对你有帮助吗，投个票吧？
page revision: 0, last edited: 23 Jun 2014 02:33
留下你的评论