继ORACLE 11Gr2下redo里的轨迹之UPDATE(一)未完成的东西进行学习和分析
chang vector中涉及到很多事务的信息记录，这里会跳过
在分析过程中发现从change vector 1到change vector 4其中SCN记录递减的，按照日志顺序写的法则，应该是递增.

1、redo header信息：
REDO RECORD – Thread:1 RBA: 0x0000d5.00000003.0010 LEN: 0x024c VLD: 0x05
SCN: 0x0000.0021f662 SUBSCN: 1 12/23/2015 17:04:28
(LWN RBA: 0x0000d5.00000003.0010 LEN: 0002 NST: 0001 SCN: 0x0000.0021f661)

REDO RECORD – Thread:1 RBA: 0x0000d5.00000003.0010 LEN: 0x024c VLD: 0x05
说明了是线程1，seq号为213，3号块的16bytes处；
这条轨迹很重要，是寻找redo entires的地址

SQL> select l.thread#,l.sequence#,l.members,l.status,f.member
2 from v$log l,v$logfile f
3 where f.group#=l.group#;

THREAD# SEQUENCE# MEMBERS STATUS MEMBER
———- ———- ———- —————- ————————————————————
1 213 1 CURRENT /oradata/TRSEN/onlinelog/o1_mf_3_c6wp4qm5_.log
LEN:是针对此条redo entires的长度而非change vector的长度，转化成10进制单位 bytes，这里的长度是怎么计算的，估计只有oracle工程师清楚
VLD:REDO ENTRIES HEADER的大小
SCN:System Change Number at this RBA.
SUBSCN:Sequence at this SCN

SCN: 0x0000.0021f662 SUBSCN: 1 12/23/2015 17:04:28
时间记录这个scn+seq一般对应着数据块头的scn+seq，这个信息在恢复时很重要，因为会去找entries记录，就这相当于时间和地点
从实际中验证可能分如下情况：
1）、update数据后commit时，data block scn+seq是和commit的redo header相同：
update时操作redo header信息
EDO RECORD – Thread:1 RBA: 0x0000eb.00000002.0010 LEN: 0x0284 VLD: 0x05
SCN: 0x0000.0022fb4d SUBSCN: 1 12/24/2015 14:31:11==================》》》》》
(LWN RBA: 0x0000eb.00000002.0010 LEN: 0002 NST: 0001 SCN: 0x0000.0022fb4c)
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.0022fb3a SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x000b sqn: 0x00002915 flg: 0x0012 siz: 248 fbi: 0

commit时redo header信息
REDO RECORD – Thread:1 RBA: 0x0000eb.00000003.00a4 LEN: 0x00a4 VLD: 0x01
SCN: 0x0000.0022fb4e SUBSCN: 1 12/24/2015 14:31:11
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.0022fb4d SEQ:1 OP:5.4 ENC:0 RBL:0=====》》》》
ktucm redo: slt: 0x000b sqn: 0x00002915 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c003e4.04e8.07 ext: 2 spc: 7324 fbi: 0
CHANGE #2 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:24.4 ENC:0

disk data block信息
Block dump from disk:
buffer tsn: 4 rdba: 0x01000083 (4/131)
scn: 0x0000.0022fb4e seq: 0x01 flg: 0x06 tail: 0xfb4e0601======》》》》》
frmt: 0x02 chkval: 0xfab0 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00002AD0D829BA00 to 0x00002AD0D829DA00

2）、update数据后，db直接发生了checkpoint or dbwr写脏数据，data block scn+seq是和update操作轨迹的redo header相同：
update时操作redo header信息
REDO RECORD – Thread:1 RBA: 0x0000ef.00000002.0010 LEN: 0x027c VLD: 0x05
SCN: 0x0000.0022fee5 SUBSCN: 1 12/24/2015 14:54:18
(LWN RBA: 0x0000ef.00000002.0010 LEN: 0002 NST: 0001 SCN: 0x0000.0022fee4)
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.0022fec8 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0005 sqn: 0x00002914 flg: 0x0012 siz: 232 fbi: 0
uba: 0x00c00180.04e9.07 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:36 AFN:3 DBA:0x00c00180 OBJ:4294967295 SCN:0x0000.0022fec7 SEQ:3 OP:5.1 ENC:0 RBL:0
…省略….

disk data block信息
Block dump from disk:
buffer tsn: 4 rdba: 0x01000083 (4/131)
scn: 0x0000.0022fee5 seq: 0x01 flg: 0x04 tail: 0xfee50601===============》》》》
frmt: 0x02 chkval: 0xa1d4 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00002B7C5AB95A00 to 0x00002B7C5AB97A00

change vector 1信息:
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.0021f64a SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0010 sqn: 0x000027df flg: 0x0012 siz: 204 fbi: 0
uba: 0x00c03a27.04ab.04 pxid: 0x0000.000.00000000

TYP: Type of the block. 0-Normal, 1-New block 2-Delayed logging etc
CLS: Class of the block. This field is same as x$bh.class column except for undo blocks/undo header.
AFN:绝对文件编号
DBA:相对数据块地址，转化成10进制为12583184
对于AFN和DBA可利用如下信息进行校验
SQL> select name from v$datafile where file#=3;
NAME
——————————————————————————–
/oradata/TRSENDB/datafile/o1_mf_undotbs1_c6ntyh03_.dbf

SQL> select dbms_utility.data_block_address_file(12583184) rfile,
2 dbms_utility.data_block_address_block(12583184) block
3 from dual;
RFILE BLOCK
———- ———-
3 272

SQL> select owner,segment_name from dba_segments where header_file=3 and header_block=272 and relative_fno=3;
OWNER SEGMENT_NAME
———————– ———————————-
SYS _SYSSMU10_817032100$
OP:5.2：Update rollback segment header即首先获取一个undo segment header

change vector 2信息:
CHANGE #2 TYP:0 CLS:36 AFN:3 DBA:0x00c03a27 OBJ:4294967295 SCN:0x0000.0021f649 SEQ:4 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 204 spc: 7312 flg: 0x0012 seq: 0x04ab rec: 0x04
xid: 0x000a.010.000027df
ktubl redo: slt: 16 rci: 0 opc: 11.1 [objn: 73316 objd: 73316 tsn: 4]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c03a27.04ab.01
prev ctl max cmt scn: 0x0000.0021f59c prev tx cmt scn: 0x0000.0021f5a8
txn start scn: 0xffff.ffffffff logon user: 49 prev brb: 12597782 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000083 hdba: 0x01000082
itli: 3 ispac: 0 maxfr: 4858
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 0
ncol: 4 nnew: 1 size: 0
col 0: [ 1] 54==================》》》》》》》
记录对3号文件14887块进行修改，此块为undo块，即为在undo块里创建需要修改块的数据
查看修改前16进制信息如下：
SQL> select dump(‘T’,16) from dual;

DUMP(‘T’,16)
—————-
Typ=96 Len=1: 54

change vector 3信息:
CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x01000083 OBJ:73316 SCN:0x0000.0021f5f4 SEQ:1 OP:11.5 ENC:0 RBL:0============》》》这SCN对应了data blockSCN
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x000a.010.000027df uba: 0x00c03a27.04ab.04
Block cleanout record, scn: 0x0000.0021f662 ver: 0x01 opt: 0x02, entries follow…
itli: 2 flg: 2 scn: 0x0000.0021f5f4
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000083 hdba: 0x01000082
itli: 3 ispac: 0 maxfr: 4858
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 3 ckix: 0
ncol: 4 nnew: 1 size: 0
col 0: [ 1] 41===========》》》》》
查看修改后16进制信息如下：
SQL> select dump(‘A’,16) from dual;
DUMP(‘A’,16)
—————-
Typ=96 Len=1: 41

change vector 4信息:
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0
session number = 148
serial number = 1817
transaction name =
version 186647552
audit sessionid 90710
Client Id =
login username = TRSEN
上面信息反映了Transaction continue audit log record，从OP操作码可清楚了解到

