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	Update undo block
		5.2	Get undo header
		5.3	Rollout a transaction begin
		5.4	On a rollback or commit
		5.5	Create rollback segmenr
		5.6	On a rollback of an insert
		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	Mark undo as applied 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

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 during rollback
		10.6	(kdxlok) Lock block (pre-split)
		10.7	(kdxulo) unlock block during undo
		10.8	(kdxlne) initialize leaf block being split
		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) (UNDO) set kdxleprv (previous 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 branch 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  insert row  piece
		11.3  delete row piece
		11.4  lock row piece
		11.5  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.17 Update multiple rows
		11.19 Array update ?
		11.20 SHK (mark as shrunk?)
		11.24 HCC update rowid map ?

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 Level1 Bitmap Block
		13.19	ktspsfredo - Format Level2 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.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.8	kteoputrn - undo operation for flush for truncate

15	Tablespace

16	Row cache

17	Recovery management
		17.1	End backup mode marker
		17.3	Crash Recovery at scn:  0x0000.02429111

18	Block image (hot backups)
		18.1	Block image
		18.3	Reuse redo entry
				   (Prewarm range reuse:  tsn=1 base=0x058000c4 nblks=8)
				or (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

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)
		23.3	MEDIA RECOVERY MARKER (Block written record 19c)

24	DDL statements
		24.1	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.


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

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 )

Google photo

You are commenting using your Google 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