Skip to content

All posts by Guang Cai Li

介绍free lists及shared pool lru list.
Shared pool中chunk的分配
1、shared pool中的chunk的大小是不一样的，但是是连续的
2、因为chunk是分配的最小单元，因此session需要给对象分配空间的时候，会以chunk为单位进行申请
3、可用的chunk（free）会形成一个链表 feee lists，便于进行分配的时候，可以通过遍历链表寻找到可用的适合的chunk，链表是chunk进行组织和管理的一种方式
4、一个可用的chunk链表是一个bucket，shared pool中会有很多的bucket，不同的bucket中的chunk的大小不同，一般是随着bucket编号的增加而增长的
5.当需要从shared pool中寻找chunk的时候，首先会定位一个bucket，然后遍历bucket，寻找最合适的chunk, 如果chunk的空间比需要的空间大，那么这个chunk就拆分成两个，一个被分配、一个成为free，重新挂接到相应大小的bucket上。
6、在寻找chunk的过程中，如果一个bucket中没有合适的chunk，接着寻找另外一个非空的bucket，如果所有的bucket中都没有合适的chunk，那么就从rec类型的链表中释放一部分的空间，为free，或将free做适当合并 。。注意：只有rec类型的chunk能够被释放空间，即使释放了空间，这些空间可能都不是连续的，都是一些很小的chunk，这样可能形成这样一种情况，shared pool中有空间但是是大量的非常小的chunk，这样在寻找chunk的时候，也很难寻找到合适的chunk–共享池碎片
7、shared pool中所有类型的chunk都是以链表的方式进行管理的
##############
free list 空闲列表
按bucket划分，共有255个，bucket 0—bucket 254
每个bucket上挂有一个 chunk list； free lists上的都是未使用的chunk–状态free
free lists上Bucket大小的分布情况： –环境LINUX 32位/ORACLE 11.2.0.4
Bucket 0-199之前 Bucket size以4bytes递增
Bucket 200-239这一段的Bucket size以64bytes递增
Bucket 240–254的Bucket size增长看起来没太大规律，总之是递增了哈哈。
因为free chunk数量非常的多，这样划分每个Bucket容纳的chunk数量减少，查找效率提高，对shared pool latch的争用也大大减少。注意：如果在shared pool中出现了大量的小的free chunk，就会出现share pool latch争用的情况，即使增加共享池的大小，这个问题随着时间还是会出现的。
RESERVED FREE LISTS:
RESERVED FREE LISTS上的bucket个数我DUMP出来的是15个。
保留FREE LISTS，在SQL语句所需CHUNK大于4400bytes时，会在RESERVED FREE LISTS中查找空闲CHUNK。
如果SQL语句所需CHUNK不大于4400bytes时，只会在free list 中查找CHUNK。
这个是由隐含参数控制的：_shared_pool_reserved_min_alloc minimum allocation size in bytes for reserved area ，默认值4400
###################
DUMP 共享池查看free lists/bucket/RESERVED FREE LISTS结构：–用新建会话来做
alter session set events ‘immediate trace name heapdump level 2’;
select value from v$diag_info where name like ‘De%’;
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_7876.trc
查看TRACE文件内容： –找这一段的方法：VI搜索HEAP DUMP
Chunk 2bffa844 sz= 22460 freeable “character set m”
Total heap size =146798680 — 146798680/1024/1024 –139.99813 初始化参数：shared_pool_size–140M
FREE LISTS: ——空闲列，可以明显看出bucket大小分配的规律–从小到大，共有255个buckets，从16bytes到64k，采用此方法分配内存，可以有效的减少内存碎片。每个Bucket之间都用double linked 相互连接
Bucket 0 size= 16
Chunk 2bc00048 sz= 0 kghdsx
Bucket 1 size=20 20字节的Bucket 1，有很多个Chunk，节约篇幅省略了
Chunk 23a60468 sz= 20 free ” ”
Chunk的地址、大小、状态
Chunk 23ceb498 sz= 20 free ” ”
Chunk 237fcde4 sz= 20 free ” ”
Bucket 2 size=24 –Bucket 2 –24字节,也就是Bucket 2的 size范围在20-24字节。。
Chunk 245b13e4 sz= 24 free ” ”
Chunk 23ace7c0 sz= 24 free ” ”
Chunk 239c5a28 sz= 24 free ” ”
Bucket 3 size=28
Chunk 24540e9c sz= 28 free ” ”
Chunk 2521209c sz= 28 free ” ”
Chunk 23483448 sz= 28 free ” ”
………………
Bucket 198 size=1388
Bucket 199 size=1452 ———- Bucket 200之前 Bucket size以4bytes递增
Chunk 2347d2ac sz= 1492 free ” ”
Bucket 200 size=1516 ———-Bucket 200之后 Bucket size以64bytes递增
Chunk 24b28a94 sz= 1548 free ” ”
Bucket 201 size=1580
Chunk 2433bb14 sz= 1620 free ” ”
Chunk 2463a89c sz= 1620 free ” ”
Chunk 24b829c4 sz= 1620 free ” ”
Bucket 202 size=1644
Chunk 23498518 sz= 1704 free ” ”
Chunk 23de90d0 sz= 1696 free ” ”
………………
Bucket 236 size=3820
Bucket 237 size=3884
Bucket 238 size=3948
Bucket 239 size=4012 ————–Bucket 200到-Bucket 239这一段的Bucket size以64bytes递增
Bucket 240 size=4096 ———–Bucket 239之后 的Bucket size增长看起来没太大规律，总之是递增了哈哈
Bucket 241 size=4100
Bucket 242 size=4108
Bucket 243 size=8204
Bucket 244 size=8460
Bucket 245 size=8464
Bucket 246 size=8468
Bucket 247 size=8472
Bucket 248 size=9296
Bucket 249 size=9300
Bucket 250 size=12320
Bucket 251 size=12324
Bucket 252 size=16396
Bucket 253 size=32780
Bucket 254 size=65548
Total free space = 518232
RESERVED FREE LISTS: –保留FREE LISTS，解析方法同上。保留池中CHUNK都比较大
Reserved bucket 0 size=16
Chunk 23420320 sz= 676 R-free ” ”
Chunk 23427b94 sz= 3420 R-free ” ”
Chunk 2342618c sz= 952 R-free ” ”
Chunk 23800050 sz= 1040 R-free ” ”
Chunk 23400050 sz= 2824 R-free ” ”
Chunk 25bff028 sz= 4032 R-free ” ”
Chunk 293ff788 sz= 2144 R-free ” ”
Reserved bucket 1 size=4400 –这个如果用的会，4564-4400，剩下164字节会成为新CHUNK
Chunk 23430a40 sz= 4564 R-free ” ”
Reserved bucket 2 size=8204
Reserved bucket 3 size=8460
Reserved bucket 4 size=8464
Reserved bucket 5 size=8468
Reserved bucket 6 size=8472
Chunk 2342b988 sz= 9136 R-free ” ”
Reserved bucket 7 size=9296
Reserved bucket 8 size=9300
Reserved bucket 9 size=12320
Reserved bucket 10 size=12324
Reserved bucket 11 size=16396
Chunk 234161f8 sz= 16448 R-free ” ”
Reserved bucket 12 size=32780
Reserved bucket 13 size=65548
Chunk 23401d50 sz= 72296 R-free ” ”
Chunk 23815668 sz= 125312 R-free ” ”
Chunk 23c00050 sz= 180380 R-free ” ”
Reserved bucket 14 size=1990652
Total reserved free space = 6712468 –总空闲保留空间是6.4M，shared_pool_reserved_size 初始化参数大小是 7M，用了0.6M
######################

