저도 실제로는 처음 본 상황이 운영중이 발생하여서 정리하고자 이번 글을 작성합니다.
사실 개념적으로는 알고 있었던(알고 있었다기 보단 정리했었던이 더 맞는거 같습니다. 이전글에 해당 내용이 간단하게 정리되어 있었지만 이해하지는 못한 상태였기 때문에..) 내용인데 실제로 보고 어떻게 대처해야 하는지 어떻게 확인해야하는지에 대해 정리하였습니다.
발생 상황은 간략히 정리하면 큰 트랙잭션의 DML작업을 alter system kill session으로 kill 하였는데 실제로 OS에서는 프로세스가 죽지 않고 계속 살아있었고 이 프로세스를 강제로 OS kill 명령어(kill -9)로 죽이면서 발생하였습니다.
먼저 DML이 일어나면 UNDO 영역이 사용됩니다. commit이 아니라 rollback될 수도 있기 때문입니다. 그것 외에도 UNDO의 역할은 몇개 더 있는데요. 아래와 같습니다. 그중 SMON이 Database 복구에 사용되는 케이스입니다.
사용 용도
- Rollback 명령문 시 트랜잭션 Rollback
- Database 복구
- 읽기일관성 제공
- Flashback 기능을 사용하여 논리적 복구
- Flashback Query 사용하여 이전 시점의 데이터 분석
저는 기본적으로 DML이 일어나고 있는 세션을 kill 할 경우에는 트랜잭션을 기준으로 세션에서 사용하고 있는 v$transaction의 used_block으로 확인합니다. (이 상태는 DB상에는 세션이 끊긴것으로 보이지만 OS상에는 프로세스는 롤백이 끝날 때 까지 계속 떠있는 상태)
하지만 롤백 중인 프로세스까지 OS상에서 죽여버리면 v$transaction 뷰에서는 정보가 나타나지 않게 됩니다.
그렇게 되면 다른 뷰에서 확인을 해야 하는데요. x$ktuxe 뷰입니다. 해당뷰는 시노님같은게 없기 떄문에 SYS에서 확인해야 하며. 이 뷰에서 DEAD transaction을 확인할 수 있습니다.
예)
select ktuxeusn, to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') "Time", ktuxesiz, ktuxesta
from x$ktuxe
where ktuxecfl = 'DEAD';
KTUXEUSN Time KTUXESIZ KTUXESTA
---------- ------------------- ---------- ----------------
134 2023/05/22 16:51:54 4806 ACTIVE
또한 v$fast_start_transactions 를 확인해서도 transaction recovery의 진행상황을 볼 수 있습니다.
예)
SQL> select * from v$fast_start_transactions;
USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS CON_ID
--------- --------- --------- ---------------- -------------- --------------- --------- --------- --------- --------- --------- -------- -------- ---------- ---------
134 3 10753 RECOVERING 1780 243252 83 6 0 0 0 0086000300002A01 0000000000000000 32 0
48 12 21787 RECOVERED 26494 26494 1852 0030000C0000551B 0 0
15 7 28450 RECOVERED 17 17 115 000F000700006F22 1 0
52 22 8465 RECOVERED 10925 10925 58 0034001600002111 32 0그리고 복구 중인 테이블에 대해서 조회는 잘 되지만 DML을 시도시에는 SMON이 해당 테이블을 Exclusive Lock을 잡고 있기 때문에 복구가 끝날때까지 대기하게 됩니다.
Holding ▽ 570 (1) EXCLUSIVE(X) 1 DFS lock handle TX 1 ACTIVE 249177 SYS oracle dbarac1 oracle@dbarac1 (SMON) PRE : gm9t6ycmb1yu6 delete from smon_scn_time where scn = (select min(scn) from smon_scn_time) ALTER SYSTEM KILL SESSION '570,38940,@1' IMMEDIATE;
|-- Waiting 1705 (1) EXCLUSIVE(X) 1 enq: TX - row lock contention TX 0 ORD_ITEM_RANDOM(T) ACTIVE 15 DEV oracle dbarac1 SQL*Plus sqlplus@dbarac1 (TNS V1-V3) baa7taznjgjwt update DEV.ORD_ITEM_RANDOM set UPPER_CASE ='ABCEDFG' ALTER SYSTEM KILL SESSION '1705,1821,@1' IMMEDIATE;
그리고 해당 복구와 관련된 파라미터가 있습니다. fast_start_parallel_rollback 라는 파라미터인데요. 해당 복구시에 parallel 로 복구를 수행하라는 파라미터입니다. default는 LOW이고 이는 CPU*2의 병렬 프로세스만큼 일을 하게하라는 뜻입니다.
FALSE(사용안함),HIGH(CPU*4) 의 값으로 설정가능합니다.
SQL> select name,value,display_value,description From v$parameter where name='fast_start_parallel_rollback';
NAME VALUE DISPLAY_VALUE DESCRIPTION
------------------------------- ---------- ----------------- ---------------------------------------------------------
fast_start_parallel_rollback LOW LOW max number of parallel recovery slaves that may be used
그리고 해당 복구시간을 단축시키는 방법이 SR에서 나와있어서 테스트를 해보았는데 , 실제로 크게 빨라지지는 않았습니다.
복구 단축방법 참고 SR
참고 : How to Disable Parallel Transaction Recovery When Parallel Txn Recovery is Active (Doc ID 238507.1)
해당 SR의 내용에는 병렬로 복구작업을 하면 오히려 느릴 수 있다고 그것을 serial하게 복구하도록 도중에 변경하는 방법에 대해서 설명하고 있습니다.
테스트
테스트에는 일반적으로 값이 LOW인 상황에서 트랜잭션의 kill 상황시 모니터링하는 것과, SR에서 권장하는 방법을 사용시 어떠한 변화가 있는지 테스트를 해보겠습니다.
DB : 12.2.0.1
사용 테이블 : ORD_ITEM_RANDOM
건수 확인
SQL> select count(*) from ORD_ITEM_RANDOM;
COUNT(*)
---------
37100206
세션을 열어 UPDATE 작업 수행
update DEV.ORD_ITEM_RANDOM set UPPER_CASE ='ABCEDFG';
세션 번호 확인하여 os에서 kill하기
$ ps -ef |grep 25755936
root 16712064 25428464 0 15:14:17 pts/6 0:00 grep 25755936
oracle 25755936 50397562 0 14:38:39 pts/3 0:00 sqlplus as sysdba
oracle 63504688 25755936 86 14:46:39 - 0:52 oracledbarac1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
$ kill -9 25755936UPDATE중인 세션에서는 끊기면서 아래와 같은 에러 발생
UPDATE 중인 세션
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 63504688
Session ID: 1895 Serial number: 57709
이 상태에서 세션열어서 해당 테이블 조회와 DML 해보기
SQL> select count(*) from ORD_ITEM_RANDOM;
COUNT(*)
----------
37100206SQL> update DEV.ORD_ITEM_RANDOM set UPPER_CASE ='ABCEDFG';그리고 LOCK 잡혀있는지 확인합니다
DML을 시도시에는 SMON이 해당 테이블을 Exclusive Lock을 잡고 있기 때문에 복구가 끝날때까지 대기하게 됩니다.
Holding ▽ 570 (1) EXCLUSIVE(X) 1 DFS lock handle TX 1 ACTIVE 249177 SYS oracle dbarac1 oracle@dbarac1 (SMON) PRE : gm9t6ycmb1yu6 delete from smon_scn_time where scn = (select min(scn) from smon_scn_time) ALTER SYSTEM KILL SESSION '570,38940,@1' IMMEDIATE;
|-- Waiting 1705 (1) EXCLUSIVE(X) 1 enq: TX - row lock contention TX 0 ORD_ITEM_RANDOM(T) ACTIVE 15 DEV oracle dbarac1 SQL*Plus sqlplus@dbarac1 (TNS V1-V3) baa7taznjgjwt update DEV.ORD_ITEM_RANDOM set UPPER_CASE ='ABCEDFG' ALTER SYSTEM KILL SESSION '1705,1821,@1' IMMEDIATE;모니터링(v$fast_start_transactions)
RECOVERING 인것을 확인하면 되고 진행상황을 확인할 수 있습니다.
SQL> select * from v$fast_start_transactions;
USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS CON_ID
--------- --------- --------- ---------------- -------------- --------------- --------- --------- --------- --------- --------- -------- -------- ---------- ---------
134 3 10753 RECOVERING 1780 243252 83 6 0 0 0 0086000300002A01 0000000000000000 32 0
48 12 21787 RECOVERED 26494 26494 1852 0030000C0000551B 0 0
15 7 28450 RECOVERED 17 17 115 000F000700006F22 1 0
52 22 8465 RECOVERED 10925 10925 58 0034001600002111 32 0
모니터링(x$ktuxe)
ktuxesiz 컬럼을 확인하여 진행상황을 확인할 수 있습니다.
select ktuxeusn, to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') "Time", ktuxesiz, ktuxesta
from x$ktuxe
where ktuxecfl = 'DEAD';
KTUXEUSN Time KTUXESIZ KTUXESTA
---------- ------------------- ---------- ----------------
15 2023/05/22 15:23:23 0 INACTIVE
52 2023/05/22 15:23:23 2564 ACTIVE
v$fast_start_transactions 에 PID부분이 있는데 PID를 확인하면 P000프로세스가 해당 복구작업을 수행하고 있습니다.
SQL> select * from v$process
where pid=83;
ADDR PID SOSID SPID STID EXECUTION_TYPE PNAME USERNAME SERIAL# TERMINAL PROGRAM TRACEID TRACEFILE BACKGROUND LATCHWAIT LATCHSPIN PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM NUMA_DEFAULT NUMA_CURR CON_ID
-------- --------- ---------------------------------- ---------------------------------- ---------------------------------- -------------- ----- --------------- --------- ------------------------------ ------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------------- ---------------- ------------ ------------- ---------------- ----------- ------------ --------- ---------
07000200368C6798 83 53805438 53805438 115409323 PROCESS P000 oracle 1 UNKNOWN oracle@dbarac1 (P000) /oracle/db/base/diag/rdbms/dbarac/dbarac1/trace/dbarac1_p000_53805438.trc 2715837 3003717 65536 3003717 268435455 268435455 0
1 rows selected.
V$FAST_START_SERVERS 뷰를 조회해서 PARALLEL프로세스가 몇개 사용중인지 확인할 수 있습니다. 다만 제가 확인했을 때는 여러개 중 한개만 일하고 있는것 처럼 조회되었습니다.
SQL> select * from V$FAST_START_SERVERS;
STATE UNDOBLOCKSDONE PID XID CON_ID
----------- -------------- --------- -------- ---------
RECOVERING 15605 83 0086000300002A01 0
RECOVERING 0 86 0086000300002A01 0
RECOVERING 0 87 0086000300002A01 0
RECOVERING 0 89 0086000300002A01 0
RECOVERING 0 90 0086000300002A01 0
RECOVERING 0 91 0086000300002A01 0
RECOVERING 0 92 0086000300002A01 0
RECOVERING 0 93 0086000300002A01 0
RECOVERING 0 94 0086000300002A01 0
RECOVERING 0 95 0086000300002A01 0
RECOVERING 0 96 0086000300002A01 0
RECOVERING 0 97 0086000300002A01 0
RECOVERING 0 98 0086000300002A01 0
RECOVERING 0 99 0086000300002A01 0
RECOVERING 0 101 0086000300002A01 0
RECOVERING 0 102 0086000300002A01 0
RECOVERING 0 103 0086000300002A01 0
RECOVERING 0 104 0086000300002A01 0
RECOVERING 0 105 0086000300002A01 0
RECOVERING 0 106 0086000300002A01 0
RECOVERING 0 107 0086000300002A01 0
RECOVERING 0 108 0086000300002A01 0
RECOVERING 0 109 0086000300002A01 0
RECOVERING 0 110 0086000300002A01 0
RECOVERING 0 111 0086000300002A01 0
RECOVERING 0 112 0086000300002A01 0
RECOVERING 0 113 0086000300002A01 0
RECOVERING 0 114 0086000300002A01 0
RECOVERING 0 115 0086000300002A01 0
RECOVERING 0 116 0086000300002A01 0
RECOVERING 0 117 0086000300002A01 0
RECOVERING 0 118 0086000300002A01 0
32 rows selected.
################################################################################################
parallel로 복구 중인 작업을 Serial하게 변경하기.
Parallel로 복구중인 경우 느린 케이스가 있을 수 있고, 이것을 빠르게 완료하기 위한 방법 중 하나라고 SR에서는 설명하고 있습니다. (제가 실제 테스트했을 때는 빠른것을 못 느꼈습니다.)
1.SMON pid 확인
SQL> select pid,pname From v$process
where pname='SMON';
PID PNAME
--------- -----
35 SMON
2. oradebug 명령어로 현재 복구작업 disable 시키기
SQL> oradebug setorapid 35;
SQL> oradebug event 10513 trace name context forever, level 2;로그 확인
로그는 SMON의 로그입니다.
*** 2023-05-22T15:49:53.036644+09:00
Received ORADEBUG command (#1) 'event 10513 trace name context forever, level 2' from process '12255674'
*** 2023-05-22T15:49:53.037031+09:00
Finished processing ORADEBUG command (#1) 'event 10513 trace name context forever, level 2'
*** 2023-05-22T15:51:45.704469+09:00
SMON: parallel recovery restart with degree=0 (!=32)
Parallel Transaction recovery caught exception 30312
*** 2023-05-22T15:51:48.004479+09:00
Parallel Transaction recovery caught error 30312
SMON:Event 10513 level 2,trans recovery disabled in pdb 0
3. 현재 복구 작업중인 P000프로세스 강제로 죽이고 system 명령어로 parameter 값 변경
SQL> select SPID from V$PROCESS where PID = 83;
SPID
----------------------------------
46137642
$ ps -ef |grep 46137642
oracle 8520180 26280192 0 16:15:38 pts/5 0:00 grep 46137642
oracle 46137642 1 5 16:15:01 - 0:00 ora_p000_dbarac1
$ kill -9 46137642
$ ps -ef |grep 46137642
oracle 16449928 26280192 0 16:16:25 pts/5 0:00 grep 46137642SQL> alter system set fast_start_parallel_rollback=false;SQL> select name,value From v$parameter where name='fast_start_parallel_rollback';
NAME VALUE
------------------------------- -------
fast_start_parallel_rollback FALSE
4.SMON 다시 enable
SQL> oradebug setorapid 35;
SQL> oradebug event 10513 trace name context off
로그 확인
작업 전에는 Parallel Transaction recovery caught error 라고 떴는데 작업 후에는 Serial 로 바뀐것을 확인할 수 있습니다.
*** 2023-05-22T15:44:42.644524+09:00
Parallel Transaction recovery caught exception 30319
Parallel Transaction recovery caught error 30319
Parallel Transaction recovery caught exception 30319
*** 2023-05-22T15:48:42.838687+09:00
Parallel Transaction recovery caught error 30319
...
*** 2023-05-22T15:52:05.008193+09:00
Received ORADEBUG command (#2) 'event 10513 trace name context off' from process '49807758'
*** 2023-05-22T15:52:05.008652+09:00
Finished processing ORADEBUG command (#2) 'event 10513 trace name context off'
Serial Transaction recovery caught exception 601
...
모니터링
recovery를 모니터링할 수 있지만 PID가 따로 보이지는 않습니다.
SQL> select * from v$fast_start_transactions;
USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS CON_ID
--------- --------- --------- ---------------- -------------- --------------- --------- --------- --------- --------- --------- -------- -------- ---------- ---------
48 12 21787 RECOVERING 1813 93494 93 0030000C0000551B 0 0
1 rows selected.
select ktuxeusn, to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') "Time", ktuxesiz, ktuxesta
from x$ktuxe
where ktuxecfl = 'DEAD';
KTUXEUSN Time KTUXESIZ KTUXESTA
---------- ------------------- ---------- ----------------
48 2023/05/19 16:33:20 86851 ACTIVE
131 2023/05/19 16:33:20 0 INACTIVE
모니터링 두쿼리 다 나타납니다. 하지만 그대로 뒀을때(Parallel)보다 복구 블록이 중간중간 멈춰있는 경우 가 많았고, 오히려 복구 종료경과 시간은 더 늦었습니다.
system 파라미터도 변경하는 방법이므로 제 생각에는 추천해주만한 방법은 아닌거 같습니다. (테스트결과도 그러합니다..)
이상입니다.
출처 : https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=hanccii&logNo=220741477253
큰 트랜잭션을 kill 했을 경우의 SMON 복구
큰 트랜잭션을 kill 했을 경우 SMON이 복구하는 과정에서 wait for a undo record 이벤트와 PX...
blog.naver.com
'Oracle > 운영' 카테고리의 다른 글
| 테이블 컬럼삭제 Drop Column과 Unused (0) | 2023.07.19 |
|---|---|
| [ACL,UTL_SMTP] 10g,11g 메일링 서비스 관련 이슈 (0) | 2023.06.16 |
| [ASM] diskgroup에 disk 추가하기 (0) | 2023.05.16 |
| [Single][linux] Oracle 자동 재기동 쉘 작성&등록 (0) | 2023.04.26 |
| 파티션 테이블 옵션사용 여부에 따른 인덱스 변화 체크 테스트 (0) | 2023.04.14 |
댓글