在DML操作之下redo日志的解析

最新推荐文章于 2024-08-27 19:00:35 发布

生命只剩百分之一

最新推荐文章于 2024-08-27 19:00:35 发布

阅读量681

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/qq_22083251/article/details/106517143

Oracle 专栏收录该内容

35 篇文章

订阅专栏

本文详细解析了Oracle数据库中insert、update和delete操作产生的REDO日志，通过实例展示了如何分析REDO日志记录，理解数据库事务的执行过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

实验一：insert 操作的REDO日志解读

1、创建一张测试表

create table tangjian (id number(20),name varchar(20),work varchar(20));

2、查看当前日志所在的日志组

select * from v$log;

alter system switch logfile;(切换日志组为了方便分析redo日志)

sys@ORCL>select * from tangjian;

   ID NAME                     WORK
---------- ---------------------------------------- ----------------------------------------
   1 aa                        zz
   1 bb                        xx
   1 cc                        yy
3、插入一条记录

insert into tangjian values (1,'dd','ii');
commit;

4、确定当前的日志组位置，并dump

oradebug setmypid;

select group#,member from v$logfile;

+SSDDG/orcl/onlinelog/group_9.368.1042043755

alter sysm dump logfile '+SSDDG/orcl/onlinelog/group_9.368.1042043755';

oradebug tracefile_name;

/opt/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_145545.trc

5、查看此对象的object_id信息。

select object_id,data_object_id from dba_objects where object_name='TANGJIAN';

6、分析trace文件内容

REDO RECORD（insert record的记录） - Thread:1 RBA: 0x0003c7.00000002.0010（redo的block地址） LEN: 0x019c VLD: 0x05
SCN: 0x0000.0b9074dc SUBSCN: 1 06/03/2020 09:19:04（插入的SN号）
(LWN RBA: 0x0003c7.00000002.0010 LEN: 0001 NST: 0001 SCN: 0x0000.0b9074dc)
CHANGE #1 TYP:0 CLS:313 AFN:3（undo端头事务表的文件号） DBA:0x00c01fe0（） OBJ:4294967295 SCN:0x0000.0b9057d3 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0009 sqn: 0x00004c47 flg: 0x0012 siz: 112 fbi: 0
            uba: 0x00c01fe2.0174.08    pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:314 AFN:3 DBA:0x00c01fe2 OBJ:4294967295 SCN:0x0000.0b9057d2 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 112 spc: 7192 flg: 0x0012 seq: 0x0174 rec: 0x08
            xid: 0x0095.009.00004c47
ktubl redo: slt: 9 rci: 0 opc: 11.1 [objn: 88027 objd: 88027 tsn: 0]（tsn：0代表表空间编号， 88027就是我们插入的对象object_id 号）
Undo type: Regular undo        Begin trans    Last buffer split: No
Temp Object: No
Tablespace Undo: No
             0x00000000 prev ctl uba: 0x00c01fe2.0174.07
prev ctl max cmt scn: 0x0000.0b8f0930 prev tx cmt scn: 0x0000.0b8f0a5a
txn start scn: 0xffff.ffffffff logon user: 0 prev brb: 71837046 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x004177c9 hdba: 0x004177c8
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3)
CHANGE #3 TYP:2 CLS:1 AFN:1 DBA:0x004177c9 OBJ:88027 SCN:0x0000.0b907404 SEQ:1 OP:11.2 ENC:0 RBL:0
KTB Redo （FAN:1是插入的文件号，OBJ代表的是文件object_id对象）
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1

op: F xid: 0x0095.009.00004c47 uba: 0x00c01fe2.0174.08
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x004177c9 hdba: 0x004177c8
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) size/delt: 12
fb: --H-FL-- lb: 0x2 cc: 3
null: ---
col 0: [ 2] c1 02（代表第一列）---------插入语句是 insert into tangjian values (1,'dd','ii');

sys@ORCL>select dump(1,16) from dual;

