Oracle Redo log contents for Insert to new table.

The following is an examination of the redo generated for an insert to a new table in a new datafile.  It includes a comparison of the redo log dump, and actual byte contents of the log.  To generate this output I ran :-

create table BBBBB (attribute1 char(5));

alter system switch logfile;
insert into BBBBB values ('AAAAA');
commit;
select vlf.member from v$log vl, v$logfile vlf where vl.group#=vlf.group# and vl.status = 'CURRENT';
alter system switch logfile;

ALTER SYSTEM DUMP LOGFILE 'D:\ORACLE\WIN64\ONLINELOG\O1_MF_3_8L5T3995_.LOG';

In the comparison the dump file output will be red, and the log byte value will be blue.

I ran the example on Windows, so the redo block size is 512 bytes.

I skipped the first block of the redo log, which contains the file header, and the second block, which contains the redo header.  Output starts with the 3rd block, which contains the first redo record.

You can see in the output in red that the RBA (Redo Byte Address) where the first redo record starts is log sequence 9, block 2 (not including file header block) offset 16.  The total length of the redo record is 668 bytes (not including the 16 byte block headers).  The record also includes the VLD which defines redo record header size, SCN, SUBSCN, and Timestamp.

REDO RECORD - Thread:1 RBA: 0x000009.00000002.0010 LEN: 0x029c VLD: 0x0d

SCN: 0x0000.00110ad8 SUBSCN:  1 02/28/2013 16:19:07

# Each block starts with a 16 byte block header containing signature, block number sequence, offset and checksum.
01 22 Signature (the same in every block)
00 00
02 00 00 00 Block Number (2)
09 00 00 00 Sequence Number
10 Offset to redo record (16)
80
66 B2 CheckSum

# At offset 16 the Redo Record Header starts.  For VLD 13 the header is 68 bytes containing VLD, SCN, and Timestamp.
9C 02  Redo Record Size (668) 
00 00
0D 00 VLD (13)
00 00
D8 0A 11 00 SCN
01 00 00 00 Sub SCN
00 00 00 00 
00 00 00 00 
00 00 01 00 
02 00 00 00 
02 00 00 00 
08 00 00 00 
D7 0A 11 00 SCN 
00 00 00 00 
00 00 00 00 
00 00 00 00 
D9 0A 11 00 SCN
00 00 00 00 
7B 1A 32 30 Timestamp

# The first change after the redo record header relates to a change to object 73524 in DBA (Database Block Address) 0x00415129.  The block Class (CLS) is 1 (data block) and the Absolute File Number (AFN) is 1.  The change is Operation (OP) 13.5 which is a "block format" prior to the insert.

CHANGE #1 TYP:1 CLS:1 AFN:1 DBA:0x00415129 OBJ:73524 SCN:0x0000.00110ad6 SEQ:1 OP:13.5 ENC:0 RBL:0
KTSFRBFMT (block format) redo: Segobjd: 0x00011f34 type: 1 itls: 2 cscn: 0x0000.00110ad6

# change record for OP 13.5 is 48 bytes
0D 05 OP (13.5)
01 00 CLS
01 00 AFN
01 00
29 51 41 00 DBA
D6 0A 11 00 00 00 SCN
DC 58 
01 SEQ
01 TYP
34 1F 
06 00
08 00 08 00 
00 00 
34 1F 01 00 SegObjd
01 00 type
02 00 itls
D6 0A 11 00 00 00 cscn
00 00 

# The second change in the redo record is to the same object and block, consisting of operation 13.6 which is a "block link modify" "lock set" following the format.

CHANGE #2 TYP:0 CLS:1 AFN:1 DBA:0x00415129 OBJ:73524 SCN:0x0000.00110ad8 SEQ:1 OP:13.6 ENC:0 RBL:0
KTSFRBLNK (block link modify) redo:  Opcode: LSET (lock set)
Next dba: 0x0041512a itli: 0

# change record for OP 13.6 is 40 bytes
0D 06 OP
01 00 CLS
01 00 AFN
01 00
29 51 41 00 DBA
D8 0A 11 00 00 00 SCN
0D 00 
01 SEQ
00 TYP
34 1F 
04 00
0C 00 02 00 
00 00 
2A 51 41 00 Next dba
00 00 00 00 itli

