實驗內容：非IMU模式下DML語句產生的REDO日志內容格式解讀 最詳細的解讀是UPDATE的。 實驗環境準備 11G中默認是開啟IMU特性的，做此實驗需要關閉此特性。 alter system set _in_memory_undo=false; alter system set _in_memory_undo=true; --實驗結束后使用
實驗內容：非IMU模式下DML語句產生的REDO日志內容格式解讀
最詳細的解讀是UPDATE的。
實驗環境準備
11G中默認是開啟IMU特性的，做此實驗需要關閉此特性。
alter system set "_in_memory_undo"=false;
alter system set "_in_memory_undo"=true; --實驗結束后使用此語句改回使用IMU特性。
修改參數完成后，重啟數據庫：
shutdown immediate;
startup;
準備好實驗用的表----BYS.DEPT表。
SYS@ bys3>select object_id from dba_objects where object_name='DEPT';
OBJECT_ID
----------
22327
SYS@ bys3>select * from bys.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
40 OPERATIONS BOSTON
11 chedan bj
22 test sh
BYS@ bys3>col file_name for a35
BYS@ bys3>select file_id,file_name from dba_data_files;
FILE_ID FILE_NAME
---------- -----------------------------------
1 /u01/oradata/bys3/system01.dbf
2 /u01/oradata/bys3/sysaux01.dbf
3 /u01/oradata/bys3/undotbs01.dbf
4 /u01/oradata/bys3/user01.dbf
BYS@ bys3>select ts#,name from v$tablespace;
TS# NAME
---------- ------------------------------
0 SYSTEM
1 SYSAUX
2 UNDOTBS1
3 TEMP
4 USERS
###################################################
開始實驗分三步，分別是插入、更新、刪除語句的操作。最詳細的解讀是UPDATE的。
實驗一：insert 操作的REDO日志解讀
會話1： --SYS用戶切換日志并查出當前日志名：
SYS@ bys3>alter system switch logfile;
System altered.
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 NO CURRENT ONLINE /u01/oradata/bys3/redo01.log
2 308 YES INACTIVE ONLINE /u01/oradata/bys3/redo02.log
3 309 YES ACTIVE ONLINE /u01/oradata/bys3/redo03.log
普通用戶做插入語句：
SYS@ bys3>conn bys/bys
select * from dept;
Connected.
BYS@ bys3>
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
40 OPERATIONS BOSTON
11 chedan bj
22 test sh
BYS@ bys3>set time on
20:34:07 BYS@ bys3>insert into dept values(33,'imutest','hz');
1 row created.
20:34:12 BYS@ bys3>commit;
Commit complete.
會話2：DUMP當前REDO日志：
SYS@ bys3>alter system dump logfile '/u01/oradata/bys3/redo01.log';
System altered.
SYS@ bys3>select value from v$diag_info where name like 'De%' ;
VALUE
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_29063.trc
######################
查看此TRACE文件： 方法是：vi /u01/diag/rdbms/bys3/bys3/trace/bys3_ora_29063.trc 然后搜索修改的對象的OBJECT_ID-- 22327
詳解參考下一步的UPDATE的REDO講解。
REDO RECORD - Thread:1 RBA: 0x000136.0000000d.0010 LEN: 0x01a0 VLD: 0x05
SCN: 0x0000.00702f08 SUBSCN: 1 01/06/2014 20:34:13
(LWN RBA: 0x000136.0000000d.0010 LEN: 0002 NST: 0001 SCN: 0x0000.00702f08)
CHANGE #1 TYP:0 CLS:21 AFN:3 DBA:0x00c000a0 OBJ:4294967295 SCN:0x0000.00702eda SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0003 sqn: 0x00000eaa flg: 0x0012 siz: 112 fbi: 0
uba: 0x00c01ac7.0250.25 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:22 AFN:3 DBA:0x00c01ac7 OBJ:4294967295 SCN:0x0000.00702ed9 SEQ:3OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 112 spc: 3494 flg: 0x0012 seq: 0x0250 rec: 0x25
xid: 0x0003.003.00000eaa
ktubl redo: slt: 3 rci: 0 opc: 11.1 [objn: 22327 objd: 22327 tsn: 4] ------- tsn: 4，插入的這個是在表空間號為4--OBJ:22327--是插入的對象的OBJECT_ID
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c01ac7.0250.22
prev ctl max cmt scn: 0x0000.007029ac prev tx cmt scn: 0x0000.007029d6
txn start scn: 0xffff.ffffffff logon user: 32 prev brb: 12589763 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: 0x010000ff hdba: 0x010000fa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
CHANGE #3 TYP:0 CLS:1 AFN:4 DBA:0x010000ff OBJ:22327 SCN:0x0000.003eec75 SEQ:1 OP:11.2 ENC:0 RBL:0 --OP:11.2 這個應該是插入的操作的了。
KTB Redo -----AFN:4，插入的這個是在4號數據文件中--OBJ:22327--是插入的對象的OBJECT_ID
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0003.003.00000eaa uba: 0x00c01ac7.0250.25
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010000ff hdba: 0x010000fa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 17
fb: --H-FL-- lb: 0x1 cc: 3
null: ---
col 0: [ 2] c1 22 ---插入語句是：insert into dept values(33,'imutest','hz'); 對應是：select dump(33,16) from dual; --要注意數字在DUMP時不要加單引號
col 1: [ 7] 69 6d 75 74 65 73 74 --對應select dump('imutest',16) from dual; [7]--中括號里的是字節數，col 1:對應的是第二列，
col 2: [ 2] 68 7a ---對應 select dump('hz',16) from dual; --中括號里的是字節數 col 2: 對應 的是第三列
REDO RECORD - Thread:1 RBA: 0x000136.0000000d.01b0 LEN: 0x0060 VLD: 0x01
SCN: 0x0000.00702f09 SUBSCN: 1 01/06/2014 20:34:13 ----OP:5.4，提交操作
CHANGE #1 TYP:0 CLS:21 AFN:3 DBA:0x00c000a0 OBJ:4294967295 SCN:0x0000.00702f08 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0003 sqn: 0x00000eaa srt: 0 sta: 9 flg: 0x2 ktucf redo:uba: 0x00c01ac7.0250.25 ext: 12 spc: 3380 fbi: 0
############################
實驗二：update 操作的REDO日志解讀
會話3： --SYS用戶切換日志：
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 310 YES ACTIVE ONLINE /u01/oradata/bys3/redo01.log
2 311 NO CURRENT ONLINE /u01/oradata/bys3/redo02.log
3 309 YES INACTIVE 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
33 imutest hz
6 rows selected.
BYS@ bys3>set time on
20:39:23 BYS@ bys3>update dept set dname='database' where deptno=11;
1 row updated.
20:39:43 BYS@ bys3>commit;
Commit complete.
20:39:46 BYS@ bys3>
會話4：DUMP當前REDO日志：
SYS@ bys3>alter system dump logfile '/u01/oradata/bys3/redo02.log';
System altered.
SYS@ bys3>select value from v$diag_info where name like 'De%' ;
VALUE
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_29141.trc
##############
查看此TRACE文件： 方法是：vi /u01/diag/rdbms/bys3/bys3/trace/bys3_ora_29141.trc 然后搜索修改的對象的OBJECT_ID:22327
UPDATE 一行記錄產生一個REDO RECORD，提交產生一個REDO RECORD。非IMU下如果一個UPDATE更新多行，則會產生多條REDO RECORD。
######一個REDO RECORD: RECORD頭+CHANGE VECTOR組成（一個CV就是一個操作）
REDO RECORD - Thread:1 RBA: 0x000137.00000003.0010 LEN: 0x01ec VLD: 0x05
SCN: 0x0000.00702fe0 SUBSCN: 1 01/06/2014 20:39:45
(LWN RBA: 0x000137.00000003.0010 LEN: 0001 NST: 0001 SCN: 0x0000.00702fdf)
##以上是日志頭,Thread:1 線程號，RAC時會有1，2等
RBA: 0x000137.00000003.0010 將16進制轉換為十進制分別是日志文件號、日志塊號、在塊上第N字節
VLD: 0x05日志類型；
SCN: 0x0000.00702fe0 SUBSCN: 1 01/06/2014 20:39:45
--是此REDO條目產生時的SCN號，轉為十進制現轉為時間戳為：06-JAN-14 08.39.42， 更新語句完成是在20:39:43 BYS@bys3>commit;
(LWN RBA: 0x000137.00000003.0010 LEN: 0001 NST: 0001 SCN: 0x0000.00702fdf)
括號中SCN: SCN: 0x0000.00702fdf 比上一句：SCN: 0x0000.00702fe0 SUBSCN: 1 01/06/2014 20:39:45 少了1個SCN。
######
CHANGE #1 TYP:0 CLS:17 AFN:3 DBA:0x00c00080 OBJ:4294967295 SCN:0x0000.00702f9a SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x000f sqn: 0x00000e9c flg: 0x0012 siz: 164 fbi: 0
uba: 0x00c01cbf.0250.0f pxid: 0x0000.000.00000000
### #####################事務信息
TYP:0 普通塊 ，CLS:17 class大于16是UNDO塊-遞增。AFN:3 絕對文件號dba_data_files.file_id--這里是UNDO文件的文件號
DBA:0x00c00080 數據塊在內存中地址
OBJ:4294967295 --十進制，轉為16進制是FFFFFFFF
SCN:0x0000.00702f9a 轉換為16進制可與操作時對比
OP:5.2 -> operation code 向UNDO段頭的事務表寫事務信息-事務開始
uba: 0x00c01cbf.0250.0f UNDO塊地址
####################
CHANGE #2 TYP:0 CLS:18 AFN:3 DBA:0x00c01cbf OBJ:4294967295 SCN:0x0000.00702f99 SEQ:2 OP:5.1 ENC:0 RBL:0
##OP:5.1 --把數據修改前值放到UNDO --AFN:3 --在UNDO文件里操作，UNDO文件號是3。CLS:18 --比CHANGE #1中大1，順序增長哈哈
ktudb redo: siz: 164 spc: 6494 flg: 0x0012 seq: 0x0250 rec: 0x0f
xid: 0x0001.00f.00000e9c
ktubl redo: slt: 15 rci: 0 opc: 11.1 [objn: 22327 objd: 22327 tsn: 4] ------- tsn: 4，插入的這個是在表空間號-USERS的表空間號，--OBJ:22327--是插入的對象的OBJECT_ID
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c01cbf.0250.0d
prev ctl max cmt scn: 0x0000.00702afa prev tx cmt scn: 0x0000.00702b2e
txn start scn: 0xffff.ffffffff logon user: 32 prev brb: 12590267 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.008.00000e9d uba: 0x00c01840.02e4.13
flg: C--- lkc: 0 scn: 0x0000.00702b1f
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: -2 ##列字符長度減少2：chedan減去database就是-2---根據多次update并DUMP的日志來看，這里的size的值應該是：當前CHANGE中的值減去另一個
col 1: [ 6] 63 68 65 64 61 6e --修改語句是：update dept set dname='database' where deptno=11; deptno=11時的為chedan-DUMP為16進制，與col 1: [ 6] 63 68 65 64 61 6e對應，[ 6]
與chedan對應，col 1是第二列--修改的dname列就是第二列。
SYS@ bys3>select dump('chedan',16) from dual;
DUMP('CHEDAN',16)
-------------------------------
Typ=96 Len=6: 63,68,65,64,61,6e
##############
CHANGE #3 TYP:2 CLS:1 AFN:4DBA:0x010000fd OBJ:22327 SCN:0x0000.00702d7a SEQ:1 OP:11.5 ENC:0 RBL:0
## OP:11.19 或者OP:11.5都是--UPDATE語句，開始修改數據， AFN:4 --這次是在USERS表空間修改數據-文件號就是4
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0001.00f.00000e9c uba: 0x00c01cbf.0250.0f
Block cleanout record, scn: 0x0000.00702fe0 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.00702b1f
itli: 2 flg: 2 scn: 0x0000.00702d7a
KDO Op code: URP row dependencies Disabled
###此語句也說明是UPDATE語句，URP row dependencies Disabled --URP=UPDATE ROW PIECE。有時會是：KDO Op code:21 row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 1 ckix: 0
ncol: 3 nnew: 1 size: 2 ##size: 2，列字符長度增加2：database減去chedan---根據多次update并DUMP的日志來看，這里的size的值應該是：當前CHANGE中的值減去另一個
col 1: [ 8] 64 61 74 61 62 61 73 65 ---update dept set dname='database' where deptno=11;，對應select dump('database',16) from dual; col 1: [ 8] ---第二列，8位，
####################################################
REDO RECORD - Thread:1 RBA: 0x000137.00000004.0010 LEN: 0x008c VLD: 0x05
SCN: 0x0000.00702fe2 SUBSCN: 1 01/06/2014 20:39:46
(LWN RBA: 0x000137.00000004.0010 LEN: 0001 NST: 0001 SCN: 0x0000.00702fe1)
CHANGE #1 TYP:0 CLS:17 AFN:3 DBA:0x00c00080 OBJ:4294967295 SCN:0x0000.00702fe0 SEQ:1OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x000f sqn: 0x00000e9c srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c01cbf.0250.0f ext: 4 spc: 6328 fbi: 0
###########
OP:5.4 表明是提交操作。
CHANGE #1 TYP:0 CLS:17 AFN:3 DBA:0x00c00080 OBJ:4294967295 SCN:0x0000.00702fe0 SEQ:1 OP:5.4 ENC:0 RBL:0
AFN:3 對應的是UNDO文件，
slt: 0x000f 修改了UNDO文件的這個事務槽
SCN: 0x0000.00702fe2 SUBSCN: 1 01/06/2014 20:39:46 提交操作的時間 ：
20:39:43 BYS@ bys3>commit;
Commit complete.
20:39:46 BYS@ bys3>
########################################
實驗三：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
聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
