Automatic Undo Management

Oracle9i New Feature Series: Automatic Undo Management

What is automatic undo?

Oracle manages undo space using the undo tablespace instead of rollback segments

Oracle manages the size and number of undo segments

Relieves DBA from creating and monitoring rollback segments

New type of tablespace required – UNDO TABLESPACE

New initialization parameters

UNDO_MANAGEMENT  - Decides the type of undo management.

AUTO specifies automatic undo management

MANUAL specifies pre-9i behavior where the DBA manages rollback segments. This is the default

UNDO_TABLESPACE – Name of the undo tablespace, this is a dynamic parameter, so you can change the name of the undo tablespace. You may have multiple undo tablespaces on the database, only one can be active at any given time. If you do not specify this parameter, oracle uses the first available undo tablespace.

UNDO_RETENTION - specifies (in seconds) the amount of committed undo information to retain in the database. The default is 900 seconds. Keep this value high to avoid “Snapshot too old” errors. This is also a dynamic parameter, which can be changed using ALTER SYSTEM.

UNDO_SUPPRESS_ERRORS - Suppress errors while executing manual undo management mode operations. If your application has SET TRANSACTION USE ROLLBACK SEGMENT statement, setting this parameter value to TRUE will suppress the error while in automatic undo.

How to create an undo tablespace?

Specify while creating the 9i database:

CREATE DATABASE … …
DATAFILE … …
UNDO TABLESPACE "UNDOTBS"
DATAFILE '/oradata/MYDB/undotbs01.dbf' SIZE 100M
CHARACTER SET … … …

Create using the CREATE TABLESPACE syntax:

CREATE UNDO TABLESPACE UNDOTBS
DATAFILE '/oradata/MYDB/undotbs01.dbf' SIZE 100M

You can specify only DATAFILE and EXTENT MANAGEMENT LOCAL clauses when creating the UNDO TABLESPACE.

I just upgraded my database to 9i, how do I enable automatic undo management?

Create an undo tablespace

Include the UNDO_ parameters in your pfile or spfile. If using spfile, make sure you specify SCOPE=SPFILE

Remove the rollback_segments parameter, if present in the init file. This will prevent the rollback segments going online when the database is started.

Shutdown the database – make sure NOT to do SHUTDOWN ABORT.

Startup the database.

Drop the rollback segments and the rollback tablespace, if empty.

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. © The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.