分类： Oracle
2012-04-01 11:13:06
今天我们要了解一些REDO LOG底层的概念，只有将这几个概念搞明白了，我们才能更加深入的了解REDO LOG，以及了解REDO LOG相关的管理和优化的要点。首先我们要了解的就是变化矢量（CHANGE VECTOR，CV），变化矢量是组成REDO信息的基础，一个变化矢量描述了对一个独立的数据块的一个独立的修改操作。这里面我们要注意的是，对于CV的定义里包含了两层含义，一个CV只针对一个数据块的变更，一个CV只包含一个变化。每个CV都包含了对文件的修改，因此在每个CV中都有一个OPCODE，指出修改的类型。不同OPCODE的CV，其组成是不同的，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
CV是组成REDO RECORD的基础，REDO RECORD是由一组CV组成的，这组CV完成对数据库的一个原子修改操作。举个例子，一个REDO RECORD里可能包含3个CV，第一个是对UNDO SEGMENT HEADER的修改，第二个是对UNDO SEGMENT的修改，第三个是对DATA BLOCK的修改。而一个事务可能包含N个REDO RECORD。
当前台进程要对某个数据块进行修改的时候，首先要形成相关的CHANGE VECTOR，然后把多个CV组成REDO RECORD，把REDO RECORD写入LOG BUFFER后，前台进程可以将CV提交到相关的数据块上。
下面我们通过一个实验来学习一下REDO RECORD和CHANGE VECTOR。我们设计的场景是，首先在SCOTT下创建一张表：SCOTT.T4：
create table t4 (a integer);
然后我们查看一下当前的SCN是什么：
SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;
MAX(KTUXESCNW*POWER(2,32)+KTUXESCNB)
------------------------------------
16230857
执行一条INSERT语句，然后以这条语句进行分析：
insert into t4 values (1);
commit;
SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;
MAX(KTUXESCNW*POWER(2,32)+KTUXESCNB)
------------------------------------
16230863
通过这两个SCN值，我们对当前的REDO LOG进行DUMP：
SQL> alter system dump logfile 'd:\oracle\oradata\ora92\redo01.log' scn min 16230857 scn max 16230863;
系统已更改。
下面的内容就是我们刚才的INSERT语句产生的，下面是第一个REDO RECORD：
REDO RECORD - Thread:1 RBA: 0x0000a1.000040ce.0010 LEN: 0x0054 VLD: 0x01
SCN: 0x0000.00f7a9c9 SUBSCN: 1 03/12/2008 09:37:49
CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800111 SCN:0x0000.00f7a9c7 SEQ: 1 OP:5.4
可以看到这个REDO RECORD的RBA是0x0000a1.000040ce.0010，翻译成10进制是161.64.16，LOG SEQUENCE号是161，在REDO LOG中的块号是64，起始字节是块内的16字节。这个REDO RECORD的长度是84字节（0X54）。
VLD:0X01表示这个REDO RECORD的类型，0X01表示CHANGE VECTOR COPIED IN。再往下看CHANGE #1,这就是这个REDO RECORD的第一个CV，我们看到OP:5.4，根据上面的OPCODE清单我们可以看到是Commit transaction (transaction table update) ，修改事务表，RDBA是2/273通过DBA_EXTENTS查询为_SYSSMU3$。
继续看下一个REDO RECORD:
REDO RECORD - Thread:1 RBA: 0x0000a1.000040cf.0010 LEN: 0x0058 VLD: 0x02
SCN: 0x0000.00f7a9cb SUBSCN: 1 03/12/2008 09:37:57
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:23.1
Block Written - afn: 2 rdba: 0x008095f1(2,38385) ----undo segment
scn: 0x0000.00f79cf1 seq: 0x02 flg:0x04
Block Written - afn: 2 rdba: 0x0080726b(2,29291) ------undo segment
scn: 0x0000.00f7a1e1 seq: 0x02 flg:0x04 ------undo segment
Block Written - afn: 2 rdba: 0x00806e2e(2,28206) ------undo segment
scn: 0x0000.00f79d50 seq: 0x02 flg:0x04
这个REDO RECORD的VLD是2，含义是A commit SCN was allocated and is stored。说明这个REDO RECORD里分配并存储了一个SCN。我们来看第一个CHANGE：
OP:23.1，OPCODE是23.1，Dummy block written callback - KCBBLWR，是产生一些WRITE BEHIND LOGGING信息。下一个REDO RECORD开始是针对T4表的操作。
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d0.0010 LEN: 0x00b8 VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN: 1 03/12/2008 09:38:03
CHANGE #1 TYP:0 CLS: 4 AFN:5 DBA:0x01401a63 SCN:0x0000.00f7a965 SEQ: 3 OP:13.28
Redo on Level1 Bitmap Block ---针对1ST BMB的操作
Redo to add range
bdba: Length: 16
CHANGE #2 TYP:0 CLS: 8 AFN:5 DBA:0x01401a61 SCN:0x0000.00f7a965 SEQ: 2 OP:13.22 ----dba (5/6753) -- scott.t4的segment header，设置高水位
Redo on Level1 Bitmap Block
Redo to set hwm
Opcode: 32 Highwater:: 0x01401a71 ext#: 0 blk#: 16 ext size: 16
#blocks in seg. hdr's freelists: 0
#blocks below: 13
mapblk 0x00000000 offset: 0
这个REDO RECORD是对SCOTT.T4的表头的操作。下面连续几个REDO RECORD是格式化数据块，为了简化起见，我只列出其中的一个：
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d0.00c8 LEN: 0x003c VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN: 1 03/12/2008 09:38:03
CHANGE #1 TYP:1 CLS: 1 AFN:5 DBA:0x01401a64 SCN:0x0000.00f7a9cd SEQ: 1 OP:13.21 --dba(5/6756) --scott.t4，格式化BLOCK
ktspbfredo - Format Pagetable Datablock
Parent(l1) DBA: typ: 1 objd: 32027 itls: 2 fmt_flag: 0 poff: 0
中间我省略了几个REDO RECORD，直接看看包含INSERT语句的REDO RECORD：
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d2.00b0 LEN: 0x015c VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN: 1 03/12/2008 09:38:03
CHANGE #1 TYP:0 CLS:23 AFN:2 DBA:0x00800071 SCN:0x0000.00f7a38b SEQ: 1 OP:5.2 ----Update rollback segment header - KTURDH SYS_SYSSMU2$
ktudh redo: slt: 0x000f sqn: 0x00004947 flg: 0x0012 siz: 80 fbi: 0
uba: 0x008090cb.0550.13 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:24 AFN:2 DBA:0x008090cb SCN:0x0000.00f7a38a SEQ: 3 OP:5.1 ---Undo block or undo segment header - KTURDB SYS_SYSSMU4$
ktudb redo: siz: 80 spc: 2746 flg: 0x0012 seq: 0x0550 rec: 0x13
xid: 0x0004.00f.00004947
ktubl redo: slt: 15 rci: 0 opc: 11.1 objn: 32027 objd: 32027 tsn: 5 ----Interpret Undo Record (Undo) ，针对scott.t4表生成UNDO数据
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x008090cb.0550.10
prev ctl max cmt scn: 0x0000.00f78d7e prev tx cmt scn: 0x0000.00f78f06
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z ------Undo of first (ever) change to the ITL，首先是对ITL的修改
KDO Op code: DRP row dependencies Disabled ------Delete Row Piece
xtype: XA bdba: 0x01401a65 hdba: 0x01401a63 -----ROWID
itli: 1 ispac: 0 maxfr: 2401
tabn: 0 slot: 0(0x0)
CHANGE #3 TYP:0 CLS: 1 AFN:5 DBA:0x01401a65 SCN:0x0000.00f7a9cd SEQ: 2 OP:11.2 ---Insert Row Piece ，插入一条记录
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x0004.00f.00004947 uba: 0x008090cb.0550.13 ---First change to ITL by this TX. Copy redo to ITL
KDO Op code: IRP row dependencies Disabled ---Single Insert Row Piece，行插入操作
xtype: XA bdba: 0x01401a65 hdba: 0x01401a63 ---对应的表是scott.t4
itli: 1 ispac: 0 maxfr: 2401
tabn: 0 slot: 0(0x0) size/delt: 6
fb: --H-FL-- lb: 0x1 cc: 1
null: -
col 0: [ 2] c1 02 ----十进制1，就是我们插入的数据
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20 ----Transaction continue audit log record，记录SESSION信息
session number = 11
serial number = 115
transaction name =
这个事务的最后一个REDO RECORD就是COMMIT产生的记录：
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d4.0010 LEN: 0x0054 VLD: 0x01
SCN: 0x0000.00f7a9cf SUBSCN: 1 03/12/2008 09:38:03
CHANGE #1 TYP:0 CLS:23 AFN:2 DBA:0x00800071 SCN:0x0000.00f7a9cd SEQ: 1 OP:5.4 ----Commit transaction (transaction table update)
ktucm redo: slt: 0x000f sqn: 0x00004947 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x008090cb.0550.13 ext: 2 spc: 2664 fbi: 0