DUMP(1,16)
----------------------------------
Typ=2 Len=2: c1,2

col 1: [ 2] 64 64（代表第二列）

sys@ORCL>select dump('dd',16) from dual;

DUMP('DD',16)
--------------------------------------
Typ=96 Len=2: 64,64

col 2: [ 2] 69 69（代表第三列）
sys@ORCL>select dump('ii',16) from dual;

DUMP('II',16)
--------------------------------------
Typ=96 Len=2: 69,69
REDO RECORD（提交record） - Thread:1 RBA: 0x0003c7.00000003.0010 LEN: 0x008c VLD: 0x05
SCN: 0x0000.0b9074de SUBSCN: 1 06/03/2020 09:19:07
(LWN RBA: 0x0003c7.00000003.0010 LEN: 0001 NST: 0001 SCN: 0x0000.0b9074dd)
CHANGE #1 TYP:0 CLS:313 AFN:3 DBA:0x00c01fe0 OBJ:4294967295 SCN:0x0000.0b9074dc SEQ:1 OP:5.4 ENC:0 RBL:0 （OP:5.4代表提交操作）
ktucm redo: slt: 0x0009 sqn: 0x00004c47 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c01fe2.0174.08 ext: 0 spc: 7078 fbi: 0

实验二：update 操作的REDO日志解读

1、切换日志组

alter sysm switch logfile;

    GROUP# SEQUENCE# ARCHIV STATUS                  TYPE         MEMBER
-------- ---------- ------ -------------------------------- --------------
   5      968 NO     ACTIVE                  ONLINE         +SSDDG/orcl/onlinelog/group_5.340.1042027193
   6      969 NO     ACTIVE                  ONLINE         +SSDDG/orcl/onlinelog/group_6.365.1042043747
   7      970 NO     CURRENT                  ONLINE         +SSDDG/orcl/onlinelog/group_7.366.1042043749
   8      965 NO     INACTIVE                  ONLINE         +SSDDG/orcl/onlinelog/group_8.367.1042043751
   9      966 NO     INACTIVE                  ONLINE         +SSDDG/orcl/onlinelog/group_9.368.1042043755
   10      967 NO     ACTIVE                  ONLINE         +SSDDG/orcl/onlinelog/group_10.369.1042043757

2、对表进行更新操作

sys@ORCL>update TANGJIAN SET name='tangjian' where work='zz';

1 row updated.

sys@ORCL>COMMIT;

Commit complete.

3、dump此current日志组。

sys@ORCL>oradebug setmypid;
Statement processed.
sys@ORCL>alter system dump logfile '+SSDDG/orcl/onlinelog/group_7.366.1042043749';

System altered.

sys@ORCL>oradebug tracefile_name;
/opt/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_48063.trc

4、对/opt/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_48063.trc进行分析

REDO RECORD - Thread:1 RBA: 0x0003ca.00000db8.0010（日志文件序列号、块号（可以使用select to_number('3ca','XXXXXX' ) from dual转换）） LEN: 0x01e8 VLD: 0x05
SCN: 0x0000.0b908712 SUBSCN: 1 06/03/2020 10:03:35
(LWN RBA: 0x0003ca.00000db8.0010 LEN: 0001 NST: 0001 SCN: 0x0000.0b908711)
CHANGE #1（对事物表的前镜像保存） TYP:0 CLS:587 AFN:3 DBA:0x00c08300 OBJ:4294967295 SCN:0x0000.0b90736c SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0015 sqn: 0x00006731 flg: 0x0012 siz: 160 fbi: 0
uba: 0x00c08302.01eb.06 pxid: 0x0000.000.00000000
CHANGE #2 （对undo块的前镜像保存）TYP:0 CLS:588 AFN:3 DBA:0x00c08302 OBJ:4294967295 SCN:0x0000.0b90736b SEQ:1 OP:5.1 ENC:0 RBL:0

