Oracle Scratchpad

July 25, 2017

Redo OP Codes:

Filed under: Infrastructure,Oracle,redo — Jonathan Lewis @ 6:17 pm BST Jul 25,2017

This posting was prompted by a tweet from Kamil Stawiarski in response to a question about how he’d discovered the meaning of Redo Op Codes 5.1 and 11.6 – and credited me and Julian Dyke with “the hardest part”.

Over the years I’ve accumulated (from Julian Dyke, or odd MoS notes, etc.) and let dribble out the occasional interpretation of a few op codes – typically in response to a question on the OTN database forum or the Oracle-L listserver, and sometimes as a throwaway comment in a blog post, but I’ve never published the full set of codes that I’ve acquired (or guessed) to date.

It’s been some time since I’ve looked closely at a redo stream, and there are many features of Oracle that I’ve never had to look at at the level of the redo so there are plenty of gaps in the list – and maybe a few people will use the comments to help fill the gaps.

It’s possible that I may be able to add more op codes over the new days – I know that somewhere I have some op codes relating to space management, and a batch relating to LOB handling, but it looks like I forgot to add them to the master list – so here’s what I can offer so far:

1       Transaction Control

2       Transaction read

3       Transaction update

4       Block cleanout
                4.1     Block cleanout record
                4.2     Physical cleanout
                4.3     Single array change
                4.4     Multiple array changes
                4.5     Format block
                4.6     ktbcc redo -  Commit Time Block Cleanout Change

5       Transaction undo management
                5.1     ktudb redo (undo block redo)
                5.2     ktudh redo (undo header redo)
                5.3     Rollout a transaction begin
                5.4     ktucm redo (change transaction slot state (commit/rollback))
                5.5     Create rollback segmenr
                5.6     ktubu redo: mark undo record "user undo done"
                5.7     Restore transaction table slot.
                        In the ktubl for 'dbms_transaction.local_transaction_id'
                        (begin transaction) - also arrives for incoming distributed
                        tx, no data change but TT slot acquired. Also for recursive
                        transaction (e.g. truncate). txn start scn:  0xffff.ffffffff
                        Also prior to securefile LOB insertion.
                5.8     Mark transaction as dead
                5.9     Rollback extension of rollback seg
                5.10    Rollback segment header change for extension of rollback seg
                5.11    Set current undo block (uba) in undo header during rollback
                5.19    Transaction audit record - first
                5.20    Transaction audit record - subsequent
                5.23    ktudbr redo: disable block level recovery (reports XID)
                5.24    ktfbhundo - File Space Header Undo
                5.28    ktuuntp - ??

6       Control file

10      Index
                10.1    SQL load index block
                10.2    Insert Leaf Row
                10.3    Purge Leaf Row
                10.4    Delete Leaf Row
                10.5    Restore Leaf Row during rollback
                10.6    (kdxlok) Lock block (pre-split)
                10.7    (kdxulo) unlock block (clear block opcode during rollback (?or commit?))
                10.8    (kdxlne) initialize (and load) newly allocated leaf block
                10.9    (kdxair) apply XAT do to ITL 1  -- (service ITL) related to leaf block split
                10.10   Set leaf block next pointer
                10.11   (kdxlpr) set kdxleprv / kdxlenxt (previous / next leaf pointer)
                10.12   Initialize root block after split
                10.13   index redo (kdxlem): (REDO) make leaf block empty,
                10.14   Restore block before image
                10.15   (kdxbin) Insert branch block row
                10.16   Purge branch row
                10.17   Initialize new branch block
                10.18   Update key data in row -- index redo (kdxlup): update keydata
                10.19   Clear split flag
                10.20   Set split flag
                10.21   index general undo (branch) operations
                10.22   Undo leaf operation
                10.23   restore block to tree
                10.24   Shrink ITL
                10.25   format root block
                10.26   format root block (undo)
                10.27   format root block (redo)
                10.28   Migrating block (undo)
                10.29   Migrating block (redo)
                10.30   Update nonkey value
                10.31   index root block redo (kdxdlr):  create/load index
                10.34   make block empty
                10.35   index redo (kdxlcnu): update nonkey
                10.37   undo index change (kdxIndexlogicalNonkeyUpdate) -- bitmap index
                10.38   index change (kdxIndexlogicalNonkeyUpdate) -- bitmap index
                10.39   index redo (kdxbur) :  branch block update range
                10.40   index redo (kdxbdu) :  branch block DBA update,

11      Table
                11.1    undo row operation
                11.2    IRP insert row  piece
                11.3    DRP delete row piece
                11.4    lock row piece
                11.5    URP update row piece
                11.6    overwrite row piece
                11.7    manipulate first column
                11.8    change forwarding address - migration
                11.9    change cluster key index
                11.10   Set Cluster key pointers
                11.11   Insert multiple rows
                11.12   Delete multiple rows
                11.13   toggle block header flags
                11.14   [M] KDODSC
                11.15   [M] KDOMBC
                11.16   [M] Logminer support - RM for rowpiece with only logminer columns - KDOLMN
                11.17   Update multiple rows
                11.18   [M] Logminer support - RM for LOB operation errors - KDOLBE
                11.19   Array update ?
                11.20   SHK (mark as shrunk?)
                11.21   [M] Logminer support - KDOURP2
                11.22   [M] Logminer support - KDOCMP
                11.23   [M] Logminer support - KDODCU
                11.24   HCC update rowid map ?
                11.25   [M] Logminer support - KDOAIR
                11.28   "CMP2" Row Store Compress - partial compression ?

