kill session之後,會話一直無法釋放的巧妙解決辦法(lsof)

BTxigua發表於2011-12-20
我的資料庫版本:
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

下面這些非法連線,需要被kill掉:
SQL> select 'alter system kill session '''||sid||','||serial#||'''; ',client_info,program,status
  2  from v$session where client_info like '132.224.36.10';
'ALTERSYSTEMKILLSESSION'''||SI             CLIENT_INFO    PROGRAM                    STATUS
------------------------------------------ -------------- -------------------------- --------
alter system kill session '1365,1937';     132.224.36.10  oracle@basdb1 (TNS V1-V3)  INACTIVE
alter system kill session '1367,17258';    132.224.36.10  oracle@basdb1 (TNS V1-V3)  INACTIVE
alter system kill session '1383,7729';     132.224.36.10  oracle@basdb1 (TNS V1-V3)  INACTIVE
alter system kill session '1402,3818';     132.224.36.10  oracle@basdb1 (TNS V1-V3)  INACTIVE
alter system kill session '1638,4784';     132.224.36.10  oracle@basdb1 (TNS V1-V3)  INACTIVE
alter system kill session '1982,7247';     132.224.36.10  oracle@basdb1 (TNS V1-V3)  INACTIVE
alter system kill session '2004,4734';     132.224.36.10  oracle@basdb1 (TNS V1-V3)  INACTIVE

為避免出現kill session之後,會話長期存在,先儲存一下spid,也方便與後面的方法做對比
一般在10g之後,不會出現session被kill之後,在v$process中無法找到spid的情況,但實際上,我又反覆碰到了
SQL> select spid from v$process where addr in
  2  (select paddr from v$session where client_info='132.224.36.10') ;
SPID
------------
15843
16581
13466
16862
8224
7332
12161
7 rows selected

利用上面的指令碼殺掉下面的會話:
alter system kill session '1365,1937'; 
alter system kill session '1367,17258';
alter system kill session '1383,7729'; 
alter system kill session '1402,3818'; 
alter system kill session '1638,4784'; 
alter system kill session '1982,7247'; 
alter system kill session '2004,4734'; 

結果KILLED狀態的會話一直不釋放,查詢v$process也找不到對應的spid
無法通過在server端kill spid來快速釋放連線了
SQL> select sid,serial#,client_info,status from v$session where status='KILLED';
       SID    SERIAL# CLIENT_INFO                    STATUS
---------- ---------- ------------------------------ --------
      1365       1937 132.224.36.10                  KILLED
      1367      17258 132.224.36.10                  KILLED
      1383       7729 132.224.36.10                  KILLED
      1402       3818 132.224.36.10                  KILLED
      1638       4784 132.224.36.10                  KILLED
      1982       7247 132.224.36.10                  KILLED
      2004       4734 132.224.36.10                  KILLED
7 rows selected
SQL> select spid from v$process where addr in
  2  (select paddr from v$session where status='KILLED') ;
SPID
------------
SQL>
SQL> select paddr from v$session where status='KILLED' ;
PADDR
----------------
C000000CFD7023D8
C000000CFD7023D8
C000000CFD7023D8
C000000CFD7023D8
C000000CFD7023D8
C000000CFD7023D8
C000000CFD7023D8
7 rows selected

因為知道IP地址等資訊,我們可以通過lsof工具,根據netstat的埠情況來判定spid
下面給出的就是這個方法:
crmdb2:/oracle>netstat -an | grep tcp | grep 132.224.36.10 |grep ESTABLISHED
tcp        0      0  130.34.3.10.1521       132.224.36.10.55190     ESTABLISHED
tcp        0      0  130.34.3.10.1521       132.224.36.10.55040     ESTABLISHED
tcp        0      0  130.34.3.10.1521       132.224.36.10.55004     ESTABLISHED
tcp        0      0  130.34.3.10.1521       132.224.36.10.55163     ESTABLISHED
tcp        0      0  130.34.3.10.1521       132.224.36.10.55065     ESTABLISHED
tcp        0      0  130.34.3.10.1521       132.224.36.10.54982     ESTABLISHED
tcp        0      0  130.34.3.10.1521       132.224.36.10.55227     ESTABLISHED
crmdb2:/oracle>/usr/local/bin/lsof -i tcp:54982 |grep 132.224.36.10
oracle  16862 oracle   22u  IPv4 0xe000000b88c31a00  0t14832  TCP crmdb2_vip:1521->132.224.36.10:54982 (ESTABLISHED)

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/10867315/viewspace-713606/,如需轉載,請註明出處,否則將追究法律責任。

相關文章