# The third change in the redo records is operation 13.6 which is a "block link modify" "lock write"

CHANGE #3 TYP:0 CLS:1 AFN:1 DBA:0x00415129 OBJ:73524 SCN:0x0000.00110ad8 SEQ:2 OP:13.6 ENC:0 RBL:0
KTSFRBLNK (block link modify) redo:  Opcode: LWRT (lock write)
Next dba: 0x00000000 itli: 0

# change record for OP 13.6 is 40 bytes
0D 06 OP
01 00 CLS
01 00 AFN
01 00
29 51 41 00 DBA
D8 0A 11 00 00 00 SCN
DC 58 
02 SEQ
00 TYP
34 1F 
04 00
0C 00 03 00 
00 00 00 00 Next dba
00 00 00 00 itli
00 00

# The fourth change in the redo record is operation 13.7 to maintain the block linked list (freelist) and high water mark (hwm).

CHANGE #4 TYP:0 CLS:4 AFN:1 DBA:0x00415128 OBJ:73524 SCN:0x0000.00110ac8 SEQ:1 OP:13.7 ENC:0 RBL:0
KTSFRGRP (fgb/shdr modify freelist) redo:
 Opcode: HWMMV (move hwm)
NBK: 1
 Opcode: LUPD_LLIST (link a list)
Slot no: 0, Count: 1
Flag: = 1 xid or slot0 ccnt:  0x0000.000.00000001 Head:  0x00415129 Tail:  0x00415129 

# change record for OP 13.7 is 72 bytes
0D 07 OP
04 00 CLS
01 00 AFN
01 00 
28 51 41 00 DBA
C8 0A 11 00 00 00 SCN
4C 4C 
01   SEQ
00 TYP
34 1F 
06 00
08 00 
20 00 38 34 
01 00 00 00 
01 00 00 00 
05 00 00 00 
01 00 00 00 
00 00 00 00 
01 00 00 00 
00 00 00 00 
01 00 00 00
29 51 41 00 head
29 51 41 00 tail

# The fifth change in the redo record is OP 11.2 which is the actual redo for the insert to the newly formatted block. It contains details on the System Change Number (SCN), Transaction ID (xid), Undo Block Address (uba), table column and data. 
# The xid is 8 bytes composed of Undo segment number(0x006) , Undo segment header transaction table slot (0x009), and sequence number wrap (0x0000032d).
# The uba is 8 bytes composed of DBA of undo block (0x00c00182), Sequence number (0x00ca), and Record number in block (0x20).

CHANGE #5 TYP:0 CLS:1 AFN:1 DBA:0x00415129 OBJ:73524 SCN:0x0000.00110ad8 SEQ:3 OP:11.2 ENC:0 RBL:0
KTB Redo 
op: 0x01  ver: 0x01  
compat bit: 4 (post-11) padding: 0
op: F  xid:  0x0006.009.0000032d    uba: 0x00c00182.00ca.20
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x00415129  hdba: 0x00415128
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 0(0x0) size/delt: 9
fb: --H-FL-- lb: 0x1  cc: 1
null: -
col  0: [ 5]  41 41 41 41 41

# change record for OP 11.2 is 112 bytes (will vary depending on table and data)
0B 02 OP
01 00 CLS
01 00 AFN
01 00 
29 51 41 00 DBA
D8 0A 11 00 00 00 SCN
00 00 
03   SEQ
00 TYP
34 1F 
08 00
14 00 31 00 
05 00 01 05 
DC 58 
06 00 09 00 2D 03 00 00 xid
82 01 C0 00 CA 00 20 00 uba
29 51 41 00 bdba
28 51 41 00 hdba
FF 12 maxfr
02 01 
01 00 itli
00 00
2C 01 
01 00 
00 00 
00 00 slot
00 00 
00 00 
00 00 
00 00 
00 00 
00 00 
00 00 
00 00 
09 00 size 
00 00 lb
00 00 
00 00 
00 00 
00 00 
41 41 41 41 41 00 00 00 DATA (AAAAA)


# The sixth change is OP 5.2 containing SCN, uba.

