1          問題描述

Oracle 資料庫活動事務異常中斷後，資料庫會進入事務恢復狀態，通過查詢x$ktuxe表可以檢查事務恢復資訊和事務恢復進度。

2          解釋x$ktuxe 表

ktuxe(Kernel Transaction Undo tranXaction Entry) 表示資料庫中的事務表，是undo 段頭塊中的一部分，由儲存事務資訊的一組事務槽組成。

 

3          詳解x$ktuxe 欄位含義

x$ktuxe 包含以下欄位，

SYS@source>desc x$ktuxe;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 KTUXEUSN                                           NUMBER
 KTUXESLT                                           NUMBER
 KTUXESQN                                           NUMBER
 KTUXERDBF                                          NUMBER
 KTUXERDBB                                          NUMBER
 KTUXESCNB                                          NUMBER
 KTUXESCNW                                          NUMBER
 KTUXESTA                                           VARCHAR2(16)
 KTUXECFL                                           VARCHAR2(24)
 KTUXEUEL                                           NUMBER
 KTUXEDDBF                                          NUMBER
 KTUXEDDBB                                          NUMBER
 KTUXEPUSN                                          NUMBER
 KTUXEPSLT                                          NUMBER
 KTUXEPSQN                                          NUMBER
 KTUXESIZ                                           NUMBER
接下來我們通過實驗的方式觀察以上表核心欄位的含義：
【測試指令碼】
create table system.test_undo27 as select * from dba_objects;
begin
 for i in 1..50 loop
   insert into system.test_undo27 select * from dba_objects;
   commit;
 end loop;
end;
/
delete from system.test_undo27;
【v$transaction】
select addr,xidusn,
           xidslot,
           xidsqn,
           ubafil,
           ubablk,
           ubasqn,
           ubarec,
          used_ublk,
          used_urec
from v$transaction;

 

                                             

【x$ktuxe 資訊】

select * from x$ktuxe where ktuxesta='ACTIVE';

 

【KTUXEUEL 欄位驗證】

 

 

4          dump undo header

oradebug setmypid

alter system dump undo header '_SYSSMU2$';

oradebug close_trace

oradebug tracefile_name

 

 

index: Slot number

 

STA (State)
0 Idle
1 Collecting
2 Prepared
3 Committed
4 Heuristic Abort (forced rollback)
5 Heuristic Commit (forced commit)
6 Heuristic Damage (forced mixed)
7 Try again later
9 Inactive, no local Tx
10 Active, begin local Tx
11 Pdml Prepared
12 Pdml Committed
13 For in-doubt use onl

 

DBA : Rollback DBA, undo bk to start the rollback

 

 

5          事務恢復

 

SYS@source>select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum < 2));
 
SPID
------------------------------------
48510
 
kill -9 48510
 
 
----查詢DEAD狀態事務
select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ  from x$ktuxe where  KTUXECFL ='DEAD';
ADDR               KTUXEUSN   KTUXESLT   KTUXESQN   KTUXESIZ
---------------- ---------- ---------- ---------- ----------
00007F74EC119E30          2          4        315      59699
 
---計算DEAD事務恢復完成需要時間(秒)
declare
  t_start number;
  t_end   number;
begin
  select ktuxesiz
    into t_start
    from x$ktuxe
   where KTUXEUSN = 2
     and KTUXESLT = 4;
  dbms_lock.sleep(60);
  select ktuxesiz
    into t_end
    from x$ktuxe
   where KTUXEUSN = 2
     and KTUXESLT = 4;
  dbms_output.put_line('Time Need Sec:' ||
                       round(t_end / (t_start - t_end), 2));
end;
/

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31442014/viewspace-2680394/，如需轉載，請註明出處，否則將追究法律責任。