Redo內部解析-Single Row update （五）

  八、更新表一行資料

   1、建立測試環境

      SQL> create table test (id number, test varchar2(20));
      SQL> insert into test values( 1, 'test1');
      SQL>commit;
      SQL> col scn format 999999999990
    
       SQL>select dbms_flashback.get_system_change_number scn from dual;
              SCN
         ----------------
        56402954217

      SQL>update test set test='test' where id=1;
      SQL>commit;
     
      SQL>select dbms_flashback.get_system_change_number scn from dual;
              SCN
         ----------------
          56402954385
    
     SQL>alter system dump logfile
               '/home/xxxxxx/oracle/oradata/xxxx/redo02.log'
               scn min 56402954981
               scn max 56402954991;

     2、解析trace檔案

REDO RECORD - Thread:1 RBA: 0x005ffc.00000002.0010 LEN: 0x0240 VLD: 0x0d
SCN: 0x000d.21e0caec SUBSCN:  5 07/21/2009 15:44:24
CHANGE #1 TYP:2 CLS: 1 AFN:4 DBA:0x01000010 OBJ:142708 SCN:0x000d.21e0c98a SEQ:  2 OP:11.5
KTB Redo
op: 0x11  ver: 0x01
op: F  xid:  0x0003.02a.000052f9    uba: 0x00800377.4ee5.33
Block cleanout record, scn:  0x000d.21e0cae9 ver: 0x01 opt: 0x02, entries follow...
  itli: 2  flg: 2  scn: 0x000d.21e0c98a
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000010  hdba: 0x0100000b
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 0
ncol: 2 nnew: 1 size: -1
col  1: [ 4]  74 65 73 74

--OP: 11.5表示更新行操作程式碼，
--URP: Update row piece 更新行操作
--更新的內容：74 65 73 74 （ASCII對應test)
--TYP:2 delayed logging CLS: 1 Data Block

CHANGE #2 TYP:0 CLS:21 AFN:2 DBA:0x00800029 OBJ:4294967295 SCN:0x000d.21e0ca32 SEQ:  1 OP:5.2
ktudh redo: slt: 0x002a sqn: 0x000052f9 flg: 0x0012 siz: 160 fbi: 0
            uba: 0x00800377.4ee5.33    pxid:  0x0000.000.00000000
-- Update Undo Header

CHANGE #3 TYP:0 CLS:21 AFN:2 DBA:0x00800029 OBJ:4294967295 SCN:0x000d.21e0caec SEQ:  1 OP:5.4
ktucm redo: slt: 0x002a sqn: 0x000052f9 srt: 0 sta: 9 flg: 0x12
ktucf redo: uba: 0x00800377.4ee5.33 ext: 3 spc: 988 fbi: 0

-- Commit操作

CHANGE #4 TYP:0 CLS:22 AFN:2 DBA:0x00800377 OBJ:4294967295 SCN:0x000d.21e0ca31 SEQ:  1 OP:5.1
ktudb redo: siz: 160 spc: 1150 flg: 0x0012 seq: 0x4ee5 rec: 0x33
            xid:  0x0003.02a.000052f9
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: 0x00800377.4ee5.32
prev ctl max cmt scn:  0x000d.21e0bf95  prev tx cmt scn:  0x000d.21e0c0e9
txn start scn:  0x0000.00000000  logon user: 55  prev brb: 8389451  prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
op: L  itl: xid:  0x000a.005.00004d71 uba: 0x0080021a.44dc.2b
                      flg: C---    lkc:  0     scn: 0x000d.21e0c97a
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000010  hdba: 0x0100000b
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 1
col  1: [ 5]  74 65 73 74 31

--Undo記錄的內容74 65 73 74 31 (ASCII對應是test1),回滾操作是URP是更新行。

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