update操作中的Redo记录（change vector的集合）

来源：互联网发布：hbuilder app源码下载编辑：程序博客网时间：2024/06/07 19:48

以修改emp表中一条记录中某个字段值为例，来说明Redo信息内容

强制切换日志文件，以保证使用新的日志文件

SQL> alter system switch logfile;

System altered

update操作并提交

SQL> update emp set sal=3000 where empno=7788;

1 row updated

SQL> commit;

Commit complete

确认当前日志文件是哪个

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
         1          1        118   52428800          1 NO       CURRENT               63527376 2014/3/26 8
         2          1        116   52428800          1 YES      ACTIVE                63501547 2014/3/25 2
         3          1        117   52428800          1 YES      ACTIVE                63523908 2014/3/26 8

SQL> select * from v$logfile;

    GROUP# STATUS TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
         3         ONLINE /home/oracle/app/oradata/ora11g/redo03.log                                       NO
         2         ONLINE /home/oracle/app/oradata/ora11g/redo02.log                                       NO
         1         ONLINE /home/oracle/app/oradata/ora11g/redo01.log                                       NO

转储日志文件

SQL> alter system dump logfile '/home/oracle/app/oradata/ora11g/redo01.log';

System altered

找到对应trace文件并分析

SQL> select * from v$diag_info where name ='Default Trace File';

INST_ID NAME VALUE
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------
1 Default Trace File /home/oracle/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_27538.trc

找出文件对应ID

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
         1 /home/oracle/app/oradata/ora11g/system01.dbf
         2 /home/oracle/app/oradata/ora11g/sysaux01.dbf
         3 /home/oracle/app/oradata/ora11g/undotbs01.dbf
         4 /home/oracle/app/oradata/ora11g/users01.dbf
         5 /opt/oracle/ora11g/MSGAGENT.ora
         6 /opt/oracle/ora11g/MSGAGENTIDX.ora
         7 /home/oracle/oradata/ap_table
         8 /home/oracle/oradata/ap_index
         9 /home/oracle/oradata/group_table
        10 /home/oracle/oradata/group_clob
        11 /home/oracle/oradata/group_index
        12 /home/oracle/oradata/ps_table
        13 /home/oracle/oradata/ps_clob
        14 /home/oracle/oradata/ps_index
        15 /home/oracle/oradata/ome_mc_4K
        16 /home/oracle/oradata/ome_mc_4k_index
        17 /home/oracle/oradata/ome_mc_4k_temp
        18 /home/oracle/oradata/mlee_data.dbf
        19 /home/oracle/oradata/mlee_index.dbf
        20 /home/oracle/app/oradata/ora11g/ebms0528.ora
        21 /home/oracle/app/oradata/ora11g/ebms0528IDX.ora
        22 /home/oracle/app/oradata/ora11g/report.ora
        23 /home/oracle/app/oradata/ora11g/reportIDX.ora
        24 /home/oracle/app/oradata/ora11g/egroup0528index01
        25 /home/oracle/app/oradata/ora11g/egroup0528data01
        26 /home/oracle/app/oradata/ora11g/econsole0528.ora
        27 /home/oracle/app/oradata/ora11g/econsole0528IDX.ora
        28 /home/oracle/app/oradata/ora11g/jforum.ora
        29 /home/oracle/oradata/temp/eygle.f
        30 /home/oracle/oradata/temp/eygle1.f
        31 /home/oracle/oradata/temp/eygle2.f
        32 /home/oracle/oradata/temp/eygle3.f
        33 /home/oracle/oradata/temp/eygle4.f

ora11g_ora_27538.trc文件中内容如下：

REDO RECORD - Thread:1 RBA: 0x000076.00000030.0170 LEN: 0x01a8 VLD: 0x01
SCN: 0x0000.03c95a0b SUBSCN: 1 03/26/2014 08:25:14
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110OBJ:4294967295 SCN:0x0000.03c95a01 SEQ: 1 OP:5.2 ENC:0   //改变向量1，对回滚段头的修改操作（OP得出），分配事务表，文件ID为3（AFN）得出是UNDO表空间
ktudh redo: slt: 0x000d sqn: 0x00010578 flg: 0x0412 siz: 176 fbi: 1
            uba: 0x00c00c52.2067.45    pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:36 AFN:3 DBA:0x00c00c52OBJ:4294967295 SCN:0x0000.03c9594e SEQ: 54OP:5.1 ENC:0 //改变向量2，记录前镜像信息（即sal为4000的原记录），对undo块或undo header操作（OP得出），仍是UNDO表空间（AFN）
ktudb redo: siz: 176 spc: 2538 flg: 0x0012 seq: 0x2067 rec: 0x45
            xid: 0x000a.00d.00010578
ktubl redo: slt: 13 rci: 0 opc: 11.1 [objn: 443 objd: 443 tsn: 0]
Undo type: Regular undo        Begin trans    Last buffer split: No
Temp Object: No
Tablespace Undo: No
             0x00000000 prev ctl uba: 0x00c00c56.2067.3d
prev ctl max cmt scn: 0x0000.03c955bd prev tx cmt scn: 0x0000.03c955f5
txn start scn: 0xffff.ffffffff logon user: 0 prev brb: 12586050 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0009.00c.000103bc uba: 0x00c00d3f.1e4e.2c
                      flg: C---    lkc: 0     scn: 0x0000.03c9272f
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x00400bc1 hdba: 0x00400ba8
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 85(0x55) flag: 0x2c lock: 0 ckix: 119
ncol: 9 nnew: 7 size: 0
Vector content:
col 2: [ 2] c1 03      //对于col 2的修改，修改前的值为4000
col 3: [ 1] 80
col 4: [ 1] 80
col 5: [ 1] 80
col 6: [ 1] 80
col 7: [ 1] 80
col 8: [ 7] 78 72 03 12 10 1a 27
CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x00400bc1 OBJ:443 SCN:0x0000.03c946c5 SEQ: 1OP:11.5 ENC:0  //改变向量3，数据块的修改操作（OP），SYSTEM表空间
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x000a.00d.00010578    uba: 0x00c00c52.2067.45
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x00400bc1 hdba: 0x00400ba8
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 85(0x55) flag: 0x2c lock: 1 ckix: 119
ncol: 9 nnew: 7 size: 0
Vector content:
col 2: [ 2] c1 04   //col 2的修改，修改后的值为3000
col 3: [ 1] 80
col 4: [ 1] 80
col 5: [ 1] 80
col 6: [ 1] 80
col 7: [ 1] 80
col 8: [ 7] 78 72 03 1a 09 1a 0f
CHANGE #4 TYP:0 CLS:21 AFN:3 DBA:0x00c000a0 OBJ:4294967295 SCN:0x0000.03c959d9 SEQ: 1OP:5.4 ENC:0 //改变向量4，事务提交（OP）
ktucm redo: slt: 0x0018 sqn: 0x000103d3 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c005b7.2158.06 ext: 2 spc: 7482 fbi: 0

0 0