Oracle Database keeps records of actions of transactions
before they are committed and Oracle needs this
information to rollback or Undo the Changes to the
database. These records in Oracle are called Rollback
or Undo Records. These records are used to Rollback
transactions when a rollback statement is issued
or during recovery of a database or to provide a
read consistent view of data.
Until Oracle 8i and also in 9i, Oracle uses Rollback
Segments to manage the Undo Data. Starting with
Oracle 9i, DBA’s are provided with some a new feature
referred to as "Undo Tablespace" which
allows the dba to exert more control on how long
Undo information is retained and also eliminates
the complexity of managing Rollback Segments in
certain environments.
Starting with 9i, the rollback segment way is referred
to as Manual Undo Management Mode and the new Undo
Tablespaces method as the Automatic Undo Management
Mode. Although both rollback Segments and Undo Tablespaces
are supported in Oracle 9i, Both modes cannot be
used. System Rollback segment exists in both the
modes. Since we are all familiar with the manual
mode, lets look at the features of the Automatic
Undo Management (Undo Tablespaces )
Init.ora Parameters for Automatic Undo Management
UNDO_MANAGEMENT : This parameter sets the mode in which oracle manages the Undo Information.
The default value for this parameter is MANUAL
so that all your old init.ora files can be used
without any changes. To set the database in an automated
mode, set this value to AUTO. ( UNDO_MANAGEMENT
= AUTO)
UNDO_TABLESPACE : This parameter defines the tablespaces
that are to be used as Undo Tablespaces. If no value
is specified oracle grabs the first available Undo
Tablespace or if there are none present, Oracle
will use the system rollback segment to startup.
This value is dynamic and can be changed online
( UNDO_TABLESPACE = undo_tbs1 )
UNDO_RETENTION : This value specifies the amount
of time, Undo is kept in the tablespace. This applies
to both committed and uncommitted transactions since
the introduction of FlashBack Query feature in Oracle
needs this information to create a read consistent
copy of the data in the past. Default value is 900
Secs ( UNDO_RETENTION = 500)
UNDO_SUPRESS_ERRORS : This is a good thing to know
about in case your code has the alter transaction
commands that perform manual undo management operations.
Set this to true to suppress the errors generated
when manual management SQL operations are issued
in an automated management mode.
Creating and Managing Undo Tablespaces :
Undo tablespaces use syntax that is similar to
regular tablespaces except that they use the keyword
UNDO. These tablespaces can be created during the
database creation time or can be added to an existing
database using the create UNDO Tablespace command
Create DATABASE uday controlfile ...........
UNDO Tablespace undo_tbs0 datafile '/vol1/data/uday/undotbs0.dbf'
...
Create UNDO Tablespace undo_tbs1
datafile '/vol1/data/uday/undotbs1.dbf' size 25m
autoextend on;All operations like Renaming a data file, Adding
a datafile, Online /Offline Swith or Start Backup
/ End Backup Switch can be made using the regular
alter tablespace command. All other operations are
managed by Oracle in the automated management mode.
Monitoring:
v$UNDOSTAT: This view contains statistics for
monitoring the effects of transaction execution
on Undo Space in the current instance. These are
available for space usage, transaction concurrency
and length of query operations. This view contains
information that spans over a 24 hour period and
each row in this view contains data for a 10 minute
interval specified by the BEGIN_TIME and END_TIME.
