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 현재 사용량 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
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 진행률 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 : 해당 작업에 대한 통계가 마지막으로 업데이트된 시간
그러나 어쩔 수 없이 강제 해제를 시켜야 한다면 다음과 같이 처리 하면 됨
# 리눅스/유닉스 계열
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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)를 확인 후 시스템 접근 후 해당 프로세스를 종료 하면 됨
|
1
|
$ KILL -9 BG_PID
|
# 윈도우 계열
윈도우 계열에서는 ORAKILL이라는 유틸리티를 제공함
|
1
2
3
4
5
6
7
8
9
10
11
|
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