Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 7, 2009

Is Your UNDO Tablespace Prepared for Oracle's Flashback Technology?

By James Koopmann

Not every database is automatically created using Oracle’s Automatic UNDO Management. Here is a  step by step approach to setup UNDO tablespaces for Oracle’s flashback technology.

 It’s easy to sit back and start using any particular feature in Oracle; but what happens when your database hasn’t been configured for that particular feature? This article will take a step-by-step look at what Oracle mentions in the manuals for setting up Automatic UNDO Management for Oracle’s Flashback Technologies; plus I’ll give scripts and instruction along the way.

The first hurdle in configuring the Oracle database for using flashback features is the setting up of Automatic Undo Management. For those of us that are not completely aware of what “undo” is, undo is simply the information stored by Oracle to rollback or undo changes to the database. In talking about flashback technology, one can easily see the importance of undo data to reconstruct information to a prior point in time. Unfortunately, like everything else, undo can be a limiting resource when trying to flashback to a point in time; if there isn’t enough undo the flashback query will fail.

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. You can view this undo tablespace with the following SQL:

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';

--------------- --------- ----------------------------------- ----------
UNDOTBS1        UNDO      /oradata/db11FS/undotbs01.dbf             2048

If you don’t have an undo tablespace, you can easily create one with the CREATE TABLESPACE statement. The only real difference is the added ‘UNDO’ tablespace type that is added. It would look something like this if using the AUTOEXETEND option:

     DATAFILE '/uoradata/db11FS/undotbs01.dbf' SIZE 2048M REUSE AUTOEXTEND ON;

Now if you’re stuck in manual undo management (using rollback segments) you can easily migrate to automatic undo management. The key is the UNDO_MANAGEMENT initialization parameter, which specifies which undo mode the system is currently using. You can check out what yours is by the SHOW PARAMETER command:


NAME                  TYPE        VALUE
--------------------- ----------- ---------
undo_management       string      AUTO
undo_retention        integer     900
undo_tablespace       string      UNDOTBS1

If you’re using Oracle 11g and UNDO_MANAGEMENT is AUTO or is not set then the database instance is already using automatic undo management and you have nothing else to do. Caution as this is a new behavior in Oracle 11g. In earlier versions, the non setting of UNDO_MANAGEMENT actually put you in manual undo management. So be careful if you are using this for a pre-11g Oracle instance. This is a good case for ALWAYS setting your initialization parameters to what they are intended to be used for. It’s just good self-documenting and you never have to guess about default values. Regardless, if your UNDO_MANAGEMENT is set to MANUAL there is no question that you’ll have to migrate from manual undo management (rollback segments) to automatic undo management (using undo tablespace). The migration is as follows:


a.  If using an init.ora file, add an entry in the init.ora file

b. IF using an spfile, ALTER SYSTEM SET undo_management=manual SCOPE=spfile;

2.  Shutdown and startup the instance, affectionately called bouncing the db

3.  To properly size the UNDO tablespace, it is suggested that you actually observe the workload through the system. After a good workload has been given to the system, you can use the following code to ask Oracle to determine the proper sizing for an UNDO tablespace.

   utbsiz_in_MB NUMBER;
  dbms_output.put_line('undo size : '||utbsiz_in_MB||'MB');
undo size : 740MB
PL/SQL procedure successfully completed.

4.  CREATE the UNDO TABLESPACE as prescribed above. Just remember that the more updates to data in the database by users, the more space that will be required in the undo tablespace for flashback operations. If you created the tablespace as prescribed above (with AUTOEXTEND ON for the data files) then Oracle will manage the total tablespace size and keep the undo data longer than the duration of the longest query as well as the UNDO_RETENTION setting (described below).

5.  Set the following initialization parameters. Again, if you’re using an init.ora file then make sure you edit and store these values in that file as well as set them with the ALTER SYSTEM command. If you’re using an spfile make sure you specify the SCOPE=spfile when setting these parameters so they remain active across databases bounces.

a.  UNDO_MANAGEMENT – sets the database instance undo management mode

ALTER SYSTEM SET undo_management=auto SCOPE=spfile;

b.  UNDO_TABLESPACE – sets the undo tablespace that will be used for AUM

ALTER SYSTEM SET undo_tablespace=undotbs01 SCOPE=spfile;

c.  UNDO_RETENTION – sets the length of time (in seconds) to keep undo information

ALTER SYSTEM SET undo_retention=900 SCOPE=spfile;

The key to remember when setting these parameters is that UNDO_MANAGEMENT is a static parameter (can’t be set dynamically) and requires a bounce of the database instance to take effect. For this reason, I think it is better to just modify the init.ora file or spfile and then bounce the database.

Switching to automatic undo management and creating an undo tablespace is not difficult. At this point in the article, if you have done every step, you will end up with a database instance that is now enabled with automatic undo management and is using an UNDO tablespace. That’s about what you get out of the box with a vanilla Oracle 11g instance creation using Oracle’s Database Creation Assistant (DBCA). The tough part comes when you actually start using it. What happens if you’ve set the UNDO_RETENTION too low or CREATED an UNDO tablespace without AUTOEXEND ON? These are some of the topics I’ll venture into within the second part of this article.

» See All Articles by Columnist James Koopmann

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM