九、删除一行数据
1、创建实验环境
SQL>col scn format 999999999990
SQL>select dbms_flashback.get_system_change_number scn from dual;
SQL>delete from test where id=1;
SQL>select dbms_flashback.get_system_change_number scn from dual;
SQL>alter system dump logfile
'/home/xxxx/oracle/oradata/xxxxx/redo02.log'
scn min 56402955922
scn max 56402955923;
2、解析trace文件
REDO RECORD - Thread:1 RBA: 0x005ffc.00000545.0010 LEN: 0x01d4 VLD: 0x0d
SCN: 0x000d.21e0ce93 SUBSCN: 6 07/21/2009 16:21:13
CHANGE #1 TYP:2 CLS: 1 AFN:4 DBA:0x01000010 OBJ:142708 SCN:0x000d.21e0caec SEQ: 2 OP:11.3
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0007.02a.00005303 uba: 0x008009e0.4ada.1a
Block cleanout record, scn: 0x000d.21e0ce93 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x000d.21e0caec
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000010 hdba: 0x0100000b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
--OP: 11.3 Delete单行数据
--DRP: delete row piece
--Type:2 Delayed logging ,CLS:1 Data Block
--DBA:01000010 可以转化成file#, block#,具体参看"Redo内部解析(二)"
--OBJ:对应dba_objects.object_id
--Xid: usn#.slot#.wrap#
--UBA: undo block addr.Seq#.Record#
CHANGE #2 TYP:0 CLS:29 AFN:2 DBA:0x00800069 OBJ:4294967295 SCN:0x000d.21e0ce19 SEQ: 1 OP:5.2
ktudh redo: slt: 0x002a sqn: 0x00005303 flg: 0x0012 siz: 176 fbi: 0
uba: 0x008009e0.4ada.1a pxid: 0x0000.000.00000000
--update undo header,具体段位置(29-11)/2=9
--type 0: normal
CHANGE #3 TYP:0 CLS:30 AFN:2 DBA:0x008009e0 OBJ:4294967295 SCN:0x000d.21e0ce18 SEQ: 1 OP:5.1
ktudb redo: siz: 176 spc: 2568 flg: 0x0012 seq: 0x4ada rec: 0x1a
xid: 0x0007.02a.00005303
ktubl redo: slt: 42 rci: 0 opc: 11.1 objn: 142708 objd: 142708 tsn: 4
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x008009e0.4ada.19
prev ctl max cmt scn: 0x000d.21e0c5eb prev tx cmt scn: 0x000d.21e0c71d
txn start scn: 0x000d.21e0caec logon user: 55 prev brb: 8391107 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0007.02b.00005308 uba: 0x008009d6.4ada.13
flg: C--- lkc: 0 scn: 0x000d.21e0c98a
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000010 hdba: 0x0100000b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 11
fb: --H-FL-- lb: 0x0 cc: 2
null: --
col 0: [ 2] c1 02
col 1: [ 4] 74 65 73 74
--Undo记录操作相反的语句,IRP:Insert Row Piece
--插入数据值记录col 0和col 1
1、创建实验环境
SQL>col scn format 999999999990
SQL>select dbms_flashback.get_system_change_number scn from dual;
SQL>delete from test where id=1;
SQL>select dbms_flashback.get_system_change_number scn from dual;
SQL>alter system dump logfile
'/home/xxxx/oracle/oradata/xxxxx/redo02.log'
scn min 56402955922
scn max 56402955923;
2、解析trace文件
REDO RECORD - Thread:1 RBA: 0x005ffc.00000545.0010 LEN: 0x01d4 VLD: 0x0d
SCN: 0x000d.21e0ce93 SUBSCN: 6 07/21/2009 16:21:13
CHANGE #1 TYP:2 CLS: 1 AFN:4 DBA:0x01000010 OBJ:142708 SCN:0x000d.21e0caec SEQ: 2 OP:11.3
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0007.02a.00005303 uba: 0x008009e0.4ada.1a
Block cleanout record, scn: 0x000d.21e0ce93 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x000d.21e0caec
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000010 hdba: 0x0100000b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
--OP: 11.3 Delete单行数据
--DRP: delete row piece
--Type:2 Delayed logging ,CLS:1 Data Block
--DBA:01000010 可以转化成file#, block#,具体参看"Redo内部解析(二)"
--OBJ:对应dba_objects.object_id
--Xid: usn#.slot#.wrap#
--UBA: undo block addr.Seq#.Record#
CHANGE #2 TYP:0 CLS:29 AFN:2 DBA:0x00800069 OBJ:4294967295 SCN:0x000d.21e0ce19 SEQ: 1 OP:5.2
ktudh redo: slt: 0x002a sqn: 0x00005303 flg: 0x0012 siz: 176 fbi: 0
uba: 0x008009e0.4ada.1a pxid: 0x0000.000.00000000
--update undo header,具体段位置(29-11)/2=9
--type 0: normal
CHANGE #3 TYP:0 CLS:30 AFN:2 DBA:0x008009e0 OBJ:4294967295 SCN:0x000d.21e0ce18 SEQ: 1 OP:5.1
ktudb redo: siz: 176 spc: 2568 flg: 0x0012 seq: 0x4ada rec: 0x1a
xid: 0x0007.02a.00005303
ktubl redo: slt: 42 rci: 0 opc: 11.1 objn: 142708 objd: 142708 tsn: 4
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x008009e0.4ada.19
prev ctl max cmt scn: 0x000d.21e0c5eb prev tx cmt scn: 0x000d.21e0c71d
txn start scn: 0x000d.21e0caec logon user: 55 prev brb: 8391107 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0007.02b.00005308 uba: 0x008009d6.4ada.13
flg: C--- lkc: 0 scn: 0x000d.21e0c98a
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000010 hdba: 0x0100000b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 11
fb: --H-FL-- lb: 0x0 cc: 2
null: --
col 0: [ 2] c1 02
col 1: [ 4] 74 65 73 74
--Undo记录操作相反的语句,IRP:Insert Row Piece
--插入数据值记录col 0和col 1
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/354732/viewspace-609811/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/354732/viewspace-609811/