Oracle Internals - Redo

最新推荐文章于 2023-11-01 09:02:11 发布

palm_civet

最新推荐文章于 2023-11-01 09:02:11 发布

阅读量3.5k

点赞数

分类专栏： Oracle 文章标签： oracle dependencies insert branch header table

Oracle 专栏收录该内容

56 篇文章 0 订阅

订阅专栏

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.

Mnemonic	Value	Description
KCRVOID	0	The contents are not valid
KCRVALID	1	Includes change vectors
KCRDEPND	2	Includes commit SCN
KCRVOID	4	Includes dependent SCN
KCRNMARK	8	New SCN mark record. SCN allocated exactly at this point in the redo log by this instance
KCROMARK	16	Old SCN mark record. SCN allocated at or before this point in the redo. May be allocated by another instance
KCRORDER	32	New SCN was allocated to ensure redo for some block would be ordered by inc/seq# when redo sorted by SCN

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:

Level	Description
4	Block Cleanout
5	Transaction Layer (Undo)
10	Index Operation
11	Table Operation (DML)
13	Block Allocation
14	Extent Allocation
17	Backup Management
18	Online Backup
19	Direct Load
20	Transaction Metadata (LogMiner)
22	Space Management (ASSM)
23	Block Write (DBWR)
24	DDL Statement

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:

Flag	Description
K	Cluster key
C	Clustered row
H	Head piece of row
D	Deleted row
F	First piece in row
L	Last piece in row
P	Previous row piece exists
N	Next row piece exists

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'
  );

palm_civet

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle Internals - Redo

RedoAll 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 onli
复制链接

扫一扫