ANBOB™

How to release still “killed“ status session in v$session? (释放killed的session) (一)
How to release still “killed“ status session in v$session? (释放killed的session) (二)
How to release still “killed“ status session in v$session? (释放killed的session) (三)
How to release still “killed“ status session in v$session? (释放killed的session) (四)
How to release still “killed“ status session in v$session? (释放killed的session) (五)

继续oracle “killed” 状态的session无法释放的问题，持有的锁可能会堵塞业务，之前有几种情况可以尝试，也有需要要重启实例才能解决的，这里再记录一种oracle 19c RAC的case,  “killed” session在操作系统层的进程已不存在。在数据库中尝试kill session也无法正常清理，后台清理进程无法自动清理。

分析思路

1， 检查OS进程是否存在
2， 尝试取call stack
3,   查看db alert log,和 后台清理进程日志Pmon, 及12c后的专门拆出来的CLMN（Cleanup Main Process,Performs cleanup of dead processes, killed sessions, killed transactions, and killed network connections）日志

现象

数据库v$session和process中记录中存在，并且session status “KILLED”, 操作系统 ps -ef|grep <spid> 不存在，查看日志

PMON trace

Found process xxx pid =xx serial=xxx already dead 
Pmon waiting for 1000 csecs

...

CLMN trace

 IPCLM:[0.2793]:-ト[MAIT]:EReTO:[1745251412365794]ipclw data chunk proce8s:1165 Digcarding msg with geq# xxxxxxx,expecting xxx
...
kssxdl success so xxx= call
kssxdl success so xx = session

现象有点像我之前<Troubleshooting oracle 19c RAC ‘gc cr block lost’ and ‘Library Cache Load Lock’>遇到的bug案例， 解决方法重启实例。

也可能尝试系列1的 oradebug crash

-- session 1

oracle@db1 ~]$ ora

SQL*Plus: Release 23.0.0.0.0 - Beta on Wed Apr 23 01:36:59 2025
Version 23.1.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Connected to:
Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Beta
Version 23.1.0.0.0


USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS                  CDB$ROOT-enmo1       db1                         1 1593  45932    23.0.0.0.0 20250422 725438     77    725434          000000019ACB8A38 00000001A041EBE8


-- session 2
[oracle@db1 ~]$ ora

SQL*Plus: Release 23.0.0.0.0 - Beta on Wed Apr 23 01:37:14 2025
Version 23.1.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Beta
Version 23.1.0.0.0


USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS                  CDB$ROOT-enmo1       db1                         1 1603  6628     23.0.0.0.0 20250422 726069     125   726068          000000019ACD5098 00000001A042F988


SQL> oradebug setorapid 77
Oracle pid: 77, Unix process pid: 725438, NID: 4026531836, image: oracle@db1 (TNS V1-V3)
SQL> oradebug event immediate crash;
ORA-00072: process "Unix process pid: 725438, NID: 4026531836, image: oracle@db1 (TNS V1-V3)" is not active

SQL> @usid 1593

USERNAME                SID                 AUDSID OSUSER           MACHINE            PROGRAM              SPID             OPID CPID                     SQL_ID         HASH_VALUE   LASTCALL STATUS   SADDR            PADDR            TADDR            LOGON_TIME
----------------------- -------------- ----------- ---------------- ------------------ -------------------- -------------- ------ ------------------------ ------------- ----------- ---------- -------- ---------------- ---------------- ---------------- -------------------
SYS                      '1593,45932'   4294967295 oracle           db1                (TNS V1-V3)          725438             77 725434                                           0         76 INACTIVE 000000019ACB8A38 00000001A041EBE8                  2025-04-23 01:36:59

[oracle@db1 ~]$ ps -ef|grep 725438
oracle    730790  730092  0 01:39 pts/5    00:00:00 grep --color=auto 725438

-- session 1
SQL> @a
A-Script: Display CURRENT active sessions...
select
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 725438
Session ID: 1593 Serial number: 45932

# pmon trace 
*** 2025-04-23T01:38:33.811116+08:00 (CDB$ROOT(1))
Marked process 0x1a041ebe8 pid=77 serial=7 ospid=725438 () newly dead
  User session 0x19acb8a38 sid=1593 serial=45932
  client details:
    O/S info: user: oracle, term: pts/4, ospid: 725434
    machine: db1 program: sqlplus@db1 (TNS V1-V3)
    application name: sqlplus@db1 (TNS V1-V3), hash value=47754774

Other issue

WARNING: CLMN Has Failed to Cleanup a Dead Process For Nnnn Attempts (Doc ID 2616322.1) bug 29794462

PMON and CLMN are stuck trying to clean a process that does not exist anymore. 

Alert log shows below:

2019-04-24T06:52:24.236556+02:00
WARNING: CLMN has failed to cleanup a dead process for 10900 attempts
2019-04-24T07:11:04.370906+02:00
WARNING: CLMN has failed to cleanup a dead process for 11000 attempts
2019-04-24T07:28:44.498370+02:00
WARNING: CLMN has failed to cleanup a dead process for 11100 attempts

From pstack and strace

PMON
*** 2019-04-29T07:02:03.436489+02:00
239136 Found process 0xdc5e40e0 pid=109 serial=107 already dead
239137 PMON waiting for 1000 csecs
239138
239139 *** 2019-04-29T07:02:13.437481+02:00
239140 Found process 0xdc5e40e0 pid=109 serial=107 already dead
239141 PMON waiting for 1000 csecs

CLMN
*** 2019-04-29T07:02:03.435213+02:00
466204 PMON waiting for 1000 csecs
466205 KSFV: unable to clean up secondary so: ad6be320
466206 PMON waiting for 1000 csecs

打赏

微信扫一扫，打赏作者吧～