UNDO - My Understanding

Introduction: -

UNDO in Oracle database is used mainly to ensure that ACID (Atomicity, Consistency, Isolation, Durability) properties of a transaction are maintained.
UNDO is implemented using UNDO segments. It can be manually managed (using rollback segments) or system managed (using UNDO tablespace). Before Oracle 9i, it was manually managed but now we have a parameter (UNDO_MANAGEMENT) to configure if we want manual/auto.

SQL> show parameter undo_management

NAME TYPE VALUE
------------------------------------ ----------- -------------------------
undo_management string AUTO



UNDO Segments: -

As we know tablespace are made up of segments logically, similarly UNDO tablespace is made up of UNDO segments. We can see the list of UNDO segments with the help of table called dba_rollback_segs.

SQL> select segment_name, file_id, block_id from dba_rollback_segs where

tablespace_name ='UNDOTBS1';

SEGMENT_NAME FILE_ID BLOCK_ID
------------------------------ ---------- ----------
_SYSSMU1_1192467665$ 3 9
_SYSSMU2_1192467665$ 3 25
_SYSSMU3_1192467665$ 3 41
_SYSSMU4_1192467665$ 3 57
_SYSSMU5_1192467665$ 3 73
_SYSSMU6_1192467665$ 3 89
_SYSSMU7_1192467665$ 3 105
_SYSSMU8_1192467665$ 3 121
_SYSSMU9_1192467665$ 3 137
_SYSSMU10_1192467665$ 3 153

10 rows selected.

‘_SYSSMU’ is the prefix used for all the UNDO segments in case of auto UNDO management.


Each UNDO segment is made up of two distinct components: -
1. UNDO Header
2. UNDO entry

UNDO Header: -

The first block of UNDO segment is reserved for UNDO header and whenever a segment is created, only header is created and remaining blocks are just allocated to the segment with no structure defined. UNDO segment header mainly contains information about the UNDO extents and UNDO slots with the help of retention table, transaction table and extent control header.
We can dump UNDO segment header with: -

SQL> alter system dump undo header '_SYSSMU5_1192467665$';

