This is an excerpt from the bestselling book
Oracle Grid & Real Application Clusters. To get immediate
access to the code depot of working RAC scripts, buy it
directly from the publisher and save more than 30%.
The initialization parameters
shown in Table 8.6 are relevant for undo management.
If AUTO, use automatic undo
management mode. If MANUAL, use manual undo management mode.
A dynamic parameter
specifying the name of an undo tablespace to use.
A dynamic parameter
specifying the length of time to retain undo. Default is 900
Set to TRUE to suppress
error messages generated by manual undo SQL statements when
operating in automatic undo management mode. If set to FALSE,
error messages are issued. This is a dynamic parameter.
Table 8.6: Undo Initialization
To view the existing parameters,
use the command:
show parameter undo
-------------------------------- ----------- ----------
UNDO Tablespace Features
Some of the general features of
undo tablespaces include:
* They are locally managed with
system extent allocation.
* The UNDO tablespace cannot be
used for any purpose other than UNDO segments and no operations can
be performed on system-generated undo segments. When trying to
create a table using the following statement:
salesid number(6) )
The following message will be
generated: ORA-30022: Cannot create segments in undo tablespace
* Only one UNDO tablespace can
be used at the instance level.
* UNDO segments are created
automatically and are owned by PUBLIC. They are named as _syssmun$
and are not manually manageable. The number of segments depends on
the sessions parameter.
* When Automatic undo management (AUM) is chosen, it will
not be possible to manage an undo or rollback.
For example, if this statement
rollback segment rsg1 tablespace rbs1 ;
The following error will be
generated: ORA-30019: Illegal rollback segment operation in
* Only the undo segments of the
active UNDO tablespace and the SYSTEM rollback segments are kept
* All the instances in RAC must
run in the same undo mode (i.e. AUTO or MANUAL).
Table 8.7 shows the views that
provide undo information.
Contains statistics for
monitoring and tuning undo space. Use this view to help estimate
the amount of undo space required for the current workload.
For automatic undo
management mode, information reflects behavior of the undo
segments in the undo tablespace.
Contains undo segment
Shows the commit time for
each extent in the undo.
Table 8.7: Dynamic performance
views related to undo
System Rollback Segment
The system rollback segment is
shared, and is used by all instances for transactions involving
system objects. Even when using the automatic undo
management method, Oracle can still use
the system rollback segment. There is only one system rollback
segment for the entire database. It resides in the SYSTEM
Using Rollback Segments ?
Even though the recommended
method is to use automatic undo management (AUM), the traditional
method of manually creating rollback segments is allowed. In such a
case, set the initialization parameter undo_management=MANUAL. When
operating in manual mode, each instance needs at least one rollback
segment to start successfully.
A rollback segment may be
acquired by only one instance. Once an instance acquires a
particular rollback segment, it is used exclusively by that
instance. No other instance
can write to it, although they can read from it for the purpose of