비정상 종료 후 복구 시간 모니터링(X$KTUXE)

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

쿼리 : 비정상 종료 후 복구 시간 모니터링

X$KTUXE : Dead Transaction Check 하는 테이블

sql 구문

select * from x$ktuxe where ktuxesta = 'ACTIVE' and ktuxecfl like '%DEAD%';

shadow process를 죽이거나 DB를 abort한 경우 v$transaction 에서의 목록은 사라짐

그래서 v$transaction.used_ublk를 확인하여 롤백이 얼마나 진행되고 있는 지 알 수가 없음

아래 쿼리로 transaction recovery(by SMON) 진행율을 조회할 수 있음

1 2 3 4 5 6 7 8

set linesize 200  alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';  SELECT usn, state,  undoblockstotal "Total",  undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",  decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone)/(undoblocksdone/cputime))/86400)) "Estimated time to complete"  FROM v$fast_start_transactions;

실제 사용 테스트

1. 샘플 테이블 및 인덱스 생성

CREATE TABLE MAXTEST(COLA VARCHAR2(20), COLB NUMBER, COLC NUMBER, 
COLD VARCHAR2(30), COLE VARCHAR2(30), COLF VARCHAR2(30), 
COLG NUMBER, COLH VARCHAR2(30), COLI VARCHAR2(30));
CREATE INDEX IDX_COLB ON MAXTEST(COLB); 

2. 샘플 데이터 삽입(1400000건)

DECLARE
TYPE tbl_ins IS TABLE OF MAXTEST%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..1400000 LOOP 
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=300000;
   w_ins(i).COLC :=99;
   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';
   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
   w_ins(i).COLG :=9999999;
   w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
   w_ins(i).COLI :='IIIIIIIIIIIIIIIIIIIIIIIIII';
END LOOP;
   FORALL i in 1..1400000 INSERT INTO MAXTEST VALUES w_ins(i);
   COMMIT;
END;
/

3. update 구문 실행 

세션2
update maxtest set cold ='AAAAAAAAAAAAAA';
 
세션3
update maxtest set colc =12;

약 20초간 진행시켜줌

4. 세션1에서 강제 종료

shutdown abort.

5. 세션1에서 mount 단계로 startup 진행

startup mount
ORACLE instance started.
 
Total System Global Area  830930944 bytes
Fixed Size            2257800 bytes
Variable Size          562039928 bytes
Database Buffers      264241152 bytes
Redo Buffers            2392064 bytes
Database mounted.

6. 세션1에서 open; 입력과 동시에 세션 4에서 아래의 쿼리 붙여넣기

세션1
alter database open;
 
동시에
세션4
set linesize 200
select * from x$ktuxe where ktuxesta = 'ACTIVE' and ktuxecfl like '%DEAD%';
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 
SELECT usn, state, 
undoblockstotal "Total", 
undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo", 
decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone)/(undoblocksdone/cputime))/86400)) "Estimated time to complete" 
FROM v$fast_start_transactions;

결과가 no rows 로 나오면 붙여넣기 몇번 더 시도해볼것

복구하는시점에 결과가 나옴

결과값 : 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

select * from x$ktuxe where ktuxesta = 'ACTIVE' and ktuxecfl like '%DEAD%';   ADDR               INDX    INST_ID     KTUXEUSN   KTUXESLT   KTUXESQN  KTUXERDBF  KTUXERDBB ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------  KTUXESCNB  KTUXESCNW KTUXESTA           KTUXECFL           KTUXEUEL  KTUXEDDBF  KTUXEDDBB ---------- ---------- ---------------- ------------------------ ---------- ---------- ----------  KTUXEPUSN  KTUXEPSLT  KTUXEPSQN   KTUXESIZ ---------- ---------- ---------- ---------- 00007F148E020C78    178         1        3      12       1955      3    21154    4022129        0 ACTIVE           DEAD                   252        0           0      0        0           0       3839   1 row selected.   SELECT usn, state,  undoblockstotal "Total",  undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",  decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone)/(undoblocksdone/cputime))/86400)) "Estimated time to complete"  FROM v$fast_start_transactions;          USN STATE         Total         Done    ToDo Estimated time to compl ---------- ---------------- ---------- ---------- ---------- -----------------------      3 RECOVERING          4740         1020    3720 12-DEC-2018 11:09:03   1 row selected.

참조 : http://dbtech.co.kr/bbs/?bo_c=1020&bo_v=460