Where '_SYSSMU5_1192467665$’ is the name of the segment to be dumped.
This dumps the contents of the segment header in the current trace file being used. The location of the trace file will be “ORACLE_BASE/diag/rdbms/<>/<>/trace/*.trc “.

Sample Trace File Contents: -


Undo Segment: _SYSSMU5_1192467665$ (5)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 231 #blocks: 34535
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00c0b97d ext#: 227 blk#: 756 ext size: 1024
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 227
Unlocked
Map Header:: next 0x00000000 #extents: 231 obj#: 0 flag: 0x40000000

Extent Map
-----------------------------------------------------------------
0x00c0004a length: 7
0x00c00051 length: 8
....
0x00c00989 length: 128
0x00c00011 length: 8
0x00c00021 length: 8

Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1269847078
Extent Number:4 Commit Time: 1269847080
....
Extent Number:229 Commit Time: 1269847077
Extent Number:230 Commit Time: 1269847078

TRN CTL:: seq: 0x07ba chd: 0x0008 ctl: 0x0006 inc: 0x00000000 nfb: 0x0001
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00c0b964.07ba.05 scn: 0x0000.0089a102
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.07ba.04 ext: 0xe3 spc: 0x1df2
uba: 0x00000000.07ba.02 ext: 0xe3 spc: 0x1f0e
uba: 0x00c0b97c.07ba.07 ext: 0xe3 spc: 0x1940
uba: 0x00000000.057c.4d ext: 0x2 spc: 0x3c4
uba: 0x00000000.02ca.01 ext: 0x2 spc: 0x1f84
TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x1a42 0x0019 0x0000.0089a332 0x00c0b97d 0x0000.000.00000000 0x00000001 0x00000000 1269853986
0x03 9 0x00 0x1a46 0x0017 0x0000.0089a19a 0x00c0b97b 0x0000.000.00000000 0x00000001 0x00000000 1269853441
......
0x20 9 0x00 0x1a43 0x0004 0x0000.0089a3ad 0x00c0b97d 0x0000.000.00000000 0x00000001 0x00000000 1269854042
0x21 9 0x00 0x1a4b 0x0006 0x0000.0089a433 0x00c0b964 0x0000.000.00000000 0x00000001 0x00000000 1269854282
EXT TRN CTL::
usn: 5
sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000
sp5:0x00000000 sp6:0x00000000 sp7:0x00000000 sp8:0x00000000
EXT TRN TBL::
index extflag extHash extSpare1 extSpare2
---------------------------------------------------
0x00 0x00000000 0x00000000 0x00000000 0x00000000
0x01 0x00000000 0x00000000 0x00000000 0x00000000
0x02 0x00000000 0x00000000 0x00000000 0x00000000
...
0x1f 0x00000000 0x00000000 0x00000000 0x00000000
0x20 0x00000000 0x00000000 0x00000000 0x00000000
0x21 0x00000000 0x00000000 0x00000000 0x00000000



From the dump, we can see the main components of the segment header: -
1. Extent Control Header
2. Extent Map
3. Retention Table
4. Free Block Pool
5. Transaction Table


Transaction Table: -
Undo is generated by a transaction and is valid only in context of that transaction. Transaction table is made up of fixed number of slots (entries). Every slot is assigned to a transaction. There are 3 main components in case of transaction table: - state, scn and dba. State can have a value of 10 for not committed transaction and 9 for committed transaction. DBA stands for data block address of last data block of UNDO entries for that transaction. An UNDO slot is made up of list of UNDO extents and UNDO extents comprise of set of UNDO block. Every UNDO block is assigned to only one transaction and has UNDO block header containing XID ,SQN etc information and UNDO records .


Two views play an important part here: - v$session and v$transaction.
v$session help in identifying the transaction it performed and gives the transaction number by TADDR column. If this column is NULL, then there is no transaction performed by the current session, otherwise we can find the more details in the v$transaction table about the transaction. If there isn't any transaction started yet,
Select * from v$transaction will yield " no rows returned " as the result.

SQL> select * from v$transaction;

No rows selected


Now issue an update...

SQL > update scott.emp set ename='parul' where empno=7934;
1 row updated.

With first DML issued, a XID is allocated to the transaction and thus a slot too containing the state of 10 (uncommitted transaction).

SQL> select ses_addr,addr,ubafil,ubablk,start_ubafil,start_ubablk from v$transaction;

SES_ADDR ADDR UBAFIL UBABLK START_UBAFIL START_UBABLK
-------- -------- ---------- ---------- ------------ ------------
99D69970 96E3CFA0 3 783 3 783


UBA here stands for Undo Block Address; Fil stands for File and Block stands for Block...
There are two things represented here.... UBAFIL and START_UBAFIL ... These are like head and tail of the transaction's UNDO chain.

Start_ubafil corresponds to the start of the undo chain, also called as the tail of the chain (since UNDO is used in the reverse order later).
And UBAFIL corresponds to the current UBA block in the chain, also called as the head of the chain. Hence, for every transaction; there is UNDO chain associated with multiple UNDO blocks, each identified by UBA (Undo Block Address). These blocks may not be physically adjacent to each other.

After we issue multiple updates , its still going on the same block but everytime for an update , a record is alocated :-
SQL> update scott.emp set job='analyst' where empno=7934;

1 row updated.

SQL> select ses_addr,addr,ubafil,ubablk,start_ubafil,start_ubablk from v$transaction;

SES_ADDR ADDR UBAFIL UBABLK START_UBAFIL START_UBABLK
-------- -------- ---------- ---------- ------------ ------------
99D69970 96E3CFA0 3 783 3 783

SQL>select used_ublk,used_urec, ubarec, start_ubarec from v$transaction;

USED_UBLK USED_UREC UBAREC START_UBAREC
------------ ------------ ------------
1 13 56 44


SQL> update scott.emp set ename='parul' where empno=7934;

1 row updated.

SQL> select used_ublk,used_urec, ubarec, start_ubarec from v$transaction;

USED_UBLK USED_UREC UBAREC START_UBAREC
------------ ------------ ------------
1 14 57 44

With every update, record count increases … and once, all the records are allocated; it assigns the records from the next block.



UNDO Entry

It contains transaction level information and contents of column before change occurred. UNDO entries are chained together to form single transaction UNDO action .
Every transaction is allocated a tranaction ID (XID) at the time of first DML statement which depends on three importants componenets :-
1. XIDUSN => Segment Number
2. XIDSLOT => Slot number from the transaction table
3. XIDSQN => Sequence Number

Dumping UNDO Block: -

Now suppose if we want to dump the UNDO block of the current transaction; we will get the block information from the v$transaction view: -

SQL> select ubafil,ubablk,ubarec from v$transaction;

UBAFIL UBABLK UBAREC
---------- ----------
3 47460 5

It tells us that the current transaction is using block with UBA 47460. We can find out from dba_extents, the file and block information for a particular extent.

SQL>select segment_name from dba_extents where file_id = 3
and 47460 between block_id and block_id + blocks –1;

SEGMENT_NAME
______________

_SYSSMU5_1192467665$

We found out the name of the segment, which contains the current block, and now we can dump the block from the file using

SQL> alter system dump datafile ‘_SYSSMU5_1192467665$’ block ‘47460’;

And this dumps the block into the trace file.

Sample Block Contents: -


*** 2010-03-29 17:54:12.187
Start dump data blocks tsn: 2 file#:3 minblk 47460 maxblk 47460
Block dump from cache:
Dump of buffer cache at level 4 for tsn=2, rdba=12630372
BH (0x187E434C) file#: 3 rdba: 0x00c0b964 (3/47460) class: 26 ba: 0x18408000
set: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0 lid: 0x00000000,0x00000000
dbwrid: 0 obj: -1 objn: 0 tsn: 2 afn: 3
hash: [0x28197220,0x28197220] lru: [0x183F9E9C,0x18FEFDBC]
ckptq: [NULL] fileq: [NULL] objq: [0x1E3E877C,0x187F3CAC]
st: XCURRENT md: NULL tch: 7
flags: block_written_once redo_since_read gotten_in_current_mode
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
buffer tsn: 2 rdba: 0x00c0b964 (3/47460)
scn: 0x0000.0089a490 seq: 0x01 flg: 0x04 tail: 0xa4900201
frmt: 0x02 chkval: 0xcdd6 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x18408000 to 0x1840A000
18408000 0000A202 00C0B964 0089A490 04010000 [....d...........]
18408010 0000CDD6 000E0005 00001A49 050507BA [........I.......]
....
18409FD0 0089A0FC 00060007 00001A23 00C08AB1 [........#.......]
18409FE0 00270675 00008000 0089831C 0000000D [u.'.............]
18409FF0 00814976 00801003 01010001 A4900201 [vI..............]

********************************************************************************
UNDO BLK:
xid: 0x0005.00e.00001a49 seq: 0x7ba cnt: 0x5 irb: 0x5 icl: 0x0 flg: 0x0000

Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f74 0x02 0x1f24 0x03 0x1e98 0x04 0x1e0c 0x05 0x1d84

*-----------------------------
* Rec #0x1 slt: 0x20 objn: 5965(0x0000174d) objd: 5965 tblspc: 1(0x00000001)
* Layer: 10 (Index) opc: 21 rci 0x00
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00c0b962
*-----------------------------
index general undo (branch) operations
KTB Redo
op: 0x05 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: R itc: 2
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.005.00001a42 0x00c0b963.07ba.01 C--- 0 scn 0x0000.0089a0fc
0x02 0x0007.006.00001a23 0x00c08ab1.0675.27 C--- 0 scn 0x0000.0089831c
Dump kdige : block dba :0x00814976, seghdr dba: 0x00801003
make leaf block empty
(2): 01 00

*-----------------------------
*-----------------------------
* Rec #0x5 slt: 0x0e objn: 69539(0x00010fa3) objd: 69539 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c0b964.07ba.04 ctl max scn: 0x0000.0089a101 prv tx scn: 0x0000.0089a102
txn start scn: scn: 0x0000.00000000 logon user: 0
prev brb: 12630387 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100001e hdba: 0x0100001b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 13(0xd) flag: 0x2c lock: 0 ckix: 0
ncol: 8 nnew: 1 size: 1
col 1: [ 6] 4d 49 4c 4c 45 52

Block dump from disk:
buffer tsn: 2 rdba: 0x00c0b964 (3/47460)
scn: 0x0000.0089a490 seq: 0x01 flg: 0x04 tail: 0xa4900201
…..

As we can see, in case of record 0x05; it contains the original value of the column name.
(col 1 : ……. )


We can analyze more further the block to see how it changes for multiple row updates or for delete. But this much is enough for basic understanding I guess.

References :-

1. www.juliandyke.com
2. http://www.scribd.com/doc/2713817/Oracle-AUTOMATIC-UNDO-INTERNALS