Automatic Undo Management in Oracle

Back to: Oracle DBA Tutorials

Automatic Undo Management in Oracle

In this article, we will try to understand the Overview of Automatic Undo Management in Oracle. Oracle handles Undo management automatically. Please read our previous article discussing Local Undo Mode vs. Shared Undo in Oracle with examples.

Automatic Undo Management in Oracle

Oracle provides a fully automated mechanism for managing undo information and space. The Oracle database manages the undo segments in the undo tablespace. So, anything related to undo data will be stored in the undo segments.

Automatic undo management is the default mode for a newly installed database. When you create the database with the Database Configuration Assistant (DBCA), an auto-extending undo tablespace named UNDOTBS1 is automatically created.

We have created multiple pluggable databases using DBCA and queried the tablespaces. Oracle creates a tablespace called UNDOTBS1, which contains an auto-extend datafile.

When the database instance starts, it automatically selects the first available undo tablespace. If no undo tablespace is available, the instance starts without one. However, it stores the undo records in the system tablespace, which is not recommended. When a system is running without the undo tablespace, an alert message is written to the alert log file.

So, the DB should always check the alert log to see all the system errors. It is recommended that a separate undo tablespace be kept for each pluggable database.

We have a parameter called UNDO_MANAGEMENT. If the parameter value is AUTO, then UNDO_MANAGEMENT is automatic. If the parameter value is manual, undo management is in manual mode. The default mode is auto. There is another value, which is null. If the value is null, it is taken as default, and the default is auto.

Note: Space management for the rollback segments is complex, so Oracle strongly recommends leaving the database in automatic undo management mode.

There is another parameter called UNDO_TABLESPACE, which is optional and valid only in automatic undo management mode. This specifies the name of an undo tablespace. Use it only when the database has multiple undo tablespaces, and you want to direct the instance to use a particular undo tablespace.

In the next article, I will discuss the Undo Retention Period in Oracle. In this article, I explain Automatic Undo Management in Oracle with examples. I hope you enjoy this article.