從Undo, Redo, DataFile看Oracle中的事務過程

Commit complete.

2：確定transaction前的scn，
SQL> connect sys/coffee@testdb as sysdba;
Connected.
SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

SQL> connect system/coffee@testdb;
Connected.
SQL> update undo_test set id=456 where id=123;

1 row updated.

4：檢視剛剛操作的transaction及回滾段資訊
SQL> select xidusn, xidslot, xidsqn,ubafil,ubablk,ubasqn,ubarec from v$transaction;

XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
10 27 20807 2 1375 1473 45

XIDUSN：回滾段Number
XIDSLOT：事務槽號
XIDSQN：事務序列號
UBAFIL：UBA所在檔案號
UBABLK：UBA所在塊號
UBASQN：UBA序列號
UBAREC：UBA在Block中的位置

SQL> select usn, name from v$rollname where usn=10;

USN NAME
---------- ------------------------------
10 _SYSSMU10$

這裡的usn即為transaction的xidusn

5：Dump回滾段的頭資訊及塊資訊
SQL> alter system dump undo header '_SYSSMU10$';

System altered.

回滾段的header中會紀錄事務槽資訊，
TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num
------------------------------------------------------------------------------------------------
0x1a 9 0x00 0x5147 0x0019 0x0000.027c09b1 0x0080055f 0x0000.000.00000000 0x00000001 0x00000000
0x1b 10 0x80 0x5147 0x0002 0x0000.027c0b03 0x0080055f 0x0000.000.00000000 0x00000001 0x00000000
0x1c 9 0x00 0x5146 0x001d 0x0000.02778562 0x0080055d 0x0000.000.00000000 0x00000001 0x00000000

注意：state：10為active, 9為inactive
dba：為回滾塊的地址，根據這個地址，我們可以計算出file#及block#
0x0080055f
0000 0000 10 00 0000 0000 0101 0101 1111
---------------- ------------------------------------
file#2 block#1375
當然這個值可以不用計算，transaction的ubafil，ubablk分別為file#, block#。

SQL> alter system dump datafile 2 block 1375;

System altered.

這裡我們可以看到資料的前映象值，
UNDO BLK:
xid: 0x000a.01b.00005147 seq: 0x5c1 cnt: 0x2d irb: 0x2d icl: 0x0 flg: 0x0000

Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1fb4 0x02 0x1f4c 0x03 0x1efc 0x04 0x1ec8 0x05 0x1e78
0x06 0x1e28 0x07 0x1df4 0x08 0x1da4 0x09 0x1d54 0x0a 0x1d20
0x0b 0x1cd0 0x0c 0x1c80 0x0d 0x1c4c 0x0e 0x1bfc 0x0f 0x1bac
0x10 0x1b78 0x11 0x1b28 0x12 0x1ad8 0x13 0x1aa4 0x14 0x1a54
0x15 0x1a04 0x16 0x19d0 0x17 0x1980 0x18 0x1930 0x19 0x18fc
0x1a 0x18ac 0x1b 0x185c 0x1c 0x1828 0x1d 0x17d8 0x1e 0x1788
0x1f 0x16d4 0x20 0x1648 0x21 0x15bc 0x22 0x1518 0x23 0x14c8
0x24 0x1460 0x25 0x13f8 0x26 0x1390 0x27 0x1328 0x28 0x12c0
0x29 0x1258 0x2a 0x11f0 0x2b 0x1188 0x2c 0x1120 0x2d 0x10b8
塊頭反映了這個塊中有多少事務槽，我們要看的就是這個紅色的0x2d, (即transaction的UBAREC=45)

*-----------------------------
* Rec #0x2d slt: 0x1b objn: 30387(0x000076b3) objd: 30387 tblspc: 0(0x00000000)undo_test）
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x0080055f.05c1.2c ctl max scn: 0x0000.0277854e prv tx scn: 0x0000.02778558
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x0040c732 hdba: 0x0040c731
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 1 nnew: 1 size: 0
col 0: [ 3] c2 02 18


6：Dump資料檔案，資料檔案有沒有內容，決定於DBWr程式有沒有將data buffer cache寫出

SQL> select file_id, block_id from dba_extents where segment_name='UNDO_TEST';

FILE_ID BLOCK_ID
---------- ----------
1 50993

SQL> alter system dump datafile 1 block min 50993 block max 50995;

System altered.

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.011.00005147 0x0080055f.05c1.23 C--- 0 scn 0x0000.027c094a
0x02 0x000a.01b.00005147 0x0080055f.05c1.2d ---- 1 fsc 0x0000.00000000
Itl：記錄了該資料塊上發生過的transaction。
Xid：xidusn.xidslot.xidsqn
Uba：uba.ubasqn.ubarec
Xid和Uba的內容和V$transactio中的內容一致。

data_block_dump,data header at 0x30a105c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x030a105c
bdba: 0x0040c732
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f99
avsp=0x1f83
tosp=0x1f83
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f99
block_row_dump:
tab 0, row 0, @0x1f99
tl: 7 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 3] c2 05 39end_of_block_dump
buffer tsn: 0 rdba: 0x0040c733 (1/50995)
scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001
frmt: 0x02 chkval: 0xc073 type: 0x00=unknown
Dump of memory from 0x030A1014 to 0x030A2FFC
30A1010 00000000 00000000 00000000 [............]
30A1020 00000000 00000000 00000000 00000000 [................]
Repeat 508 times
30A2FF0 00000000 00000000 00000000 [............]
End dump data blocks tsn: 0 file#: 1 minblk 50993 maxblk 50995

7：提交事務

SQL> commit;

Commit complete.

SQL> connect sys/coffee@testdb as sysdba;
Connected.

檢視事務提交後的scn，我們將以此作為redo log dump的內容。
SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

MAX(KTUXESCNW*POWER(2,32)+KTUXESCNB)
------------------------------------
41684393

檢視當前活動的redo log，
SQL> select log.group#, log.status, logfile.member
2 from v$log log, v$logfile logfile
3 where log.group#=logfile.group#;

GROUP# STATUS MEMBER
---------- ---------------- ----------------------------------------
1 INACTIVE D:ORACLEORADATATESTDBREDO01.LOG
2 CURRENT D:ORACLEORADATATESTDBREDO02.LOG
3 INACTIVE D:ORACLEORADATATESTDBREDO03.LOG

SQL> alter system dump logfile 'D:ORACLEORADATATESTDBREDO02.LOG' scn min 41683588 scn max 41684393;

System altered.

Redo中對於一個transaction的改變向量紀錄如下

REDO RECORD - Thread:1 RBA: 0x00008a.000004a2.0010 LEN: 0x0190 VLD: 0x01
SCN: 0x0000.027c0b03 SUBSCN: 1 06/11/2009 15:01:35
CHANGE #1 TYP:0 CLS:35 AFN:2 DBA:0x00800099 SCN:0x0000.027c09bb SEQ: 1 OP:5.2ktudh redo: slt: 0x001b sqn: 0x00005147 flg: 0x0012 siz: 104 fbi: 0
uba: 0x0080055f.05c1.2d pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:36 AFN:2 DBA:0x0080055f SCN:0x0000.027c09ba SEQ: 1 OP:5.1

ktudb redo: siz: 104 spc: 4278 flg: 0x0012 seq: 0x05c1 rec: 0x2d
xid: 0x000a.01b.00005147
ktubl redo: slt: 27 rci: 0 opc: 11.1 objn: 30387 objd: 30387 tsn: 0
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x0080055f.05c1.2c
prev ctl max cmt scn: 0x0000.0277854e prev tx cmt scn: 0x0000.02778558
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x0040c732 hdba: 0x0040c731
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 1 nnew: 1 size: 0
col 0: [ 3] c2 02 18CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x0040c732 SCN:0x0000.027c094a SEQ: 1 OP:11.5
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x000a.01b.00005147 uba: 0x0080055f.05c1.2d
Block cleanout record, scn: 0x0000.027c0b03 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.027c094a
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x0040c732 hdba: 0x0040c731
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 1 nnew: 1 size: 0
col 0: [ 3] c2 05 39CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20session number = 9
serial number = 10
transaction name =

......

CHANGE #1 TYP:0 CLS:35 AFN:2 DBA:0x00800099 SCN:0x0000.027c0b03 SEQ: 1 OP:5.4
ktucm redo: slt: 0x001b sqn: 0x00005147 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x0080055f.05c1.2d ext: 2 spc: 4172 fbi: 0

從上面的過程來看，
Undo：分配事務槽及記錄資料前映象。
DataFile：記錄事務資訊及修改後的資料。
Redo：記錄事務槽分配、回滾段資訊、修改後資料資訊、事務提交資訊等。