（OP:5.1 --把数据修改前值放到UNDO   --AFN:3 --在UNDO文件里操作，UNDO文件号是3）
ktudb redo: siz: 160 spc: 7280 flg: 0x0012 seq: 0x01eb rec: 0x06
            xid: 0x011e.015.00006731
ktubl redo: slt: 21 rci: 0 opc: 11.1 [objn: 88027 objd: 88027 tsn: 0]（和update语句一样是object的id以及所在的表空间）
Undo type: Regular undo        Begin trans    Last buffer split: No
Temp Object: No
Tablespace Undo: No
             0x00000000 prev ctl uba: 0x00c08302.01eb.05
prev ctl max cmt scn: 0x0000.0b8f0ee5 prev tx cmt scn: 0x0000.0b8f0ff7
txn start scn: 0xffff.ffffffff logon user: 0 prev brb: 54587576 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: 0x004f.004.00005c65 uba: 0x00c0103a.01ce.0d（记录了事务槽的位置，以及undo 块的地址。）
                      flg: C---    lkc: 0     scn: 0x0000.0b907404
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x004177c9 hdba: 0x004177c8
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 12（slot代表了块中的第一行，可以使用dbms_rowid确认）
ncol: 3 nnew: 1 size: -6（字符长度减少6）aa 更改为tangjian（2-8）原来的长度减去现在的长度
col 1: [ 2] 61 61-------（修改语句中update TANGJIAN SET name='tangjian' where work='zz'; zz可以转换为16进制）

sys@ORCL>select dump('aa',16) from dual;

DUMP('AA',16)
--------------------------------------
Typ=96 Len=2: 61,61

============
CHANGE #3（update操作） TYP:2 CLS:1 AFN:1 DBA:0x004177c9 OBJ:88027 SCN:0x0000.0b9074de SEQ:1 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1

op: F xid: 0x011e.015.00006731 uba: 0x00c08302.01eb.06
Block cleanout record（执行了块清除）, scn: 0x0000.0b908712 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.0b907404
itli: 2 flg: 2 scn: 0x0000.0b9074de
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x004177c9 hdba: 0x004177c8
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 12
ncol: 3 nnew: 1 size: 6 （update TANGJIAN SET name='tangjian' where work='zz';）新的行记录为6
col 1: [ 8] 74 61 6e 67 6a 69 61 6e

sys@ORCL>select dump('tangjian',16) from dual;（与上面记录的内容一致。）

DUMP('TANGJIAN',16)
--------------------------------------------------------------------------
Typ=96 Len=8: 74,61,6e,67,6a,69,61,6e

================
REDO RECORD（提交操作） - Thread:1 RBA: 0x0003ca.00000db9.0010 LEN: 0x008c VLD: 0x05
SCN: 0x0000.0b908715 SUBSCN: 1 06/03/2020 10:03:38
(LWN RBA: 0x0003ca.00000db9.0010 LEN: 0001 NST: 0001 SCN: 0x0000.0b908713)
CHANGE #1 TYP:0 CLS:587 AFN:3 DBA:0x00c08300 OBJ:4294967295 SCN:0x0000.0b908712 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0015 sqn: 0x00006731 srt: 0 sta: 9（改为状态9） flg: 0x2 ktucf redo: uba: 0x00c08302.01eb.06 ext: 0 spc: 7118 fbi: 0
END OF REDO DUMP（修改了 0x0015的事务槽）

实验三：delete 操作的REDO日志解读

会话5： --SYS用户切换日志：
alter system switch logfile;
SYS@ bys3>col member for a30
SYS@ bys3>select a.group#,a.sequence#,a.archived,a.status,b.type,b.member from v$log a,v$logfile b where a.group#=b.group#;
    GROUP# SEQUENCE# ARC STATUS           TYPE    MEMBER
---------- ---------- --- ---------------- ------- ------------------------------
         1        310 YES INACTIVE         ONLINE /u01/oradata/bys3/redo01.log
         2        311 YES ACTIVE           ONLINE /u01/oradata/bys3/redo02.log
         3        312 NO CURRENT          ONLINE /u01/oradata/bys3/redo03.log