CHANGE #6 TYP:0 CLS:27 AFN:3 DBA:0x00c000d0 OBJ:4294967295 SCN:0x0000.00110a95 SEQ:2 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0009 sqn: 0x0000032d flg: 0x0012 siz: 108 fbi: 0
            uba: 0x00c00182.00ca.20    pxid:  0x0000.000.00000000

# change record for OP 5.2 is 60 bytes
05 02 OP
1B 00 CLS
03 00 AFN
FF FF 
D0 00 C0 00 DBA
95 0A 11 00 00 00 SCN
DC 58 
02   SEQ
00 TYP
FF FF 
04 00 20 00 
09 00 slt
3A 00 
2D 03 00 00 sqn
82 01 C0 00 CA 00 20 uba
00
12 00 flg 
6C siz (108)
00 
00 00 00 00 
00 00 00 00 
00 00 00 00

# The seventh change is OP 5.4 is the final commit, containing SCN, uba.

CHANGE #7 TYP:0 CLS:27 AFN:3 DBA:0x00c000d0 OBJ:4294967295 SCN:0x0000.00110ad8 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0009 sqn: 0x0000032d srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c00182.00ca.20 ext: 3 spc: 4250 fbi: 0 

# change record for OP 5.4 is 72 bytes
05 04 OP
1B 00 CLS
03 00 AFN
FF FF 
D0 00 C0 00 DBA
D8 0A 11 00 00 00 SCN
DC 58 
01   SEQ
00 TYP
FF FF 
08 00 14 00 
10 00 04 00 
09 00 00 00 
2D 03 00 00 sqn
00 00 
00 00 srt
09 00 sta
00 00 
02 00 flg
00 00 
82 01 C0 00 uba part 1

# The redo record did not fit into one 512 byte block, so now continues in block 3, which starts with a 16 byte header including offset.

# block header = 16 bytes
01 22 Signature
00 00
03 00 00 00 Block Number
09 00 00 00 Sequence Number
BC Offset 
80
EE 58 CheckSum

# continuation of change 7 in new block
CA 00 20 00 uba part 2
03 00  ext
9A 10 spc
00 07 00 00
FB 12 2F 51 

# The eighth and final change is OP 5.1 which is the undo record.

CHANGE #8 TYP:0 CLS:28 AFN:3 DBA:0x00c00182 OBJ:4294967295 SCN:0x0000.00110a95 SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 108 spc: 4360 flg: 0x0012 seq: 0x00ca rec: 0x20
            xid:  0x0006.009.0000032d  
ktubl redo: slt: 9 rci: 0 opc: 11.1 [objn: 73524 objd: 73524 tsn: 0]
Undo type:  Regular undo        Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
             0x00000000  prev ctl uba: 0x00c00182.00ca.1d 
prev ctl max cmt scn:  0x0000.00110540  prev tx cmt scn:  0x0000.00110544 
txn start scn:  0x0000.00110ad1  logon user: 0  prev brb: 12583297  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo 
op: 0x03  ver: 0x01  
compat bit: 4 (post-11) padding: 0
op: Z
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x00415129  hdba: 0x00415128
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 0(0x0)
END OF REDO DUMP

# change record for OP 5.1 is 156 bytes
05 01 OP
1C 00 CLS
03 00 AFN
FF FF 
82 01 C0 00 DBA
95 0A 11 00 00 00 SCN
00 00 
03   SEQ
00 TYP
FF FF 
0A 00 14 00 
4C 00 02 00 
14 00 
00 00 
6C siz (108)
00 
08 11 
12 00 flg
00 00 
06 00 09 00 2D 03 00 00    xid
CA 00 seq
20 00 rec
34 1F 01 00 objn
34 1F 01 00 objd
00 00 00 00 
00 00 00 00 
0B 01 09 00 
08 0C 01 00 
00 00 00 00 
82 01 C0 00 DBA
CA 00 
1D 00 
40 05 11 00 00 00 prev ctl max cmt scn
C0 00 
44 05 11 00 00 00   prev tx cmt scn
00 00 
00 00  
00 00 
D1 0A 11 00 00 00   txn start scn
00 00 
81 01 
C0 00 
00 00 00 00 
00 00 00 00 
03 05 
FF FF 
29 51 41 00 bdba
28 51 41 00 hdba
FF 12 maxfr
03 01 
01 00
00 00
00 00
00 58