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

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

TABLESPACE_NAME CONTENTS FILE_NAME SIZE_MEG
————— ——— ———————————– ———-
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:

CREATE UNDO TABLESPACE undotbs1
     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:


SQL> SHOW PARAMETER undo

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:

1. 
Set UNDO_MANAGEMENT=MANUAL

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.


SQL> SET SERVEROUTPUT on
SQL> DECLARE
utbsiz_in_MB NUMBER;
BEGIN
utbsiz_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
dbms_output.put_line(‘undo size : ‘||utbsiz_in_MB||’MB’);
end;
/
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

James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles