Oracle Concepts and Architecture – Part 4

Part 1 subjects –

1- Flashback Versions Query, Drop Table and Recyclebin Management
2- How shared pool works in Oracle and binding example
3- The Storage Hierarchy Summary in an Oracle Database
4- Concurrency and Isolation Levels Concepts
5- Automated Checkpoint Tuning and Mean time to recover(MTTR) Concepts
6- Concurrency and Consistency Concepts
7- Latch and Lock Concepts

Part 2 subject –

8- Oracle Database Architecture: Overview

Part 3 subjects –

9- How Atomicity is implemented on Oracle
10- NULL values and Oracle

11- Overview of Transaction Management Internals

Transactions are one of the major features that set a database apart from a file system. Main purpose of them are to take the database from one consistent state to next, guaranteed that either all of your changes including various rules and checks that implement data integrity have been saved or none of them are saved when you commit your work. All statements are atomic.

I advice you also use Julian Dyke’s related great presentation in slide show mode during reading below simplified descriptions for much more deep informations on Transaction Internals with Oracle.

Overview of Transaction Management Internals

Undo segments are central to the database and they are used for both transaction rollback recovery and building cosistent read database operations.

a. A transaction begins when a slot is allocated in the transaction table at the head of an undo segment. The transaction identifier(txid=undo_segment_number.transaction_slot_id.SCN_wrap) is a pointer to this location.
b. Before a transaction iserts, updates or deletes a table row a Interested Transaction List(ITL) is allocated in the block containing the row. The ITL is used to mark the row as locked until the transaction is either committed or rollbacked. And the ITL contains the transaction identifier.
c. When the change is applied to the block undo information is also generated and is stored in the undo segment. The transaciton table slot contains a pointer to the undo information.

Read consistency

When a differet session wants to read the same block and the first trasaction has not been yet ended, the session reading the block(most possibly still in the cache) first finds the open ITL. It checks the transaction status by reading the undo segment header and finds that there is an active transaction still. This means the session must create a snapshot of the block and rollback all the changes made by the active transaction. This task is called making a consistent read(CR) copy of the block and is achieved by first cloning the block and then rolling back the latest changes by applying undo records from the udo segment. In this very simple explaination still CR requires I/O to data block, undo segment header and undo records.


This time lets suppose another session wants to change(update or delete) the row that was changed by the first transaction and the first trasaction has not been yet ended. This time when the session reading the block(most possibly still in the cache) first finds the open ITL. It checks the transaction status by reading the undo segment header and finds that there is an active transaction still, it starts to wait on the transaction table slot for the trasaction to complete(commit or rollback).

Commit and Delayed Block Cleanout(DLBC)

Now lets suppose the first active transaction is now committed, this is recorded immediately by marking the transaction table slot as inactive however the data block itself may not be updated until later meaning ITL in the block may remain open for some time after the commit. When Oracle read this block for some reason, the transaction table will be checked also, commit will be cofirmed and ITL this time is also closed. This behaviour is known as delayed block cleanout. Here if the undo segment has since been deleted the SCN recorded in undo$ is used to cofirm the commit.

Transaction Recover: Rollback – Process Crash(PMON) – Instance Crash(SMON)

When a rollback statement is issued the undo records for the current transaction in reverse order(latest first) is scanned and applied back. When rollback statement returns successfully this means all the block changes have been undone and the ITL has been cleared, there is no delay for rollback. This process alse generates new redo which are written to the redo logs.

If the server(shadow) process somehow crashes with an active transaction, PMON detects this and roll back the transaction. Event 10012 and 10246 can be set and used to monitor this.

If the instance crashes there is no chance to rollback the active transactions. The next time database is opened, SMON background process will do crash recovery and it will return the block to its pre-crash consistent state. Active transactions in the SYSTEM undo segment are immediately rolled back, where as active transactions in other undo segments are marked as “dead” and SMON scan these segments at a later time to perform rollback since the database need to start up as fast as possible. To list the dead transactions –
select * from x$ktuxe where ktuxecfl = ‘DEAD’ or
alter system dump undo header — here dead transactions are identified by having cflg = ‘0x10’

Problematic cases

Above operations need access to the undo segment header, undo records and related data blocks for ITL information. If any of these are corrupted or lost then operations will be affected. Event 10013 monitors transaction recovery during startup and Event 10015 dump undo segment header listed in undo$ before and after transaction recovery.

Also only as a last resort and with the assistance of Oracle support “_offline_rollback_segments” and “_corrupted_rollback_segments” hidden parameters can be used. When database opens a list of offline and corrupted undo segment numbers(USNs) are constructed. If you use these two hidden parameters you change the default behavþior mentioned above, the transaction table is not read during startup so the transacitons are not marked as dead or rolled back. The segments listed in the parameters are appeared as OFFLINE in dba_rollback_segs(undo$) and they can not be acquired for the new transactions. This is an unsupported state so media recovery is preferred instead.

Continue reading with part 5

Refences Used :
Oracle® Database Concepts 10g Release 2 (10.2)
Chapter 4 Transaction Management
Chapter 13 Data Concurrency and Consistency

1 Comment

Leave a Comment

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