Introduction

So much to tell about undo management that even this post is not sufficient to explain the concept. Well a small effort to explain the undo concept in Oracle Database 9i. Lets start with the main funda and then we will see how automatic undo management and manual undo management differs. We will also see undo segments and difference between private and public undo segments.

Undo fundamentals

Undo is the rather the most important functionality present in database, without which a database cannot work. undo serves following purpose in database

• Provide transaction level read consistency of data to all users in database
• Permits user to rollback or discard the changes that have been made in transaction
• Provide transaction recovery in case of instance failure while the user is in the middle of transaction

When a user fires a DML (Either update, delete or insert), oracle will fetch the data from disk into database buffer cache for user to change the data also at the same time a copy of the original data is placed in undo segments. This happens for every DML transaction that is executed in database. When a user makes changes to the data, he can verify the change before doing COMMIT. If he thinks that the data change was not correct, he can ROLLBACK the changes back to original. This functionality is provided by undo. The copy of data which was placed in Undo segment will be copied back and any data change will be discarded. This is a very basic principle. The actual transaction is bit complicated.

When a data is fetched into database buffer cache a copy of the same has to be placed in undo segment. Now imagine that there are many users that are working in the database simultaneously and each firing a DML statement needs to place the original copy in undo segment. How the undo segment will be managed amongst so many transactions? To answer this question we need to understand Automatic undo management and manual undo management. Lets see first Auto undo management.

Automatic Undo Management

In case of automatic undo management, we delegate all the burden of undo segment management to oracle. Oracle will take care of creating the required number of segments as and when required. In return, what oracle want is a tablespace from where he can create segments. So we have to provide a undo tablespace which oracle can use to create undo segments and store the original copy of data for user to view. For enabling automatic undo management, we need to set following parameters

1) UNDO_MANAGEMENT (either AUTO or MANUAL).

For automatic undo management this parameter should be set to AUTO.

2) UNDO-TABLESPACE

Here we provide the name of undo tablespace that we want oracle to use. Undo tablespace is a permanent tablespace that we create (just like other permanent tablespace) and gives it to oracle.

3) UNDO_RETENTION

To understand undo retention consider this scenario. User A fetches the data to update, a copy of data is also placed in undo segment in undo tablespace. User B queries for same data and he is given the old copy of data from undo segment (because user A has not yet committed the changes). After few mins, user A commits the changes.

When the data was copied to undo tablespace in one of the undo segment a lock was also placed on that data, which prevents other undo data to overwrite this data. As soon as user commits the data, this lock will be taken off and data can be overwritten.

Imagine that this data gets overwritten quickly and when user B re-queries the data in the same transaction he gets the error ORA-15555 Snapshot too old.

This happened because the undo segment from which user B was querying the data has been overwritten by some other undo segment data. When a user fires a DML oracle will look for free undo segments to put the copy of data and as soon as he finds the free undo segment he overwrites the previous data. However It would be good if oracle retains the data in these segments for some more time even after the user has committed the data and lock is remove. But for how much time should oracle keep this data after commit? This time is decided by this init.ora parameter UNDO_RETENTION. The time is specified in sec. Usually a value of 900 (sec) is seen to be fine.

In the header of undo segment there is a undo segment table. This table contains the records about which transaction is In-active and which transaction is using the undo segment currently. Also the size of undo segment is decided automatically by oracle and is sufficient to support current number of transactions. When a segments get full by data from various transactions, additional space is allocated to the undo segments by allocating extents to the segments. Extents are similar to segment but are allocated automatically if the segment get full. If the extent also gets full, next extent will get allocated. This continues till oracle accommodates current active transactions.

Manual Undo Management

In case of manual undo management a DBA is supposed to create undo segments manually. For understanding this lets first understand system and non-system undo segments and public and private undo segment.

System and non-system undo segments

System undo segment is the one which resides in system tablespace and is created by oracle when you create a database. This undo segment will be used by oracle while updating the data dictionary. A normal user or a DBA is not supposed to use this segment.

non-system undo segment is the one which is created out side system tablespace and which will be used for normal transactions. A non-system undo segment can be pubic or private

Public undo segment and private undo segment

A private undo segment is the one which can be created by initializing the init.ora parameter – ROLLBACK_SEGMENTS. You can specify the name(s) of undo segment which you want to create in this parameter as comma separated names and oracle will create those undo segment when it starts the database. These are called private undo segments because these are acquired by instance explicitly after starting the instance.

Public undo segments are the one that are available in the database pool from undo tablespace that you create.

When configuring the undo management manually, you need to define 2 parameters in init.ora

1. TRANSACTIONS
2. TRANSACTIONS_PER_ROLLBACK_SEGMENT

