Undo Management
.
Undo, why undo ?
We require undo for following
Read Consistency : Read consistency gives your query the output
as it was at that point in time
Example : Imagine you issued select query which takes 4-5 min to
execute, and on the second minute someone modified the data by
issuing update command, inspite of this the first query's output is
unaffected. That is because the before modified image is stored
in the undo
Rollback Transaction : To undo any modified data Example suppose
you have modified any data and then you change mind not to modify
the data, then you could undo that.
When a rollback statement is issued, undo records are used to undo changes that were made to the
database by uncommited transaction.
Recover Database : During recovery undo records are used to undo
any uncommited changes applied from the redo logs to the datafiles.
Flashback Query : Here you could get the image of the database as
it was before some time. say 15-20 minutes if undo is configured properly
Configuring Undo
We have three imp undo parameter for configuration
undo_management = auto
undo_tablespace = <undo tablespace name>
undo_retention = <time in seconds>
Undo Management : Setting it to auto lets oracle manage the undo
Undo Tablespace : Undo tablespace which will be active.
Undo retention : Time in seconds til which oracle will store the undo
information in tablespace.
Sizing Undo Tablespaces
Oracle provides tool v$undostat, it stores undo block generation data,
which are taken at 10 minutes interval. In the v$undostat table you
would find column undoblks which is in bytes.
Two parameter vital for preventing errors related to undo are
1) undo retention
2) Undo tablespace size
SQL>select max(maxquerylen) from v$undostat;
above query gives us the undo retention, now we have to calculate the
size of undo
For getting the undo size we again query the v$undostat, which
store the undo blocks generated in 10 minutes interval
Note : v$undostat keeps 4 days of information
Now,
undo size = transaction rate * block size * undo retention
select max(undoblks) from v$undostat;
transaction rate = [select max(undoblks) from v$undostat;] / 600
Now undo size = transaction rate * block size * undo retention
Monitor Undo status
select status , sum(blocks)*8192/1024/1024/1024 GB from dba_undo_extents group by status;
Additional Details
Each transaction is assigned to only one undo segment,however one undo segment can be
assigned to multiple transaction at a given point of time, to check which transaction have
which undo segments assigned query the v$transaction view.
Undo segments expand and contract automatically as needed and act as circular storage
buffer for transactions
Transaction fills extents in its undo segment until a transaction is completed or all space is
consumed.
When an extend fills up and more space is needed, the transaction will acquire more space
from next extent in the segment.
When all extent in the undo segment are consumed, the transaction overwrites undo data
which is no longer needed. Undo tablespace contain undo segments, an instance can
have only one active undo tablespace at a time
Undo segments are owned by SYS and they have segment type "TYPE 2 UNDO"
Undo tablespace are specialized, permanent tablespace with automatic
extent allocation.
There are only two this one needs to know to monitor undo
1) Undo space
2) Snapshot to old error
UNDO Space : when huge transaction takes place in the database, this requires huge space
in the undo tablespace to store the original data
insert operation requires very little undo space
delete operation requies huge undo space
when undo tablespace runs out of space, the oracle server raises ORA-01650
unable to extend rollback segment
Snapshot to old error : Here when a query tries to access data that no longer exists in the
undo tablespace, this happens when a query takes long time to execute, here we get ORA-1555
snapshot too old error
Undo management.
Two Types Of Undo management
Automatic : Here the original data is stored in undo segment
Oracle server determines the time period for which the undo
data is retained in undo segments, data is stored in the undo
segments even after the transaction is complete (called unexpired data)
Undo retention : Amount of time for which the commited undo data should be retained in the
undo tablespace, after which it will expire or will be overwritten. In Automatic undo management
undo is retained till it expires, however if an active transaction required more space the
commited but unexpired data is overwritten. If undo retention is set to 0, automatic undo retention
tuning is enabled, this retains undo information as required to support longest running query
Manual : The segment where the original data is stored is called as rollback segment
Undo data falls in three categories
1) Uncommited undo info for active transaction, used for rollback or recovering failed transaction,
this is never overwritten
2) Commited undo : These is retained to meed the retention period
3) Expired undo : these is not required to support an active transaction and is overwritten when
an active transaction needs space and no free is available When space is required by active
transaction and there is no free space Commited undo data is overwritten instead of failing
the transaction. However if the retention_guarantee is set the, these would not happen and lets
transaction to fail for lack of undo space.