shared pool LRU链
shared pool LRU链上挂的都是recreate、freeabl状态的chunk,一个SQL语句可能需要多个CHUNK，在LRU链上找到recreate状态的chunk，然后在 recreate状态的chunk下再下挂freeabl状态的CHUNK，–避免全部CHUNK在LRU链上导致LRU链太长。
TRACE文件中找到关于(lru first)的一段，方法同上：
Reserved bucket 14 size=1990652
Total reserved free space = 6712468
UNPINNED RECREATABLE CHUNKS (lru first):
Chunk 246c9848 sz= 348 recreate “KGLHD ” latch=(nil) — latch状态为空， Chunk SIZE是348字节，状态 recreate，
Chunk 237cb10c sz= 4096 recreate “KGLH0^b9197c6e ” latch=(nil)
Chunk 24bb5df0 sz= 364 recreate “KGLHD ” latch=(nil)
Chunk 241aa1b8 sz= 4096 recreate “KGLH0^59449e50 ” latch=(nil)
Chunk 252640a0 sz= 364 recreate “KGLHD ” latch=(nil)
Chunk 23a619a0 sz= 4096 recreate “KGLH0^d5f1e0d7 ” latch=(nil)
Chunk 23465600 sz= 348 recreate “KGLHD ” latch=(nil)
Chunk 2346575c sz= 1036 recreate “KGLHD ” latch=(nil)
Chunk 23465b68 sz= 4096 recreate “KGLH0^c6e0d102 ” latch=(nil) –一个recreate状态CHUNK下的多个freeable状态CHUNK
ds 24bdecb0 sz= 4096 ct= 1
Chunk 23466b68 sz= 4096 freeable “SQLA^1536bb77 ” ds=0x23db5bd8
Chunk 23467b68 sz= 144 freeable “KGLDA ”
Chunk 23467bf8 sz= 4096 freeable “KGLH0^ba3f9b05 ” ds=0x2425e238

