This post explains how to use the Automatic Undo Management (AUM) feature as compared to the Manual Undo Management related to conventional rollback segments.
Managing Rollback/Undo Segments in Automatic Undo Management:
The Automatic Undo Management (AUM) feature simplifies and automates the management of undo segments. DBAs have the choice to manage rollback segments as they used to do under older versions of the Oracle RDBMS (Oracle V6 – 8i) or to let the RDBMS do it (available in 9i and higher).
There are now two modes of rollback segments management and usage:
- AUTOMATIC
- MANUAL
To distinguish between the two types of segments, ROLLBACK segments are called UNDO segments when AUM is enabled. In both cases, rollback/undo segments are still the only way for transactions to execute and complete. This means that with either method, rollback/undo segments are present in the database and use disk space.
INIT.ORA parameters
UNDO_MANAGEMENT can be set to AUTO if you want the RDBMS to manage undo segments automatically:
- RDBMS creates them when you create a new undo tablespace
- RDBMS alters them ONLINE/OFFLINE when you choose a specific undo tablespace
- RDBMS drops them when you drop an undo tablespace
In this case, DBAs cannot manage undo segments at all, though they still do exist as “rollback” segments. Though you can create rollback segments in undo tablespaces, it is strongly recommended not to do it. UNDO_MANAGEMENT can be set to MANUAL if you want to keep control of rollback segments. If you decide to use AUM, you have to create at least one undo tablespace to store the undo segments automatically created.
Even if AUM uses only one undo tablespace at the instance level, you can create several undo tablespaces. In this case, specify which undo tablespace is to be used:
UNDO_TABLESPACE=rbs
SQL> select name,value from v$parameter
where name in ('undo_management','undo_tablespace');
NAME VALUE
------------------------------------ ------------------------------
undo_management AUTO
undo_tablespace RBS
Having several undo tablespaces available in the database provides the possibility to switch and use a different tablespace with smaller or larger global size for different purposes of usage, such as OLTP, BATCH.
UNDO Tablespace Creation
1. You normally create the undo tablespace at database creation.
2. You can also perform this action after database creation:
SQL> create undo tablespace UNDO_RBS1
datafile 'undorbs1.dbf' size 100m;
Tablespace created.
UNDO Tablespace Characteristics
1. Undo tablespaces are locally-managed with system extent allocation:
SQL> select TABLESPACE_NAME, CONTENTS,
EXTENT_MANAGEMENT, ALLOCATION_TYPE,
SEGMENT_SPACE_MANAGEMENT
from dba_tablespaces where contents='UNDO';
TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ --------- ---------- --------- ------
RBS UNDO LOCAL SYSTEM MANUAL
UNDO_RBS1 UNDO LOCAL SYSTEM MANUAL
Undo tablespace uses extent management that follows the below scheme to allocate the next extent size:
- Until segment size is 1M, we allocate 64K extents (16 such)
- Until segment size reaches 64M, we allocate 1M extents (63 such)
- Until segment size reaches 1G, we allocate 8M extents (120 such)
- Beyond 1G, extent sizes are 64M
During extent allocation, the extents sizes are scaled down only if space is fragmented and the preferred extent size could not be allocated. If the tablespace had sufficient free space, with 32765 extents (which is the maximum # of extents a rollback or undo segment can contain) the segment can map up to 2TB worth of undo generated by the transaction(s).
2. You cannot use undo tablespaces for other purposes than undo segments and you cannot perform any operation on system generated undo segments:
SQL> create table T (c number) tablespace undo_rbs1; create table T (c number) tablespace undo_rbs1 * ERROR at line 1: ORA-30022: Cannot create segments in undo tablespace SQL> create rollback segment undo_rs1 tablespace undo_rbs1; create rollback segment undo_rs1 tablespace undo_rbs1 * ERROR at line 1: ORA-30019: RBU Rollback Segment operation not supported in SMU mode
3. Only one undo tablespace can be used at the instance level:
- use UNDO_TABLESPACE=rbs in init.ora parameter file to set it before instance startup
- or use the SQL command to change the UNDO tablespace during the instance lifetime:
SQL> alter system set undo_tablespace=undo_rbs1; System altered.
Rollback Segments versus UNDO Segments
1. When creating an UNDO tablespace, these are automatically created:
- n undo segments (based on SESSIONS parameter value)
- named as _SYSSMUn$
- owned by PUBLIC (usable for RAC configuration)
- not manually manageable
SQL> select owner,segment_name,tablespace_name
from dba_rollback_segs order by 3;
OWNER SEGMENT_NAME TABLESPACE_NAME
------ ------------------------------ ------------------------------
PUBLIC _SYSSMU1$ RBS
PUBLIC _SYSSMU2$ RBS
PUBLIC _SYSSMU3$ RBS
PUBLIC _SYSSMU5$ RBS
PUBLIC _SYSSMU7$ RBS
PUBLIC _SYSSMU9$ RBS
PUBLIC _SYSSMU10$ RBS
PUBLIC _SYSSMU8$ RBS
PUBLIC _SYSSMU6$ RBS
PUBLIC _SYSSMU4$ RBS
SYS SYSTEM SYSTEM
PUBLIC _SYSSMU11$ UNDO_RBS1
PUBLIC _SYSSMU12$ UNDO_RBS1
PUBLIC _SYSSMU13$ UNDO_RBS1
PUBLIC _SYSSMU14$ UNDO_RBS1
PUBLIC _SYSSMU15$ UNDO_RBS1
PUBLIC _SYSSMU16$ UNDO_RBS1
PUBLIC _SYSSMU17$ UNDO_RBS1
PUBLIC _SYSSMU18$ UNDO_RBS1
PUBLIC _SYSSMU19$ UNDO_RBS1
PUBLIC _SYSSMU20$ UNDO_RBS1
2. If you choose to use AUM, you have no chance to manage any undo or rollback, not even in a non undo tablespace.
SQL> create public rollback segment rs1 tablespace system; create public rollback segment rs1 tablespace system * ERROR at line 1: ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
3. Only undo segments of the active undo tablespace and the SYSTEM rollback segment are kept ONLINE. All other rollback segments and undo segments of other undo tablespaces are OFFLINE.
Nevertheless, not all undo segments of the active undo tablespace are ONLINE at startup: this depends on the SESSIONS parameter. For example, if 10 undo segments exist and you startup the instance with a lower SESSIONS parameter value, the existing undo segments are kept but only a few of them are onlined. The OFFLINE undo segments of the active undo tablespace are onlined when more transactions require the use of offlined undo segments.
Automatic Undo Management and Real Application Clusters
The Automatic Undo Management feature is also useful in Real Application Clusters environments.
1. All instances within Real Application Clusters environments must run in the same undo mode.
2. Set the global parameter UNDO_MANAGEMENT to AUTO in your server parameter file. If you use client-side parameter files, the setting for UNDO_MANAGEMENT must be identical in all the files.
3. Set the UNDO_TABLESPACE parameter to assign the appropriate undo tablespace to each respective instance. Each instance requires its own undo tablespace.
If you do not set the UNDO_TABLESPACE parameter, each instance uses the first available undo tablespace.
Conclusion
1. There is another undo_ init.ora parameter: UNDO_SUPPRESS_ERRORS.
Use it very carefully; when set to TRUE, it suppresses any error message issued when attempting manual undo operations while in AUM mode:
SQL> alter rollback segment "_SYSSMU1$" online; Rollback segment altered. SQL> alter rollback segment "_SYSSMU13$" offline; Rollback segment altered. SQL> alter rollback segment rs1 online; Rollback segment altered.
All these statements seem to have executed the operation, but in reality, did not do anything.
2. Like rollback segments dropped MANUALLY, queries that need to access the transaction undo information residing in a dropped UNDO tablespace may result in ORA-01555 “snapshot too old” error if the snapshot is older than the DROP-SCN of the UNDO tablespace.