关于回滚机制的一些测试
2024-07-21 02:35:48
供稿：网友
SQL> connect test/test@test
已连接。
SQL> create table test (a number);
表已创建。
SQL> insert into test values(1);
已创建 1 行。
SQL> select segment_name,header_file,header_block from dba_segments where segmen
t_name like 'TEST';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
TEST 1 35387
SQL> alter system dump datafile 1 block 35388;
系统已更改。
DUMP出数据头文件查看itl(interested transaction list)
*** 2003-06-09 18:41:19.359
Start dump data blocks tsn: 0 file#: 1 minblk 35388 maxblk 35388
buffer tsn: 0 rdba: 0x00408a3c (1/35388)
scn: 0x0000.00105cd3 seq: 0x04 flg: 0x00 tail: 0x5cd30604
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x00408a3c
Object id on Block? Y
seg/obj: 0x6487 csc: 0x00.105cd2 itc: 1 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0008.000.00000002 uba: 0x00800dc4.0000.05 ---- 1 fsc 0x001c.00000000
/*这里可以看到xid= 0x0008.000.00000002(事务id)
uba= 0x00800dc4.0000.05(undo block address)
lck= 1(受影响的行数)
根据Xid的结构得到
0x0008.000.00000002
0x0008 – Undo Segment Number
000 – Transaction Table Slot Number
00000002– Wrap
根据uba的结构得到
0x00800dc4.0000.05
0x00800dc4– Address of the last undo block used
0000 – Sequence
05 – Last Entry in UNDO record map
*/
data_block_dump
===============
以下省略。。。。。。
根据 0x0008 – Undo Segment Number，
SQL> select a.segment_name,a.header_file,a.header_block from dba_segments a,dba
_rollback_segs b where a.segment_name=b.segment_name and b.segment_id='8';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
RBS7 2 3522
然后dump rbs头查看trans table
Start dump data blocks tsn: 1 file#: 2 minblk 3522 maxblk 3522
buffer tsn: 1 rdba: 0x00800dc2 (2/3522)
scn: 0x0000.00105cd2 seq: 0x01 flg: 0x00 tail: 0x5cd20e01
frmt: 0x02 chkval: 0x0000 type: 0x0e=KTU UNDO HEADER W/UNLIMITED EXTENTS
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 space2: 0 #extents: 8 #blocks: 511
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x00800dc4 ext#: 0 blk#: 1 ext size: 63
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 8 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00800dc3 length: 63
0x00800d42 length: 64
0x00800582 length: 64
0x00800342 length: 64
0x00800482 length: 64
0x008017c2 length: 64
0x00801802 length: 64
0x00800c42 length: 64
TRN CTL:: seq: 0x0000 chd: 0x0001 ctl: 0x0061 inc: 0x00000000 nfb: 0x0000
mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00800dc4.0000.01 scn: 0x0000.00000000
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub
------------------------------------------------------------------------------------------------
0x00 10 0x80 0x0002 0x0000 0x0000.00105cd2 0x00800dc4 0x0000.000.00000000 0x00000001
0x01 9 0x00 0x0001 0x0002 0x0000.00000000 0x00000000 0x0000.000.00000000 0x00000000
以下省略。
。。。。。。
根据从xid中得到的000 – Transaction Table Slot Number
去找到事务表中记载的undo块的地址dba＝0x00800dc4（也可从uba中直接得到）
接下来我们来看一下undo头的地址rdba: 0x00800dc2 (2/3522)
所以我们去dump 3524即undo头+2
*** 2003-06-09 18:42:52.734
Start dump data blocks tsn: 1 file#: 2 minblk 3524 maxblk 3524
buffer tsn: 1 rdba: 0x00800dc4 (2/3524)
scn: 0x0000.00105cd3 seq: 0x04 flg: 0x00 tail: 0x5cd30204
frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK
********************************************************************************
UNDO BLK:
xid: 0x0008.000.00000002 seq: 0x0 cnt: 0x5 irb: 0x5 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f80 0x02 0x1f18 0x03 0x1eb0 0x04 0x1e48 0x05 0x1de0
*-----------------------------
* Rec #0x1 slt: 0x00 objn: 25735(0x00006487) objd: 25735 tblspc: 0(0x00000000)
* 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: 0x00000000.0000.00 ctl max scn: 0x0000.00000000 PRv tx scn: 0x0000.00000000
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: scn: 0x0004.049.000000d8 uba: 0x00800716.009f.3a
flg: C-U- lkc: 0 scn: 0x0000.00105ccf
KDO Op code: DRP xtype: XA bdba: 0x00408a3c hdba: 0x00408a3b
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 1(0x1)
根据KDO Op code: DRP，表明反操作是delete,所以我们可以知道这就是刚才insert后在undo segment里记载的信息
我们知道当发生insert的时候undo segment里仅记载了记录的rowid，下面我们把它找出来
SQL> select rowid from test;
ROWID
------------------
AAAGSHAABAAAIo8AAC
Translate the value: AAAGSHAABAAAIo8AAC
Data Object number = AAAGSH
File = AAB
Block = AAAIo8
ROW = AAC
然后根据公式转换
得到data object number=25735
file=1
block=35388
row=2