shared pool之二：free lists/shared pool lru list

日志挖掘概念：
结论如下：执行日志挖掘操作的可以使用DBA用户或SYSDBA用户，不能使用普通用户。
日志挖掘可以查看到当前用户自己的操作，也可以查看到其它用户的操作。
其它用户已经执行但未提交的操作，也可以查到。
可以挖掘到其它DBA用户或SYSDBA用户的操作。
—–有些语句的返回提示如下面一句连接后的“Connected.”这种为节约篇幅，删了。不要质疑哈哈。
对于DDL操作 ：对于执行DDL前的操作无法挖掘，但是不报错。只显示DDL语句及DDL之后的DML操作。见： logmnr挖掘中间有DDL的操作示例
BYS@ bys001>conn scott/tiger
Connected.
SCOTT@ bys001>desc v$logmnr_contents
ERROR:
ORA-04043: object “SYS”.”V_$LOGMNR_CONTENTS” does not exist
证明普通用户不能使用日志挖掘，连这个视图都看不到的。
##############################################
实验1：使用SYSDBA用户对SYS用户进行日志挖掘
在SYSDBA用户下建表插入数据进行日志挖掘。
SCOTT@ bys001>conn / as sysdba
Connected.
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
14390465
SYS@ bys001>create table t(a number);
SYS@ bys001>insert into t values(1);
SYS@ bys001>commit;
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
14390508

可以直接使用这样一条语句：select a.group#,a.member,b.status from v$logfile a,v$log b where a.group#=b.group#;

SYS@ bys001>select group#,status from v$log;
GROUP# STATUS
———- —————-
1 INACTIVE
2 INACTIVE
3 CURRENT
SYS@ bys001>col member for a50

SYS@ bys001>select group#,member,type from v$logfile;

GROUP# MEMBER TYPE
———- ————————————————– ——-
3 /u01/app/oracle/oradata/bys001/redo03.log ONLINE
2 /u01/app/oracle/oradata/bys001/redo02.log ONLINE
1 /u01/app/oracle/oradata/bys001/redo01.log ONLINE
1 /u01/app/oracle/oradata/bys001/redo01a.log ONLINE
SYS@ bys001>execute dbms_logmnr.add_logfile(LogFileName => ‘/u01/app/oracle/oradata/bys001/redo03.log’,Options => dbms_logmnr.new);
PL/SQL procedure successfully completed.
SYS@ bys001>execute dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_online_catalog,startscn =>14390465,endscn =>14390508);
PL/SQL procedure successfully completed.
SYS@ bys001>select operation,sql_redo,sql_undo from v$logmnr_contents where table_name=’T’;
OPERATION
——————————–
SQL_REDO
—————————————————————————————————-
SQL_UNDO
—————————————————————————————————-
DDL
create table t(a number);

INSERT
insert into “SYS”.”T”(“A”) values (‘1’);
delete from “SYS”.”T” where “A” = ‘1’ and ROWID = ‘AAASuWAABAAAVS5AAA’;
#########################################################
实验二：使用SYSDBA用户对普通用户SCOTT下的操作进行挖掘
SYS@ bys001>conn scott/tiger
Connected.
SCOTT@ bys001>create table test(a number);
SCOTT@ bys001>select dbms_flashback.get_system_change_number from dual;
select dbms_flashback.get_system_change_number from dual
*
ERROR at line 1:
ORA-00904: : invalid identifier —普通用户不能查询当前SCN
SCOTT@ bys001>conn / as sysdba
Connected.
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
14390661
开始插入一条数据。
SYS@ bys001>conn scott/tiger
Connected.
SCOTT@ bys001>insert into test values(3);
SCOTT@ bys001>commit;
SCOTT@ bys001>conn / as sysdba
Connected.
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
14390688
SYS@ bys001>execute dbms_logmnr.add_logfile(LogFileName => ‘/u01/app/oracle/oradata/bys001/redo03.log’,Options => dbms_logmnr.new);
PL/SQL procedure successfully completed.
SYS@ bys001>execute dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_online_catalog, startscn =>14390661,endscn =>14390688);
PL/SQL procedure successfully completed.
SYS@ bys001>select operation,sql_redo,sql_undo from v$logmnr_contents where table_name=’TEST’;
OPERATION
——————————–
SQL_REDO
—————————————————————————————————-
SQL_UNDO
—————————————————————————————————-
INSERT
insert into “SCOTT”.”TEST”(“A”) values (‘3’);
delete from “SCOTT”.”TEST” where “A” = ‘3’ and ROWID = ‘AAASuXAAEAAAAlGAAA’;
####################################################

