• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Managing Rollback/Undo Segments in AUM (Automatic Undo Management)

By admin

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:

  1. AUTOMATIC
  2. 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
Note: You can create rollback segments on an undo tablespace while the database runs in manual mode, but it is useless since these rollback segments cannot be set online when running in AUM 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.

Filed Under: oracle

Some more articles you might also be interested in …

  1. Script To Find Redolog Switch History And Find Archivelog Size For Each Instances In Oracle RAC
  2. Common Init.ora Parameters and Unix, Linux Kernel Parameters and Relationship Between Them
  3. How to Verify if a Disk/Partition is in Use by Oracle ASM, was used by Oracle ASM or is never used by Oracle ASM
  4. Oracle Database : How to set Environment Variables Using Srvctl
  5. Oracle Interview Questions – Flash Recovery Area
  6. How to Disable os-prober in CentOS/RHEL 7
  7. How to Convert STANDARD ASM to FLEX ASM in 12C
  8. How to duplicate a Oracle Database to a previous Incarnation
  9. Oracle Database – How to recover from a lost datafile with no backup
  10. How to start up the ASM instance when the spfile is misconstrued or lost

You May Also Like

Primary Sidebar

Recent Posts

  • How to Disable IPv6 on Ubuntu 18.04 Bionic Beaver Linux
  • How to Capture More Logs in /var/log/dmesg for CentOS/RHEL
  • Unable to Start RDMA Services on CentOS/RHEL 7
  • How to rename a KVM VM with virsh
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary