[20200319]KILL STATUS ='KILLED'的程式.txt

--//生產系統例行檢查，我發現存在一些STATUS ='KILLED'的會話。
--//看看這些程式如何刪除。

1.環境：
SYS@ZZZZ/SIDSID> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@ZZZZ/SIDSID> select paddr,inst_id,SID,S.SERIAL#,status,client_info ,logon_time,CREATOR_ADDR,CREATOR_SERIAL# from gv$session S where STATUS ='KILLED' order by sid;
PADDR               INST_ID        SID    SERIAL# STATUS   CLIENT_INFO          LOGON_TIME          CREATOR_ADDR     CREATOR_SERIAL#
---------------- ---------- ---------- ---------- -------- -------------------- ------------------- ---------------- ---------------
000000134207A620          1        167       6305 KILLED   aaa.bbb.c5.116       2020-03-11 20:22:26 0000001291D9E998              77
000000134207A620          1       1747      12439 KILLED   AAA.BBB.C.74         2020-03-10 07:23:33 00000012A1E0DCE0              89
000000134207A620          1       3499       1473 KILLED   aaa.bbb.c0.224       2020-03-13 15:40:14 00000012A1DBB418             218
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
000000134207A620          1       5055      15825 KILLED   aaa.bbb.c0.224       2020-03-13 10:31:55 00000012B1D5A100             121
000000134207A620          2       6393      11793 KILLED   aaa.bbb.c5.116       2020-03-11 20:24:13 00000012D1D47F68              20
000000134207A620          1       6980       3377 KILLED   aaa.bbb.c0.224       2020-03-13 10:31:56 00000012B1F37978             122
000000134207A620          1       7261      10139 KILLED   aaa.bbb.c5.116       2020-03-11 20:22:25 00000012A1DFC0A8              71
000000134207A620          1       7423      23319 KILLED   AAA.BBB.C.74         2020-03-10 07:23:33 00000012A1E32608             215
8 rows selected.
--//出現這樣的情況PADDR地址指向都已一樣的，如果你使用該地址查詢檢視v$process無法獲得spid程式號的。
SYS@ZZZZ/SIDSID> select * from v$process where addr=hextoraw('000000134207A620');
no rows selected

--//只能查詢使用CREATOR_ADDR查詢。
SYS@ZZZZ/SIDSID> select * from v$process where addr=hextoraw('00000012A1DBB418')
2 @ prxx
==============================
ADDR                          : 00000012A1DBB418
PID                           : 203
SPID                          : 102216
PNAME                         :
USERNAME                      : grid
SERIAL#                       : 218
TERMINAL                      : UNKNOWN
PROGRAM                       : oracle@ZZZZZZZZZZZZZZZZ
TRACEID                       :
TRACEFILE                     : /u01/app/oracle/diag/rdbms/SIDSID/SIDSID1/trace/SIDSID1_ora_102216.trc
BACKGROUND                    :
LATCHWAIT                     :
LATCHSPIN                     :
PGA_USED_MEM                  : 1927126
PGA_ALLOC_MEM                 : 3231990
PGA_FREEABLE_MEM              : 1048576
PGA_MAX_MEM                   : 5329142
PL/SQL procedure successfully completed.
--//奇怪 USERNAME='grid',什麼回事。
SELECT s.paddr
              ,s.inst_id
              ,s.SID
              ,S.SERIAL#
              ,s.status
              ,s.client_info
              ,s.logon_time
              ,s.CREATOR_ADDR
              ,s.CREATOR_SERIAL#
              ,p.spid
              ,p.username
          FROM gv$session s, gv$process p
         WHERE     s.STATUS = 'KILLED'
               AND p.addr = s.CREATOR_ADDR
               AND (    (s.USERNAME IS NOT NULL)
                    AND (NVL (s.osuser, 'x') <> 'SYSTEM')
                    AND (s.TYPE <> 'BACKGROUND'))
               AND (s.ownerid = 2147483644)
               AND (   s.module <> 'TOAD background query session' OR s.module IS NULL)
               AND p.inst_id = s.inst_id;
--//我查詢發現都是USERNAME='grid'。加入其它條件是抄toad的查詢，主要避免下面kill時刪除重要程式。

# netstat -tnop 2>/dev/null | grep 102216
tcp        0      0 192.168.100.104:1521        aaa.bbb.c0.224:2549         ESTABLISHED 102216/oracleSIDSID1 keepalive (61.59/0/0)

# ps -fp 102216
UID         PID   PPID C STIME TTY          TIME CMD
oracle   102216      1 0 Mar13 ?        00:00:00 oracleSIDSID1 (LOCAL=NO)

--//說明該程式存在。如果採用原來的方式是不行的。
SYS@ZZZZ/SIDSID> alter system disconnect session 3499,1473 immediate ;
alter system disconnect session 3499,1473 immediate
                                *
ERROR at line 1:
ORA-00026: missing or invalid session ID

--//知道程式號直接殺程式就ok了。

SELECT 'kill -9 ' || spid c20, inst_id
FROM (SELECT s.paddr
              ,s.inst_id
              ,s.SID
              ,S.SERIAL#
              ,s.status
              ,s.client_info
              ,s.logon_time
              ,s.CREATOR_ADDR
              ,s.CREATOR_SERIAL#
              ,p.spid
          FROM gv$session s, gv$process p
         WHERE     s.STATUS = 'KILLED'
               AND p.addr = s.CREATOR_ADDR
               AND (    (s.USERNAME IS NOT NULL)
                    AND (NVL (s.osuser, 'x') <> 'SYSTEM')
                    AND (s.TYPE <> 'BACKGROUND'))
               AND (s.ownerid = 2147483644)
               AND (   s.module <> 'TOAD background query session'
                    OR s.module IS NULL)
               AND p.inst_id = s.inst_id) order by 2;

C20              INST_ID
---------------- -------
kill -9 102216         1
kill -9 77715          1
kill -9 125001         1
kill -9 77731          1
kill -9 92612          1
kill -9 125003         1
kill -9 92620          1
kill -9 60858          2
8 rows selected.

--//注意例項號。最後1行是另外一臺機器的。先嚐試1個程式看看。

# kill -9 102216
# ps -fp 102216
UID         PID   PPID C STIME TTY          TIME CMD
# netstat -tnop 2>/dev/null | grep 102216

--//執行前還是建議檢視一下。
$ ps -fp 102216,77715,125001,77731,92612,125003,92620

--//然後copy and paste執行以上命令，注意伺服器千萬別選擇錯了。一般等2，3秒程式就會消失。我翻了以前筆記以前寫成如下：
select spid from v$process where addr in(select creator_addr from v$session where status='KILLED');
select 'kill -9 '||spid from v$process where addr in(select creator_addr from v$session where status='KILLED');
--//感覺執行前還是給仔細看看什麼程式,貿然操作還是存在一定風險。

[20200319]KILL STATUS ='KILLED'的程式.txt

相關文章