IMU模式下DML语句所产生的REDO RECORD格式解读

标签：SCN -- DML BYS RECORD IMU bys3 CHANGE OP

总结：IMU模式下DML语句所产生的REDO RECORD格式，是先有操作的 change rector，再有向向UNDO段头的事务表写事务信息的change rector，再提交操作的change rector后，才进行把数据修改前值放到UNDO的change rector。
注意：实验中INSERT和DELETE是先后做的，UPDATE操作是其它时间做的，UPDATE实验时的表数据和另两步不一样。

DML操作的change rector产生顺序汇总如下： --DML实验及DUMP的REDO日志见下面具体实验步骤。

INSERT --涉及有索引的字段--操作加提交只产生一条REDO RECORD

CHANGE #1 OP:11.2 --插入操作

CHANGE #2 OP:5.2 --operation code 向UNDO段头的事务表写事务信息-事务开始

CHANGE #3 OP:10.2 --10.2 是插入索引叶子块

CHANGE #4 OP:5.4 ----提交

CHANGE #5 OP:5.1 --把表内数据修改前值放到UNDO--objn: 22327，插入的表的对象ID。

CHANGE #6 OP:5.1 --把索引数据修改前值放到UNDO--objn: 22818，索引对象ID。

一条INSERT语句为什么写了两次OP:5.1操作，是因为存在索引。

#################

UPDATE：--这个操作没涉及索引的字段--操作加提交只产生一条REDO RECORD

CHANGE#1 OP:11.19 --或者OP:11.5都是--UPDATE语句，开始修改数据，

CHANGE#2 OP:5.2 --operation code 向UNDO段头的事务表写事务信息-事务开始

CHANGE#3 OP:11.19 --或者OP:11.5都是--UPDATE语句，开始修改数据，

CHANGE #4 OP:5.4 --提交

CHANGE #5 OP:5.1 --把表内数据修改前值放到UNDO

CHANGE #6 OP:5.1 --把表内数据修改前值放到UNDO

################

DELETE: --涉及有索引的字段--操作加提交只产生一条REDO RECORD

CHANGE #1 OP:11.3 --DELETE语句的操作

CHANGE #2 OP:5.2 --operation code 向UNDO段头的事务表写事务信息-事务开始

CHANGE #3 OP:10.4 --删除索引叶子块

CHANGE #4 OP:5.4 --提交

CHANGE #5 OP:5.1 --把表内数据修改前值放到UNDO

CHANGE #6 OP:5.1 --把索引数据修改前值放到UNDO

一条DELETE语句为什么写了两次OP:5.1操作，是因为存在索引。

以上INSERT及DELETE时涉及的对索引的操作，如表上无索引，将涉及索引的CHANGE #条目去除，就是正常的CHANGE 产生顺序。

具体实验详情如下：--确保环境已经改为使用IMU。alter system set "_in_memory_undo"=true;

insert操作实验过程：

SYS@ bys3>alter system switch logfile;

System altered.

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 322 YES INACTIVE ONLINE /u01/oradata/bys3/redo01.log

2 323 YES ACTIVE ONLINE /u01/oradata/bys3/redo02.log

3 324 NO

CURRENT ONLINE /u01/oradata/bys3/redo03.log

SYS@ bys3>conn bys/bys

Connected.

BYS@ bys3>select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

40 OPERATIONS BOSTON

11 database bj

22 dataoracle sh

BYS@ bys3>select a.index_owner,a.index_name,b.object_id,a.table_owner,a.table_name,a.column_name from all_ind_columns a,dba_objects b where a.index_owner='BYS' and a.index_name=b.object_name;

INDEX_OWNE INDEX_NAME OBJECT_ID TABLE_OWNE TABLE_NAME COLUMN_NAM

---------- ---------- ---------- ---------- ------------------------------ ----------

BYS INDTEXT

22818 BYS DEPT DEPTNO

BYS@ bys3>set time on

19:35:01 BYS@ bys3>insert into dept values(66,'imutest2','zhengzhou');

1 row created.

19:35:33 BYS@ bys3>commit;

Commit complete.

19:35:40 BYS@ bys3>

另一会话：

BYS@ bys3>alter system dump logfile '/u01/oradata/bys3/redo03.log';

System altered.

BYS@ bys3>select value from v$diag_info where name like 'De%' ;

VALUE

----------------------------------------------------------------------------------------------------

/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_28763.trc

BYS@ bys3>select chr(to_number(substr(replace('c1 43',' '),rownum*2-1,2),'xxxxxxxx')),chr(to_number(substr(replace('69 6d 75 74 65 73 74 32',' '),rownum*2-1,2),'xxxxxxxx')),chr(to_number(substr(replace('7a 68 65 6e 67 7a 68 6f 75',' '),rownum*2-1,2),'xxxxxxxx')) from v$bh where rownum<9;

CHR( CHR( CHR(

---- ---- ----

? i z

C m h

u e

t n

e g

s z

t h

2 o

对于数字和字符的16进制，转换为ASCII码可以用：utl_raw.cast_to_number utl_raw.cast_to_varchar2

BYS@ bys3>select utl_raw.cast_to_number(replace('c1 43',' ')) text_num,utl_raw.cast_to_varchar2(replace('64 61 74 61 62 61 73 65',' ')) text from dual;
TEXT_NUM TEXT
---------- ----------
66 database

#####################################

INSERT操作DUMP REDO 内容

REDO RECORD - Thread:1 RBA: 0x000144.0000000e.0010 LEN: 0x02e4 VLD: 0x0d

SCN: 0x0000.00729c6b SUBSCN: 1 01/08/2014 19:35:40

(LWN RBA: 0x000144.0000000e.0010 LEN: 0002 NST: 0001 SCN: 0x0000.00729c6a)

#######一个REDO RECORD: RECORD头+CHANGE VECTOR组成（一个CV就是一个操作）

以上是日志头,Thread:1 线程号，RAC时会有1，2等

RBA: 0x000144.0000000e.0010 将16进制转换为十进制分别是日志文件号、日志块号、在块上第N字节

VLD: 0x0d日志类型--IMU模式时是这个；非IMU时是：VLD: 0x05

SCN: 0x0000.00729c6b SUBSCN: 1 01/08/2014 19:35:40

BYS@ bys3>select scn_to_timestamp(to_number('729c6b','xxxxxxxx')) from dual;

SCN_TO_TIMESTAMP(TO_NUMBER('729C6B','XXXXXXXX'))

---------------------------------------------------------------------------

08-JAN-14 07.35.38.000000000 PM

--是此REDO条目产生时的SCN号，转为十进制现转为时间戳为：19:35:33，插入语句完成是在19:35:33 BYS@ bys3>commit;

(LWN RBA: 0x000144.0000000e.0010 LEN: 0002 NST: 0001 SCN: 0x0000.00729c6a)

括号中SCN: 0x0000.00729c6a 比上一行：SCN: 0x0000.00729c6b 少了1个SCN。

################

CHANGE #1 TYP:2 CLS:1 AFN:4 DBA:0x010000fd OBJ:22327 SCN:0x0000.00719188 SEQ:3

OP:11.2 ENC:0 RBL:0

##AFN:4，操作是在4号文件做的-dba_data_files.file_id；OBJ:22327--操作的对象的OBJECT_ID。OP:11.2--插入操作

KTB Redo

op: 0x01 ver: 0x01

compat bit: 4 (post-11) padding: 1

op: F xid: 0x0001.00f.00000f13 uba: 0x00c017b7.0262.08

KDO Op code: IRP row dependencies Disabled --这个是IRP --INSERT ROW PIECE

xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa

itli: 1 ispac: 0 maxfr: 4858

tabn: 0 slot: 2(0x2) size/delt: 25

fb: --H-FL-- lb: 0x1 cc: 3

null: ---

col 0: [ 2] c1 43 --col 0: [ 2]，第一列，2个字符

col 1: [ 8] 69 6d 75 74 65 73 74 32 --第2列，8个字符

col 2: [ 9] 7a 68 65 6e 67 7a 68 6f 75

#####可以将插入的值转为16进制，可以与这里的值对应上。

insert into dept values(66,'imutest2','zhengzhou');

BYS@ bys3>select dump(66,16),dump('imutest2',16),dump('zhengzhou',16) from dual;

DUMP(66,16) DUMP('IMUTEST2',16) DUMP('ZHENGZHOU',16)

------------------ ------------------------------------- ----------------------------------------

Typ=2 Len=2: c1,43 Typ=96 Len=8: 69,6d,75,74,65,73,74,32 Typ=96 Len=9: 7a,68,65,6e,67,7a,68,6f,75

CHANGE #2 TYP:0 CLS:17 AFN:3 DBA:0x00c00080 OBJ:4294967295 SCN:0x0000.00729c37 SEQ:2

OP:5.2 ENC:0 RBL:0

ktudh redo: slt: 0x000f sqn: 0x00000f13 flg: 0x0012 siz: 136 fbi: 0

---OP:5.2，向UNDO段头的事务表写事务信息-事务开始

uba: 0x00c017b7.0262.08 pxid: 0x0000.000.00000000

CHANGE #3 TYP:0 CLS:1 AFN:4 DBA:0x01003d53 OBJ:22818 SCN:0x0000.00729c68 SEQ:1

OP:10.2 ENC:0 RBL:0

index redo (kdxlin): insert leaf row --也说明是向索引插入，OBJ:22818就是索引的对象ID，OP:10.2-插入索引叶子块

KTB Redo

op: 0x01 ver: 0x01

compat bit: 4 (post-11) padding: 1

op: F xid: 0x0001.00f.00000f13 uba: 0x00c017b7.0262.09

REDO: SINGLE / -- / --

itl: 2, sno: 5, row size 14

insert key: (10): 02 c1 43 06 01 00 00 fd 00 02 --向索引叶子插入的KEY值

CHANGE #4 TYP:0 CLS:17 AFN:3 DBA:0x00c00080 OBJ:4294967295 SCN:0x0000.00729c6b SEQ:1

OP:5.4 ENC:0 RBL:0

ktucm redo: slt: 0x000f sqn: 0x00000f13 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c017b7.0262.09 ext: 3 spc: 7012 fbi: 0

###OP:5.4 --在这个CHANGE #4中对此事务做了提交操作，，

slot是槽位号，slot是每一个事务的入口。

CHANGE #5 TYP:0 CLS:18 AFN:3 DBA:0x00c017b7 OBJ:4294967295 SCN:0x0000.00729c37 SEQ:3 OP:5.1 ENC:0 RBL:0

ktudb redo: siz: 136 spc: 7252 flg: 0x0012 seq: 0x0262 rec: 0x08

----OP:5.1-数据修改前值放到UNDO

xid: 0x0001.00f.00000f13

ktubl redo: slt: 15 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: 0x00c017b7.0262.05

prev ctl max cmt scn: 0x0000.00729783 prev tx cmt scn: 0x0000.0072978f

txn start scn: 0x0000.00729c68 logon user: 32 prev brb: 12588976 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: 0x0005.005.00000ed4 uba: 0x00c029f3.02e9.0e

flg: C--- lkc: 0 scn: 0x0000.007164a1

KDO Op code: DRP row dependencies Disabled

xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa

itli: 1 ispac: 0 maxfr: 4858

tabn: 0 slot: 2(0x2)

CHANGE #6 TYP:0 CLS:18 AFN:3 DBA:0x00c017b7 OBJ:4294967295 SCN:0x0000.00729c6b SEQ:1 OP:5.1 ENC:0 RBL:0

ktudb redo: siz: 100 spc: 7114 flg: 0x0022 seq: 0x0262 rec: 0x09

xid: 0x0001.00f.00000f13

ktubu redo: slt: 15 rci: 8 opc: 10.22

objn: 22818 objd: 22818 tsn: 4 --- objn: 22818是索引的OBJECT_ID

Undo type: Regular undo Undo type: Last buffer split: No

Tablespace Undo: No

0x00000000

index undo for leaf key operations

KTB Redo

op: 0x04 ver: 0x01

compat bit: 4 (post-11) padding: 1

op: L itl: xid: 0x0005.005.00000ed4 uba: 0x00c029f3.02e9.0f

flg: C--- lkc: 0 scn: 0x0000.007164a1

Dump kdilk : itl=2, kdxlkflg=0x1 sdc=1 indexid=0x1003d52 block=0x01003d53

(kdxlpu): purge leaf row ----这里的purge leaf row也证明了CHANGE #6这个是对索引的操作

key :(10): 02 c1 43 06 01 00 00 fd 00 02

END OF REDO DUMP

#################################################################

UPDATE操作实验过程：

SYS@ bys3>alter system switch logfile;

System altered.

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 319 YES INACTIVE ONLINE /u01/oradata/bys3/redo01.log

2 320 YES ACTIVE ONLINE /u01/oradata/bys3/redo02.log

3 321 NO CURRENT ONLINE /u01/oradata/bys3/redo03.log

SYS@ bys3>conn bys/bys

Connected.

BYS@ bys3>select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

40 OPERATIONS BOSTON

11 chedan bj

22 test sh

BYS@ bys3>BYS@ bys3>set time on

20:26:21 BYS@ bys3>update dept set dname='database' where deptno=11;

1 row updated.

20:26:34 BYS@ bys3>update dept set dname='dataoracle' where deptno=22;

1 row updated.

20:27:00 BYS@ bys3>commit;

Commit complete.

会话4：DUMP当前REDO日志：

BYS@ bys3>alter system dump logfile '/u01/oradata/bys3/redo03.log';

System altered.

BYS@ bys3>select value from v$diag_info where name like 'De%' ;

VALUE

----------------------------------------------------------------------------------------------------

/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_13319.trc

#########################################

UPDATE操作DUMP REDO 内容

REDO RECORD - Thread:1 RBA: 0x000141.00000027.0010 LEN: 0x031c VLD: 0x0d

SCN: 0x0000.00719188 SUBSCN: 1 01/07/2014 20:27:05

(LWN RBA: 0x000141.00000027.0010 LEN: 0002 NST: 0001 SCN: 0x0000.00719187)

####一个REDO RECORD: RECORD头+CHANGE VECTOR组成（一个CV就是一个操作）

以上是日志头,Thread:1 线程号，RAC时会有1，2等

RBA: 0x000141.00000027.0010 将16进制转换为十进制分别是日志文件号、日志块号、在块上第N字节

VLD: 0x0d日志类型--IMU模式时是这个；非IMU时是：VLD: 0x05

SCN: 0x0000.00719188 SUBSCN: 1 01/07/2014 20:27:05 ----

BYS@ bys3>select scn_to_timestamp(to_number('719188','xxxxxxxx')) from dual;

SCN_TO_TIMESTAMP(TO_NUMBER('719188','XXXXXXXX'))

---------------------------------------------------------------------------

07-JAN-14 08.27.05.000000000 PM

--是此REDO条目产生时的SCN号，转为十进制现转为时间戳为：08.27.05，插入语句完成是在20:27:00 BYS@ bys3>commit;-- -

-这个是在插入语句完成5秒后，此SCN与CHANGE#4提交时SCN一致。

(LWN RBA: 0x000141.00000027.0010 LEN: 0002 NST: 0001 SCN: 0x0000.00719187)

括号中SCN: 0x0000.00719187 比上一行：SCN: 0x0000.00719187 少了1个SCN。

####

CHANGE #1 TYP:2 CLS:1 AFN:4 DBA:0x010000fd OBJ:22327 SCN:0x0000.007164a1 SEQ:1 OP:11.5 ENC:0 RBL:0

#####

AFN:4，操作是在4号文件做的-dba_data_files.file_id；OBJ:22327--操作的对象的OBJECT_ID。OP:11.5-有的版本是OP:11.19--更新操作

KTB Redo

op: 0x11 ver: 0x01

compat bit: 4 (post-11) padding: 1

op: F xid: 0x0005.002.00000edc uba: 0x00c041cd.02ea.01

Block cleanout record, scn: 0x0000.0071917c ver: 0x01 opt: 0x02, entries follow...

itli: 1 flg: 2 scn: 0x0000.007164a1

KDO Op code: URP row dependencies Disabled -- --URP=UPDATE ROW PIECE。有时会是：KDO Op code:21 row dependencies Disabled

xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa

itli: 2 ispac: 0 maxfr: 4858

tabn: 0 slot: 8(0x8) flag: 0x2c lock: 2 ckix: 0

ncol: 3 nnew: 1 size: 2 --ncol: 3 nnew: 1，size: 2

--列字符长度增加2：database减去chedan---根据多次update并DUMP的日志来看，这里的size的值应该是：当前CHANGE中的值减去另一个。。

col 1: [ 8] 64 61 74 61 62 61 73 65 --set dname='database' --col 1: [ 8]，第二列，8个字符

BYS@ bys3>select dump('database',16),dump('dataoracle',16) from dual;

DUMP('DATABASE',16) DUMP('DATAORACLE',16)

------------------------------------- --------------------------------------------

Typ=96 Len=8: 64,61,74,61,62,61,73,65 Typ=96 Len=10: 64,61,74,61,6f,72,61,63,6c,65

#########################

CHANGE #2 TYP:0 CLS:25 AFN:3 DBA:0x00c000c0 OBJ:4294967295 SCN:0x0000.00719153 SEQ:1

OP:5.2 ENC:0 RBL:0

ktudh redo: slt: 0x0002 sqn: 0x00000edc flg: 0x000a siz: 164 fbi: 0

uba: 0x00c041cd.02ea.01 pxid: 0x0000.000.00000000

### #####################

事务信息

TYP:0 普通块，CLS:25 class大于16是UNDO块-递增。AFN:3 绝对文件号dba_data_files.file_id--是UNDO的文件号

DBA:0x00c000c0 数据块在内存中地址

OBJ:4294967295 --十进制，转为16进制是FFFFFFFF

SCN:0x0000.00719153 转换为16进制可与操作时对比

OP:5.2 -> operation code 向UNDO段头的事务表写事务信息-事务开始

uba: 0x00c041cd.02ea.01 UNDO块地址

#######################

CHANGE #3 TYP:0 CLS:1 AFN:4 DBA:0x010000fd

OBJ:22327 SCN:0x0000.00719188 SEQ:1 OP:11.5 ENC:0 RBL:0

KTB Redo

--同CHANGE #1的解析

op: 0x02 ver: 0x01

compat bit: 4 (post-11) padding: 1

op: C uba: 0x00c041cd.02ea.02

KDO Op code: URP row dependencies Disabled ---UNDO ROW PIECE

xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa

itli: 2 ispac: 0 maxfr: 4858

tabn: 0 slot: 9(0x9) flag: 0x2c lock: 2 ckix: 0

ncol: 3 nnew: 1 size: 6

col 1: [10] 64 61 74 61 6f 72 61 63 6c 65 --第2列，10个字符--此次操作的字符数

BYS@ bys3>select dump('database',16),dump('dataoracle',16) from dual;

DUMP('DATABASE',16) DUMP('DATAORACLE',16)

------------------------------------- --------------------------------------------

Typ=96 Len=8: 64,61,74,61,62,61,73,65 Typ=96 Len=10: 64,61,74,61,6f,72,61,63,6c,65

###########################

CHANGE #4 TYP:0 CLS:25

AFN:3DBA:0x00c000c0 OBJ:4294967295

SCN:0x0000.00719188 SEQ:1

OP:5.4 ENC:0 RBL:0

ktucm redo: slt: 0x0002 sqn: 0x00000edc srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c041cd.02ea.02 ext: 15 spc: 7890 fbi: 0

######

OP:5.4 表明是提交操作。AFN:3 对应的是UNDO文件，slt: 0x0002 修改了UNDO文件的这个事务槽,uba: 0x00c041cd.02ea.02

CHANGE #5 TYP:1 CLS:26 AFN:3 DBA:0x00c041cd OBJ:4294967295 SCN:0x0000.0071917c SEQ:1 OP:5.1 ENC:0 RBL:0

ktudb redo: siz: 164 spc: 0 flg: 0x000a seq: 0x02ea rec: 0x01

###

OP:5.1 --把数据修改前值放到UNDO --AFN:3 --在UNDO文件里操作，UNDO文件号是3。。CLS:26 --比CHANGE #2中大1，顺序增长哈哈

xid: 0x0005.002.00000edc

ktubl redo: slt: 2 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: 0x00c041cc.02ea.04

prev ctl max cmt scn: 0x0000.00718dff prev tx cmt scn: 0x0000.00718e4e

txn start scn: 0x0000.00000000 logon user: 32 prev brb: 12599753 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.004.00000ebc uba: 0x00c037d5.0249.08

flg: C--- lkc: 0 scn: 0x0000.0070cfea

KDO Op code: URP row dependencies Disabled -----UNDO ROW PIECE

xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa

itli: 2 ispac: 0 maxfr: 4858

tabn: 0 slot: 8(0x8) flag: 0x2c lock: 0 ckix: 0

ncol: 3 nnew: 1 size: -2 ----列字符长度减少2：chedan 减去database---根据多次update并DUMP的日志来看，这里的size的值应该是：当前CHANGE中的值减去另一个

col 1: [ 6] 63 68 65 64 61 6e ---- 原值是chedan，，第二列，6个字符

BYS@ bys3>select dump('chedan',16),dump('test',16) from dual;

DUMP('CHEDAN',16) DUMP('TEST',16)

------------------------------- -------------------------

Typ=96 Len=6: 63,68,65,64,61,6e Typ=96 Len=4: 74,65,73,74

CHANGE #6 TYP:0 CLS:26 AFN:3 DBA:0x00c041cd OBJ:4294967295 SCN:0x0000.00719188 SEQ:1

OP:5.1ENC:0 RBL:0 --解析同上

ktudb redo: siz: 92 spc: 7984 flg: 0x0022 seq: 0x02ea rec: 0x02

xid: 0x0005.002.00000edc

ktubu redo: slt: 2 rci: 1 opc: 11.1 objn: 22327 objd: 22327 tsn: 4

Undo type: Regular undo Undo type: Last buffer split: No

Tablespace Undo: No

0x00000000

KDO undo record:

KTB Redo

op: 0x02 ver: 0x01

compat bit: 4 (post-11) padding: 1

op: C uba: 0x00c041cd.02ea.01

KDO Op code: URP row dependencies Disabled -----UNDO ROW PIECE

xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa

itli: 2 ispac: 0 maxfr: 4858

tabn: 0 slot: 9(0x9) flag: 0x2c lock: 0 ckix: 0

ncol: 3 nnew: 1 size: -6 -列字符长度减少2：test减去database---根据多次update并DUMP的日志来看，这里的size的值应该是：当前CHANGE中的值减去另一个

col 1: [ 4] 74 65 73 74 --此次操作，第二列，4个字符

BYS@ bys3>select dump('chedan',16),dump('test',16) from dual;

DUMP('CHEDAN',16) DUMP('TEST',16)

------------------------------- -------------------------

Typ=96 Len=6: 63,68,65,64,61,6e Typ=96 Len=4: 74,65,73,74

###################################################################

DELETE操作实验过程：

SYS@ bys3>alter system switch logfile;

System altered.

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 325 NO CURRENT ONLINE /u01/oradata/bys3/redo01.log

2 323 YES INACTIVE ONLINE /u01/oradata/bys3/redo02.log

3 324 YES ACTIVE ONLINE /u01/oradata/bys3/redo03.log

SYS@ bys3>conn bys/bys

Connected.

BYS@ bys3>select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

66 imutest2 zhengzhou

40 OPERATIONS BOSTON

11 database bj

22 dataoracle sh

BYS@ bys3>set time on

20:32:58 BYS@ bys3>delete dept where deptno=66;

1 row deleted.

20:33:02 BYS@ bys3>commit;

Commit complete.

20:33:06 BYS@ bys3>

另一会话DUMP REDO LOGFILE:

BYS@ bys3>alter system dump logfile '/u01/oradata/bys3/redo01.log';

System altered.

BYS@ bys3>select value from v$diag_info where name like 'De%' ;

VALUE

----------------------------------------------------------------------------------------------------

/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_29403.trc

#######################

DELETE操作DUMP REDO 内容

REDO RECORD - Thread:1 RBA: 0x000145.00000003.0010 LEN: 0x0308 VLD: 0x0d

SCN: 0x0000.0072a6f2 SUBSCN: 1 01/08/2014 20:33:06

(LWN RBA: 0x000145.00000003.0010 LEN: 0002 NST: 0001 SCN: 0x0000.0072a6f1)

CHANGE #1 TYP:2 CLS:1 AFN:4 DBA:0x010000fd OBJ:22327 SCN:0x0000.00729c6b SEQ:2

OP:11.3 ENC:0 RBL:0

KTB Redo

op: 0x11 ver: 0x01

compat bit: 4 (post-11) padding: 1

op: F xid: 0x000a.002.00000f0c uba: 0x00c0175c.026f.01

Block cleanout record, scn: 0x0000.0072a6ee ver: 0x01 opt: 0x02, entries follow...

itli: 1 flg: 2 scn: 0x0000.00729c6b

itli: 2 flg: 2 scn: 0x0000.00719188

KDO Op code: DRP row dependencies Disabled --DRP DROP ROW PIECE

xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa

itli: 2 ispac: 0 maxfr: 4858

tabn: 0 slot: 2(0x2)

CHANGE #2 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.0072a6b9 SEQ:1

OP:5.2 ENC:0 RBL:0

ktudh redo: slt: 0x0002 sqn: 0x00000f0c flg: 0x000a siz: 200 fbi: 0

uba: 0x00c0175c.026f.01 pxid: 0x0000.000.00000000

CHANGE #3 TYP:0 CLS:1 AFN:4 DBA:0x01003d53 OBJ:22818 SCN:0x0000.0072a6ef SEQ:1

OP:10.4 ENC:0 RBL:0

index redo (kdxlde): delete leaf row ---删除索引叶

KTB Redo

op: 0x01 ver: 0x01

compat bit: 4 (post-11) padding: 1

op: F xid: 0x000a.002.00000f0c uba: 0x00c0175c.026f.02

REDO: SINGLE / -- / --

itl: 2, sno: 5, row size 14

CHANGE #4 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.0072a6f2 SEQ:1

OP:5.4 ENC:0 RBL:0

ktucm redo: slt: 0x0002 sqn: 0x00000f0c srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c0175c.026f.02 ext: 4 spc: 7846 fbi: 0

CHANGE #5 TYP:1 CLS:36 AFN:3 DBA:0x00c0175c OBJ:4294967295 SCN:0x0000.0072a6ee SEQ:1

OP:5.1ENC:0 RBL:0

ktudb redo: siz: 200 spc: 0 flg: 0x000a seq: 0x026f rec: 0x01

xid: 0x000a.002.00000f0c

ktubl redo: slt: 2 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: 0x00c0175b.026f.07

prev ctl max cmt scn: 0x0000.0072a2c6 prev tx cmt scn: 0x0000.0072a2d5

txn start scn: 0x0000.0072a6ef logon user: 32 prev brb: 12588886 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: 0x0005.002.00000edc uba: 0x00c041cd.02ea.02

flg: C--- lkc: 0 scn: 0x0000.00719188

KDO Op code: IRP row dependencies Disabled

xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa

itli: 2 ispac: 0 maxfr: 4858

tabn: 0 slot: 2(0x2) size/delt: 25

fb: --H-FL-- lb: 0x0 cc: 3

null: ---

col 0: [ 2] c1 43 --这三行是删除前的值，参见第一步INSERT里的DUMP计算

col 1: [ 8] 69 6d 75 74 65 73 74 32

col 2: [ 9] 7a 68 65 6e 67 7a 68 6f 75

#####

可以将第一步插入的值转为16进制，可以与这里的值对应上。 -- 66 imutest2 zhengzhou

BYS@ bys3>select dump('66',16),dump('imutest2',16),dump('zhengzhou',16) from dual;

DUMP('66',16) DUMP('IMUTEST2',16) DUMP('ZHENGZHOU',16)

------------------- ------------------------------------- ----------------------------------------

Typ=96 Len=2: 36,36 Typ=96 Len=8: 69,6d,75,74,65,73,74,32 Typ=96 Len=9: 7a,68,65,6e,67,7a,68,6f,75

CHANGE #6 TYP:0 CLS:36 AFN:3 DBA:0x00c0175c OBJ:4294967295 SCN:0x0000.0072a6f2 SEQ:1

OP:5.1 ENC:0 RBL:0

ktudb redo: siz: 100 spc: 7948 flg: 0x0022 seq: 0x026f rec: 0x02

xid: 0x000a.002.00000f0c

ktubu redo: slt: 2 rci: 1 opc: 10.22 objn: 22818 objd: 22818 tsn: 4

Undo type: Regular undo Undo type: Last buffer split: No

Tablespace Undo: No

0x00000000

index undo for leaf key operations ---索引叶子值的UNDO

KTB Redo

op: 0x04 ver: 0x01

compat bit: 4 (post-11) padding: 1

op: L itl: xid: 0x0001.00f.00000f13 uba: 0x00c017b7.0262.09

flg: C--- lkc: 0 scn: 0x0000.00729c6b

Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x1003d52 block=0x01003d53

(kdxlre): restore leaf row (clear leaf delete flags) --这个CHANGE #6往UNDO里写恢复索引叶子的，

key :(10): 02 c1 43 06 01 00 00 fd 00 02

标签：SCN,--,DML,BYS,RECORD,IMU,bys3,CHANGE,OP
From： https://blog.51cto.com/u_1652968/6839147

IMU模式下DML语句所产生的REDO RECORD格式解读

DML操作的change rector产生顺序汇总如下： --DML实验及DUMP的REDO日志见下面具体实验步骤。

insert操作实验过程：

INSERT操作DUMP REDO 内容

UPDATE操作实验过程：

UPDATE操作DUMP REDO 内容

DELETE操作实验过程：

DELETE操作DUMP REDO 内容

相关文章

赞助商

阅读排行