Oracle UNDO Management

Undo Management stores the previous data (state) of data that’s been modified (INSERT, UPDATE, DELETE).

Undo Records Used To:

• Transaction Rollback: Allows users to undo changes made by uncommitted transactions.
• Read Consistency: Ensures that queries always see a consistent snapshot of the data, even if other transactions are modifying the data simultaneously.
• Flashback Queries: Provides the ability to view data as it existed at a prior point in time.
• Recovering from Database Failures: Uses undo data during recovery to restore the database to a consistent state after a crash.

Oracle saves undo data until:

• Commits the Transaction
• Rollback the Transaction
• Execute The DDL Queries
• Session was terminated abnormally. (Transaction was Rollback)
• Session was terminated normally. (Transaction was committed)

Only One Active undo tablespace for an instance and SYS users is owner of undo tablespace.

Oracle provides two modes of undo management:

1. Manual Undo Management: Involves manually configuring. This mode was used in older Oracle versions.
2. Automatic Undo Management (AUM): Introduced in Oracle 9i and In Oracle 12c and 19c, AUM is the default, this mode automatically handling the creation, sizing, and management of undo tablespaces.

Benefits of Automatic Undo Management:

• Reduces the manual effort in managing rollback segments.
• Automatically resizes undo segments based on workload.
• Helps in providing stable read consistency and support for flashback operations.
• Oracle AUM enhances performance, reduces administrative overhead, and improves overall database reliability.

Undo Management in Oracle 12c/19c Environments:

Container Databases (CDB) and Pluggable Databases (PDB):

• In multitenant architectures introduced in Oracle 12c, undo management is slightly different.
• Shared Undo: In Oracle 12c, all PDBs in a CDB share a common undo tablespace managed at the CDB level.
• Local Undo Mode: Starting with Oracle 12.2 and default in 19c, each PDB can have its own undo tablespace, enhancing isolation and management.

In-Memory Undo:

• Oracle 12c introduced in-memory undo to improve performance for in-memory databases by storing undo records in memory.
• Reduces I/O overhead and speeds up transaction processing for in-memory workloads.

Parameters:

UNDO_MANAGEMENT: Specifies the mode of undo management (AUTO or MANUAL).

UNDO_TABLESPACE: Specifies the name of the undo tablespace to be used.

UNDO_RETENTION: Sets the retention period for undo data (Seconds), useful for queries that require historical data (like Flashback).

List out the tablespace:

select tablespace_name, contents, status from dba_tablespaces;

select tablespace_name, file_name, bytes/1024/1024 from dba_data_files;

select segement_name, owner, tablespace_name, status from dba_rollback_segs;

show parameter undo;

Add the data file on existing undo tablespace:

alter tablespace undotbs1 add datafile ‘/disk1/prod/data/undotbs1a.dbf’ size 100m autoextend on next 10m maxsize 500m;

alter tablespace undotbs1 add datafile ‘/disk1/prod/data/undotbs1a.dbf’ size 100m autoextend on next 10m maxsize unlimited;

Create the Undo tablespace:

create undo tablespace undotbs2 ‘/disk1/prod/data/undotbs2a.dbf’ size 1g reuse autoextend on;

alter system set undo_management auto;

alter system set undo_tablespace=undotbs2 scope=both;

alter system set undo_retention=2400 scope=both;

1. Undo Retention Guarantee:

• Guarantees that undo data is retained for the full duration specified by UNDO_RETENTION.
• Even if the undo tablespace runs out of space, Oracle won’t overwrite existing undo data within that time frame.
• This is useful when you need to ensure that long-running queries or Flashback operations can always access the required undo data.

ALTER TABLESPACE undo_tbsp_name RETENTION GUARANTEE;

Impact of Guarantee:

• Pros: Ensures that undo data is available for the full retention period, improving reliability for queries and Flashback operations.
• Cons: If the undo tablespace runs out of space, Oracle might prevent new DML (INSERT, UPDATE, DELETE) transactions until space is freed up, potentially causing errors like ORA-30036: unable to extend segment in undo tablespace.

2. Undo Retention NoGuarantee:

• In NoGuarantee mode (the default mode), Oracle does not guarantee that undo data will be kept for the full UNDO_RETENTION period.
• If the undo tablespace needs space for new transactions, Oracle can overwrite undo data, even if it hasn’t yet reached the UNDO_RETENTION time.
• This is typically used in environments where you prioritize new transactions over keeping old undo data.

ALTER TABLESPACE undo_tbsp_name RETENTION NOGUARANTEE;

Impact of NoGuarantee:

• Pros: Ensures that new DML transactions can proceed, even if it means undo data is overwritten early.
• Cons: There’s a risk that long-running queries or Flashback operations might fail due to missing undo data, leading to issues like ORA-01555: snapshot too old.