Undo and Redo

Quick and Rough Notes :

Undo and Redo


Undo is where Oracle logs how to reverse a transaction (one or more DMLs in a transaction)

Redo is where Oracle logs how to replay a transaction

Undo and Redo are written to as the transaction proceeds, not merely at the end of the transaction
(imagine a transaction that consists of 1million single-row inserts,  each distinct insert is written to undo and redo)

Undo segments
Oracle dynamically creates and drops Undo segments depending on transaction volume
An undo segment consists of multiple extents.  As a transaction grows beyond the current extent, a new extent may be allocated
One undo segment can support multiple transactions but a transaction cannot span multiple undo segments
After COMMIT the undo information is retained for undo_retention or autotuned_undo_retention.  
At the end of the retention period, the undo is discarded, the extent is expired 

Undo retention
Oracle may autotune the undo retention
If the datafile(s) for the active undo tablespace are set to autoextend OFF, Oracle automatically uses the datafile to the fullest and ignores undo_retention
If the datafile(s) are set to autoextend ON, Oracle autotunes undo_retention to match query lengths
Check V$undostat for this information

Undo and Read Consistency
Oracle's implementation of MultiVersionReadConsistency relies on a user session being able to read the undo generated by another session
A session may need to read the prior image of data because the data has been modified (and may even have been commited) by another session
It clones the current version of the block it is reading and applies the undo for that block to create its read consistent version
Flashback Query is supported by reading from Undo


Isolation levels (READ COMMITTED, SERIALIZABLE, READ ONLY) 
Read Consistency with READ COMMITTED is at *statement* level by default
A session running multiple queries may each read a different version by default because Read Committed is enforced for each statement
(This also means that if you have a PLSQL block running the same SQL multiple times, each execution can see a different version of the data-- if the data is modified by another session between executions of the SQL !)
A session can choose to set it's ISOLATION LEVEL to SERIALIZABLE which means that every query sees the same version of data
This works only for short running queries and with few changes to the data or read only data.
SERIALIZABLE can update data provided that the same data hasn't been updated and committed by another session after the start (else you get ORA-08177)
READ ONLY does not allow the session to make changes 

Transactions
When a transaction is in progress, it is identified by the Transaction Address, Undo segment, slot and sequence
The ITL slot in the block header contains the reference (address) to the Undo
The SCN is assigned at commit time (therefore a transaction doesn't begin with an SCN)

Temp Undo
12c also allows temporary undo
Normally, changes to GTT generate undo which needs to be written to undo segments
With 12c temp undo, those undo entries are also, like the actual changes, temporary and can be discarded when the commit is issued
Thus, the undo doesn't need to be written to disk (remember data in a GTT is not visible to another session, so there is no need to persist the undo)
Redo also captures Undo
One transaction (or multiple concurrent transactions) may have updated multiple database blocks
So, DBWR may have written down some of the modified buffers to disk, even before the transaction COMMIT has been issued
This means that some of the blocks on disk may have uncomitted changes
What happens if the instance were to fail (e.g. a bug takes down a background process or the server crashes due to an OS bug or a CPU failure ?)
On instance recovery, Oracle must identify the uncommited transactions and roll them back
But if the undo for that was only in memory and was lost on instance/server failure, how can Oracle rollback the uncomitted transaction ?
Oracle knows that it must "undo" modified blocks
This is done by protecting the undo through the redo as well
Before a modified buffer is written to disk by DBWR, LGWR writes the redo for it
That redo also captures the undo
This ensures that, on the need to do Instance Recovery or Media Recovery, the undo is also available
The Rollforward process writes the undo to the undo segments
This allows Oracle to rollback the uncommitted transaction because the undo is now on disk (and not lost from memory)

Redo Strands
Redo consists of multiple strands 
Since 10g, Oracle has introduced private strands for single-instance databases
This allows a process to manage it's private strand of redo until it decides to commit
At commit time, the private strand is written into the public redo area and this allows LGWR to flush the redo to disk

IMU
Similarly, Oracle also manages undo "in memory" (using IMU pools).  
This means that, for a short period or small transactions, Undo is managed in memory rather than through undo segments
Therefore, Oracle doesn't have to track undo segment changes in the redo
This also allows bundling the undo for multiple changes into a single redo record, instead of separate redo records

RAC
In RAC, every instance has (a) a seperate Redo Thread  (b) a separate Undo Tablespace
However, the redo thread must be readable by every other instance -- as instance recovery by another (surviving) instance needs to read the redo
Similarly, the undo tablespace is read by any other instance because queries in instance 2 may need to read undo of instance 1 for read-consistency