深入了解REDO和UNDO复制
Oracle有个非常重要的特性就是REDO和UNDO，每一个DBA或者开发人员，不得不去好好研究REDO和UNDO。
因为其重要的特性，我们想彻底了解其内部原理或者实现方法是不是件容易的事情。
下面我从浅到深做了一翻实验，通过示例来理解原理和方法，但要知道，这仅仅是一小部分内容。
要想了解更多，请学者们查阅更多资料并从中获取。
下面进入我的实验。
首先，来看我的实验环境：
sys@WWW> select * from v$version;
BANNER
--------------------------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64biPL/SQL Release 10.2.0.5.0 - ProductionCORE 10.2.0.5.0 ProductionTNS for Linux: Version 10.2.0.5.0 - ProductionNLSRTL Version 10.2.0.5.0 - Production
sys@WWW> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------WWW.LUOCS.COM
这是我用到的几个脚本：
[oracle@khm22 ~]$ cat show_undo.sql
select used_ublk
from v$transaction
where addr = (select taddr
from v$session
where sid = (select sid
from v$mystat
where rownum = 1 ))/
[oracle@khm22 ~]$ cat show_redo.sql
column NAME for a20
select
b.name,
a.value
from
v$mystat a,
v$statname b
where
a.statistic# = b.statistic# and b.name = ‘redo size’/
查看事务信息[oracle@khm22 ~]$ cat showtra.sql
set line 150column STATUS for a15
select XIDUSN,
XIDSLOT,
XIDSQN,
UBAFIL,
UBABLK,
UBASQN,
UBAREC,
STATUS
from v$transaction
where ADDR = ( SELECT TADDR
FROM V$SESSION
WHERE SID=&SID
)/
查看rowid
[oracle@khm22 ~]$ cat showrowid.sql
select rowid, dbms_rowid.rowid_object(rowid) object_id, dbms_rowid.rowid_relative_fno(rowid) file_id, dbms_rowid.rowid_block_number(rowid) block_id, dbms_rowid.rowid_row_number(rowid) num, rowidtochar(rowid)
from &table_name
where id=&id
/
1、演示DML操作，从相关视图中查询产生的UNDO和REDO大小。
luocs@WWW> show user
USER is “LUOCS”
– 当前SESSION下，没有任何redo和undo
luocs@WWW> @show_redo
NAME VALUE
-------------------- ----------redo size 0
luocs@WWW> @show_undo
no rows selected
创建一张表，并插入数据luocs@WWW> create table khm(id number, domain varchar2(20));
Table created.
luocs@WWW> insert into khm values(1,‘www.luocs.com’);
1 row created.
luocs@WWW> insert into khm values(2,‘www.oracle.com’);
1 row created.
luocs@WWW> commit;
Commit complete.
因为INSERT操作，已经看到有REDO产生luocs@WWW> @show_redo
NAME VALUE
-------------------- ----------redo size 13672
luocs@WWW> @show_undo
no rows selected
下面更新一行数据，发现在没提交时redo没有变化，undo却产生了luocs@WWW> update khm set domain = ‘www.luocs.com’ where id = 2;
1 row updated.
luocs@WWW> @show_redo
NAME VALUE
-------------------- ----------redo size 13672
luocs@WWW> @show_undo
USED_UBLK
---------- 1待我们commit之后，看到redo变化，undo则消失。luocs@WWW> commit;
Commit complete.
luocs@WWW> @show_redo
NAME VALUE
-------------------- ----------redo size 14216
luocs@WWW> @show_undo
no rows selected
下面是查看rollback操作，结果是rollback也会产生redo。luocs@WWW> update khm set domain = ‘www.oracle.com’ where id = 2;
1 row updated.
luocs@WWW> @show_redo
NAME VALUE
-------------------- ----------redo size 14216
luocs@WWW> @show_undo
USED_UBLK
---------- 1
luocs@WWW> rollback;
Rollback complete.
luocs@WWW> @show_redo
NAME VALUE
-------------------- ----------redo size 14548
luocs@WWW> @show_undo
no rows selected
OK，从这里我们了解到，DML操作即产生REDO也产生UNDO。
下面，我们较深入去研究一下。
首先，下面是REDO CV(Change Vector,变化矢量)中OPCODE的取值范围：
Layer 1 : Transaction Control - KCOCOTCT
Opcode 1 : KTZFMT
Opcode 2 : KTZRDH
Opcode 3 : KTZARC
Opcode 4 : KTZREP
Layer 2 : Transaction Read - KCOCOTRD Layer 3 : Transaction Update - KCOCOTUP Layer 4 : Transaction Block - KCOCOTBK [ktbcts.h] Opcode 1 : Block Cleanout Opcode 2 : Physical Cleanout Opcode 3 : Single Array Change Opcode 4 : Multiple Changes to an Array Opcode 5 : Format Block Layer 5 : Transaction Undo - KCOCOTUN [ktucts.h] Opcode 1 : Undo block or undo segment header - KTURDB Opcode 2 : Update rollback segment header - KTURDH Opcode 3 : Rollout a transaction begin Opcode 4 : Commit transaction (transaction table update) - no undo record Opcode 5 : Create rollback segment (format) - no undo record Opcode 6 : Rollback record index in an undo block - KTUIRB Opcode 7 : Begin transaction (transaction table update) Opcode 8 : Mark transaction as dead Opcode 9 : Undo routine to rollback the extend of a rollback segment Opcode 10 :Redo to perform the rollback of extend of rollback segment to the segment header. Opcode 11 :Rollback DBA in transaction table entry - KTUBRB Opcode 12 :Change transaction state (in transaction table entry) Opcode 13 :Convert rollback segment format (V6 -> V7) Opcode 14 :Change extent allocation parameters in a rollback segment Opcode 15 : Opcode 16 : Opcode 17 : Opcode 18 : Opcode 19 : Transaction start audit log record Opcode 20 : Transaction continue audit log record Opcode 24 : Kernel Transaction Undo Relog CHanGe - KTURLGU Layer 6 : Control File - KCOCODCF [tbs.h] Layer 10 : INDEX - KCOCODIX [kdi.h] Opcode 1 : load index block (Loader with direct mode) Opcode 2 : Insert leaf row Opcode 3 : Purge leaf row Opcode 4 : Mark leaf row deleted Opcode 5 : Restore leaf row (clear leaf delete flags) Opcode 6 : Lock index block Opcode 7 : Unlock index block Opcode 8 : Initialize new leaf block Opcode 9 : Apply Itl Redo Opcode 10 :Set leaf block next link Opcode 11 :Set leaf block previous link Opcode 12 :Init root block after split Opcode 13 :Make leaf block empty Opcode 14 :Restore block before image Opcode 15 :Branch block row insert Opcode 16 :Branch block row purge Opcode 17 :Initialize new branch block Opcode 18 :Update keydata in row Opcode 19 :Clear row's split flag Opcode 20 :Set row's split flag Opcode 21 :General undo above the cache (undo) Opcode 22 :Undo operation on leaf key above the cache (undo) Opcode 23 :Restore block to b-tree Opcode 24 :Shrink ITL (transaction entries) Opcode 25 :Format root block redo Opcode 26 :Undo of format root block (undo) Opcode 27 :Redo for undo of format root block Opcode 28 :Undo for migrating block Opcode 29 :Redo for migrating block Opcode 30 :IOT leaf block nonkey update Opcode 31 :Cirect load root redo Opcode 32 :Combine operation for insert and restore rows Layer 11 : Row Access - KCOCODRW [kdocts.h] Opcode 1 : Interpret Undo Record (Undo) Opcode 2 : Insert Row Piece Opcode 3 : Drop Row Piece Opcode 4 : Lock Row Piece Opcode 5 : Update Row Piece Opcode 6 : Overwrite Row Piece Opcode 7 : Manipulate First Column (add or delete the 1rst column) Opcode 8 : Change Forwarding address Opcode 9 : Change the Cluster Key Index Opcode 10 :Set Key Links (change the forward & backward key links on a cluster key) Opcode 11 :Quick Multi-Insert (ex: insert as select ...) Opcode 12 :Quick Multi-Delete Opcode 13 :Toggle Block Header flags Layer 12 : Cluster - KCOCODCL [?] Layer 13 : Transaction Segment - KCOCOTSG [ktscts.h] Opcode 1 : Data segment format Opcode 2 : Merge Opcode 3 : Set link in block Opcode 4 : Not used Opcode 5 : New block (affects segment header) Opcode 6 : Format block (affects data block) Opcode 7 : Record link Opcode 8 : Undo free list (undo) Opcode 9 : Redo free list head (called as part of undo) Opcode 9 : Format free list block (freelist group) Opcode 11 :Format new blocks in free list Opcode 12 :free list clear Opcode 13 :free list restore (back) (undo of opcode 12) Layer 14 : Transaction Extent - KCOCOTEX [kte.h] Opcode 1 : Add extent to segment Opcode 2 : Unlock Segment Header Opcode 3 : Extent DEaLlocation (DEL) Opcode 4 : Undo to Add extent operation (see opcode 1) Opcode 5 : Extent Incarnation number increment Opcode 6 : Lock segment Header Opcode 7 : Undo to rollback extent deallocation (see opcode 3) Opcode 8 : Apply Position Update (truncate) Opcode 9 : Link blocks to Freelist Opcode 10 :Unlink blocks from Freelist Opcode 11 :Undo to Apply Position Update (see opcode 8) Opcode 12 :Convert segment header to 6.2.x type Layer 15 : Table Space - KCOCOTTS [ktt.h] Opcode 1 : Format deferred rollback segment header Opcode 2 : Add deferred rollback record Opcode 3 : Move to next block Opcode 4 : Point to next deferred rollback record Layer 16 : Row Cache - KCOCOQRC Layer 17 : Recovery (REDO) - KCOCORCV [kcv.h] Opcode 1 : End Hot Backup : This operation clears the hot backup in-progress flags in the indicated list of files Opcode 2 : Enable Thread : This operation creates a redo record signalling that a thread has been enabled Opcode 3 : Crash Recovery Marker Opcode 4 : Resizeable datafiles Opcode 5 : Tablespace ONline Opcode 6 : Tablespace OFFline Opcode 7 : Tablespace ReaD Write Opcode 8 : Tablespace ReaD Only Opcode 9 : ADDing datafiles to database Opcode 10 : Tablespace DRoP Opcode 11 : Tablespace PitR Layer 18 : Hot Backup Log Blocks - KCOCOHLB [kcb.h] Opcode 1 : Log block image Opcode 2 : Recovery testing Layer 19 : Direct Loader Log Blocks - KCOCODLB [kcbl.h] Opcode 1 : Direct block logging Opcode 2 : Invalidate range Opcode 3 : Direct block relogging Opcode 4 : Invalidate range relogging Layer 20 : Compatibility Segment operations - KCOCOKCK [kck.h] Opcode 1 : Format compatibility segment - KCKFCS Opcode 2 : Update compatibility segment - KCKUCS Layer 21 : LOB segment operations - KCOCOLFS [kdl2.h] Opcode 1 : Write data into ILOB data block - KDLOPWRI Layer 22 : Tablespace bitmapped file operations - KCOCOTBF [ktfb.h] Opcode 1 : format space header - KTFBHFO Opcode 2 : space header generic redo - KTFBHREDO Opcode 3 : space header undo - KTFBHUNDO Opcode 4 : space bitmap block format - KTFBBFO Opcode 5 : bitmap block generic redo - KTFBBREDO Layer 23 : write behind logging of blocks - KCOCOLWR [kcbb.h] Opcode 1 : Dummy block written callback - KCBBLWR Layer 24 : Logminer related (DDL or OBJV# redo) - KCOCOKRV [krv.h] Opcode : common portion of the ddl - KRVDDL Opcode : direct load redo - KRVDLR Opcode : lob related info - KRVLOB Opcode : misc info - KRVMISC Opcode : user info - KRVUSER复制
2、INSERT 、UPDATE、DELETE与UNDO
我们以INSERT为举例说明
luocs@WWW> select * from khm;
ID DOMAIN
---------- ---------------------------------------- 1 www.luocs.com
2 www.luocs.com
查看当前窗口的SID
luocs@WWW> select distinct sid from v$mystat;
SID
---------- 159
luocs@WWW> @show_redo
NAME VALUE
-------------------- ----------redo size 0
luocs@WWW> @show_undo
no rows selected
sys@WWW> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------ 973967
luocs@WWW> insert into khm values(3,‘www.oracle.com’);
1 row created.
sys@WWW> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------ 973970
查找ID为3的行所在BLOCK
luocs@WWW> @showrowidEnter value for table_name: khm
old 7: from &table_name
new 7: from khm
Enter value for id: 3old 8: where id=&id
new 8: where id=3
ROWID OBJECT_ID FILE_ID BLOCK_ID NUM ROWIDTOCHAR(ROWID)------------------ ---------- ---------- ---------- ---------- --------------------AAANCKAAFAAAAAMAAC 53386 5 12 2 AAANCKAAFAAAAAMAAC
sys@WWW> alter system dump datafile 5 block 12;
System altered.
sys@WWW> oradebug setmypid
Statement processed.sys@WWW> oradebug tracefile_name
/u01/app/oracle/admin/L10GR205/udump/l10gr205_ora_4091.trc
Block header dump: 0x0140000c Object id on Block? Y
seg/obj: 0xd08a csc: 0x00.e7b25 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1400009 ver: 0x01 opc: 0 inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0001.014.000001ad 0x008053c5.01b7.17 ---- 1 fsc 0x0000.000000000x02 0x0008.024.000001a8 0x00800692.0219.37 C— 0 scn 0x0000.000e797b-- 首先我们看下Lck持有1的事务，其Itl为Ox01,再看Xid 0x0001.014.000001ad，这是该事物相关信息，其中我们可以得知usn=1,slot=14，sqn=429，计算方法如下：-- 求SLOT
sys@WWW> select to_number(‘14’,‘xxxxxx’) from dual;
TO_NUMBER(‘14’,‘XXXXXX’)------------------------ 20-- 求SQN
sys@WWW> select to_number(‘1ad’,‘xxxxxx’) from dual;
TO_NUMBER(‘1AD’,‘XXXXXX’)------------------------- 429
再看Uba 0x008053c5.01b7.17，Uba(undo block address)顾名思义就是指向该block的undo信息的位置，其构成如下：uba=DBA.seq#.rec#其中我们通过dba科获得undo的file和block，如下方式：sys@WWW> select dbms_utility.data_block_address_file(to_number(‘8053c5’,‘xxxxxxxxxxxx’))file#, 2 dbms_utility.data_block_address_block(to_number(‘8053c5’,‘xxxxxxxxxxxx’))block# 3 from dual;
FILE# BLOCK#---------- ---------- 2 21445
– 当然，我们可以通过脚本查看事物和UNDO相关信息：sys@WWW> @showtraEnter value for sid: 159old 13: WHERE SID=&SID
new 13: WHERE SID=159
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS
---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- 1 20 429 2 21445 439 23 ACTIVE
继续看dump文件block_row_dump:tab 0, row 0, @0x1f84tl: 20 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 02col 1: [13] 77 77 77 2e 6c 75 6f 63 73 2e 63 6f 6dtab 0, row 1, @0x1f32tl: 20 fb: --H-FL-- lb: 0x0 cc: 2col 0: [ 2] c1 03col 1: [13] 77 77 77 2e 6c 75 6f 63 73 2e 63 6f 6dtab 0, row 2, @0x1f08tl: 21 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 2] c1 04col 1: [14] 77 77 77 2e 6f 72 61 63 6c 65 2e 63 6f 6d-- 这里就是行相关信息了，简单转换看看是不是匹配：sys@WWW> select dump(‘www.luocs.com’,16) from dual;
DUMP(‘WWW.LUOCS.COM’,16)----------------------------------------------------------------------------------------------------------Typ=96 Len=13: 77,77,77,2e,6c,75,6f,63,73,2e,63,6f,6d
sys@WWW> select dump(‘www.oracle.com’,16) from dual;
DUMP(‘WWW.ORACLE.COM’,16)----------------------------------------------------------------------------------------------------------------Typ=96 Len=14: 77,77,77,2e,6f,72,61,63,6c,65,2e,63,6f,6d-- 毋庸置疑完全匹配另外c1 02、c1 03、c1 04对应的就是数字1、2、3了
当然，我们现在需要查看的是UNDO块里面的内容，dump undo块儿：sys@WWW> conn / as sysdba – 这里为什么切换？这是技巧，为的是产生新的trace文件，不然会追加到上一次dump的trace里，不便阅读Connected.sys@WWW> alter system dump datafile 2 block 21445;
System altered.
sys@WWW> oradebug setmypid
Statement processed.sys@WWW> oradebug tracefile_name
/u01/app/oracle/admin/L10GR205/udump/l10gr205_ora_4369.trc
********************************************************************************UNDO BLK:xid: 0x0001.014.000001ad seq: 0x1b7 cnt: 0x17 irb: 0x17 icl: 0x0 flg: 0x0000-- 这里看到事物ID了吧，这里主要看irb:0x17，即是Uba中的rec#，那我们直接跳到rec#查看：----------------------------- Rec #0x17 slt: 0x14 objn: 53386(0x0000d08a) objd: 53386 tblspc: 6(0x00000006)* Layer: 11 (Row) opc: 1 rci 0x00Undo type: Regular undo Begin trans Last buffer split: NoTemp Object: NoTablespace Undo: Nordba: 0x00000000*-----------------------------uba: 0x008053c5.01b7.15 ctl max scn: 0x0000.000e7987 prv tx scn: 0x0000.000e79batxn start scn: scn: 0x0000.000e7e31 logon user: 58 prev brb: 8402480 prev bcl: 0KDO undo record:KTB Redoop: 0x04 ver: 0x01op: L itl: xid: 0x0004.00b.000001af uba: 0x00802a4b.0197.02 flg: C— lkc: 0 scn: 0x0000.000e793aKDO Op code: DRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x0140000c hdba: 0x0140000bitli: 1 ispac: 0 maxfr: 4858tabn: 0 slot: 2(0x2)
从上面信息中可了解到：objn: 53386(0x0000d08a) -> rowid的 data_object_id
bdba: 0x0140000c -> rowid的file#, block#slot: 2(0x2) -> rowid的row_number
然后我们开始计算：sys@WWW> select dbms_utility.data_block_address_file(to_number(‘140000c’,‘xxxxxxxxxxxx’))file#,dbms_utility.data_block_address_block(to_number(‘140000c’,‘xxxxxxxxxxxx’))block# from dual;
FILE# BLOCK#---------- ---------- 5 12
这和上面获得ROWID信息一致：luocs@WWW> @showrowidEnter value for table_name: khm
old 7: from &table_name
new 7: from khm
Enter value for id: 3old 8: where id=&id
new 8: where id=3
ROWID OBJECT_ID FILE_ID BLOCK_ID NUM ROWIDTOCHAR(ROWID)------------------ ---------- ---------- ---------- ---------- --------------------AAANCKAAFAAAAAMAAC 53386 5 12 2 AAANCKAAFAAAAAMAAC
这时候UNDO里已经有INSERT相关内容了，那么看REDO有没有随之产生?sys@WWW> select lf.member, l.status from v$logfile lf, v$log l where lf.group# = l.group#;
MEMBER STATUS
------------------------------------------------------- --------------------------------/u01/app/oracle/oradata/L10GR205/redo03.log CURRENT
/u01/app/oracle/oradata/L10GR205/redo02.log INACTIVE
/u01/app/oracle/oradata/L10GR205/redo01.log INACTIVE
– 当前REDO为第三组。sys@WWW> alter system dump logfile ‘/u01/app/oracle/oradata/L10GR205/redo03.log’ scn min 973967 scn max 973970;
System altered.
sys@WWW> oradebug setmypid
Statement processed.sys@WWW> oradebug tracefile_name
/u01/app/oracle/admin/L10GR205/udump/l10gr205_ora_4430.trc
– 但我们阅读TRACE内容也找不出任何INSERT相关信息。
然后再提交之后再看REDO：luocs@WWW> commit;
Commit complete.
sys@WWW> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------ 953083
sys@WWW> conn / as sysdba
Connected.sys@WWW> alter system dump logfile ‘/u01/app/oracle/oradata/L10GR205/redo03.log’ scn min 973970 scn max 973974;
System altered.
sys@WWW> oradebug setmypid
Statement processed.sys@WWW> oradebug tracefile_name
/u01/app/oracle/admin/L10GR205/udump/l10gr205_ora_4442.trc
– 这下我们看到了插入的信息。-- 这里细心的朋友可能已经发现了，为什么xid和uba都与上面不一致，这是我需要解释的问题，因为我做实验是早上在家里做的，做着做着发现得上班，因此我就随带笔记本到公司，但这时候我只能创建一个新事务来重演过程，因此实验上不会出差的。REDO RECORD - Thread:1 RBA: 0x000025.000000db.0010 LEN: 0x01f0 VLD: 0x0d-- RBA: 0x000025.000000db.0010 转换成十进制是37.219.16，LOG的序列号就是37，和下面的当前日志文件SEQENCE一致：sys@WWW> select lf.member, l.status, l.sequence# from v$logfile lf, v$log l where lf.group# = l.group#;
MEMBER STATUS SEQUENCE#------------------------------------------------------- -------------------------------- ----------/u01/app/oracle/oradata/L10GR205/redo03.log CURRENT 37/u01/app/oracle/oradata/L10GR205/redo02.log INACTIVE 36/u01/app/oracle/oradata/L10GR205/redo01.log INACTIVE 35-- 然后在redo日志中的块号是219,16B则是起始字节。-- 另外LEN: 0x01f0是这条记录的长度，十进制大小为240B。
SCN: 0x0000.000edc95 SUBSCN: 1 03/21/2013 16:38:20CHANGE #1 TYP:2 CLS: 1 AFN:5 DBA:0x0140000c OBJ:53386 SCN:0x0000.000edc7e SEQ: 2 OP:11.2-- 这里是我们插入的一条记录(OP:11.2 : Insert Row Piece)KTB Redoop: 0x01 ver: 0x01op: F xid: 0x0005.00a.000001b8 uba: 0x00803230.01e0.39KDO Op code: IRP row dependencies Disabled-- 行插入操作 xtype: XA flags: 0x00000000 bdba: 0x0140000c hdba: 0x0140000bitli: 1 ispac: 0 maxfr: 4858tabn: 0 slot: 3(0x3) size/delt: 21fb: --H-FL-- lb: 0x1 cc: 2null: --col 0: [ 2] c1 04col 1: [14] 77 77 77 2e 6f 72 61 63 6c 65 2e 63 6f 6d-- 我们插入的行内容CHANGE #2 TYP:0 CLS:25 AFN:2 DBA:0x00800049 OBJ:4294967295 SCN:0x0000.000edc65 SEQ: 2 OP:5.2-- 更新回滚段头信息 Update rollback segment header - KTURDH (OP:5.2)
ktudh redo: slt: 0x000a sqn: 0x000001b8 flg: 0x0012 siz: 132 fbi: 0 uba: 0x00803230.01e0.39 pxid: 0x0000.000.00000000CHANGE #3 TYP:0 CLS:25 AFN:2 DBA:0x00800049 OBJ:4294967295 SCN:0x0000.000edc95 SEQ: 1 OP:5.4-- 提交事务 Commit，这是COMMIT产生的 transaction (transaction table update) - no undo record
ktucm redo: slt: 0x000a sqn: 0x000001b8 srt: 0 sta: 9 flg: 0x0(OP:5.4)CHANGE #4 TYP:0 CLS:26 AFN:2 DBA:0x00803230 OBJ:4294967295 SCN:0x0000.000edc65 SEQ: 1 OP:5.1-- Undo block or undo segment header - KTURDB(OP:5.1)ktudb redo: siz: 132 spc: 422 flg: 0x0012 seq: 0x01e0 rec: 0x39 xid: 0x0005.00a.000001b8ktubl redo: slt: 10 rci: 0 opc: 11.1 objn: 53386 objd: 53386 tsn: 6-- 我们对表khm插入一条数据所生成的UNDO
Undo type: Regular undo Begin trans Last buffer split: NoTemp Object: NoTablespace Undo: No 0x00000000 prev ctl uba: 0x00803230.01e0.38prev ctl max cmt scn: 0x0000.000e86c2 prev tx cmt scn: 0x0000.000e86dbtxn start scn: 0x0000.00000000 logon user: 58 prev brb: 8401457 prev bcl: 0 KDO undo record:KTB Redoop: 0x04 ver: 0x01-- 对ITL的修改op: L itl: xid: 0x0008.000.000001ac uba: 0x008006a5.0219.07 flg: C— lkc: 0 scn: 0x0000.000e8af7KDO Op code: DRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x0140000c hdba: 0x0140000bitli: 1 ispac: 0 maxfr: 4858tabn: 0 slot: 3(0x3)
从上面我们可以很明确的看到直到commit了之后日志才写入到磁盘，这个在DML所有DML操作上是一样的。
这里简单叙述下commit的机制：
• 在commit之前，DML已经在SGA中产生了UNDO块儿
• 在commit之前，已经在SGA中产生了已修改的数据块儿
• 在commit之前，redo log buffer中已经产生了上面两项的redo
• 根据lgwr将redo从redo log buffer写到磁盘的机制，比如工作时间，有可能将部分redo数据写入磁盘
• 已经获得了所需全部锁
• 在commit当时，为事务生成一个SCN
• lgwr将剩余的redo写入磁盘，并且把SCN标记到current redo log file里。
• 到这里事物提交结束，该事物从v$transaction里消失
• 释放锁
从以上内容可见，commit时需要进行的工作内容其实非常少，这也就是为什么一个大事物和小事物commit时间基本相同快的原因。
至于insert、update、delete操作与UNDO相关的实验我已经做过，并且发了篇文章《Oracle UPDATE、DELETE、INSERT与Undo(UPDATE、DELETE如何从Undo里找到前镜像)》，请阅读。
其中insert、update、delete操作与UNDO记录的关系是：
1）update : 在Undo中记录被更新列的前镜像和被更新行ROWID；
2）delete : 在Undo中记录被删除行所有列的前镜像和其ROWID；
3）insert : 在Undo中记录插入行的ROWID。
备注：上面我做实验中，如果你是在有很多事务发生的环境上进行实验，那是非常难受的，我们这里主要是为了底层理解redo与undo的工作，需要做的是尽可能让实验简易，好容易理解。就像我采集SCN一样，我在insert和commit操作前后迅速获取SCN号，这样至少其他操作不会影响我分析dump出的trace内容。
3、比较INSERT,DELETE和UPDATE各自产生的UNDO和REDO的大小
这边我利用TOM编程艺术里的脚本来计算redo和undo大小
create or replace function get_stat_val( p_name in varchar2 ) return number
as l_val number;begin select b.value
into l_val
from v$statname a, v$mystat b
where a.statistic# = b.statistic# and a.name = p_name; return l_val;end;/
查看REDO与UNDO大小方式：首先进行DML语句，不进行commit，查看产生的UNDO大小，然后commit，再查看产生的REDO大小。
另外，ROLLBACK操作也会产生相应的REDO。
1）INSERT
luocs@WWW> variable redo number
luocs@WWW> variable undo number
luocs@WWW> exec :redo := get_stat_val(‘redo size’);
PL/SQL procedure successfully completed.
luocs@WWW> exec :undo := get_stat_val(‘undo change vector size’);
PL/SQL procedure successfully completed.
luocs@WWW> insert into khm values (4,‘www.enmoedu.com’);
1 row created.
luocs@WWW> exec dbms_output.put_line((get_stat_val(‘undo change vector size’) - :undo) || ‘bytes of undo generated!’);132bytes of undo generated!
PL/SQL procedure successfully completed.
luocs@WWW> commit;
Commit complete.
luocs@WWW> exec dbms_output.put_line((get_stat_val(‘redo size’) - :redo) || ‘bytes of redo generated!’);512bytes of redo generated!
PL/SQL procedure successfully completed.
2）UPDATE
luocs@WWW> exec :redo := get_stat_val(‘redo size’);
PL/SQL procedure successfully completed.
luocs@WWW> exec :undo := get_stat_val(‘undo change vector size’);
PL/SQL procedure successfully completed.
luocs@WWW> update khm set domain = ‘www.acoug.com’ where id = 4;
1 row updated.
luocs@WWW> exec dbms_output.put_line((get_stat_val(‘undo change vector size’) - :undo) || ‘bytes of undo generated!’);168bytes of undo generated!
PL/SQL procedure successfully completed.
luocs@WWW> commit;
Commit complete.
luocs@WWW> exec dbms_output.put_line((get_stat_val(‘redo size’) - :redo) || ‘bytes of redo generated!’);576bytes of redo generated!
PL/SQL procedure successfully completed.
4）DELETE
luocs@WWW> exec :redo := get_stat_val(‘redo size’);
PL/SQL procedure successfully completed.
luocs@WWW> exec :undo := get_stat_val(‘undo change vector size’);
PL/SQL procedure successfully completed.
luocs@WWW> delete khm where id = 4;
1 row deleted.
luocs@WWW> exec dbms_output.put_line((get_stat_val(‘undo change vector size’) - :undo) || ‘bytes of undo generated!’);188bytes of undo generated!
PL/SQL procedure successfully completed.
luocs@WWW> commit;
Commit complete.
luocs@WWW> exec dbms_output.put_line((get_stat_val(‘redo size’) - :redo) || ‘bytes of redo generated!’);552bytes of redo generated!
PL/SQL procedure successfully completed.
INSERT UPDATE DELETE复制
UNDO 132 168 188
REDO 512 576 552
在这里，我想注重的内容应该不是INSERT、DELETE、UPDATE之间产生UNDO、REDO大小的比较，而是需要了解，为什么它们之间产生的UNDO、REDO有差异。
首先，我们在上面已经总结过：
1）update : 在Undo中记录被更新列的前镜像和被更新行ROWID；
2）delete : 在Undo中记录被删除行所有列的前镜像和其ROWID；
3）insert : 在Undo中记录插入行的ROWID。
可见，一般情况下，UNDO(INSERT) < UNDO(UPDATE) < UNDO(DELETE)
另外，根据上面实验可见，REDO(INSERT) < REDO(DELETE) < REDO(UPDATE)
但是，这不是绝对，在某种情况有可能会发生顺序颠倒的时候。
4、DDL操作与UNDO和REDO
DDL操作会不会产生UNDO或者产生REDO呢？我们来验证一下，方法和DML验证方法一样。
luocs@WWW> exec :redo := get_stat_val(‘redo size’);
PL/SQL procedure successfully completed.
luocs@WWW> create table test as select * from user_objects;
Table created.
luocs@WWW> exec dbms_output.put_line((get_stat_val(‘redo size’) - :redo) || ‘bytes of redo generated!’);20760bytes of redo generated!
luocs@WWW> variable undo number
luocs@WWW> exec :undo := get_stat_val(‘undo change vector size’);
PL/SQL procedure successfully completed.
luocs@WWW> create table test as select * from user_objects;
Table created.
luocs@WWW> exec dbms_output.put_line((get_stat_val(‘undo change vector size’) - :undo) || ‘bytes of undo generated!’);5904bytes of undo generated!
PL/SQL procedure successfully completed.
从结果中可以看出，DDL操作即产生REDO也产生UNDO。
有些人可能会奇怪DDL怎么产生了UNDO？DDL不是不能ROLLBACK么？
这里我们需要查看DDL语句执行过程。我通过10046trace来查看：
luocs@WWW> alter session set events ‘10046 trace name context forever, level 1’;
Session altered.
luocs@WWW> create table test as select * from user_objects;
Table created.
luocs@WWW> alter session set events ‘10046 trace name context off’;
Session altered.
column trace new_val T
select c.value || ‘/’ || d.instance_name || ‘ora’ || a.spid || ‘.trc’ || case when e.value is not null then ‘_’||e.value end trace
from v$process a, v$session b, v$parameter c, v$instance d, v$parameter e
where a.addr = b.paddr
and b.audsid = userenv(‘sessionid’) and c.name = ‘user_dump_dest’ and e.name = ‘tracefile_identifier’/
TRACE
----------------------------------------------------------------------------------------------------/u01/app/oracle/admin/L10GR205/udump/L10GR205_ora_4066.trc
– 注意，我们在查看的时候是/u01/app/oracle/admin/L10GR205/udump/l10gr205_ora_4066.trc，是小写的，否则找不到文件。[root@khm22 ~]# egrep “insert|update|delete” /u01/app/oracle/admin/L10GR205/udump/l10gr205_ora_4066.trcinsert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16, :17)insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,bitmapranges,hwmincr, spare1, scanhint) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,0,:16,DECODE(:17,0,NULL,:17),:18)update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv2=:7,priv3=:8 where ts#=:1 and user#=:2insert into tab$(obj#,ts#,file#,block#,bobj#,tab#,intcols,kernelcols,clucols,audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime,samplesize,cols,property,degree,instances,dataobj#,avgspc_flb,flbcnt,trigflag,spare1,spare6)values(:1,:2,:3,:4,decode(:5,0,null,:5),decode(:6,0,null,:6),:7,:8,decode(:9,0,null,:9),:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,decode(:26,1,null,:26),decode(:27,1,null,:27),:28,:29,:30,:31,:32,:33)insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/DTYIYM/,:7,183/DTYIDS/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/MAXSB1MINAL/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/DTYIYM/,:7,183/DTYIDS/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/MAXSB1MINAL/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/DTYIYM/,:7,183/DTYIDS/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/MAXSB1MINAL/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/DTYIYM/,:7,183/DTYIDS/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/MAXSB1MINAL/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/DTYIYM/,:7,183/DTYIDS/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/MAXSB1MINAL/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/DTYIYM/,:7,183/DTYIDS/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/MAXSB1MINAL/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/DTYIYM/,:7,183/DTYIDS/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/MAXSB1MINAL/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/DTYIYM/,:7,183/DTYIDS/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/MAXSB1MINAL/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/DTYIYM/,:7,183/DTYIDS/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/MAXSB1MINAL/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/DTYIYM/,:7,183/DTYIDS/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/MAXSB1MINAL/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/DTYIYM/,:7,183/DTYIDS/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/MAXSB1MINAL/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/DTYIYM/,:7,183/DTYIDS/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/MAXSB1MINAL/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3-- 可见DDL语句递归做了很多DML操作，这些都将产生REDO和UNDO。
Maclean.Liu把DDL操作分解如下：
begin
commit;
– 编译ddl
begin
– 实现ddl,包括一系列递归的数据字典维护操作及其他操作
commit;
exception
when others than
rollback;
end;
end;
详细请阅读：《ddl操作是否会产生undo?》
5、SELECT与REDO和UNDO
可能很多人会说SELECT即不产生REDO也不产生UNDO，我也做了如下测试：
luocs@WWW> exec :redo := get_stat_val(‘redo size’);
PL/SQL procedure successfully completed.
luocs@WWW> exec :undo := get_stat_val(‘undo change vector size’);
PL/SQL procedure successfully completed.
luocs@WWW> select * from khm;
ID DOMAIN
---------- ---------------------------------------- 1 www.luocs.com
2 www.luocs.com
5 www.enmoedu.com
3 www.oracle.com
luocs@WWW> exec dbms_output.put_line((get_stat_val(‘redo size’) - :redo) || ‘bytes of redo generated!’);0bytes of redo generated!
PL/SQL procedure successfully completed.
luocs@WWW> exec dbms_output.put_line((get_stat_val(‘undo change vector size’) - :undo) || ‘bytes of undo generated!’);0bytes of undo generated!
PL/SQL procedure successfully completed.
– 可见SELECT确实没有生成REDO和UNDO
那我们能断定SELECT不能生成REDO和UNDO吗？
asktom上有这样一个话题，tom说：
I think you mean “redo”, not UNDO in general.
A select for update will generate both redo and undo.
A read only select will USE undo (for read consistency)
A read only select may GENERATE redo due to block cleanouts.
原文地址：http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:44798632736844
因此，根据怎样的SELECT，也可能会产生REDO和UNDO。
我们照tom给的示例进行实验：
创建hm表并设置表统计信息luocs@WWW> create table hm (id number primary key, x char(2000), y char(2000), z char(2000));
Table created.
luocs@WWW> exec dbms_stats.set_table_stats(user,‘hm’,numrows=>10000,numblks=>10000);
PL/SQL procedure successfully completed.
下面语句将报错，这是正常的，因为hm表目前是空表，作此步骤只是为了缓存这条PL/SQL和SQL。luocs@WWW> declare
2 l_rec hm%rowtype; 3 begin 4 for i in 1 … 10000 5 loop
6 select * into l_rec from hm where id = i; 7 end loop; 8 end; 9 /declare
*ERROR at line 1:ORA-01403: no data found
ORA-06512: at line 6
开始插入数据，并提交：luocs@WWW> insert into hm select rownum, ‘x’ , ‘y’ , ‘z’ from all_objects where rownum <= 10000;
10000 rows created.
luocs@WWW> commit;
Commit complete.
下面开始检查：luocs@WWW> variable redo number
luocs@WWW> variable undo number
luocs@WWW> exec :redo := get_stat_val(‘redo size’);
PL/SQL procedure successfully completed.
luocs@WWW> declare
2 l_rec hm%rowtype; 3 begin 4 for i in 1 … 10000 5 loop
6 select * into l_rec from hm where id = i; 7 end loop; 8 end; 9 /
PL/SQL procedure successfully completed.
luocs@WWW> exec dbms_output.put_line((get_stat_val(‘redo size’) - :redo) || ‘bytes of redo generated!’);616220bytes of redo generated!
PL/SQL procedure successfully completed.
– 可见SELECT也生成了redo，产生redo是因为块清除操作所导致的。具体请看TOM的回答。-- 块清除完，SELECT操作就不产生REDO了。如下：luocs@WWW> exec :redo := get_stat_val(‘redo size’);
PL/SQL procedure successfully completed.
luocs@WWW> /
PL/SQL procedure successfully completed.
luocs@WWW> exec dbms_output.put_line((get_stat_val(‘redo size’) - :redo) || ‘bytes of redo generated!’);0bytes of redo generated!
PL/SQL procedure successfully completed.
OK，再次说下，REDO和UNDO是Oracle里非常重要的特性，其内容不可能仅一篇文章来说得清除，希望各位通过其他途径更深入研究。
