ORA-00031: session marked for kill

OS 환경 : Oracle Linux 6.8 (64bit)

 

DB 환경 : Oracle Database 11.2.0.4

 

에러 : ORA-00031: session marked for kill

 

해결 방법 : 정상적인 Rollback 처리를 위해서 기다리는 걸 권장함

Lock이 해제 되지 않는 경우 처리 되는 사항

테이블이 Lock이 걸려서 특정 Row 또는 테이블을 수정할 수 없는 경우 일반적으로 

 

Alter system kill session 'SID_no, SERIAL_no' ;

 

명령을 날려서 강제로 종료 하는 경우가 많음

 

그런데 이렇게 해도 해제가 되지 않고 오류메세지와 함께

'ORA-00031 : session marked for kill ' 이라는 메세지를 띄워주고 종료되어버림

 

물론 기다리면 해제되는 경우가 일반적이고 정상적인 Rollback 처리를 위해서는 기다리는 걸 권함

본문에서는 rollback 중인 내용을 확인하는 방법을 설명함

 

 

Undo 현재 사용량 확인

SQL> 
col SID_SERIAL for a10
col ORAUSER for a10
col PROGRAM for a30
col UNDOSEG for a30
col undo for a10
SELECT TO_CHAR(s.sid)||',' ||TO_CHAR(s.serial#) sid_serial,
         NVL(s.username, 'None') orauser,
         s.program,
         r.name undoseg,
         t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM   sys.v_$rollname    r,
        sys.v_$session     s,
        sys.v_$transaction t,
        sys.v_$parameter   x
WHERE s.taddr = t.addr
AND r.usn   = t.xidusn(+)
AND x.name  = 'db_block_size'
/
 
SID_SERIAL ORAUSER    PROGRAM                             UNDOSEG                Undo
---------- ---------- ------------------------------ ------------------------------ ----------
46,1127    JSH          sqlplus@prsjedb2 (TNS V1-V3)   _SYSSMU14_2585002358$        65552K

 

 

현재 Rollback 진행률 확인

SQL>
col OPNAME for a10
col TARGET for a20 
col SOFAR for 999999999
col UNITS for a10
select OPNAME,TARGET,SOFAR,TOTALWORK,UNITS,START_TIME,TIME_REMAINING, ELAPSED_SECONDS 
from v$session_longops where username='JSH'
/
 
OPNAME       TARGET             SOFAR  TOTALWORK UNITS     START_TIM TIME_REMAINING ELAPSED_SECONDS
---------- -------------------- ---------- ---------- ---------- --------- -------------- ---------------
Table Scan JSH.MAXTEST             25397    25397 Blocks     02-JAN-19        0           36
Table Scan JSH.MAXTEST             25397    25397 Blocks     02-JAN-19        0           67

Oracle Database 10g에서는 transaction rollback이 수행될때 , 진행중인 transaction이 

$SESSION_LONGOPS view에 event가 기록되어짐

이 view는 10g 이전 데이타베이스에도 존재하였지만, transaction을 rollback을 하는것은 

기록하지 못했었음. 

Rollback의 경우에 , 6초보다 길어지는 경우에 이 view에 기록되어짐

 

$SESSION_LONGOPS view는 모든 진행중인 작업에 대해 보여주는데, "Transaction Rollback" 

이라고 기록되어진 OPNAME 컬럼을 통해 확인할수 있음

 

각 컬럼 설명

TIME_REMAINING : 예상된 남겨진 시간을 보여줌 (초단위) 

ELAPSED_SECONDS : 이제까지 수행한 시간을 보여줌 (초단위) 

TOTALWORK : 수행하는 작업의 전체 양을 보여줌

SOFAR : 이제까지 수행한 작업의 양을 보여줌

UNITS : 작업한 양의 단위를 보여줌

LAST_UPDATE_TIME : 해당 작업에 대한 통계가 마지막으로 업데이트된 시간

 

그러나 어쩔 수 없이 강제 해제를 시켜야 한다면 다음과 같이 처리 하면 됨

 

# 리눅스/유닉스 계열

SQL> 
SELECT
X.SID,
X.USERNAME,
X.OSUSER,
X.PROCESS AS FG_PID,
Y.SPID BG_PID
FROM V$SESSION X, V$PROCESS Y
WHERE X.PADDR = Y.ADDR 
AND X.USERNAME IS NOT NULL
/
 
       SID USERNAME              OSUSER             FG_PID           BG_PID
---------- ------------------------------ ---------------------------------
       27 SYS                  oracle             6124              18598
       48 JSH                  oracle             5139              11271
       45 SYS                  oracle             6124              9601
       46 JSH                  oracle             11796              11809

 

 

해당 쿼리를 SYSTEM 계정에서 실행한 뒤 PID(PROCESSID)를 확인 후 시스템 접근 후 해당 프로세스를 종료 하면 됨

$ KILL -9 BG_PID

 

 

# 윈도우 계열

윈도우 계열에서는 ORAKILL이라는 유틸리티를 제공함

SELECT
X.SID,
X.USERNAME,
X.OSUSER,
X.PROCESS AS FG_PID,
Y.SPID BG_PID
FROM V$SESSION X, V$PROCESS Y
WHERE X.PADDR = Y.ADDR ;
* ORACL = SID
* 1234 = BG_PID
C:\> ORAKILL ORACL 1234;

이렇게 처리 하면 해당 프로세를 강제 종료하여 해당 세션의 값을 강제 종료 할 수 있다. 그러나 가급적 LOCK이 걸렸을 때에는 원인을 분석하여 해당 쿼리 또는 응용프로그램을 확인한 후 처리 하는 것을 권고 함

 

 

원인 : kill 명령 후 트랜젝션 rollback 중이기 때문에 바로 kill 되지 않은 것

dml에 의해 lock 가 걸리고 해당 세션이 끊겼을때 rollback 중인 내용이 많을 때 발생

 

기다리면 해제되는 경우가 일반적이고 정상적인 Rollback 처리를 위해서는 기다리는 걸 권함

 

 

참조 : 
http://uple.net/868 [공간사랑]
http://redkite777.tistory.com/entry/ORA00031-session-marked-for-kill-의-해결책 [All Days 무한도전]http://support.dbworks.co.kr/index.php?document_srl=3534&mid=ora_tb
1493261.1