Redo
All changes to the database are recorded by redo. Redo includes all changes to datafiles, but does not include changes to control files or the parameter file.
Redo is initially written to online redo logs. The contents of a redo log file depend on a combination of Oracle version, operating system and server architecture. In general redo logs written on one architecture cannot be read on another. There are a few exceptions to this rule. For example, in Oracle 10.2 a redo log written in Linux can be read by a Windows database.
Redo Threads
Each online redo log has a thread number and a sequence number. The thread number is mainly relevant in RAC databases where there can be multiple threads; one for each instance. The thread number is not necessarily the same as the instance number. For single instance databases there is only one redo log thread at any time.
Redo Log Groups
A redo thread consists of two or more redo log groups.
Each redo log group contains one or more physical redo log files known as members. Multiple members are configured to provide protection against media failure (mirroring). All members within a redo log group should be identical at any time.
Each redo log group has a status. Possible status values include UNUSED, CURRENT, ACTIVE and INACTIVE. Initially redo log groups are UNUSED. Only one redo log group can be CURRENT at any time. Following a log switch, redo log group continues to be ACTIVE until a checkpoint has completed. Thereafter the redo log group becomes INACTIVE until it is reused by the LGWR background process.
Log Switches
Log switches occur when the online redo log becomes full. Alternatively log switches can be triggered externally by commands such as:
ALTER SYSTEM SWITCH LOGFILE;
When a log switch occurs, the sequence number is incremented and redo continues to be written to the next file in the sequence. If archive logging is enabled, then following a low switch the completed online redo log will be copied to the archive log destination(s) either by the ARCH background process or the LNSn background process depending on the configuration.
Redo Log Files
A redo log file consists of a number of fixed size blocks. The overall size of the redo log file is specified when the log group is created. For most platforms including Linux and Solaris the redo log block size is 512 bytes. On other platforms including HP/UX Itanium the redo log block size can be 1024 bytes.
Each redo log file has a fixed header. In recent versions of Oracle (8.0 and above) this header is two blocks. Therefore on Linux/Solaris the header is 1024 bytes. The second block of the header contains a standard Oracle file header which includes the following information:
- Database name
- Thread
- Compatibility Version
- Start Time
- End Time
- Start SCN
- End SCN
Other data is stored in the header. Note that the End SCN is actually the Start SCN of the next redo log file.
Redo Blocks
The body of the redo log file is used to store redo blocks. Each redo block has a 16 byte header (Oracle 9.2 and 10.2). The remainder of each redo block is used to store redo records.
Redo Records
Redo records are a logical structure. The upper size limit is probably 65536 bytes. Redo records can therefore span multiple physical redo blocks. A physical redo block can also contain multiple redo records.
Each redo record has a header. The VLD field in the redo record header specifies the type of the redo record. The size of the redo record header varies depending on the type.
In Oracle 9.2 the redo record header is normally 12 bytes, though they can occasionally increase in size to 28 bytes. In Oracle 10.2 the redo record header is normally 24 bytes, though under some circumstances they can increase to 68 bytes.
The following is an example of a redo record header from Oracle 10.2:
REDO RECORD - Thread:1 RBA: 0x000092.00000193.0088 LEN: 0x0050 VLD: 0x01 SCN: 0x0000.00181068 SUBSCN: 1 05/07/2009 21:53:48
The header includes the following fields
- Thread - redo log thread number
- RBA - redo byte address - address of redo record within redo log. Format is <sequence_number>.<block_number>.<offset>
- LEN - length of redo record in bytes including header
- VLD - see below
- SCN - system change number of redo record
- SUBSCN: Unknown
- Timestamp e.g. 05/07/2009 21:53:48
The VLD field determines the size of the redo record header. Known values are shown in the following table. These values may vary from one release to another.
|
Change Vectors
A redo record consists of one or more change records known as change vectors. Each change vector consists of:
- change header
- list of element lengths
- list of elements
The size of the change header is 28 bytes in both Oracle 9.2 and 10.2.
The list of element lengths has a two byte header specifying the overall length of the element length list in bytes. The length of each element is stored in a two byte field. Finally if the structure does not align on a four byte boundary, a further two byte field is appended.
The list of elements consists of one or more elements aligned on a four byte boundary. Element sizes can range from four bytes to at least 32K.
If supplemental logging is enabled then for update operations (11.5), additional elements are appended to the change vector containing the primary key, unique key or column values of the row.
Operation Codes
Each change vector has an operation code. In Oracle 9.2 there were over 150 redo log operations; this number has grown significantly in Oracle 10.2 though the exact figure is not known. The operation code consists of a major number and a minor number.
The major number describes the level in the kernel where the redo is generated. The following table shows common levels:
|
For each level there is one or more subcode. Follow the hyperlinks for more details on individual operations:
- Level 4 - Block Cleanout
- Level 5 - Transaction Layer (Undo)
- Level 10 - Index Operation
Operation | Kernel Function | Description |
10.2 | kdxlin | Insert Leaf Row |
10.4 | kdxlde | Delete Leaf Row |
10.5 | kdxlre | Restore Leaf Row |
10.6 | kdxlok | Lock Index Block |
10.7 | kdxulo | Clear Block Opcode during Commit |
10.8 | kdxlne | Initialize Newly Allocated Leaf Block |
10.9 | kdxair | Save Current Leaf Block |
10.10 | kdxlnx | Set Pointer to next Leaf Block |
10.11 | kdxlpr | Set Pointer to previous Leaf Block |
10.12 | kdxrsp | Initialize Root Block after Split |
10.13 | kdxlem | Make Index Leaf Block Empty |
10.14 | kdxima | Restored Block Before Image |
10.15 | kdxbin | Insert Branch Block Row |
10.16 | kdxbpu | Purge Branch Block Row |
10.17 | kdxbne | Initialize Branch Block |
10.18 | kdxlup | Update keydata |
10.35 | kdxlcnu | Update non-key value |
10.2 - Insert Index Row
This operation inserts a row into an index.
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit)
Consider the following code:
CREATE TABLE team ( team_code VARCHAR2(3), team_name VARCHAR2(30), country_code VARCHAR2(3) ); CREATE INDEX team_pk ON team (team_code); INSERT INTO team VALUES ('MCL','McLaren','GBR'); COMMIT;
The statement
INSERT INTO team VALUES ('FER','Ferrari','GER');
generates the following index redo
CHANGE #2 TYP:2 CLS: 1 AFN:4 DBA:0x01001b84 OBJ:53058 SCN:0x0000.0017bd28 SEQ: 1 OP:10.2 index redo (kdxlin): insert leaf row KTB Redo op: 0x11 ver: 0x01 op: F xid: 0x0005.02e.000001d7 uba: 0x00800932.016c.09 Block cleanout record, scn: 0x0000.0017bd66 ver: 0x01 opt: 0x02, entries follow... itli: 2 flg: 2 scn: 0x0000.0017bd28 REDO: SINGLE / -- / -- itl: 2, sno: 0, row size 15 insert key: (11): 03 46 45 52 06 01 00 1b 7c 00 01
index redo (kdxlin): insert leaf row
kdxlin: operation code 10.2
KTB Redo op: 0x11 ver: 0x01 op: F xid: 0x0005.02e.000001d7 uba: 0x00800932.016c.09
See KTBRedo
Block cleanout record, scn: 0x0000.0017bd66 ver: 0x01 opt: 0x02, entries follow...
If the ITL has not been updated out since the last transaction changes where committed or rolled back then a block cleanout is performed
scn System change number of block cleanout. Format is wrap#.base#
ver Version number
opt Option number
itli: 2 flg: 2 scn: 0x0000.0017bd28
ITL element being cleaned out.
itli ITL element number (1 based)
flg Flag
scn System Change Number of entry
REDO: SINGLE / -- / --
Redo for single row (as opposed to array)
itl: 2, sno: 0, row size 15
ITL slot number 2
Index block slot number 0
Row size 15 bytes
- Slot - 2 bytes
- Row Header - 3 bytes
- Flag byte - 1 byte
- Lock byte - 1byte
- Key Length (11) - 1 byte
- Row Data - 11 bytes
- VARCHAR2 column length (3) - 1 byte
- VARCHAR2 column (FER) - 3 bytes
- ROWID - 6 bytes
insert key: (11): 03 46 45 52 06 01 00 1b 7c 00 01
insert key: hex dump of key to be inserted
The above statement generated the following undo
CHANGE #1 TYP:0 CLS:26 AFN:2 DBA:0x00800932 OBJ:4294967295 SCN:0x0000.0017bd66 SEQ: 1 OP:5.1 ktudb redo: siz: 100 spc: 6498 flg: 0x0022 seq: 0x016c rec: 0x09 xid: 0x0005.02e.000001d7 ktubu redo: slt: 46 rci: 8 opc: 10.22 objn: 53058 objd: 53058 tsn: 4 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 op: L itl: xid: 0x0006.025.000001ed uba: 0x008004ab.0164.02 flg: C--- lkc: 0 scn: 0x0000.0017bd28 Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x1001b83 block=0x01001b84 (kdxlpu): purge leaf row key :(11): 03 46 45 52 06 01 00 1b 7c 00 01
10.4 - Delete Index Row
This operation deletes a row from an index.
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit)
Consider the following code:
CREATE TABLE team ( team_code VARCHAR2(3), team_name VARCHAR2(30), country_code VARCHAR2(3) ); CREATE INDEX team_pk ON team (team_code); INSERT INTO team VALUES ('MCL','McLaren','GBR'); INSERT INTO team VALUES ('FER','Ferrari','GER'); COMMIT;
The statement
DELETE FROM team WHERE team_code = 'FER';
generates the following index redo
CHANGE #2 TYP:0 CLS: 1 AFN:4 DBA:0x01001b84 OBJ:53058 SCN:0x0000.0017c1e9 SEQ: 1 OP:10.4 index redo (kdxlde): delete leaf row KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0008.015.000001ae uba: 0x008009a5.02e6.2a REDO: SINGLE / -- / -- itl: 2, sno: 0, row size 15
index redo (kdxlde): delete leaf row
kdxlde: operation code 10.4
KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0008.015.000001ae uba: 0x008009a5.02e6.2a
See KTBRedo
REDO: SINGLE / -- / --
Redo for single row (as opposed to array)
itl: 2, sno: 0, row size 15
ITL slot number 2
Index block slot number 0
Row size 15 bytes
- Slot - 2 bytes
- Row Header - 3 bytes
- Flag byte - 1 byte
- Lock byte - 1byte
- Key Length (11) - 1 byte
- Row Data - 11 bytes
- VARCHAR2 column length (3) - 1 byte
- VARCHAR2 column (FER) - 3 bytes
- ROWID - 6 bytes
The above statement generated the following undo
CHANGE #1 TYP:0 CLS:32 AFN:2 DBA:0x008009a5 OBJ:4294967295 SCN:0x0000.0017c1e9 SEQ: 1 OP:5.1 ktudb redo: siz: 100 spc: 3276 flg: 0x0022 seq: 0x02e6 rec: 0x2a xid: 0x0008.015.000001ae ktubu redo: slt: 21 rci: 41 opc: 10.22 objn: 53058 objd: 53058 tsn: 4 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 op: L itl: xid: 0x0005.02e.000001d7 uba: 0x00800932.016c.09 flg: C--- lkc: 0 scn: 0x0000.0017bd67 Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x1001b83 block=0x01001b84 (kdxlre): restore leaf row (clear leaf delete flags) key :(11): 03 46 45 52 06 01 00 1b 7c 00 01
10.5 - Restore Leaf Row
This operation restores a leaf row to an index block, normally following an index block split.
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit)
Consider the following objects:
CREATE TABLE t1 ( c1 VARCHAR2(10), c2 NUMBER ); CREATE INDEX i1 ON t1 (c1);
The PL/SQL block
DECLARE l_c1 VARCHAR2(10); l_c2 NUMBER; BEGIN FOR i IN 1..512 LOOP l_c1 := 'A'||LPAD (TO_CHAR (i),9,'0'); l_c2 := i; INSERT INTO t1 VALUES (l_c1,l_c2); END LOOP; END; /
generates the following index redo when an index block split occurs
CHANGE #2 TYP:0 CLS: 1 AFN:4 DBA:0x01001bc0 OBJ:53071 SCN:0x0000.00181068 SEQ: 3 OP:10.5 index redo (kdxlre): restore leaf row KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0009.000.000001e2 uba: 0x008002fb.0193.03 REDO: SINGLE / -- / -- itl: 2, sno: 0, row size 22
index redo (kdxlre): restore leaf row
kdxlre: operation code 10.5
KTB Redo op: 0x11 ver: 0x01 op: F xid: 0x0005.02e.000001d7 uba: 0x00800932.016c.09
See KTBRedo
REDO: SINGLE / -- / --
Redo for single row (as opposed to array)
itl: 2, sno: 0, row size 22
ITL slot number 2
Index block slot number 0
Row size 22 bytes
- Slot - 2 bytes
- Row Header - 3 bytes
- Flag byte - 1 byte
- Lock byte - 1byte
- Key Length (11) - 1 byte
- Row Data - 17 bytes
- VARCHAR2 column length (10) - 1 byte
- VARCHAR2 column (A000000364) - 10 bytes
- ROWID - 6 bytes
The above statement generated the following undo
CHANGE #1 TYP:0 CLS:34 AFN:2 DBA:0x008002fb OBJ:4294967295 SCN:0x0000.00181068 SEQ: 3 OP:5.1 ktudb redo: siz: 84 spc: 7962 flg: 0x0022 seq: 0x0193 rec: 0x03 xid: 0x0009.000.000001e2 ktubu redo: slt: 0 rci: 2 opc: 10.22 objn: 53071 objd: 53071 tsn: 4 Undo type: Regular undo Undo type: Last buffer split: No Tablespace Undo: No 0x00000000 index undo for leaf key operations KTB Redo op: 0x03 ver: 0x01 op: Z Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x1001bbb block=0x01001bc0 (kdxlpu): purge leaf row key :(18): 0a 41 30 30 30 30 30 30 33 36 34 06 01 00 1b b7 01 6b
10.6 - Lock Index Block
This operation locks an index block, normally as part of an index block split.
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit)
Consider the following objects:
CREATE TABLE t1 ( c1 VARCHAR2(10), c2 NUMBER ); CREATE INDEX i1 ON t1 (c1);
The PL/SQL block
DECLARE l_c1 VARCHAR2(10); l_c2 NUMBER; BEGIN FOR i IN 1..512 LOOP l_c1 := 'A'||LPAD (TO_CHAR (i),9,'0'); l_c2 := i; INSERT INTO t1 VALUES (l_c1,l_c2); END LOOP; END; /
generates the following index redo when an index block split occurs
CHANGE #3 TYP:0 CLS: 1 AFN:4 DBA:0x01001bbc OBJ:53071 SCN:0x0000.00181068 SEQ:112 OP:10.6 index redo (kdxlok): lock block, count=2 KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0009.020.000001e1 uba: 0x008002fc.0193.01 lock itl 1 operation = 0x5, pre-split
index redo (kdxlok): lock block, count=2
kdxlok: operation code 10.6
KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0009.020.000001e1 uba: 0x008002fc.0193.01
See KTBRedo
lock itl 1
Lock ITL slot #1
operation = 0x5, pre-split
Operation code = 5 - pre-split
The above statement generated the following undo
CHANGE #2 TYP:1 CLS:34 AFN:2 DBA:0x008002fc OBJ:4294967295 SCN:0x0000.00181068 SEQ: 1 OP:5.1 ktudb redo: siz: 108 spc: 0 flg: 0x000a seq: 0x0193 rec: 0x01 xid: 0x0009.020.000001e1 ktubl redo: slt: 32 rci: 0 opc: 10.21 objn: 53071 objd: 53071 tsn: 4 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No 0x00000000 prev ctl uba: 0x008002f2.0193.10 prev ctl max cmt scn: 0x0000.00180495 prev tx cmt scn: 0x0000.001804a7 txn start scn: 0xffff.ffffffff logon user: 59 prev brb: 8389349 prev bcl: 0 index general undo (branch) operations KTB Redo op: 0x03 ver: 0x01 op: Z Dump kdige : block dba :0x01001bbc, seghdr dba: 0x01001bbb unlock block (1): 01
10.7 - Clear Block Opcode during Commit
This operation resets a block during index leaf block deletion
Consider the following code:
CREATE TABLE t1 ( c1 VARCHAR2(10), c2 NUMBER ); CREATE INDEX i1 ON t1 (c1); -- Insert some rows DECLARE l_c1 VARCHAR2(10); l_c2 NUMBER; BEGIN FOR i IN 1..1024 LOOP l_c1 := 'A'||LPAD (TO_CHAR (i),9,'0'); l_c2 := i; INSERT INTO t1 VALUES (l_c1,l_c2); END LOOP; END; COMMIT; -- Delete the rows again DECLARE l_c1 VARCHAR2(10); BEGIN FOR i IN 1..1024 LOOP l_c1 := 'A'||LPAD (TO_CHAR (i),9,'0'); DELETE FROM t1 WHERE c1 = l_c1; END LOOP; END; COMMIT; /
The DDL statement
ALTER INDEX i1 COALESCE; /
generates the following index redo each time an index block is deleted:
CHANGE #2 TYP:0 CLS: 1 AFN:4 DBA:0x01001bdd OBJ:53093 SCN:0x0000.001919a8 SEQ: 3 OP:10.7 index redo (kdxulo): clear block opcode during commit
index redo (kdxulo): clear block opcode during commit
kdxulo: operation code 10.7
The above statement does not generate any undo. It is, however, immediately preceded by a commit (5.4) of the recursive transaction
10.8 - Initialize New Leaf Block
This operation initializes the header of a new leaf block, normally as part of an index block split. In general an index block split will result in two 10.8 changes, one for each of the two new leaf blocks.
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit)
Consider the following objects:
CREATE TABLE t1 ( c1 VARCHAR2(10), c2 NUMBER ); CREATE INDEX i1 ON t1 (c1);
The PL/SQL block
DECLARE l_c1 VARCHAR2(10); l_c2 NUMBER; BEGIN FOR i IN 1..512 LOOP l_c1 := 'A'||LPAD (TO_CHAR (i),9,'0'); l_c2 := i; INSERT INTO t1 VALUES (l_c1,l_c2); END LOOP; END; /
generates the following index redo when an index block split occurs
CHANGE #3 TYP:0 CLS: 1 AFN:4 DBA:0x01001bbf OBJ:53071 SCN:0x0000.00181068 SEQ: 2 OP:10.8 index redo (kdxlne): (count=4) init header of newly allocated leaf block KTB Redo op: 0x05 ver: 0x01 op: R itc: 2 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.020.000001e1 0x008002fe.0193.01 -B-- 0 fsc 0x0000.00000000 0x02 0x0009.000.000001e2 0x008002fb.0193.01 ---- 0 fsc 0x0000.00000000 kdxlnitl = 1 kdxlnnco = 2 kdxlndsz = 0 kdxlncol = 56 kdxlnflg = 0 kdxlnnxt = 0x0 kdxlnprv = 0x0 new block has 363 rows dumping row index Dump of memory from 0xB78E2278 to 0xB78E2550 B78E2270 00160000 0042002C [....,.B.] B78E2280 006E0058 009A0084 00C600B0 00F200DC [X.n.............] B78E2290 011E0108 014A0134 01760160 01A2018C [....4.J.`.v.....] ..... B78E2540 1EAE1E98 1EDA1EC4 1F061EF0 1F321F1C [..............2.] dumping rows Dump of memory from 0xB78E2550 to 0xB78E41AC B78E2550 410A0200 30303030 30303030 00010631 [...A000000001...] B78E2560 0000B71B 410A0200 30303030 30303030 [.......A00000000] B78E2570 00010632 0100B71B 410A0200 30303030 [2..........A0000] B78E2580 30303030 00010633 0200B71B 410A0200 [00003..........A] B78E2590 30303030 30303030 00010634 0300B71B [000000004.......] ..... B78E4170 410A0200 30303030 36333030 00010631 [...A000000361...] B78E4180 6801B71B 410A0200 30303030 36333030 [...h...A00000036] B78E4190 00010632 6901B71B 410A0200 30303030 [2......i...A0000] B78E41A0 36333030 00010633 6A01B71B [00363......j]
Note that lines have been omitted from the block dumps
index redo (kdxlne): (count=4) init header of newly allocated leaf block
kdxlne: operation code 10.8
KTB Redo op: 0x05 ver: 0x01 op: R itc: 2 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.020.000001e1 0x008002fe.0193.01 -B-- 0 fsc 0x0000.00000000 0x02 0x0009.000.000001e2 0x008002fb.0193.01 ---- 0 fsc 0x0000.00000000
See KTBRedo
kdxlnitl = 1
ITL slot number
kdxlnnco = 2
Number of columns in index key - includes ROWID for non-unique indexes
kdxlndsz = 0
Size of data - 0 for non-unique indexes
kdxlncol = 56
Unknown
kdxlnflg = 0
Flag - values unknown
kdxlnnxt = 0x0
Address of next block
kdxlnprv = 0x0
Address of previous block
new block has 363 rows
Number of leaf rows in new block
The above statement generated the following undo
CHANGE #2 TYP:1 CLS:34 AFN:2 DBA:0x008002fe OBJ:4294967295 SCN:0x0000.00181068 SEQ: 1 OP:5.1 ktudb redo: siz: 120 spc: 0 flg: 0x000a seq: 0x0193 rec: 0x01 xid: 0x0009.020.000001e1 ktubu redo: slt: 32 rci: 0 opc: 10.21 objn: 53071 objd: 53071 tsn: 4 Undo type: Regular undo Undo type: Last buffer split: No Tablespace Undo: No 0x008002fd index general undo (branch) operations KTB Redo op: 0x05 ver: 0x01 op: R itc: 2 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 Dump kdige : block dba :0x01001bbf, seghdr dba: 0x01001bbb make leaf block empty (2): 01 00
10.9 - Save Current Leaf Block
This operation sets a flag in the ITL of a current leaf block prior to that leaf block being split. A side effect of this operation is that the entire contents of the leaf block is written to undo. Therefore, although this operation only generates around 80 bytes of redo, it generates undo roughly equivalent to the block size of the index tablespace.
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit)
Consider the following objects:
CREATE TABLE t1 ( c1 VARCHAR2(10), c2 NUMBER ); CREATE INDEX i1 ON t1 (c1);
The PL/SQL block
DECLARE l_c1 VARCHAR2(10); l_c2 NUMBER; BEGIN FOR i IN 1..512 LOOP l_c1 := 'A'||LPAD (TO_CHAR (i),9,'0'); l_c2 := i; INSERT INTO t1 VALUES (l_c1,l_c2); END LOOP; END; /
generates the following index redo when an index block split occurs
CHANGE #3 TYP:0 CLS: 1 AFN:4 DBA:0x01001bbc OBJ:53071 SCN:0x0000.00181068 SEQ:113 OP:10.9 index redo (kdxair): apply xat do to itl 1 (count=2) KTB Redo op: 0x05 ver: 0x01 op: R itc: 1 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.020.000001e1 0x008002fd.0193.01 -B-- 1 fsc 0x0000.00000000
index redo (kdxair): apply xat do to itl 1 (count=2)
kdxair: operation code 10.9
KTB Redo op: 0x05 ver: 0x01 op: R itc: 1 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.020.000001e1 0x008002fd.0193.01 -B-- 1 fsc 0x0000.00000000
See KTBRedo
The above statement generated the following undo
CHANGE #2 TYP:1 CLS:34 AFN:2 DBA:0x008002fd OBJ:4294967295 SCN:0x0000.00181068 SEQ: 1 OP:5.1 ktudb redo: siz: 8148 spc: 8040 flg: 0x000a seq: 0x0193 rec: 0x01 xid: 0x0009.020.000001e1 ktubu redo: slt: 32 rci: 0 opc: 10.21 objn: 53071 objd: 53071 tsn: 4 Undo type: Regular undo Undo type: Last buffer split: No Tablespace Undo: No 0x008002fc index general undo (branch) operations KTB Redo op: 0x05 ver: 0x01 op: R itc: 2 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.020.000001e1 0x008002fc.0193.01 ---- 1 fsc 0x0000.00000000 0x02 0x0009.000.000001e2 0x008002fb.0193.01 ---- 363 fsc 0x0000.00000000 Dump kdige : block dba :0x01001bbc, seghdr dba: 0x01001bbb restore block before image (8032): 00 01 85 02 00 00 00 00 6b 01 fa 02 04 03 0a 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 60 1f 00 00 4c 1f 38 1f 24 1f 10 1f fc 1e e8 1e d4 1e c0 1e ac 1e 98 1e 84 1e 70 1e 5c 1e 48 1e 34 1e 20 1e 0c 1e f8 1d e4 1d d0 1d bc 1d a8 1d 94 1d 80 1d 6c 1d 58 1d 44 1d 30 1d 1c 1d 08 1d f4 1c e0 1c ...... 00 04 00 02 0a 41 30 30 30 30 30 30 30 30 34 06 01 00 1b b7 00 03 00 02 0a 41 30 30 30 30 30 30 30 30 33 06 01 00 1b b7 00 02 00 02 0a 41 30 30 30 30 30 30 30 30 32 06 01 00 1b b7 00 01 00 02 0a 41 30 30 30 30 30 30 30 30 31 06 01 00 1b b7 00 00
Note that lines have been omitted from the block dumps
10.10 - Set Pointer to next Leaf Block
This operation sets a pointer to the next leaf block in an existing leaf block. Usually executed immediately after an 10.8 operation
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit)
Consider the following objects:
CREATE TABLE t1 ( c1 VARCHAR2(10), c2 NUMBER ); CREATE INDEX i1 ON t1 (c1);
The PL/SQL block
DECLARE l_c1 VARCHAR2(10); l_c2 NUMBER; BEGIN FOR i IN 1..512 LOOP l_c1 := 'A'||LPAD (TO_CHAR (i),9,'0'); l_c2 := i; INSERT INTO t1 VALUES (l_c1,l_c2); END LOOP; END; /
generates the following index redo after index block split occurs
CHANGE #1 TYP:0 CLS: 1 AFN:4 DBA:0x01001bbf OBJ:53071 SCN:0x0000.00181068 SEQ: 3 OP:10.10 index redo (kdxlnx): set kdxlenxt = 0x1001bc0 (count=1, len[0]=4)
index redo (kdxlnx): set kdxlenxt = 0x1001bc0 (count=1, len[0]=4)
kdxlnx: operation code 10.10
kdxlenxt DBA of next leaf block in index
This operation did not generate any undo
10.11 - Set Pointer to previous Leaf Block
This operation sets a pointer to the previous leaf block in an existing leaf block.
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit). This example occured during an index COALESCE operation and shows the redo generated when an index leaf block is deleted. The change is applied to the next index leaf block in the list if and only if it exists.
Consider the following code:
CREATE TABLE t1 ( c1 VARCHAR2(10), c2 NUMBER ); CREATE INDEX i1 ON t1 (c1); -- Insert some rows DECLARE l_c1 VARCHAR2(10); l_c2 NUMBER; BEGIN FOR i IN 1..1024 LOOP l_c1 := 'A'||LPAD (TO_CHAR (i),9,'0'); l_c2 := i; INSERT INTO t1 VALUES (l_c1,l_c2); END LOOP; END; COMMIT; -- Delete the rows again DECLARE l_c1 VARCHAR2(10); BEGIN FOR i IN 1..1024 LOOP l_c1 := 'A'||LPAD (TO_CHAR (i),9,'0'); DELETE FROM t1 WHERE c1 = l_c1; END LOOP; END; COMMIT; /
The DDL statement
ALTER INDEX i1 COALESCE; /
generates the following index redo when an index leaf block is deleted:
CHANGE #2 TYP:2 CLS: 1 AFN:4 DBA:0x01001bdd OBJ:53093 SCN:0x0000.001918de SEQ: 1 OP:10.11 index redo (kdxlpr) : (REDO) set kdxleprv=0x0, itl=1, count=2 KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0008.011.000001c6 uba: 0x00800cf6.02eb.2c
index redo (kdxlpr): (REDO) set kdxleprv=0x0, itl=1, count=2
kdxlpr: operation code 10.11
kdxleprv DBA of previous leaf block in index - in this case 0 because leaf block is being deleted
itl ITL slot number
count Number of elements in change vector
This operation generated the following undo:
CHANGE #1 TYP:0 CLS:32 AFN:2 DBA:0x00800cf6 OBJ:4294967295 SCN:0x0000.001919a7 SEQ: 1 OP:5.1 ktudb redo: siz: 88 spc: 4008 flg: 0x0022 seq: 0x02eb rec: 0x2c xid: 0x0008.011.000001c6 ktubu redo: slt: 17 rci: 43 opc: 10.21 objn: 53093 objd: 53093 tsn: 4 Undo type: Regular undo Undo type: Last buffer split: No Tablespace Undo: No 0x00000000 index general undo (branch) operations KTB Redo op: 0x04 ver: 0x01 op: L itl: xid: 0x0008.02a.000001c5 uba: 0x00800cee.02eb.01 flg: CB-- lkc: 0 scn: 0x0000.001918d5 Dump kdige : block dba :0x01001bdd, seghdr dba: 0x01001bdb set leaf block previous link (8): 01 01 00 00 e0 1b 00 01
10.12 - Initialize Root Block After Split
This operation initializes an index root block after an index split. Initially an index only contains one leaf block. When this block is full it will be split into two new leaf blocks and the original leaf block will be converted into a branch block. The 10.12 operation initializes this branch block which is also known as the root block as it is the top level block in the index.
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit)
Consider the following objects:
CREATE TABLE t1 ( c1 VARCHAR2(10), c2 NUMBER ); CREATE INDEX i1 ON t1 (c1);
The PL/SQL block
DECLARE l_c1 VARCHAR2(10); l_c2 NUMBER; BEGIN FOR i IN 1..512 LOOP l_c1 := 'A'||LPAD (TO_CHAR (i),9,'0'); l_c2 := i; INSERT INTO t1 VALUES (l_c1,l_c2); END LOOP; END; /
generates the following index redo after index block split occurs
CHANGE #2 TYP:0 CLS: 1 AFN:4 DBA:0x01001bbc OBJ:53071 SCN:0x0000.00181068 SEQ:114 OP:10.12 index redo (kdxrsp): initialize root block after split,count=3 service itl 1 lmc 0x1001bbf rmc 0x1001bc0 seperator key: (12): 0a 41 30 30 30 30 30 30 33 36 34 fe
index redo (kdxrsp): initialize root block after split,count=3
kdxrsp: operation code 10.12
service itl 1
service itl Unknown
lmc 0x1001bbf
lmc DBA of left hand leaf block
rmc 0x1001bc0
rmc DBA of right hand leaf block
seperator key: (12): 0a 41 30 30 30 30 30 30 33 36 34 fe
separator key Unique prefix of first key in right hand leaf block. Note that only the prefix of the key is stored in the branch block. The prefix must be suffiiciently long to identify the first row in the block. However, it does not necessarily need to contain all the bytes in the key. In this case the ROWID has been omitted; if the index contained many duplicate rows, all or part of the ROWID may be included to ensure that the prefix is unique.
In this example the first byte of the separator key is the length (0x0a = 10)
The next ten bytes are the key (A000000364)
The final byte terminates the separator key (0xfe = 254)
The above statement does not generate any undo. A copy of the block will have been previously written to undo as part of the 10.9 operation
Note however that this operation is immediately preceded by a commit operation (5.4) for the recursive transaction
10.13 - Make Index Leaf Block Empty
This operation empties an index leaf block that currently contains no rows
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit). This example occured during an index COALESCE operation.
Consider the following code:
CREATE TABLE t1 ( c1 VARCHAR2(10), c2 NUMBER ); CREATE INDEX i1 ON t1 (c1); -- Insert some rows DECLARE l_c1 VARCHAR2(10); l_c2 NUMBER; BEGIN FOR i IN 1..1024 LOOP l_c1 := 'A'||LPAD (TO_CHAR (i),9,'0'); l_c2 := i; INSERT INTO t1 VALUES (l_c1,l_c2); END LOOP; END; COMMIT; -- Delete the rows again DECLARE l_c1 VARCHAR2(10); BEGIN FOR i IN 1..1024 LOOP l_c1 := 'A'||LPAD (TO_CHAR (i),9,'0'); DELETE FROM t1 WHERE c1 = l_c1; END LOOP; END; COMMIT; /
The DDL statement
ALTER INDEX i1 COALESCE; /
generates the following index redo when an index leaf block is deleted:
CHANGE #3 TYP:0 CLS: 1 AFN:4 DBA:0x01001be0 OBJ:53093 SCN:0x0000.001919a7 SEQ: 1 OP:10.13 index redo (kdxlem): (REDO) make leaf block empty,itl=1,count=2 KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0008.011.000001c6 uba: 0x00800cf6.02eb.2b
index redo (kdxlem): (REDO) make leaf block empty,itl=1,count=2
kdxlem: operation code 10.13
itl ITL slot number
count Number of elements in change vector
This operation generated the following undo:
CHANGE #2 TYP:0 CLS:32 AFN:2 DBA:0x00800cf6 OBJ:4294967295 SCN:0x0000.001918d6 SEQ: 43 OP:5.1 ktudb redo: siz: 192 spc: 4202 flg: 0x0012 seq: 0x02eb rec: 0x2b xid: 0x0008.011.000001c6 ktubl redo: slt: 17 rci: 0 opc: 10.21 objn: 53093 objd: 53093 tsn: 4 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No 0x00000000 prev ctl uba: 0x00800cef.02eb.06 prev ctl max cmt scn: 0x0000.0019138d prev tx cmt scn: 0x0000.001913cc txn start scn: 0xffff.ffffffff logon user: 59 prev brb: 8391895 prev bcl: 0 index general undo (branch) operations KTB Redo op: 0x04 ver: 0x01 op: L itl: xid: 0x0008.00d.000001c6 uba: 0x00800ce5.02eb.01 flg: CB-- lkc: 0 scn: 0x0000.001918d3 Dump kdige : block dba :0x01001be0, seghdr dba: 0x01001bdb restore block to b-tree (1): 01 (36): 00 00 80 02 01 00 00 00 00 00 24 00 60 1f 3c 1f 00 00 00 00 dd 1b 00 01 00 00 00 00 00 00 00 00 60 1f 00 00 (20): 01 00 0a 41 30 30 30 30 30 30 30 30 31 06 01 00 1b d4 00 00
10.14 - Restored Block Before Image
This operation occurs when a new branch block is initialized
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit)
Consider the following objects:
CREATE TABLE t4 ( c1 VARCHAR2(30), c2 NUMBER ); CREATE INDEX i4 ON t4 (c1);
the statement
DECLARE l_c1 VARCHAR2(30); l_c2 NUMBER; BEGIN FOR i IN 1..50000 LOOP l_c1 := 'A'||LPAD (TO_CHAR (i),29,'0'); l_c2 := i; INSERT INTO t4 VALUES (l_c1,l_c2); END LOOP; END; /
generates the following redo
CHANGE #1 TYP:0 CLS: 1 AFN:4 DBA:0x01001d38 OBJ:53102 SCN:0x0000.00197e86 SEQ:193 OP:10.14 index redo (kdxima): restored block before image, count=2 trans layer b.i. KTB Redo op: 0x05 ver: 0x01 op: R itc: 2 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0007.007.000001a3 0x0080087f.0145.03 ---- 1 fsc 0x0000.00000000 0x02 0x0007.024.000001a1 0x00800884.0145.0d ---- 190 fsc 0x0000.00000000 data block b.i. Dump of memory from 0xB6014264 to 0xB60161C4 B6014260 02850100 00000001 01A000BE [............] B6014270 001001B0 00000000 00000000 01001D37 [............7...] B6014280 1E871E00 1E3A1F60 1F101F38 1EC01EE8 [....`.:.8.......] B6014290 1E701E98 1E201E48 1DD01DF8 1D801DA8 [..p.H. .........] B60142A0 1D301D58 1CE01D08 1C901CB8 1C401C68 [X.0.........h.@.] B60142B0 1BF01C18 1BA01BC8 1B501B78 1B001B28 [........x.P.(...] B60142C0 1AB01AD8 1A601A88 1A101A38 19C019E8 [......`.8.......] ....... B6016120 9900301D 411E0200 30303030 30303030 [.0.....A00000000] B6016130 30303030 30303030 30303030 30303030 [0000000000000000] B6016140 33303730 00010634 9800301D 411E0200 [07034....0.....A] B6016150 30303030 30303030 30303030 30303030 [0000000000000000] B6016160 30303030 30303030 33303730 00010633 [0000000007033...] B6016170 9700301D 411E0200 30303030 30303030 [.0.....A00000000] B6016180 30303030 30303030 30303030 30303030 [0000000000000000] B6016190 33303730 00010632 9600301D 411E0200 [07032....0.....A] B60161A0 30303030 30303030 30303030 30303030 [0000000000000000] B60161B0 30303030 30303030 33303730 00010631 [0000000007031...] B60161C0 9500301D
index redo (kdxima): restored block before image, count=2
kdxbin: operation code 10.15
trans layer b.i.
Transaction level before image (KTB Redo follows)
KTB Redo op: 0x05 ver: 0x01 op: R itc: 2 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0007.007.000001a3 0x0080087f.0145.03 ---- 1 fsc 0x0000.00000000 0x02 0x0007.024.000001a1 0x00800884.0145.0d ---- 190 fsc 0x0000.00000000
See KTBRedo
data block b.i.
Data block before image (block dump follows)
This operation does not generate any undo. However it appears to be followed by a 5.11 operation. For example:
CHANGE #2 TYP:0 CLS:29 AFN:2 DBA:0x00800069 OBJ:4294967295 SCN:0x0000.00197e86 SEQ: 8 OP:5.11 ktubu redo: slt: 7 rci: 0 opc: 10.21 objn: 53102 objd: 53102 tsn: 4 Undo type: Regular undo Undo type: User undo done Last buffer split: No Tablespace Undo: No 0x0080087f
10.15 - Insert Branch Block Row
This operation inserts a new row into a branch block. This operation usually occurs during an index block split.
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit)
Consider the following objects:
CREATE TABLE t1 ( c1 VARCHAR2(10), c2 NUMBER ); CREATE INDEX i1 ON t1 (c1);
The PL/SQL block
DECLARE l_c1 VARCHAR2(10); l_c2 NUMBER; BEGIN FOR i IN 1..1024 LOOP l_c1 := 'A'||LPAD (TO_CHAR (i),9,'0'); l_c2 := i; INSERT INTO t1 VALUES (l_c1,l_c2); END LOOP; END; /
generates the following index redo after index block split occurs
CHANGE #2 TYP:0 CLS: 1 AFN:4 DBA:0x01001bcc OBJ:53091 SCN:0x0000.0018ec81 SEQ: 1 OP:10.15 index redo (kdxbin) : insert branch block row, count=3 KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0002.01e.000001e2 uba: 0x00801118.020b.02 REDO itl: 1 insert into slot 1, child dba 0x1001bce new key : (12): 0a 41 30 30 30 30 30 30 37 32 37 fe
index redo (kdxbin) : insert branch block row,count=3
kdxbin: operation code 10.15
KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0002.01e.000001e2 uba: 0x00801118.020b.02
See KTBRedo
REDO itl: 1 insert into slot 1, child dba 0x1001bce
itl ITL Slot number
child dba DBA of new leaf block - in this example 0x1001bce
new key : (12): 0a 41 30 30 30 30 30 30 37 32 37 fe
new key - 12 bytes
In this example the first byte of the new key is the length (0x0a = 10)
The next ten bytes are the key (A000000727)
The final byte terminates the separator key (0xfe = 254)
The above statement generated the following undo
CHANGE #1 TYP:0 CLS:20 AFN:2 DBA:0x00801118 OBJ:4294967295 SCN:0x0000.0018ec81 SEQ: 2 OP:5.1 ktudb redo: siz: 84 spc: 8028 flg: 0x0022 seq: 0x020b rec: 0x02 xid: 0x0002.01e.000001e2 ktubu redo: slt: 30 rci: 1 opc: 10.21 objn: 53091 objd: 53091 tsn: 4 Undo type: Regular undo Undo type: Last buffer split: No Tablespace Undo: No 0x00000000 index general undo (branch) operations KTB Redo op: 0x04 ver: 0x01 op: L itl: xid: 0x0002.017.000001e2 uba: 0x0080110d.020b.01 flg: CB-- lkc: 0 scn: 0x0000.0018ec80 Dump kdige : block dba :0x01001bcc, seghdr dba: 0x01001bcb branch block row purge (4): 01 00 01 00
10.16 - Purge Branch Block Row
This operation deletes an existing row from a branch block. This operation occurs when a leaf block is removed. Note that index leaf blocks are not removed immediately when they become empty as there is a possibility that new rows will be reinserted. Leaf blocks are, however, removed during a COALESCE operation.
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit)
Consider the following code:
CREATE TABLE t1 ( c1 VARCHAR2(10), c2 NUMBER ); CREATE INDEX i1 ON t1 (c1); -- Insert some rows DECLARE l_c1 VARCHAR2(10); l_c2 NUMBER; BEGIN FOR i IN 1..1024 LOOP l_c1 := 'A'||LPAD (TO_CHAR (i),9,'0'); l_c2 := i; INSERT INTO t1 VALUES (l_c1,l_c2); END LOOP; END; COMMIT; -- Delete the rows again DECLARE l_c1 VARCHAR2(10); BEGIN FOR i IN 1..1024 LOOP l_c1 := 'A'||LPAD (TO_CHAR (i),9,'0'); DELETE FROM t1 WHERE c1 = l_c1; END LOOP; END; COMMIT; /
The DDL statement
ALTER INDEX i1 COALESCE;
generates the following index redo when an index leaf block is deleted:
CHANGE #2 TYP:0 CLS: 1 AFN:4 DBA:0x01001bdc OBJ:53093 SCN:0x0000.001919a8 SEQ: 2 OP:10.16 index redo (kdxbpu) : purge branch block row, count=2 KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0008.021.000001c6 uba: 0x00800cf6.02eb.30 REDO itl: 1 slot -1
index redo (kdxbpu) : purge branch block row,count=2
kdxbin: operation code 10.16
KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0008.021.000001c6 uba: 0x00800cf6.02eb.30
See KTBRedo
REDO itl: 1 slot -1
slot ITL Slot number - appears to always be -1 - may be a bug in the redo log dump
The above statement generated the following undo
CHANGE #1 TYP:0 CLS:32 AFN:2 DBA:0x00800cf6 OBJ:4294967295 SCN:0x0000.001919a8 SEQ: 2 OP:5.1 ktudb redo: siz: 100 spc: 3532 flg: 0x0022 seq: 0x02eb rec: 0x30 xid: 0x0008.021.000001c6 ktubu redo: slt: 33 rci: 47 opc: 10.21 objn: 53093 objd: 53093 tsn: 4 Undo type: Regular undo Undo type: Last buffer split: No Tablespace Undo: No 0x00000000 index general undo (branch) operations KTB Redo op: 0x04 ver: 0x01 op: L itl: xid: 0x0008.011.000001c6 uba: 0x00800cf6.02eb.2d flg: C--- lkc: 0 scn: 0x0000.001919a8 Dump kdige : block dba :0x01001bdc, seghdr dba: 0x01001bdb branch block row insert (8): 01 00 ff ff dd 1b 00 01 (12): 0a 41 30 30 30 30 30 30 37 32 37 fe
10.17 - Initialize Branch Block
This operation occurs when a new branch block is initialized
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit)
Consider the following objects:
CREATE TABLE t1 ( c1 VARCHAR2(30), c2 NUMBER ); CREATE INDEX i1 ON t1 (c1);
the statement
DECLARE l_c1 VARCHAR2(30); l_c2 NUMBER; BEGIN FOR i IN 1..50000 LOOP l_c1 := 'A'||LPAD (TO_CHAR (i),29,'0'); l_c2 := i; INSERT INTO t1 VALUES (l_c1,l_c2); END LOOP; END; /
generates the following redo
CHANGE #2 TYP:0 CLS: 1 AFN:4 DBA:0x01001ee7 OBJ:53102 SCN:0x0000.00197f3a SEQ: 2 OP:10.17 index redo (kdxbne): initialize branch block,count=4 KTB Redo op: 0x05 ver: 0x01 op: R itc: 1 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0007.022.000001a7 0x00800e3a.014f.03 -B-- 0 fsc 0x0000.00000000 NEW itl: 1 nco: 2 lev: 1 lmc: 0x1001ce5 new block has 211 rows dumping row index Dump of memory from 0xB6014258 to 0xB6014400 B6014250 00260000 0072004C [..&.L.r.] B6014260 00BE0098 010A00E4 01560130 01A2017C [........0.V.|...] B6014270 01EE01C8 023A0214 02860260 02D202AC [......:.`.......] B6014280 031E02F8 036A0344 03B60390 040203DC [....D.j.........] B6014290 044E0428 049A0474 04E604C0 0532050C [(.N.t.........2.] ....... 60143C0 1ADE1AB8 1B2A1B04 1B761B50 1BC21B9C [......*.P.v.....] B60143D0 1C0E1BE8 1C5A1C34 1CA61C80 1CF21CCC [....4.Z.........] B60143E0 1D3E1D18 1D8A1D64 1DD61DB0 1E221DFC [..>.d.........".] B60143F0 1E6E1E48 1EBA1E94 1F061EE0 1F521F2C [H.n.........,.R.] dumping rows Dump of memory from 0xB6014400 to 0xB60161AC B6014400 01001CE6 3030411E 30303030 30303030 [.....A0000000000] B6014410 30303030 30303030 30303030 30303030 [0000000000000000] B6014420 FE313931 01001CE8 3030411E 30303030 [191......A000000] B6014430 30303030 30303030 30303030 30303030 [0000000000000000] ........ B6016160 FE313137 01001EDB 3030411E 30303030 [711......A000000] B6016170 30303030 30303030 30303030 30303030 [0000000000000000] B6016180 39333030 FE313039 01001EDF 3030411E [0039901......A00] B6016190 30303030 30303030 30303030 30303030 [0000000000000000] B60161A0 30303030 30343030 FE313930 [00000040091.]
index redo (kdxbne): initialize branch block,count=4
kdxbne: operation code 10.17
KTB Redo op: 0x05 ver: 0x01 op: R itc: 1 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0007.022.000001a7 0x00800e3a.014f.03 -B-- 0 fsc 0x0000.00000000
See KTBRedo
NEW itl: 1 nco: 2 lev: 1 lmc: 0x1001ce5
itl ITL slot number (1)
nco Number of columns (2)
lev Level of new block (in this case 1 - root block is level 0)
lmc DBA of left hand leaf block
new block has 211 rows
Number of branch rows in new block
dumping row index
Followed by a hexadecimal dump of row (slot) index
dumping rows
Followed by a hexadecimal dump of row data (branch rows)
10.18 - Update keydata
This operation updates the data in a unique key. The key value is unchanged
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit) and shows a 10.18 operation generated by the update of the data in a bitmap index
Consider the following objects:
CREATE TABLE t1 ( c1 NUMBER, c2 NUMBER ); CREATE BITMAP INDEX i1 ON t1 (c2); INSERT INTO t1 VALUES (0,10); COMMIT;
The statement
UPDATE t1 SET c2 = 11 WHERE c1 = 0;
generates the following index redo:
CHANGE #2 TYP:0 CLS: 1 AFN:4 DBA:0x01000efc OBJ:53205 SCN:0x0000.001b03cf SEQ: 1 OP:10.18 index redo (kdxlup): update keydata, count=2 KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0005.02a.00000231 uba: 0x00800055.019b.2a REDO: SINGLE / -- / -- itl: 2, sno: 0, row size 28
index redo (kdxlup): update keydata, count=2
kdxlup: operation code 10.18
KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0005.02a.00000231 uba: 0x00800055.019b.2a
See KTBRedo
REDO: SINGLE / -- / --
SINGLE row (as opposed to array)
itl: 2, sno: 0, row size 28
itl ITL Slot number (2)
sno Leaf block slot number (0)
row size Row size in bytes (28)
The above statement generated the following undo
CHANGE #1 TYP:0 CLS:26 AFN:2 DBA:0x00800055 OBJ:4294967295 SCN:0x0000.001b03cf SEQ: 1 OP:5.1 ktudb redo: siz: 112 spc: 508 flg: 0x0022 seq: 0x019b rec: 0x2a xid: 0x0005.02a.00000231 ktubu redo: slt: 42 rci: 41 opc: 10.22 objn: 53205 objd: 53205 tsn: 4 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 op: L itl: xid: 0x0004.018.000001e4 uba: 0x0080047f.0121.1b flg: C--- lkc: 0 scn: 0x0000.001b03cb Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x1000efb block=0x01000efc (kdxlup): update keydata in row key :(24): 02 c1 0b 06 00 00 00 00 00 00 06 01 00 0e f4 00 07 06 c0 8c dd 95 e8 05
10.35 - Update non-key value
This operation updates a non-key value in an index leaf
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit) and shows a 10.35 operation generated by an update of a non-key value in an IOT
Consider the following objects:
CREATE TABLE t1 ( c1 NUMBER, c2 NUMBER, CONSTRAINT t1_pk PRIMARY KEY (c1) ) ORGANIZATION INDEX; INSERT INTO t1 VALUES (1,10); COMMIT;
The statement
UPDATE t1 SET c2 = 20 WHERE c1 = 1;
generates the following index redo:
CHANGE #3 TYP:0 CLS: 1 AFN:4 DBA:0x01001df4 OBJ:53111 SCN:0x0000.00198a4e SEQ: 1 OP:10.35 index redo (kdxlcnu): update nonkey, count=4 KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0009.017.000001fd uba: 0x00800067.0199.23 REDO: SINGLE / NONKEY / -- itl: 2, sno: 0, row size 13 ncol: 1 nnew: 1 size: 0 flag: 0x02 nonkey columns updated: col 0: [ 2] c1 15
index redo (kdxlcnu): update nonkey, count=4
kdxlcnu: operation code 10.35
KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0009.017.000001fd uba: 0x00800067.0199.23
See KTBRedo
REDO: SINGLE / NONKEY / --
SINGLE row (as opposed to array)
NONKEY data (as opposed to key data)
itl: 2, sno: 0, row size 13
itl ITL Slot number (2)
sno Leaf block slot number (0)
row size Row size in bytes (13)
ncol: 1 nnew: 1 size: 0 flag: 0x02
ncol Number of columns updated (1)
nnew Number of new columns (1)
size Unknown 0 - Row size includes all columns and row header
flag Unknown (0x02) - may indicate non-key value
nonkey columns updated:
List of nonkey columns follows
col 0: [ 2] c1 15
Column in slot zero. Two byte value Oracle NUMBER C115 is 20
The above statement generated the following undo
CHANGE #2 TYP:0 CLS:34 AFN:2 DBA:0x00800067 OBJ:4294967295 SCN:0x0000.00198a0b SEQ: 1 OP:5.1 ktudb redo: siz: 192 spc: 3806 flg: 0x0012 seq: 0x0199 rec: 0x23 xid: 0x0009.017.000001fd ktubl redo: slt: 23 rci: 0 opc: 10.22 objn: 53111 objd: 53111 tsn: 4 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No 0x00000000 prev ctl uba: 0x00800067.0199.22 prev ctl max cmt scn: 0x0000.00198256 prev tx cmt scn: 0x0000.00198261 txn start scn: 0xffff.ffffffff logon user: 59 prev brb: 8388709 prev bcl: 0 index undo for leaf key operations KTB Redo op: 0x04 ver: 0x01 op: L itl: xid: 0x0007.014.000001aa uba: 0x00800f9f.0152.0f flg: C--- lkc: 0 scn: 0x0000.00198a40 Dump kdilk : itl=2, kdxlkflg=0x91 sdc=0 indexid=0x1001df3 block=0x01001df4 (kdxlcnu): column-vector nonkey update ncol: 1 nnew: 1 size: 0 flag: 0x02 key :(3): 02 c1 02 nonkey columns updated: col 0: [ 2] c1 0b LOGMINER DATA: opcode: UPDATE Number of columns supplementally logged: 0 Flag: SE [ ] NSRCI Objv#: 1 segcol# in Undo starting from 1 segcol# in Redo starting from 2
- Level 11 - Table Operation (DML)
- 11.1 - Undo Table Operation(IUR)
- 11.2 - Insert Row Piece (IRP)
- 11.3 - Delete Row Piece (DRP)
- 11.4 - Select for Update
- 11.5 - Update Row Piece (URP)
- 11.11 - Array Insert
- 11.12 - Array Delete (undo only)
Code | Mnemonic | Description |
11.1 | IUR | Undo |
11.2 | IRP | Insert Row Piece |
11.3 | DRP | Delete Row Piece |
11.4 | LKR | Lock Row |
11.5 | URP | Update Row Piece |
11.6 | ORP | Overflow Row Piece |
11.7 | MFC | |
11.8 | CFA | |
11.9 | CKI | |
11.10 | SKL | |
11.11 | QMI | Insert Row Array |
11.12 | QMD | Delete Row Array |
11.14 | DSC | |
11.16 | LMN | |
11.17 | LLB | |
11.19 | 19 | Update Row Array |
11.20 | SHK | |
11.21 | 21 |
11.1 - Undo table operation
This operation is the undo for 11.2 (IRP), 11.3 (DRP) and 11.5 (URP) (and possibly other level 11 operations)
11.2 - Insert Row Piece (IRP)
This operation inserts a row piece into a table.
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit)
Consider the following code:
CREATE TABLE team ( team_code VARCHAR2(3), team_name VARCHAR2(30), country_code VARCHAR2(3) ); INSERT INTO team VALUES ('MCL','McLaren','GBR'); COMMIT;
Note that an initial row has been inserted into the table to simplify the redo log output
The statement:
INSERT INTO team VALUES ('FER','Ferrari','ITA');
generates the following redo:
CHANGE #1 TYP:2 CLS: 1 AFN:4 DBA:0x01001af0 OBJ:52432 SCN:0x0000.0012223a SEQ: 3 OP:11.2 KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0006.010.000001b1 uba: 0x008004f1.0151.0b KDO Op code: IRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x01001af0 hdba: 0x01001aeb itli: 2 ispac: 0 maxfr: 4858 tabn: 0 slot: 1(0x1) size/delt: 19 fb: --H-FL-- lb: 0x2 cc: 3 null: --- col 0: [ 3] 46 45 52 col 1: [ 7] 46 65 72 72 61 72 69 col 2: [ 3] 49 54 41
KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0006.010.000001b1 uba: 0x008004f1.0151.0b
See KTBRedo
KDO Op code: IRP row dependencies Disabled
IRP Operation code: Insert Row Piece
row dependencies specifies whether row dependencies are disabled (default) or enabled for this table
xtype: XA flags: 0x00000000 bdba: 0x01001af0 hdba: 0x01001aeb
xtype transaction type. Can be XA, XR, CR or KDO_KDOM2
flags awaiting further information
bdba block DBA. Data block address of this block
hdba header DBA. Probably data block address of extent header
itli: 2 ispac: 0 maxfr: 4858
itli specifes the ITL slot number of the transaction performing the operation. In this example the row is locked by the transaction in the second ITL slot.
ispac awaiting further information
maxfr awaiting further information
tabn: 0 slot: 1(0x1) size/delt: 19
tabn specifies the table number. For non-clustered tables this will always be 0
slot specifies the slot number. Each block has an variable length array of slots. Each element in this array specifies the location of a row within the block. The first slot in the block is 0. In this example the row has been written to the second slot in the table (slot 1)
size/delt: specifies the change in size of the block. In this example, the size has increased by 19 bytes which consists of the following:
- 3 bytes for the row header
- 4 bytes for column 0 (1 length byte and 3 data bytes)
- 8 bytes for column 1 (1 length byte and 7 data bytes)
- 4 bytes for column 2 (1 length byte and 3 data bytes)
fb: --H-FL-- lb: 0x2 cc: 3
fb is the flag byte which contains eight bit flags with the following values:
|
lb is the lock byte which specifies the transaction in the ITL table that is currently locking this row. In this example the row is locked by the transaction in the second ITL slot.
cc is the column count of the number of columns being inserted. In this example 3 column values are being inserted into the table.
col 0: [ 3] 46 45 52
Column 0 is the first column in the row. In this case the 3 byte VARCHAR2 value is 'FER'
col 1: [ 7] 46 65 72 72 61 72 69
Column 1 is the second column in the row. In this case the 7 byte VARCHAR2 value is 'FERRARI'
col 2: [ 3] 49 54 41
Column 2 is the third column in the row. In this case the 3 byte VARCHAR2 value is 'ITA' (Italy)
The above statement generated the following undo:
CHANGE #4 TYP:0 CLS:28 AFN:2 DBA:0x008004f1 OBJ:4294967295 SCN:0x0000.00122119 SEQ: 1 OP:5.1 ktudb redo: siz: 108 spc: 6734 flg: 0x0012 seq: 0x0151 rec: 0x0b xid: 0x0006.010.000001b1 ktubl redo: slt: 16 rci: 0 opc: 11.1 objn: 52432 objd: 52432 tsn: 4 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No 0x00000000 prev ctl uba: 0x008004f1.0151.0a prev ctl max cmt scn: 0x0000.00121033 prev tx cmt scn: 0x0000.00121034 txn start scn: 0x0000.00000000 logon user: 59 prev brb: 0 prev bcl: 0 KDO undo record: KTB Redo op: 0x03 ver: 0x01 op: Z KDO Op code: DRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x01001af0 hdba: 0x01001aeb itli: 2 ispac: 0 maxfr: 4858 tabn: 0 slot: 1(0x1)
11.3 - Delete Row Piece (DRP)
This operation deletes a row piece from a table
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit)
Consider the following code:
CREATE TABLE team ( team_code VARCHAR2(3), team_name VARCHAR2(30), country_code VARCHAR2(3) ); INSERT INTO team VALUES ('MCL','McLaren','GBR'); INSERT INTO team VALUES ('FER','Ferrari','ITA'); COMMIT;
The statement:
DELETE FROM team WHERE team_code = 'FER';
generates the following redo:
CHANGE #1 TYP:2 CLS: 1 AFN:4 DBA:0x01001af0 OBJ:52432 SCN:0x0000.00123482 SEQ: 3 OP:11.3 KTB Redo op: 0x11 ver: 0x01 op: F xid: 0x0008.014.00000172 uba: 0x00800025.02bc.2a Block cleanout record, scn: 0x0000.0012348c ver: 0x01 opt: 0x02, entries follow... itli: 1 flg: 2 scn: 0x0000.00123482 itli: 2 flg: 2 scn: 0x0000.00123473 KDO Op code: DRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x01001af0 hdba: 0x01001aeb itli: 2 ispac: 0 maxfr: 4858 tabn: 0 slot: 1(0x1)
KTB Redo op: 0x011 ver: 0x01 op: F xid: 0x0008.014.00000172 uba: 0x00800025.02bc.2a
See KTBRedo
Block cleanout record, scn: 0x0000.0012348c ver: 0x01 opt: 0x02, entries follow...
If the ITL has not been updated out since the last transaction changes where committed or rolled back then a block cleanout is performed
scn System change number of block cleanout. Format is wrap#.base#
ver Version number
opt Option number
itli: 1 flg: 2 scn: 0x0000.00123482
First ITL element being cleaned out.
itli ITL element number (1 based)
flg Flag
scn System Change Number of entry
itli: 2 flg: 2 scn: 0x0000.00123473
Second ITL element being cleaned out.
KDO Op code: DRP row dependencies Disabled
DRP Operation code: Dnsert Row Piece
row dependencies specifies whether row dependencies are disabled (default) or enabled for this table
xtype: XA flags: 0x00000000 bdba: 0x01001af0 hdba: 0x01001aeb
xtype transaction type. Can be XA, XR, CR or KDO_KDOM2
flags awaiting further information
bdba block DBA. Data block address of this block
hdba header DBA. Probably data block address of extent header
itli: 2 ispac: 0 maxfr: 4858
itli specifes the ITL slot number of the transaction performing the operation. In this example the row is locked by the transaction in the second ITL slot.
ispac awaiting further information
maxfr awaiting further information
tabn: 0 slot: 1(0x1)
tabn specifies the table number. For non-clustered tables this will always be 0
slot specifies the slot number. Each block has an variable length array of slots. Each element in this array specifies the location of a row within the block. The first slot in the block is 0. In this example the row was written to the second slot in the table (slot 1)
The above statement generated the following undo:
CHANGE #4 TYP:0 CLS:32 AFN:2 DBA:0x00800025 OBJ:4294967295 SCN:0x0000.001233e7 SEQ: 1 OP:5.1 ktudb redo: siz: 188 spc: 1568 flg: 0x0012 seq: 0x02bc rec: 0x2a xid: 0x0008.014.00000172 ktubl redo: slt: 20 rci: 0 opc: 11.1 objn: 52432 objd: 52432 tsn: 4 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No 0x00000000 prev ctl uba: 0x00800025.02bc.29 prev ctl max cmt scn: 0x0000.0012205e prev tx cmt scn: 0x0000.00122072 txn start scn: 0x0000.00000000 logon user: 59 prev brb: 8388625 prev bcl: 0 KDO undo record: KTB Redo op: 0x04 ver: 0x01 op: L itl: xid: 0x0007.00f.0000013a uba: 0x0080012d.012a.1c flg: C--- lkc: 0 scn: 0x0000.00123473 KDO Op code: IRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x01001af0 hdba: 0x01001aeb itli: 2 ispac: 0 maxfr: 4858 tabn: 0 slot: 1(0x1) size/delt: 19 fb: --H-FL-- lb: 0x0 cc: 3 null: --- col 0: [ 3] 46 45 52 col 1: [ 7] 46 65 72 72 61 72 69 col 2: [ 3] 49 54 41
11.4 - Select for Update
This operation locks a row to prevent updates by other transactions
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit)
Consider the following code:
CREATE TABLE team ( team_code VARCHAR2(3), team_name VARCHAR2(30), country_code VARCHAR2(3) ); INSERT INTO team VALUES ('MCL','McLaren','GBR'); INSERT INTO team VALUES ('FER','Ferrari','ITA'); COMMIT;
The statement:
SELECT * FROM team WHERE team_code = 'FER' FOR UPDATE;
generates the following redo:
CHANGE #1 TYP:2 CLS: 1 AFN:4 DBA:0x01001af0 OBJ:52432 SCN:0x0000.00123aab SEQ: 2 OP:11.4 KTB Redo op: 0x11 ver: 0x01 op: F xid: 0x0004.01e.00000145 uba: 0x00800725.0105.0e Block cleanout record, scn: 0x0000.00123b5e ver: 0x01 opt: 0x02, entries follow... itli: 2 flg: 2 scn: 0x0000.00123aab KDO Op code: LKR row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x01001af0 hdba: 0x01001aeb itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 1 to: 1
KTB Redo op: 0x011 ver: 0x01 op: F xid: 0x0004.01e.00000145 uba: 0x00800725.0105.0e
See KTBRedo
Block cleanout record, scn: 0x0000.00123b5e ver: 0x01 opt: 0x02, entries follow...
If the ITL has not been updated out since the last transaction changes where committed or rolled back then a block cleanout is performed
scn System change number of block cleanout. Format is wrap#.base#
ver Version number
opt Option number
itli: 2 flg: 2 scn: 0x0000.00123aab
First ITL element being cleaned out - slot 2
itli ITL element number (1 based)
flg Flag
scn System Change Number of entry
KDO Op code: LKR row dependencies Disabled
LKR Operation code: Lock Row
row dependencies specifies whether row dependencies are disabled (default) or enabled for this table
xtype: XA flags: 0x00000000 bdba: 0x01001af0 hdba: 0x01001aeb
xtype transaction type. Can be XA, XR, CR or KDO_KDOM2
flags awaiting further information
bdba block DBA. Data block address of this block
hdba header DBA. Probably data block address of extent header
itli: 1 ispac: 0 maxfr: 4858
tli specifes the ITL slot number of the transaction performing the operation. In this example the row is locked by the transaction in the second ITL slot.
ispac awaiting further information
maxfr awaiting further information
tabn: 0 slot: 1 to: 1
abn specifies the table number. For non-clustered tables this will always be 0
slot specifies the slot number. Each block has an variable length array of slots. Each element in this array specifies the location of a row within the block. The first slot in the block is 0. In this example the row was written to the second slot in the table (slot 1)
to specifies whether the lock is being set (1) or released (0)
The above statement generated the following undo:
CHANGE #4 TYP:0 CLS:24 AFN:2 DBA:0x00800725 OBJ:4294967295 SCN:0x0000.00123936 SEQ: 1 OP:5.1 ktudb redo: siz: 132 spc: 4892 flg: 0x0012 seq: 0x0105 rec: 0x0e xid: 0x0004.01e.00000145 ktubl redo: slt: 30 rci: 0 opc: 11.1 objn: 52432 objd: 52432 tsn: 4 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No 0x00000000 prev ctl uba: 0x00800725.0105.0d prev ctl max cmt scn: 0x0000.00122231 prev tx cmt scn: 0x0000.00122236 txn start scn: 0x0000.00000000 logon user: 59 prev brb: 8388667 prev bcl: 0 KDO undo record: KTB Redo op: 0x04 ver: 0x01 op: L itl: xid: 0x0001.003.00000155 uba: 0x00800688.016a.05 flg: C--- lkc: 0 scn: 0x0000.00123a9a KDO Op code: LKR row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x01001af0 hdba: 0x01001aeb itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 1 to: 0
11.5 - Update Row Piece (URP)
This operation updates a row piece in a table
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit)
Consider the following code:
CREATE TABLE team ( team_code VARCHAR2(3), team_name VARCHAR2(30), country_code VARCHAR2(3) ); CREATE UNIQUE INDEX team_pk ON team (team_code); INSERT INTO team VALUES ('MCL','McLaren','GBR'); INSERT INTO team VALUES ('FER','Ferrari','ITA'); INSERT INTO team VALUES ('RBR','Red Bull','RBR'); COMMIT;
Note that if there is not a unique index on this table, in Oracle 10.2.0.4 (at least) an 11.19 operation will be generated instead of a 11.5 operation.
The statement:
UPDATE team SET country_code = 'OST' WHERE team_code = 'RBR';
generates the following redo:
CHANGE #1 TYP:2 CLS: 1 AFN:4 DBA:0x01001af0 OBJ:52432 SCN:0x0000.00124511 SEQ: 1 OP:11.5 KTB Redo op: 0x11 ver: 0x01 op: F xid: 0x0001.012.00000154 uba: 0x00800687.016a.0e Block cleanout record, scn: 0x0000.00124514 ver: 0x01 opt: 0x02, entries follow... itli: 2 flg: 2 scn: 0x0000.00124511 KDO Op code: URP row dependencies Disabled xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01001af0 hdba: 0x01001aeb itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 5(0x5) flag: 0x2c lock: 1 ckix: 191 ncol: 3 nnew: 1 size: 0 Vector content: col 2: [ 3] 4f 53 54
KTB Redo op: 0x011 ver: 0x01 op: F xid: 0x0001.012.00000154 uba: 0x00800687.016a.0e
See KTBRedo
Block cleanout record, scn: 0x0000.00124514 ver: 0x01 opt: 0x02, entries follow...
If the ITL has not been updated out since the last transaction changes where committed or rolled back then a block cleanout is performed
scn System change number of block cleanout. Format is wrap#.base#
ver Version number
opt Option number
itli: 2 flg: 2 scn: 0x0000.00124511
First ITL element being cleaned out - slot 2
itli ITL element number (1 based)
flg Flag
scn System Change Number of entry
KDO Op code: URP row dependencies Disabled
URP Operation code: Update Row Piece
row dependencies specifies whether row dependencies are disabled (default) or enabled for this table
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01001af0 hdba: 0x01001aeb
xtype transaction type. Can be XA, XR, CR or KDO_KDOM2. In this case the transaction type is KDO_KDOM2. This structure can potentially store an array of change vectors for an object.
flags awaiting further information
bdba block DBA. Data block address of this block
hdba header DBA. Probably data block address of extent header
itli: 1 ispac: 0 maxfr: 4858
tli specifes the ITL slot number of the transaction performing the operation. In this example the row is locked by the transaction in the first ITL slot.
ispac awaiting further information
maxfr awaiting further information
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 1 ckix: 191
abn specifies the table number. For non-clustered tables this will always be 0
slot specifies the slot number. Each block has an variable length array of slots. Each element in this array specifies the location of a row within the block. The first slot in the block is 0. In this example the row was written to the second slot in the table (slot 1)
flag awaiting further information
lock probably set to 1 indicating a lock has been taken on the row header
ckix awaiting further information
ncol: 3 nnew: 1 size: 0
ncol Number of columns in row piece
nnew Number of changed columns in row piece
size (Probably) change in size of row piece
Vector content:
Vector content: List of updated column values
col 2: [ 3] 4f 53 54
Update of column 2 (country_code) to 'OST'
The above statement generated the following undo:
CHANGE #4 TYP:0 CLS:18 AFN:2 DBA:0x00800687 OBJ:4294967295 SCN:0x0000.00124487 SEQ: 1 OP:5.1 ktudb redo: siz: 152 spc: 6280 flg: 0x0012 seq: 0x016a rec: 0x0e xid: 0x0001.012.00000154 ktubl redo: slt: 18 rci: 0 opc: 11.1 objn: 52432 objd: 52432 tsn: 4 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No 0x00000000 prev ctl uba: 0x00800687.016a.0d prev ctl max cmt scn: 0x0000.00122c72 prev tx cmt scn: 0x0000.00122c86 txn start scn: 0x0000.00124511 logon user: 59 prev brb: 8390264 prev bcl: 0 KDO undo record: KTB Redo op: 0x04 ver: 0x01 op: L itl: xid: 0x0009.005.00000195 uba: 0x0080019a.0187.19 flg: C--- lkc: 0 scn: 0x0000.00124473 KDO Op code: URP row dependencies Disabled xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01001af0 hdba: 0x01001aeb itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 191 ncol: 3 nnew: 1 size: 0 Vector content: col 2: [ 3] 47 42 52
11.11 - Array Insert
This operation inserts a set of rows into a table
The following example was developed in Oracle 10.2.0.4 (Linux 32-bit)
Consider the following code:
CREATE TABLE team ( team_code VARCHAR2(3), team_name VARCHAR2(30), country_code VARCHAR2(3) ); INSERT INTO team VALUES ('MCL','McLaren','GBR'); INSERT INTO team VALUES ('FER','Ferrari','ITA'); COMMIT; CREATE TABLE team2 ( team_code VARCHAR2(3), team_name VARCHAR2(30), country_code VARCHAR2(3) ); INSERT INTO team2 VALUES ('BMW','BMW','GER'); INSERT INTO team2 VALUES ('WIL','Williams','GBR'); INSERT INTO team2 VALUES ('REN','Renault','REN'); COMMIT;
The statement:
INSERT INTO team SELECT * FROM team2;
generates the following redo:
CHANGE #3 TYP:2 CLS: 1 AFN:4 DBA:0x01001b14 OBJ:52798 SCN:0x0000.00157cf0 SEQ: 1 OP:11.11 KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0007.01c.0000014c uba: 0x00800fc7.012f.14 KDO Op code: QMI row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x01001b14 hdba: 0x01001b13 itli: 2 ispac: 0 maxfr: 4858 tabn: 0 lock: 2 nrow: 3 slot[0]: 2 tl: 15 fb: --H-FL-- lb: 0x0 cc: 3 col 0: [ 3] 42 4d 57 col 1: [ 3] 42 4d 57 col 2: [ 3] 47 45 52 slot[1]: 3 tl: 20 fb: --H-FL-- lb: 0x0 cc: 3 col 0: [ 3] 57 49 4c col 1: [ 8] 57 69 6c 6c 69 61 6d 73 col 2: [ 3] 47 42 52 slot[2]: 4 tl: 19 fb: --H-FL-- lb: 0x0 cc: 3 col 0: [ 3] 52 45 4e col 1: [ 7] 52 65 6e 61 75 6c 74 col 2: [ 3] 52 45 4e
KTB Redo op: 0x01 ver: 0x01 op: F xid: 0x0007.01c.0000014c uba: 0x00800fc7.012f.14
See KTBRedo
KDO Op code: QMI row dependencies Disabled
QMI Operation code: Insert array of rows
row dependencies specifies whether row dependencies are disabled (default) or enabled for this table
xtype: XA flags: 0x00000000 bdba: 0x01001b14 hdba: 0x01001b13
xtype transaction type. Can be XA, XR, CR or KDO_KDOM2
flags awaiting further information
bdba block DBA. Data block address of this block
hdba header DBA. Probably data block address of extent header
itli: 2 ispac: 0 maxfr: 4858
itli specifes the ITL slot number of the transaction performing the operation. In this example the row is locked by the transaction in the second ITL slot.
ispac awaiting further information
maxfr awaiting further information
tabn: 0 lock: 2 nrow: 3
tabn specifies the table number. For non-clustered tables this will always be 0
lock possibly ITL slot holding the lock
nrow number of rows
slot[0]: 2
first row
tl: 15 fb: --H-FL-- lb: 0x0 cc: 3
tl total length (15 bytes = 3 bytes header + 3 length bytes + 9 data bytes)
fb flag byte
lb lock byte - rows locked in table header on this block only
cc column count
col 0: [ 3] 42 4d 57
Row 0 Column 0 - 3 bytes - BMW
col 1: [ 3] 42 4d 57
Row 0 Column 1 - 3 bytes - BMW
col 2: [ 3] 47 45 52
Row 0 Column 2 - 3 bytes - GER
slot[1]: 3
second row
tl: 20 fb: --H-FL-- lb: 0x0 cc: 3
tl total length (20 bytes = 3 bytes header + 3 length bytes + 14 data bytes)
fb flag byte
lb lock byte - rows locked in table header on this block only
cc column count
col 0: [ 3] 57 49 4c
Row 1 Column 0 - 3 bytes - WIL
col 1: [ 8] 57 69 6c 6c 69 61 6d 73
Row 1 Column 1 - 8 bytes - Williams
col 2: [ 3] 47 42 52
Row 1 Column 2 - 3 bytes - GBR
slot[2]: 4
third row
tl: 19 fb: --H-FL-- lb: 0x0 cc: 3
tl total length (19 bytes = 3 bytes header + 3 length bytes + 13 data bytes)
fb flag byte
lb lock byte - rows locked in table header on this block only
cc column count
col 0: [ 3] 52 45 4e
Row 2 Column 0 - 3 bytes - REN
col 1: [ 7] 52 65 6e 61 75 6c 74
Row 2 Column 1 - 7 bytes - Renault
col 2: [ 3] 52 45 4e
Row 2 Column 2 - 3 bytes - FRA
The above statement generated the following undo:
CHANGE #2 TYP:0 CLS:30 AFN:2 DBA:0x00800fc7 OBJ:4294967295 SCN:0x0000.00157d25 SEQ: 1 OP:5.1 ktudb redo: siz: 140 spc: 5916 flg: 0x0012 seq: 0x012f rec: 0x14 xid: 0x0007.01c.0000014c ktubl redo: slt: 28 rci: 0 opc: 11.1 objn: 52798 objd: 52798 tsn: 4 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No 0x00000000 prev ctl uba: 0x00800fc7.012f.05 prev ctl max cmt scn: 0x0000.0015760e prev tx cmt scn: 0x0000.00157655 txn start scn: 0xffff.ffffffff logon user: 59 prev brb: 8392639 prev bcl: 0 KDO undo record: KTB Redo op: 0x03 ver: 0x01 op: Z KDO Op code: QMD row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x01001b14 hdba: 0x01001b13 itli: 2 ispac: 0 maxfr: 4858 tabn: 0 lock: 0 nrow: 3 slot[0]: 2 slot[1]: 3 slot[2]: 4
11.12 - Array Delete (undo only)
This operation deletes a set of rows from a table. Apparently this operation is only called by undo as the inverse operation to the 11.11 Array Insert operation.
- Level 13 - Block Allocation
- Level 14 - Extent Allocation
- Level 17 - Backup Management
- Level 18 - Online Backup
- Level 19 - Direct Load
- Level 20 - Transaction Metadata (LogMiner)
- Level 22 - Space Management (ASSM)
- Level 23 - Block Write (DBWR)
- Level 24 - DDL Statement
Log File Dumps
Symbolic dumps can be created for both online redo logs and archived redo logs using the following syntax:
ALTER SYSTEM DUMP LOGFILE '<filename>';
For online redo logs the filename of the current redo log can be obtained using the following SQL:
SELECT member FROM v$logfile WHERE group# = ( SELECT group# FROM v$log WHERE status = 'CURRENT' );