实验三：使用SYSDBA，SCOTT用户插入数据不提交，可以挖掘到相应日志————也证明了commit和写日志的无关性。
这里需要使用两个会话，因为在同一个SQLPLUS会话下，切换用户会引起COMMIT。—–实验得出
会话一：查询出当前SCN
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
14391177
会话二：使用SCOTT用户插入一条记录
SCOTT@ bys001>insert into test values(99);
SCOTT@ bys001>select * from test;
A
———-
3
333
99
会话一：记录当前SCN
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
14391194
SYS@ bys001>execute dbms_logmnr.add_logfile(LogFileName => ‘/u01/app/oracle/oradata/bys001/redo03.log’,Options => dbms_logmnr.new);
PL/SQL procedure successfully completed.
SYS@ bys001>execute dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_online_catalog,startscn =>14391177,endscn =>14391194);
PL/SQL procedure successfully completed.
SYS@ bys001>select operation,sql_redo,sql_undo from v$logmnr_contents where table_name=’TEST’;
OPERATION
——————————–
SQL_REDO
—————————————————————————————————-
SQL_UNDO
—————————————————————————————————-
INSERT
insert into “SCOTT”.”TEST”(“A”) values (’99’);
delete from “SCOTT”.”TEST” where “A” = ’99’ and ROWID = ‘AAASuXAAEAAAAlGAAC’;

会话二：回滚之前的插入，证明之前的插入确实是未提交的。
SCOTT@ bys001>rollback;
Rollback complete.
SCOTT@ bys001>select * from test;
A
———-
3
333
##########################################################
实验四：使用普通DBA用户可以挖掘出SYS用户的操作
SYS@ bys001>conn bys/bys
Connected.
BYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
14391461
BYS@ bys001>conn / as sysdba
SYS@ bys001>insert into t values(9);
SYS@ bys001>commit;
SYS@ bys001>conn bys/bys
BYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
14391482
BYS@ bys001>execute dbms_logmnr.add_logfile (LogFileName => ‘/u01/app/oracle/oradata/bys001/redo03.log’,Options => dbms_logmnr.new);
PL/SQL procedure successfully completed.
BYS@ bys001>execute dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_online_catalog ,startscn =>14391461,endscn =>14391482);
PL/SQL procedure successfully completed.
BYS@ bys001>select operation, sql_redo,sql_undo from v$logmnr_contents where table_name=’T’;
OPERATION
——————————–
SQL_REDO
—————————————————————————————————-
SQL_UNDO
—————————————————————————————————-
INSERT
insert into “SYS”.”T”(“A”) values (‘9’);
delete from “SYS”.”T” where “A” = ‘9’ and ROWID = ‘AAASuWAABAAAVS5AAB’;

使用logmnr对其它用户的操作执行日志挖掘的四个对比实验

注意：每个实验前最好做下日志切换，更详细方法dump redo 文件的方法及本篇DUMP的TRACE文件见下载附件：本篇TRACE等附件下载，或者另一篇博客 ：非IMU模式下DML语句产生的REDO日志内容格式解读

实验1：
BYS@ bys3>select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
10 database NEW YORK
20 database DALLAS
40 OPERATIONS BOSTON
11 database bj
22 test sh
update dept set dname=’oracle’ where deptno<40;
4 rows updated.

这个更新语句会产生四条REDO RECORD，因为对四条记录改为oracle
#############################
实验2：
BYS@ bys3>select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
40 OPERATIONS BOSTON
11 database bj
22 test sh
BYS@ bys3>update dept set dname=’database’ where deptno<22;
3 rows updated.
这个更新语句中，已经有一条是database，这次更新了三条语句，但是只有两条REDO RECORD，
有一个REDO RECORD的CHANGE #1不一样：这个REDO RECORD只有CHANGE #1，CHANGE #2，CHANGE #1中就把这两个值放入UNDO，详见后面日志。
##########################################
实验3：
BYS@ bys3>select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
10 oracle NEW YORK
20 oracle DALLAS
40 OPERATIONS BOSTON
11 oracle bj
22 oracle sh
BYS@ bys3>set time on
09:47:59 BYS@ bys3>update dept set dname=’oracle’ where deptno=10;
1 row updated.
将一行中oracle–UPDATE为oracle，一条REDO RECORD
####################
实验4：
10:08:32 BYS@ bys3>select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
10 oracle NEW YORK
20 oracle DALLAS
40 OPERATIONS BOSTON
11 oracle bj
22 oracle sh
10:08:57 BYS@ bys3>update dept set dname=’oracle’ where deptno<40;
4 rows updated.
更新了四行，这四行原值oracle，更新为oracle，只有一条一条REDO RECORD

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

附dump 每次操作的redo日志

实验1的：
REDO RECORD – Thread:1 RBA: 0x00013b.00000029.0010 LEN: 0x01e4 VLD: 0x05
SCN: 0x0000.00704cb2 SUBSCN: 1 01/06/2014 22:04:42
(LWN RBA: 0x00013b.00000029.0010 LEN: 0003 NST: 0001 SCN: 0x0000.00704cb1)
CHANGE #1 TYP:0 CLS:33 AFN:3 DBA:0x00c00100 OBJ:4294967295 SCN:0x0000.00704c81 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0015 sqn: 0x00000e9f flg: 0x0012 siz: 164 fbi: 0
uba: 0x00c02e0e.0247.2a pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:34 AFN:3 DBA:0x00c02e0e OBJ:4294967295 SCN:0x0000.00704c80 SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 164 spc: 3160 flg: 0x0012 seq: 0x0247 rec: 0x2a
xid: 0x0009.015.00000e9f
ktubl redo: slt: 21 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: 0x00c02e0e.0247.27
prev ctl max cmt scn: 0x0000.007049b5 prev tx cmt scn: 0x0000.007049e3
txn start scn: 0xffff.ffffffff logon user: 32 prev brb: 12591203 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: 0x0001.00f.00000e9c uba: 0x00c01cbf.0250.0f
flg: C— lkc: 0 scn: 0x0000.00702fe2
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 2
col 1: [ 8] 64 61 74 61 62 61 73 65
CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x010000fd OBJ:22327 SCN:0x0000.00703bc4 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: 0x0009.015.00000e9f uba: 0x00c02e0e.0247.2a
Block cleanout record, scn: 0x0000.00704cb2 ver: 0x01 opt: 0x02, entries follow…
itli: 2 flg: 2 scn: 0x0000.00703bc4
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 1 ckix: 0
ncol: 3 nnew: 1 size: -2
col 1: [ 6] 6f 72 61 63 6c 65
##############################################################
REDO RECORD – Thread:1 RBA: 0x00013b.0000002a.0010 LEN: 0x0108 VLD: 0x01
SCN: 0x0000.00704cb2 SUBSCN: 1 01/06/2014 22:04:42
CHANGE #1 TYP:0 CLS:34 AFN:3 DBA:0x00c02e0e OBJ:4294967295 SCN:0x0000.00704cb2 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 96 spc: 2994 flg: 0x0022 seq: 0x0247 rec: 0x2b
xid: 0x0009.015.00000e9f
ktubu redo: slt: 21 rci: 42 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: 0x00c02e0e.0247.2a
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 2
col 1: [ 8] 64 61 74 61 62 61 73 65
CHANGE #2 TYP:0 CLS:1 AFN:4 DBA:0x010000fd OBJ:22327 SCN:0x0000.00704cb2 SEQ:1 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c02e0e.0247.2b
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 1 ckix: 0
ncol: 3 nnew: 1 size: -2
col 1: [ 6] 6f 72 61 63 6c 65
###################################################
REDO RECORD – Thread:1 RBA: 0x00013b.0000002a.0118 LEN: 0x0108 VLD: 0x01
SCN: 0x0000.00704cb2 SUBSCN: 1 01/06/2014 22:04:42
CHANGE #1 TYP:0 CLS:34 AFN:3 DBA:0x00c02e0e OBJ:4294967295 SCN:0x0000.00704cb2 SEQ:2 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 96 spc: 2896 flg: 0x0022 seq: 0x0247 rec: 0x2c
xid: 0x0009.015.00000e9f
ktubu redo: slt: 21 rci: 43 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: 0x00c02e0e.0247.2b
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
col 1: [ 8] 64 61 74 61 62 61 73 65
CHANGE #2 TYP:0 CLS:1 AFN:4 DBA:0x010000fd OBJ:22327 SCN:0x0000.00704cb2 SEQ:2 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c02e0e.0247.2c
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: 1 ckix: 0
ncol: 3 nnew: 1 size: -2
col 1: [ 6] 6f 72 61 63 6c 65
###################################################################
REDO RECORD – Thread:1 RBA: 0x00013b.0000002b.0030 LEN: 0x0104 VLD: 0x01
SCN: 0x0000.00704cb2 SUBSCN: 1 01/06/2014 22:04:42
CHANGE #1 TYP:0 CLS:34 AFN:3 DBA:0x00c02e0e OBJ:4294967295 SCN:0x0000.00704cb2 SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 92 spc: 2798 flg: 0x0022 seq: 0x0247 rec: 0x2d
xid: 0x0009.015.00000e9f
ktubu redo: slt: 21 rci: 44 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: 0x00c02e0e.0247.2c
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 6(0x6) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: -2
col 1: [ 4] 74 65 73 74
CHANGE #2 TYP:0 CLS:1 AFN:4 DBA:0x010000fd OBJ:22327 SCN:0x0000.00704cb2 SEQ:3 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c02e0e.0247.2d
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 6(0x6) flag: 0x2c lock: 1 ckix: 0
ncol: 3 nnew: 1 size: 2
col 1: [ 6] 6f 72 61 63 6c 65
################################################提交的日志条目：
REDO RECORD – Thread:1 RBA: 0x00013b.0000002c.0010 LEN: 0x008c VLD: 0x05
SCN: 0x0000.00704cb4 SUBSCN: 1 01/06/2014 22:04:43
(LWN RBA: 0x00013b.0000002c.0010 LEN: 0001 NST: 0001 SCN: 0x0000.00704cb3)
CHANGE #1 TYP:0 CLS:33 AFN:3 DBA:0x00c00100 OBJ:4294967295 SCN:0x0000.00704cb2 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0015 sqn: 0x00000e9f srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c02e0e.0247.2d ext: 8 spc: 2704 fbi: 0
END OF REDO DUMP

实验2的：
REDO RECORD – Thread:1 RBA: 0x00013a.00000002.0010 LEN: 0x01c0 VLD: 0x05
SCN: 0x0000.00703bc1 SUBSCN: 1 01/06/2014 21:56:03
(LWN RBA: 0x00013a.00000002.0010 LEN: 0002 NST: 0001 SCN: 0x0000.00703bc0)
CHANGE #1 TYP:0 CLS:23 AFN:3 DBA:0x00c000b0 OBJ:4294967295 SCN:0x0000.00703b80 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0004 sqn: 0x00000ec7 flg: 0x0012 siz: 168 fbi: 0
uba: 0x00c01ddb.0283.21 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:24 AFN:3 DBA:0x00c01ddb OBJ:4294967295 SCN:0x0000.00703b7f SEQ:9 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 168 spc: 3658 flg: 0x0012 seq: 0x0283 rec: 0x21
xid: 0x0004.004.00000ec7
ktubl redo: slt: 4 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: 0x00c01ddb.0283.01
prev ctl max cmt scn: 0x0000.007034c7 prev tx cmt scn: 0x0000.007034f1
txn start scn: 0xffff.ffffffff logon user: 32 prev brb: 12590550 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: 0x0003.013.00000eac uba: 0x00c01ac7.0250.06
flg: C— lkc: 0 scn: 0x0000.00702d7a
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 2 —ACCOUNTING 比database多两个字节
col 1: [10] 41 43 43 4f 55 4e 54 49 4e 47 —对应的是将第一行第二列的值放进UNDO—ACCOUNTING
CHANGE #3 TYP:0 CLS:1 AFN:4 DBA:0x010000fd OBJ:22327 SCN:0x0000.00703bb3 SEQ:3 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0004.004.00000ec7 uba: 0x00c01ddb.0283.21
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 2 ckix: 0
ncol: 3 nnew: 1 size: -2
col 1: [ 8] 64 61 74 61 62 61 73 65

REDO RECORD – Thread:1 RBA: 0x00013a.00000002.01d0 LEN: 0x0190 VLD: 0x01 –这个REDO RECORD只有CHANGE #1，CHANGE #2
SCN: 0x0000.00703bc1 SUBSCN: 1 01/06/2014 21:56:03
CHANGE #1 TYP:0 CLS:24 AFN:3 DBA:0x00c01ddb OBJ:4294967295 SCN:0x0000.00703bc1 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 164 spc: 3488 flg: 0x0022 seq: 0x0283 rec: 0x22
xid: 0x0004.004.00000ec7
ktubu redo: slt: 4 rci: 33 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: 0x00c01ddb.0283.21
Array Update of 2 rows:
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 8] 52 45 53 45 41 52 43 48 —DUMP(‘RESEARCH’,16)
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 8] 64 61 74 61 62 61 73 65 –DUMP(‘DATABASE’,16)
CHANGE #2 TYP:0 CLS:1 AFN:4 DBA:0x010000fd OBJ:22327 SCN:0x0000.00703bc1 SEQ:1 OP:11.19 ENC:0 RBL:0
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c01ddb.0283.22
Array Update of 2 rows:
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 2 ckix: 0
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 8] 64 61 74 61 62 61 73 65
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 2 ckix: 0
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 8] 64 61 74 61 62 61 73 65
REDO RECORD – Thread:1 RBA: 0x00013a.00000004.0010 LEN: 0x008c VLD: 0x05 –这个是提交语句的。
SCN: 0x0000.00703bc4 SUBSCN: 1 01/06/2014 21:56:07
(LWN RBA: 0x00013a.00000004.0010 LEN: 0001 NST: 0001 SCN: 0x0000.00703bc3)
CHANGE #1 TYP:0 CLS:23 AFN:3 DBA:0x00c000b0 OBJ:4294967295 SCN:0x0000.00703bc1 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0004 sqn: 0x00000ec7 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c01ddb.0283.22 ext: 16 spc: 3322 fbi: 0