12      Cluster

13      Segment management
                13.1    ktsfm redo: -- allocate space ??
                13.5    KTSFRBFMT (block format) redo
                13.6    (block link modify) (? index )  (options: lock clear, lock set)
                13.7    KTSFRGRP (fgb/shdr modify freelist) redo: (options unlink block, move HWM)
                13.13   ktsbbu undo - undo operation on bitmap block
                13.14   ktsbbu undo - undo operation on bitmap block
                13.17   ktsphfredo - Format Pagetable Segment Header
                13.18   ktspffredo - Format Level 1 Bitmap Block
                13.19   ktspsfredo - Format Level 2 Bitmap Block
                13.21   ktspbfredo - Format Pagetable Datablock
                13.22   State change on level 1 bitmap block
                13.23   Undo on level 1 bitmap block
                13.24   State change on level 2 bitmap block
                13.25   Undo on level 2 bitmap block
                13.26   State change on level 3 bitmap block
                13.27   Undo on level 3 bitmap block
                13.28   Update LHWM and HHWM on segment header
                13.29   Undo on segment header
                13.31   Segment shrink redo for L1 bitmap block
                13.32   Segment shrink redo for segment header block
                13.36   Segment shrink redo for L2 bitmap block
                13.41   KTSL - Format LOB Extent Header Block (EHB)
                13.42   KTSL - Format LOB Persisten Undo Block (PUB)
                13.43   KTSL - Format LOB Hash Bucket Block (UFS)  -- uncommitted free space
                13.44   KTSL - Format LOB Free Space Block (FSB)   -- also CFS, committed free space
                13.45   KTSL - Format LOB Segment Header Block (SHB)
                13.49   KTSL - HBB redo record
                13.50   KTSL - LOB Hash Bucket Block (HBB) undo
                13.51   KTSL - FSB redo record (Free Space Block)
                13.52   KTSL - LOB Free Space Block (FSB) undo record:
                13.53   KTSL - PUA redo record: (Persistent Undo Amend ?)
                13.54   KTSL - LOB Persistent Undo Block (PUB) undo record
                13.55   KTSL - Segment Header (SHB) redo record
                13.56   KTSL - LOB Segment Header Block (SHB) undo record

14      Extent management
                14.1    ktecush redo: clear extent control lock
                14.2    ktelk redo - lock extent (map)
                14.3    Extent de-allocate
                14.4    kteop redo - redo operation on extent map
                14.5    kteopu undo - undo operation on extent map
                14.6    kteopf redo - format an extent map block
                14.8    kteoputrn - undo operation for flush for truncate
                14.12   kteuUpdredo redo - Propagate Extent commit timetmep

15      Tablespace

16      Row cache

17      Recovery management
                17.1    End backup mode marker
                17.3    Crash Recovery at scn:  0x0000.02429111
                17.28   STANDBY METADATA CACHE INVALIDATION (after commit on sys table update)
                17.51   RC Invalidation Marker

18      Block image (hot backups)
                18.1    Block image
                18.3    Object / Range reuse
                               (Range reuse:  pdb = 3 tsn=7 base=0x04402380 nblks=128)
                               (Object reuse: tsn=2 objd=76515)

19      Direct loader
                19.1    Direct Loader block redo entry (for logged direct path writes)
                19.2    Direct Loader invalidate block range redo entry (for nologging direct path writes)
                19.3    Direct block relogging - KCBLCRLB
                19.4    Invalidate range relogging - KCBLCRIR

20      Compatibility segment (?)
                20.24   Media Recovery Marker - related to LOB load (KDLI)

21      LOB segment
                21.1    kdlop (Long Feild) redo:  [sic]
                                (insert basicfile clob)

22      Locally managed tablespace
                22.2    ktfbhredo - File Space Header Redo:
                22.3    ktfbhundo - File Space Header Undo:
                22.5    ktfbbredo - File BitMap Block Redo:
                22.16   File Property Map Block (FPM)

23      Block writes
                23.1    MEDIA RECOVERY MARKER (Block written record 12c)
                23.2    Block read record (BRR) - written when db_lost_write_protect enabled
                23.3    MEDIA RECOVERY MARKER (Block written record 19c)

24      DDL statements
                24.1    MEDIA RECOVERY MARKER - DDL
                24.2    Direct load block end mark
                24.4    Media recovery marker
                24.10   ??
                24.11   ??

25      Queues

26      LOB
                26.4    Invalidate LOB (?) (KDLI) 
                26.6    Load LOB (KDLI)

27      Block Change Tracking

(E & O.E) – you’ll notice that some of the descriptions include question marks – those are guesses – and some are little more than the raw text extracted from a redo change vector with no interpretation of what they might mean. Some are preceded by [M], these are copied directly from the “askmaclean” list mentioned in the update below.


It didn’t take long for someone to email me a much longer list that has been published elsewhere on the Internet. The results don’t have the hierarchical style display I have above, so I may copy the extra entries into the list above when I get a little time.


  1. Nice!!!
    Thank you for that! :)

    I also have:
    26.6 – add or modify a lob segment (always in 8060 bytes chunks, so one LOB can have many redo records) Of course only for out-of-row storage

    5.4 – I’ve noticed, that it’s used only for COMMIT in 12.1 (but of course rollback is like applying reverse operations and then – commiting them ;)) Funny thing is, that there is no direct XID number in this change vector – you have to calculate it from CLS (next byte after opcode), which will give you XIDUSN = (u_short) ((cls-17)/2+1), XIDSLT (30 bytes from opcode is an array_size byte and you have to jump 30+array_size to get it – sometimes also +2 if the array_size is not even) and XIDSQN, which is 4 bytes from XIDSLT. All together gives the XID of the committed transaction.

    5.6 and 5.11 – used with rollback; each row of an object get’s its own rollback record with partial XID information (XIDSLT and XIDSQN)


    Comment by Kamil Stawiarski (@ora600pl) — July 25, 2017 @ 7:06 pm BST Jul 25,2017 | Reply

  2. Thanks for the link.
    Honestly the link
    is called: Layer and corresponding Operation codes description
    and it is well layer-ed, and much more rich.

    Comment by opnion — August 21, 2017 @ 7:16 am BST Aug 21,2017 | Reply

    • opnion.

      That list is the one I linked to in the update at the end of the note.

      There is a (minor) drawback to that list – at least in my browser – it isn’t easy to spot the lines which separate the list by layers, and the opcodes don’t include the layer code so you can’t just search for the (e.g.) 10.27. As far as content is concerned it is, as I pointed out, a much longer list.

      Comment by Jonathan Lewis — August 24, 2017 @ 2:56 pm BST Aug 24,2017 | Reply

  3. […] Each redo record consists of one or more change vectors and each change vector has it’s own op code. Check out this article by Jonathan Lewis: […]

    Pingback by rollback internals | Ora-600 — October 2, 2017 @ 8:43 pm BST Oct 2,2017 | Reply

  4. […] the first part of the test most of the redo entries consisted of a pair of redo change vectors with OP codes 5.1 (modify undo block) and 11.5 (update row piece). The 5.1 corresponded to the undo needed to […]

    Pingback by 255 Columns | Oracle Scratchpad — February 28, 2018 @ 12:28 pm GMT Feb 28,2018 | Reply

  5. […] the lines showing the redo change record length (LEN:) and the redo change vector op codes (OP:). Here’s the output, with a little cosmetic modification, for the 90-10 […]

    Pingback by Index splits | Oracle Scratchpad — November 6, 2018 @ 8:53 am GMT Nov 6,2018 | Reply

  6. […] numbers may vary slightly if you try to reproduce the example), and here’s a summary of the redo OP codes that showed up in those change vectors in order of […]

    Pingback by Truncate | Oracle Scratchpad — November 27, 2018 @ 8:56 pm GMT Nov 27,2018 | Reply

  7. […] records that contain change vectors of type BRR (block read records) which is what the layer 23 opcode 2 line is […]

    Pingback by Redo Dumps | Oracle Scratchpad — June 11, 2019 @ 12:53 pm BST Jun 11,2019 | Reply

  8. […] containing “OP:”, stripping them down to just the “OP:” followed by the redo op code, and then sorting and summarising the use of op codes. Here (with some annotations) is what I […]

    Pingback by Update restarts | Oracle Scratchpad — September 10, 2019 @ 11:00 am BST Sep 10,2019 | Reply

  9. […] terms of redo op codes this is done through “layer 19”, the set of op codes relating to direct path loads, […]

    Pingback by Nologging | Oracle Scratchpad — September 13, 2019 @ 12:20 pm BST Sep 13,2019 | Reply

  10. […] the details of what I looked at I’m just going to show you the layer 11 (table) redo change vectors for my DML, which I extracted from the trace file using the command grep […]

    Pingback by Flashback Bug | Oracle Scratchpad — August 24, 2020 @ 9:35 am BST Aug 24,2020 | Reply

  11. […] Redo Op Codes (July 2017): my personal reference for translating redo op codes. […]

    Pingback by Infrastructure Catalogue | Oracle Scratchpad — February 19, 2022 @ 10:41 am GMT Feb 19,2022 | Reply

  12. […] I’m going to do is use grep to pull out the redo OP codes of every change vector in the file and show you a couple of extracts. First a commonly occurring […]

    Pingback by Shrinking indexes | Oracle Scratchpad — September 10, 2022 @ 12:20 pm BST Sep 10,2022 | Reply

  13. […] dump is from 12c (or later) the same limit holds across all (modern) versions of Oracle. The redo Op Code (OP 11.11 – in line 1) is “Insert Multiple Rows”, and at line 9 you can see: […]

    Pingback by Quiz night | Oracle Scratchpad — September 14, 2022 @ 10:53 am BST Sep 14,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: