Oracle Database, Back to Basics – Undo Tablespace

Oracle Database, Back to Basics – Undo Tablespace

I attended an advance class (Oracle RAC Administration) at Oracle University a week ago.  I realized that basic concept and fundamental builds the base for advance topics like Oracle RAC or Oracle GoldenGate. Without a solid understanding how Oracle works internally, you will not understand or appreciate why thing works the way it worked.  Thus, I decided to write down a series of articles related to Oracle concepts (Blogging helps to reinforced my concepts!). The topics were chosen because I revised them recently =D.

Undo Tablespace 

An undo tablespace is a locally managed tablespace reserved for system-managed undo data. Like other permanent tablespaces, undo tablespaces contain data files. Undo blocks in these files are grouped in extents.

The purpose of Undo Tablespace

  1. Roll back an active transaction
  2. Recover a terminated transaction
  3. Provide read consistency
  4. Perform some logical flashback operations

Oracle Database stores undo data inside the database so it is updated like a normal data block. When a transaction starts, the database assigns the transaction to an undo segment, and therefore to a transaction table, in the current undo tablespace. If a dedicated Undo tablespace is not created, it will use the SYSTEM tablespace (NOT RECOMMENDED!!).

In Oracle 11gR2, Automatic Undo Management (AUM) is enabled by default. You just need to assign a default Undo tablespace and Oracle will manage the Undo segments automatically for you.

The undo retention period is the minimum amount of time that Oracle Database attempts to retain old undo data before overwriting it. Undo retention is important because long-running queries may require older block images to supply read consistency. It should be sized as large as your longest running query else you will hit ORA-01555 snapshot too old.

When a transaction commits, the undo segments used are no longer needed for rollback or transaction recovery. The database can retain old undo data if the undo tablespace has space for new transactions. When available space is low, the database begins to overwrite old undo data for committed transactions.

Different States of Undo Segment

  1. ACTIVE – Currently used by transaction, running DML statement that are not committed yet. VERY IMPORTANT! Need this data for read consistency throughout the database
  2. EXPIRED – Not used by any transaction. Data in the segments are commited and can be reused for other transactions
  3. UNEXPIRED – Non-Active extends that still honours UNDO_RETENTION. You need this for Read Consistency. Will be reused if there’s no more EXPIRED segments in the tablespace.

Here’s a scenario;

User 1 does an SQL statement that updates 1 million rows at 12pm.

SQL> update emp set salary=salary*1.05

Undo segments binded to the transaction starts growing and generating the Undo statement to rollback the transaction.  The Undo data will be known as ACTIVE. At this point, if the DML statement is too huge to be stored in the dedicated Undo tablespace, you will hit ORA-30036 error.  User 2 started a SQL query at 12.01pm

SQL> select * from emp;

Oracle will use the active Undo data from the Undo tablespace and present the consistent records as of 12.01pm. At 12.03pm, User 1 has committed the DML transaction. The Undo data will become UNEXPIRED. If User 1 starts another huge DML statement

SQL> update emp set leave=leave+3;

There are no more EXPIRED to be reused, the UNEXPIRED Undo segments previously will be re-used for this DML statement. User 2 will hit the all time classic error, ORA-01555 Snapshot Too Old!

How to prevent errors regarding Undo tablespaces;

  1. Turn on AUM
  2. Size your UNDO_RETENTION correctly
  3. Size your UNDO_TABLESPACE correctly
  4. Schedule your long running queries during off-peak hours

The information here was taken from various blogs, websites, Oracle 11gR2 documentation plus my own understanding. This ends of my first post of Oracle Database, Back to Basics! Next post will be on Redo Logs!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s