Failures within Your UNDO Tablespace-When do they occur?

Let’s take a quick look at the typical errors within an Oracle UNDO tablespace, by breaking the UNDO mechanism, and helping to demystify the occurrence of errors.

In the last article, Is Your UNDO Tablespace Prepared for Oracle’s Flashback Technology?, we took a quick look at how an out of the box vanilla UNDO tablespace might look. That article also took a quick look at how you might want to bring your pre-Oracle 11g UNDO up to current configuration best practices (at least out of the box standards).

I’ve often found that UNDO is one of those areas that seems to bring quite a bit of confusion to many developers and DBAs. Much of the fire fighting surrounding UNDO has DBAs fixing an error code but never really understanding UNDO internals and why they are fixing it. So this article will take a look at when the most basic of errors happen within UNDO. This will be done by actually trying to break the UNDO mechanism and then showing how errors progress within UNDO.

Let’s just remember firstly that UNDO is nothing scary. UNDO is simply the information stored by Oracle to rollback or undo changes to the database. Moreover, all UNDO problems occur from one of two sides of users doing some form of DML within the database. These two sides can be simplified into:

• Updating DML – INSERTs, UPDATEs, & DELETEs
• Selecting DML – SELECTs

Now that is really all there is to it. From here, we can simply agree that within our database there are SELECTing SQL and UPDATing SQL. Normally, when UNDO is tuned properly, these two types of SQL can live and execute quite comfortably together. However, minor flaws in the configuration of UNDO will create havoc, either for the UPDATEing SQL alone, or for the SELECTing SQL that has to try to reconstruct information that the UPDATing SQL is changing. So let’s start tackling this a bit.

To begin let’s create an UNDO tablespace that, while very small, will represent those databases that have not yet switched over to Automatic UNDO Management. Notice the lacking of the AUTOEXEND ON clause as was mentioned in the last article.

SQL> CREATE UNDO TABLESPACE undotbs2 
	DATAFILE '/oradata/db11FS/undotbs02.dbf' SIZE 2M;
SQL> alter system set undo_tablespace=undotbs2;

We’ve introduced a static UNDO tablespace of size 2M. The problems this causes will quickly be seen when we start performing UPDATEing SQL that approaches 2M in size. Since we have not told the UNDO tablespace that it can AUTOEXTEND the sessions that are performing UPDATEs, we will soon get the following error to contend with:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'

This ORA-30036 error has NOTHING to do with another session doing SELECTs; it is completely the doing of the session performing UPDATEing SQL. There are one cause and two possible solutions to consider when getting this type of error. The cause is simply that UPDATEing sessions (may be more than one session performing updates) have not defined a unit of work for their applications that is in line with the amount of UNDO available (2M). Basically the sessions are doing more work than can fit into the limited UNDO space. To remedy this you have the option of increasing the UNDO tablespace size or modifying an application’s unit of work (add more commits). Because it is often difficult to gain access to code the simple route of increasing UNDO tablespace size is often taken. Just don’t forget that increasing the size is not an end-all solution. Someone should be aware that applications are not defining units of work properly as this could lead to very large problems down the road, especially for those SELECTing sessions that we will talk about next.

When SELECTing sessions try to use the database at the same time that there are UPDATEing sessions, another issue can begin to occur which has to do with the SELECTing sessions not being able to extract a result set properly because the UPDATEing sessions are overwriting the UNDO with new changes. Oracle has a feature call read consistency that states, "when a SELECTing session begins to select data from a table Oracle will try to maintain and return the values within the rows as they were when the select statement began." This can require heavy use of the UNDO area and if the UNDO is overwritten by excessive UPDATEing, the following error will occur in the alert log:

ORA-01555 caused by SQL statement below (SQL ID: 4fwzm0uu3kx8w, Query Duration=15 sec, SCN: 0x0000.00181a0e):

So why do we turn AUTOEXTEND ON for the UNDO tablespaces? Let’s take a look at the way it should work. Under the prescribed method from the last article, we first create an UNDO tablespace that uses the AUTOEXTEND ON clause.

CREATE UNDO TABLESPACE undotbs2 DATAFILE '/oradata/db11FS/undotbs02.dbf' SIZE 2M AUTOEXTEND ON;

SQL>  SELECT dt.tablespace_name, dt.contents,
             ddf.file_name, ddf.bytes/1024/1024 size_MEG
        FROM dba_tablespaces dt,
             dba_data_files ddf
       WHERE dt.tablespace_name = ddf.tablespace_name
         AND dt.contents = 'UNDO';

TABLESPACE_NAME CONTENTS  FILE_NAME                           SIZE_MEG
--------------- --------- ----------------------------------- --------
UNDOTBS1        UNDO      /oradata/db11FS/undotbs02.dbf              2

Now, by using a couple of sessions we have one do a bunch of UPDATEing in one session and in the other session do a long running SELECT statement.

SQL> INSERT & UPDATE a bunch of rows 
SQL>> SELECT from those rows being INSERTed and UPDATEd

After both sessions complete successfully, we can again query the UNDO tablespace and see that it has grown to 11.5M. Nice! No errors and no need to concern ourselves with managing the space.

SQL>  SELECT dt.tablespace_name, dt.contents,
             ddf.file_name, ddf.bytes/1024/1024 size_MEG
        FROM dba_tablespaces dt,
             dba_data_files ddf
       WHERE dt.tablespace_name = ddf.tablespace_name
         AND dt.contents = 'UNDO';

TABLESPACE_NAME CONTENTS  FILE_NAME                             SIZE_MEG
--------------- --------- ----------------------------------- ----------
UNDOTBS2        UNDO      /oradata/db11FS/undotbs02.dbf             11.5

Oracle has long touted the use of Automatic Undo Management, and for good reason. Anyone who remembers fighting undo management manually can attest to the difficulties in properly creating, sizing, managing, and sizing again rollback segments to eradicate the ever-present ORA-1555 snapshot too old error. With Automatic Undo Management (AUM), Oracle now automatically monitors and manages the undo segments and space for you. Thankfully, starting in Oracle 11g, AUM is the default and most databases will have an auto-extending undo tablespace named UNDOTBS1. Switching to automatic undo management and creating an undo tablespace is not difficult. Just revisit my last article and you’ll be on your way.

» See All Articles by Columnist James Koopmann