UNDO: How does Oracle picks up undo

When transactions hit the database. Each transaction will be allocated one UNDO segment. The transaction will look for extents in the UNDO segment to place UNDO data. It will pick up segments as follows -

(1) Pick up an UNDO segment, which has no ACTIVE extent, if none allocate a new segment. If space does not permit new segment creation, return ERROR.

(2) If the UNDO Segment picked up has got autoextend on

An UNDO segment is picked in arbitrary way. Oracle does not pre calculate the amount of UNDO. So a very large transaction may not reuse a very large UNDO Segment. It will arbitrarily pick existing small UNDO segment, try to extend it if datafile permits. If datafile does not permit then it will truncate the other big segment and then reuse extents from them. So it is advisable to have AUTEXTEND OFF or have a suitable MAXSIZE in UNDO datafiles.

UNDO Basics:- (as one of my friend asked this today, thought of post here, since we tend to forget the oldies)

Undo vs. Rollback

In earlier versions of Oracle, the term rollback was used instead of undo, and instead of managing undo segments, the DBA was responsible for managing rollback segments.

· Rollback segments were one of the primary areas where problems often arose; thus, the conversion to automatic undo management is a significant improvement.

· You will see parts of the data dictionary and certain commands still use the term Rollback for backward compatibility.

Undo data – old data values from tables are saved as undo data by writing a copy of the image from a data block on disk to an undo segment. This also stores the location of the data as it existed before modification.

Undo segment header – this stores a transaction table where information about current transactions using this particular segment is stored. A serial transaction uses only one undo segment to store all of its undo data. A single undo segment can support multiple concurrent transactions.

Purpose of Undo Segments – Undo segments have three purposes: (1) Transaction Rollback, (2) Transaction Recovery, and (3) Read Consistency.

Undo Segment Types

A SYSTEM undo segment is created in the SYSTEM tablespace when a database is created. SYSTEM undo segments are used for modifications to objects stored in the SYSTEM tablespace. This type of Undo Segment works identically in both manual and automatic mode.

Oracle always uses a SYSTEM rollback segment for performing system transactions. There is only one SYSTEM rollback segment and it is created automatically at CREATE DATABASE time and is always brought online at instance startup. You are not required to perform any operations to manage the SYSTEM rollback segment.

non-SYSTEM undo segment  (Undo Tablespace/Rollback Tablespace)

Databases with more than one tablespace must have at least one non-SYSTEM undo segment for manual mode or a separate Undo tablespace for automatic mode.

At present we are not using the manual method below, Private/Public and Manual mode.

Manual Mode: A non-SYSTEM undo segment in system tablespace (before to 8i) is created by a DBA and is used for changes to objects in a non-SYSTEM tablespace.

There are two types of non-SYSTEM undo segments: (1) Private and (2) Public.

Private Undo Segments: These are brought online by an instance if they are listed in the parameter file. They can also be brought online by issuing an ALTER ROLLBACK SEGMENT segment_name ONLINE command (prior to Oracle 9i, undo segments were named rollback segments and the command has not changed). Private undo segments are used for a single Database Instance.

Public Undo Segments: These form a pool of undo segments available in a database. These are used with Oracle Real Application Clusters as a pool of undo segments available to any of the Real Application Cluster instances. You can learn more about public undo segments by studying the Oracle9i Real Application Clusters and Administration manual.

Deferred Undo Segments aka Automatic undo: These are maintained by the Oracle Server so a DBA does not have to maintain them. They can be created when a tablespace is brought offline (immediate, temporary, or recovery) and are used for undo transactions when the tablespace is brought back online. They are dropped by the Oracle Server automatically when they are no longer needed.

Hope this helps

Thanks

Geek DBA