Based on the values of these parameter, oracle will decide the number of rollback segments required for the proper working of oracle database.

Example if number of transactions during normal operation is defined as 146 in init.ora file and TRANSACTIONS_PER_ROLLBACK_SEGMENT is defined as 18, then oracle needs 8 rollback segment for proper functioning. Oracle then checks if ROLLBACK_SEGMENTS parameter contains 8 rollback segments defined. If not then the difference of the rollback segments are taken from the pool of public undo segments.

So at any given time there are many undo segments and many extents are allocated to each undo segments. The extents are sequential. Example for the first transaction extent 1 will be allocated, for transaction 2 extent 2 will be allocated and so on.

Consider the below figure. In case currently 5 extents has been allocated.

Now if a new transaction comes, oracle will check if any of the extent is free (In-active). Even a single extent can hold more then one transaction. Extents are made of oracle block. But a single oracle block can hold data from only one transaction. Now imaging that Extent 5 is having some space and all other extents are full. In this case new transaction will be allocated to extent 5. As transaction proceeds, the space in the extent will start getting occupied. A stage will come when extent 5 will get full. Not ideally one of the other extent should get freed (In-active) because of completion of some other transaction. But if all the transactions are long running then none of the extent will be freed. In this case oracle will pull a new extent into this “cycle” – an extent 6 as given below.

With that taken place, now we have 6 extents in a cycle. After some time some of the transactions commits and there extents gets freed and can be allocated to other active transactions if required. Again if extents are insufficient a new extent will be pulled. This continuous until the tablespace is full.

For manual undo management, you need to set UNDO_MANAGEMENT=manual

“Optimal” Clause

From the above scenario you can imaging that if the transactions are big enough then the cycle of extents will grow big and can be come unmanageable. To over come this problem there is a clause defined while creating undo segments. This clause is “OPTIMAL” clause. We can define optimal to some value, either in KB or on MB. If we take above example, consider each extent to be of size 1M and we have defined OPTIMAL=5M while creating undo segment. Now untill 5 extents are present in the cycle, no action will be taken. But when there is no space in any of the extent a 6th extent will be pulled. As soon as any extent gets freed it will be removed from the cycle and only 5 extent (equal to size specified by optimal) will be maintained. If some of the extents amongst 5 are free, none of them will be removed, because optimal is specified as 5M. Oracle will always try to maintain the size of segment as 5M. Following is the example for creating rollback segment with optimal parameter

CREATE ROLLBACK SEGMENT rollseg01 TABLESPACE UNDO_TBS1 STORAGE ( INITIAL 12k NEXT 12k MINEXTENTS 25 MAXEXTENTS 400 OPTIMAL 300k);

Data Dictionary

You can see the name of rollback segments, tablespace they reside into and status using following data dictionary view.

SQL> select SEGMENT_NAME, TABLESPACE_NAME, SEGMENT_ID, STATUS
2 from dba_rollback_segs
3 order by segment_id;

SEGMENT_NAME TABLESPACE SEGMENT_ID STATUS
—————————— ———- ———- —————-
SYSTEM SYSTEM 0 ONLINE
_SYSSMU1_1207284872$ UNDO_TBS 1 ONLINE
_SYSSMU2_1207309696$ UNDO_TBS 2 ONLINE
_SYSSMU3_1207980887$ UNDO_TBS 3 ONLINE
_SYSSMU4_1207980890$ UNDO_TBS 4 ONLINE
_SYSSMU5_1207980890$ UNDO_TBS 5 ONLINE
_SYSSMU6_1207984687$ UNDO_TBS 6 ONLINE
_SYSSMU7_1207984689$ UNDO_TBS 7 ONLINE
_SYSSMU8_1207984689$ UNDO_TBS 8 ONLINE
_SYSSMU9_1207984689$ UNDO_TBS 9 ONLINE
_SYSSMU10_1207984689$ UNDO_TBS 10 OFFLINE

SEGMENT_NAME TABLESPACE SEGMENT_ID STATUS
—————————— ———- ———- —————-
_SYSSMU11_1207984689$ UNDO_TBS 11 OFFLINE
_SYSSMU92_1204797035$ UNDO_TBS 92 ONLINE
_SYSSMU93_1204797035$ UNDO_TBS 93 ONLINE
_SYSSMU94_1204797035$ UNDO_TBS 94 ONLINE
_SYSSMU95_1204797035$ UNDO_TBS 95 ONLINE
_SYSSMU96_1204797035$ UNDO_TBS 96 ONLINE

If you can see carefully here, segment SYSTEM belongs to SYSTEM tablespace and having ID as 0. This is a system undo segment.

Hope this helps !!