经11gr2上测试部分opcode,其所对应的操作确实如说明所说
redo dump ‘s opcode reference
==========================================================================================
opcode 2:Transaction Read
opcode 3:Transaction Update
opcode 4:Transaction Block
—-subopcode 1:Block cleanout
—-subopcode 2:physical cleanout
—-subopcode 3:single array change
—-subopcode 4:Multiple changes to an array
—-subopcode 5:format block
—-subopcode 6:commit-time block cleanout
—-subopcode 7:ITL cleanout callback
—-subopcode 8:Transaction Block Redo Block Commit Cleanout
opcode 5:Transaction Undo
—-subopcode 1:Undo block or undo segment header
—-subopcode 2:Update rollback segment header
—-subopcode 3:Rollout a transaction begin
—-subopcode 4:Commit transaction
—-subopcode 5:Create rollback segment
—-subopcode 6:Rollback record index in an undo block
—-subopcode 7:Begin transaction
—-subopcode 8:Mark transaction as dead
—-subopcode 9:Undo routine to rollback the extend of a rollback segment
—-subopcode 10:Redo to perform the rollback of extend of rollback segment to the segment header
—-subopcode 11:Rollback DBA in transaction table entry
—-subopcode 12:Change transaction state
—-subopcode 13:Convert rollback segment forma
—-subopcode 14:Change extent allocation parameters in a rollback segment
—-subopcode 15:Undo Redo ConverT transaction table
—-subopcode 16:KTU Redo for ConverT to Unlimited extents format
—-subopcode 17:KTU Redo Convert for extent Move in extent map in unlimited format to segment header
—-subopcode 18:Transaction Undo segment Redo set Parent Xid
—-subopcode 19:Transaction start audit log record
—-subopcode 20:Transaction continue audit log record
—-subopcode 21:Transaction Control Redo ConverT undo seg
—-subopcode 22:Transaction Redo PHysical Changes
—-subopcode 23:Disable Block opcode Recovery
—-subopcode 24:Kernel Transaction Undo Relog CHanGe
—-subopcode 25:Join sub Transaction
—-subopcode 26:Undo STopper undo callback
—-subopcode 27:Transaction Control System Managed us Format
—-subopcode 28:Undo Need To Propagate
—-subopcode 29:big undo
—-subopcode 30:change ondisk state for a distributed transaction
—-subopcode 31:Flashback Archive Txn Table Redo Callback
—-subopcode 32:Flashback Archive Txn Table Redo Set
—-subopcode 33:change notification commit marker
—-subopcode 34:NTP bit for change notfn
—-subopcode 35:Flashback Archive Collect Txn Table Redo Set
opcode 6:Control File
—-subopcode 1:TaBleSpace Remove DataFile
—-subopcode 2:TaBleSpace Add DataFile
—-subopcode 3:TaBleSpace OFfLine
—-subopcode 4:TaBleSpace ONLine
—-subopcode 5:TaBleSpace ReaD-Write
—-subopcode 6:TaBleSpace ReaD-Only
—-subopcode 7:TaBleSpace Remove TableSpace
—-subopcode 8:TaBleSpace Add TableSpace
—-subopcode 9:TaBleSpace Undo TsPitr
—-subopcode 10:TaBleSpace undo plugged datafile convert
—-subopcode 11:Tablespace Undo Rename
opcode 10:INDEX
—-subopcode 1:load index block
—-subopcode 2:Insert leaf row
—-subopcode 3:Purge leaf row
—-subopcode 4:Mark leaf row deleted
—-subopcode 5:Restore leaf row
—-subopcode 6:Lock index block
—-subopcode 7:Unlock index block
—-subopcode 8:Initialize new leaf block
—-subopcode 9:Apply Itl Redo
—-subopcode 10 :Set leaf block next link
—-subopcode 11 :Set leaf block previous link
—-subopcode 12 :Init root block after split
—-subopcode 13 :Make leaf block empty
—-subopcode 14 :Restore block before image
—-subopcode 15 :Branch block row insert
—-subopcode 16 :Branch block row purge
—-subopcode 17 :Initialize new branch block
—-subopcode 18 :Update keydata in row
—-subopcode 19 :Clear row’s split flag
—-subopcode 20 :Set row’s split flag
—-subopcode 21 :General undo above the cache
—-subopcode 22 :Undo operation on leaf key above the cache
—-subopcode 23 :Restore block to b-tree
—-subopcode 24 :Shrink ITL
—-subopcode 25 :Format root block redo
—-subopcode 26 :Undo of format root bloc
—-subopcode 27 :Redo for undo of format root block
—-subopcode 28 :Undo for migrating block
—-subopcode 29 :Redo for migrating block
—-subopcode 30 :IOT leaf block nonkey update
—-subopcode 31 :Direct load root redo
—-subopcode 32 :Combine operation for insert and restore rows
—-subopcode 33:Temp index redo apply
—-subopcode 34:Remove block from b-tree and empty block
—-subopcode 35:- KDICLCU
—-subopcode 36:Supplemental logging
—-subopcode 37:Undo of non-key updates
—-subopcode 38:Logical non-key update
—-subopcode 39:Branch update range
—-subopcode 40:Branch DBA update
opcode 11:Row Access
—-subopcode 1:Interpret Undo Record
—-subopcode 2:Insert Row Piece
—-subopcode 3:Drop Row Piece
—-subopcode 4:Lock Row Piece
—-subopcode 5:Update Row Piece
—-subopcode 6:Overwrite Row Piece
—-subopcode 7:Manipulate First Column
—-subopcode 8:Change Forwarding address
—-subopcode 9:Change the Cluster Key Index
—-subopcode 10:Set Key Links- KDOSKL
—-subopcode 11:Quick Multi-Insert
—-subopcode 12:Quick Multi-Delete
—-subopcode 13:Toggle Block Header flags
—-subopcode 14:KDODSC
—-subopcode 15:KDOMBC
—-subopcode 16:Logminer support RM for rowpiece with only logminer columns
—-subopcode 17:Logminer support RM for LOB id key information
—-subopcode 18:Logminer support RM for LOB operation errors
—-subopcode 19:Logminer support array updates
—-subopcode 20:Logminer support
—-subopcode 21:Logminer support
—-subopcode 22:Logminer support
—-subopcode 23:Logminer support
—-subopcode 24:Logminer support
—-subopcode 25:Logminer support
opcode 13:Transaction Segment
—-subopcode 1:Data Segment Format
—-subopcode 2:format free list block
—-subopcode 3:redo for convert to unlimited extents format
—-subopcode 4:fix segment header by moving its extent to ext 0
—-subopcode 5:format data block
—-subopcode 6:set link value on block
—-subopcode 7:freelist related fgroup/segheader redo
—-subopcode 8:freelist related fgroup/segheader undo
—-subopcode 9:undo for linking block to xnt freelist
—-subopcode 10:BITMAP format segment header
—-subopcode 11:BITMAP format bitmap block
—-subopcode 12:BITMAP format bitmap index block
—-subopcode 12:BITMAP format bitmap index block
—-subopcode 13:BITMAP redo for bmb
—-subopcode 14:BITMAP undo for BMB
—-subopcode 15:BITMAP redo for index map
—-subopcode 16:BITMAP undo for index map
—-subopcode 17:Bitmap Seg format segment Header
—-subopcode 18:Bitmap Seg format First opcode bitmap block
—-subopcode 19:Bitmap Seg format Second opcode bitmap block
—-subopcode 20:Bitmap Seg format Third opcode bitmap block
—-subopcode 21:Bitmap Seg format data block
—-subopcode 22:Bitmap Seg Redo for L1 bmb
—-subopcode 23:Bitmap Seg Undo for L1 BMB
—-subopcode 24:Bitmap Seg Redo for L2 bmb
—-subopcode 25:Bitmap Seg Undo for L2 BMB
—-subopcode 26:Bitmap Seg Redo for L3 bmb
—-subopcode 27:Bitmap Seg Undo for L3 BMB
—-subopcode 28:Bitmap Seg Redo for pagetable segment header block
—-subopcode 29:Bitmap Seg Undo for pagetable segment header block
—-subopcode 30:Bitmap Seg format L1 BMB for LOB segments
—-subopcode 31:Bitmap Seg Shrink redo for L1
—-subopcode 32:Bitmap Seg Shrink redo for segment header
—-subopcode 33:Bitmap Seg Shrink redo for extent map blk
—-subopcode 34:Bitmap Seg Shrink undo for segment header
—-subopcode 35:Bitmap Seg Shrink undo for L1
—-subopcode 36:Bitmap Seg shrink related
—-subopcode 37:Bitmap Seg shrink related
—-subopcode 38:Bitmap Seg shrink related
—-subopcode 39:Bitmap Seg shrink related
—-subopcode 40:Bitmap Seg Shrink redo for extent map blk
—-subopcode 41:NGLOB format opcode Extent Header
—-subopcode 42:NGLOB format opcode Persistent Undo
—-subopcode 43:NGLOB format opcode Hash bucket
—-subopcode 44:NGLOB format opcode Free SPace
—-subopcode 45:NGLOB format opcode Segment Header
—-subopcode 46:NGLOB format opcode data block
—-subopcode 47:NGLOB block update Extent Header redo
—-subopcode 48:NGLOB block update Extent Header undo
—-subopcode 49:NGLOB block update Hash Bucket redo
—-subopcode 50:NGLOB block update Hash Bucket undo
—-subopcode 51:NGLOB block update Free Space redo
—-subopcode 52:NGLOB block update Free Space undo
—-subopcode 53:NGLOB block update Persistent Undo redo
—-subopcode 54:NGLOB block update Persistent Undo undo
—-subopcode 55:NGLOB block update Segment Header redo
—-subopcode 56:NGLOB block update Segment Header undo
opcode 14:Transaction Extent
—-subopcode 1:Unlock Segment Header
—-subopcode 2:Redo set extent map disk LocK
—-subopcode 3:redo for conversion to unlimited format
—-subopcode 4:extent operation redo
—-subopcode 5:extent operation undo
—-subopcode 6:extent map format redo
—-subopcode 7:redo
—-subopcode 8:undo for truncate ops, flush the object
—-subopcode 9:undo for reformat of a ctl block
—-subopcode 10:redo to facilitate above undo
—-subopcode 11:redo to clean xids in seghdr/fgb
—-subopcode 12:SMU-Retention: Redo to propagate extent commit time
opcode 15:Table Space
—-subopcode 1:format save undo header
—-subopcode 2:add save undo record
—-subopcode 3:move to next block
—-subopcode 4:point to next save undo record
—-subopcode 5:update saveundo blk during save undo application
opcode 17:Recovery
—-subopcode 1:End Hot Backup
—-subopcode 2:ENable Thread
—-subopcode 3:Crash Recovery Marker
—-subopcode 4:ReSiZeable datafiles
—-subopcode 5:tablespace ONline
—-subopcode 6:tablespace OFFline
—-subopcode 7:tablespace ReaD Write
—-subopcode 8:tablespace ReaD Only
—-subopcode 9:ADDing datafiles to database
—-subopcode 10:tablespace DRoP
—-subopcode 11:Tablespace PitR
—-subopcode 12:PLUgging datafiles to database
—-subopcode 13:convert plugged in datafiles
—-subopcode 14:ADding dataFiles to database
—-subopcode 15:heart-beat redo
—-subopcode 16:tablespace rename
—-subopcode 17:ENable Thread
—-subopcode 18:tablespace ONline
—-subopcode 19:tablespace OFFline
—-subopcode 20:tablespace ReaD Write
—-subopcode 21:tablespace ReaD Only
—-subopcode 22:PLUgging datafiles to db
—-subopcode 23:ADding dataFiles to database
—-subopcode 24:convert plugged in datafiles
—-subopcode 25:Tablespace PitR
—-subopcode 26:for file drop in tablespace
—-subopcode 27:for internal thread enable
—-subopcode 28:readable standby metadata flush
—-subopcode 29:database key creation
—-subopcode 30:ADding dataFiles to database
—-subopcode 31:PLUgging datafiles to db
—-subopcode 32:for modifying space header info
—-subopcode 33:TSE Masterkey Rekey
opcode 18:Hot Backup Log Blocks
—-subopcode 1:Log block image
—-subopcode 2:Recovery testing
—-subopcode 3:Object/Range reuse
opcode 19:Direct Loader Log Blocks
—-subopcode 1:Direct block logging
—-subopcode 2:Invalidate range
—-subopcode 3:Direct block relogging
—-subopcode 4:Invalidate range relogging
opcode 20:Compatibility Segment operations
—-subopcode 1:Format compatibility segment
—-subopcode 2:Update compatibility segment
—-subopcode 3:Update Root Dba in controlfile and file header
—-subopcode 4:Set bit in a SQL Tuning Existence Bit Vector
—-subopcode 5:Invalidate an SQL Statement by Signature
—-subopcode 6:Unauthorize cursors after sys privilege revoke
opcode 21:LOB segment operations
—-subopcode 1:Write data into ILOB data block
opcode 22:Tablespace bitmapped file operations
—-subopcode 1:format space header
—-subopcode 2:space header generic redo
—-subopcode 3:space header undo
—-subopcode 4:space bitmap block format
—-subopcode 5:bitmap block generic redo
opcode 23:write behind logging of blocks
—-subopcode 1:Dummy block written callback
—-subopcode 2:log reads
—-subopcode 3:log DirectWrites
opcode 24:Logminer related
—-subopcode 1:common portion of the ddl
—-subopcode 2:direct load redo
—-subopcode 3:lob related info
—-subopcode 4:misc info
—-subopcode 5:user info
—-subopcode 6:direct load redo 10i
—-subopcode 7:logminer undo
—-subopcode 8:xmlredo
—-subopcode 9:PL/SQL redo
—-subopcode 10:Uniform Redo Unchained
—-subopcode 11:txn commit marker
—-subopcode 12:supplog marker
opcode 25:Queue Related
—-subopcode 1:undo
—-subopcode 2:init
—-subopcode 3:enqueue
—-subopcode 4:update
—-subopcode 5:delete
—-subopcode 6:lock
—-subopcode 7:min/max
opcode 26:Local LOB Related
—-subopcode 1:generic lob undo
—-subopcode 2:generic lob redo
—-subopcode 3:lob block format redo
—-subopcode 4:lob invalidation redo
—-subopcode 5:lob cache-load redo
—-subopcode 6:direct lob direct-load redo
—-subopcode 7:dummy calibration redo
opcode 27:Block Change Tracking
—-subopcode 1:op-code for bitmap switch
==========================================================================================