Redo OP Codes:

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.

Update

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.