实验3的日志：
REDO RECORD – Thread:1 RBA: 0x00013d.00000002.0010 LEN: 0x0214 VLD: 0x05
SCN: 0x0000.0070cae5 SUBSCN: 1 01/07/2014 09:48:10
(LWN RBA: 0x00013d.00000002.0010 LEN: 0002 NST: 0001 SCN: 0x0000.0070cae4)
CHANGE #1 TYP:0 CLS:23 AFN:3 DBA:0x00c000b0 OBJ:4294967295 SCN:0x0000.0070caaa SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x000d sqn: 0x00000ee5 flg: 0x0012 siz: 188 fbi: 0
uba: 0x00c040fa.0286.12 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:24 AFN:3 DBA:0x00c040fa OBJ:4294967295 SCN:0x0000.0070caa9 SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 188 spc: 5884 flg: 0x0012 seq: 0x0286 rec: 0x12
xid: 0x0004.00d.00000ee5
ktubl redo: slt: 13 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: 0x00c040fa.0286.0f
prev ctl max cmt scn: 0x0000.0070c50b prev tx cmt scn: 0x0000.0070c522
txn start scn: 0xffff.ffffffff logon user: 32 prev brb: 12599542 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: 0x0004.004.00000ec7 uba: 0x00c01ddb.0283.22
flg: C— lkc: 0 scn: 0x0000.00703bc4
Array Update of 1 rows:
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 6] 6f 72 61 63 6c 65
CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x010000fd OBJ:22327 SCN:0x0000.00704cb4 SEQ:1 OP:11.19 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0004.00d.00000ee5 uba: 0x00c040fa.0286.12
Block cleanout record, scn: 0x0000.0070cae5 ver: 0x01 opt: 0x02, entries follow…
itli: 1 flg: 2 scn: 0x0000.00704cb4
Array Update of 1 rows:
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 2 ckix: 0
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 6] 6f 72 61 63 6c 65

REDO RECORD – Thread:1 RBA: 0x00013d.00000004.0010 LEN: 0x008c VLD: 0x05
SCN: 0x0000.0070cae7 SUBSCN: 1 01/07/2014 09:48:11
(LWN RBA: 0x00013d.00000004.0010 LEN: 0001 NST: 0001 SCN: 0x0000.0070cae6)
CHANGE #1 TYP:0 CLS:23 AFN:3 DBA:0x00c000b0 OBJ:4294967295 SCN:0x0000.0070cae5 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x000d sqn: 0x00000ee5 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c040fa.0286.12 ext: 19 spc: 5694 fbi: 0

实验4的：
REDO RECORD – Thread:1 RBA: 0x00013f.000000de.0010 LEN: 0x0304 VLD: 0x05
SCN: 0x0000.0070cfe9 SUBSCN: 1 01/07/2014 10:09:20
(LWN RBA: 0x00013f.000000de.0010 LEN: 0002 NST: 0001 SCN: 0x0000.0070cfe8)
CHANGE #1 TYP:0 CLS:33 AFN:3 DBA:0x00c00100 OBJ:4294967295 SCN:0x0000.0070cfbf SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0004 sqn: 0x00000ebc flg: 0x0012 siz: 308 fbi: 0
uba: 0x00c037d5.0249.08 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:34 AFN:3 DBA:0x00c037d5 OBJ:4294967295 SCN:0x0000.0070cfbe SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 308 spc: 7252 flg: 0x0012 seq: 0x0249 rec: 0x08
xid: 0x0009.004.00000ebc
ktubl redo: slt: 4 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: 0x00c037d5.0249.05
prev ctl max cmt scn: 0x0000.0070cc57 prev tx cmt scn: 0x0000.0070cc81
txn start scn: 0xffff.ffffffff logon user: 32 prev brb: 12597202 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: 0x0004.00d.00000ee5 uba: 0x00c040fa.0286.12
flg: C— lkc: 0 scn: 0x0000.0070cae7
Array Update of 4 rows:
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 6] 6f 72 61 63 6c 65
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 6] 6f 72 61 63 6c 65
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 6] 6f 72 61 63 6c 65
tabn: 0 slot: 6(0x6) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 6] 6f 72 61 63 6c 65
CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x010000fd OBJ:22327 SCN:0x0000.0070cb7f SEQ:1 OP:11.19 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0009.004.00000ebc uba: 0x00c037d5.0249.08
Block cleanout record, scn: 0x0000.0070cfe9 ver: 0x01 opt: 0x02, entries follow…
itli: 1 flg: 2 scn: 0x0000.0070cb7f
Array Update of 4 rows:
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 2 ckix: 0
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 6] 6f 72 61 63 6c 65
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 2 ckix: 0
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 6] 6f 72 61 63 6c 65
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 2 ckix: 0
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 6] 6f 72 61 63 6c 65
tabn: 0 slot: 6(0x6) flag: 0x2c lock: 2 ckix: 0
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000fd hdba: 0x010000fa
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 6] 6f 72 61 63 6c 65

REDO RECORD – Thread:1 RBA: 0x00013f.000000df.0124 LEN: 0x0060 VLD: 0x01
SCN: 0x0000.0070cfea SUBSCN: 1 01/07/2014 10:09:20
CHANGE #1 TYP:0 CLS:33 AFN:3 DBA:0x00c00100 OBJ:4294967295 SCN:0x0000.0070cfe9 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0004 sqn: 0x00000ebc srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c037d5.0249.08 ext: 10 spc: 6942 fbi: 0

非IMU模式下一条update语句产生REDO RECORD条数的探究

ORA-01555是因为回滚段中的原数据镜像被覆盖，一致性读时查询时不到。

模拟ORA-01555错误 ：

1.配置一个不能自动扩展的UNDO表空间
这里要提前 创建一个UNDO表空间，大小5M并且表空间不支持自动扩展。–为方便实验当然可以设置更小的表空间比如1M 2M等。
所需语句是：create undo tablespace test1_undo datafile ‘/u01/app/oracle/oradata/bys001/test1_undo.dbf’ size 5m; –默认不启用自动扩展，通过select file_name,autoextensible from dba_data_files;可以查看到。删除表空间用： drop tablespace test1_undo including contents and datafiles;
然后切换当 前UNDO表空间为新创建的，语句alter system set undo_tablespace=test1_undo;
最终是如下状态：
SQL> show parameter undo
NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string TEST1_UNDO
SQL> select name,value/1024 as KB from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where name=’redo size’ or name like ‘undo change%’;
NAME KB
—————————————————————- ———-
redo size 1.75
undo change vector size 0.18359375
SQL> col tablespace_name for a15
SQL> col file_id for a5
SQL> col file_name for a30
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 MB from dba_data_files where file_name like ‘%test1_undo%’;
FILE_ FILE_NAME TABLESPACE_NAME MB
—– —————————— ————— ———-
7 /u01/app/oracle/oradata/bys001 TEST1_UNDO 5
/test1_undo.dbf
SQL> set time on
11:16:58 SQL> drop table test;
Table dropped

2.建表并执行循环删除的脚本
11:17:38 SQL> create table test as select * from dba_objects;
Table created
11:18:33 SQL> select current_scn from v$database;
CURRENT_SCN
———–
1556131
11:19:16 SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
1556132
11:19:21 SQL> select count(*) from test;
COUNT(*)
———-
72795
11:19:37 SQL>
11:19:55 SQL> begin
2 for i in 1 .. 20000 loop
3 delete from test where rownum < 3;
4 commit;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed
11:20:30 SQL> select count(*) from test;
COUNT(*)
———-
32795

3.使用as of scn方式 查询之前的数据，出现ORA-01555
11:21:29 SQL> select count(*) from test as of scn 1556132;

select count(*) from test as of scn 1556132

ORA-01555: snapshot too old: rollback segment number 19 with name “_SYSSMU19_425896126$” too small

11:21:43 SQL> select count(*) from test as of timestamp to_timestamp(‘2013/06/24 11:19:00′,’yyyy/mm/dd hh24:mi:ss’);

select count(*) from test as of timestamp to_timestamp(‘2013/06/24 11:19:00′,’yyyy/mm/dd hh24:mi:ss’)

ORA-01555: snapshot too old: rollback segment number 19 with name “_SYSSMU19_425896126$” too small

附实验用到的一个小脚本：
begin
for i in 1 .. 20000 loop
delete from test where rownum < 3;
commit;
end loop;
end;
/

重现ORA-01555的示例

总结：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 表示操作的表有3个列，操作了一列，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

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