切换到普通用户做删除语句：
conn bys/bys
BYS@ bys3>select * from dept;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        40 OPERATIONS     BOSTON
        11 database       bj
        22 test           sh
        33 imutest        hz
6 rows selected.
BYS@ bys3>set time on
20:43:18 BYS@ bys3>delete dept where deptno=33;
1 row deleted.
20:43:25 BYS@ bys3>commit;
Commit complete.
会话6，DUMP当前REDO日志：
SYS@ bys3>alter system dump logfile '/u01/oradata/bys3/redo03.log';
System altered.
SYS@ bys3>select value from v$diag_info where name like 'De%' ;
VALUE
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_29214.trc
##############
查看此TRACE文件：方法是：vi /u01/diag/rdbms/bys3/bys3/trace/bys3_ora_29214.trc 然后搜索修改的对象的OBJECT_ID:22327

REDO RECORD - Thread:1 RBA: 0x000138.0000002b.0010 LEN: 0x01c8 VLD: 0x05
SCN: 0x0000.0070306c SUBSCN: 1 01/06/2014 20:43:26
(LWN RBA: 0x000138.0000002b.0010 LEN: 0002 NST: 0001 SCN: 0x0000.0070306b)
CHANGE #1 TYP:0 CLS:29 AFN:3 DBA:0x00c000e0 OBJ:4294967295 SCN:0x0000.0070303f SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0008 sqn: 0x00000e9b flg: 0x0012 siz: 168 fbi: 0
            uba: 0x00c0193e.02b8.03    pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:30 AFN:3 DBA:0x00c0193e OBJ:4294967295 SCN:0x0000.0070303e SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 168 spc: 7794 flg: 0x0012 seq: 0x02b8 rec: 0x03
            xid: 0x0007.008.00000e9b
ktubl redo: slt: 8 rci: 0 opc: 11.1 [objn: 22327 objd: 22327 tsn: 4]
Undo type: Regular undo        Begin trans    Last buffer split: No
Temp Object: No
Tablespace Undo: No
             0x00000000 prev ctl uba: 0x00c0193e.02b8.02
prev ctl max cmt scn: 0x0000.00702b2d prev tx cmt scn: 0x0000.00702b3e
txn start scn: 0xffff.ffffffff logon user: 32 prev brb: 12589367 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010000ff hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 17
fb: --H-FL-- lb: 0x0 cc: 3
null: ---
col 0: [ 2] c1 22    ---记录删除前的原值，delete dept where deptno=33;   删除的是这一行值： 33 imutest        hz
col 1: [ 7] 69 6d 75 74 65 73 74

col 2: [ 2] 68 7a
###################
BYS@ bys3>select dump(33,16),dump('imutest',16),dump('hz',16) from dual;
DUMP(33,16) DUMP('IMUTEST',16) DUMP('HZ',16)
------------------ ---------------------------------- -------------------
Typ=2 Len=2: c1,22 Typ=96 Len=7: 69,6d,75,74,65,73,74 Typ=96 Len=2: 68,7a
#####################

CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x010000ff OBJ:22327 SCN:0x0000.00702f09 SEQ:1 OP:11.3 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0007.008.00000e9b uba: 0x00c0193e.02b8.03
Block cleanout record, scn: 0x0000.0070306c ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.00702f09
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010000ff hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)

REDO RECORD - Thread:1 RBA: 0x000138.0000002b.01d8 LEN: 0x0060 VLD: 0x01
SCN: 0x0000.0070306d SUBSCN: 1 01/06/2014 20:43:26
CHANGE #1 TYP:0 CLS:29 AFN:3 DBA:0x00c000e0 OBJ:4294967295 SCN:0x0000.0070306c SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0008 sqn: 0x00000e9b srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c0193e.02b8.03 ext: 3 spc: 7624 fbi: 0