Not every database is automatically created using Oracles Automatic UNDO Management. Here is a step by step approach to setup UNDO tablespaces for Oracles flashback technology.
Its easy to sit back and start using any particular feature in Oracle; but what happens when your database hasnt 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 Oracles Flashback Technologies; plus Ill 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 isnt 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 dont 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 youre 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 youre 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. Its 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 youll 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.
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 youre 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 youre 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 (cant 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. Thats about what you get out of the box with a vanilla Oracle 11g instance creation using Oracles Database Creation Assistant (DBCA). The tough part comes when you actually start using it. What happens if youve set the UNDO_RETENTION too low or CREATED an UNDO tablespace without AUTOEXEND ON? These are some of the topics Ill venture into within the second part of this article.