Managing Undo Data Flashcards

A long-running report fails with an ORA-01555 "snapshot too old" error. What is the most likely cause?

Answer: The UNDO_RETENTION period is shorter than the query's execution time, causing necessary undo data to be overwritten.

The ORA-01555 error occurs when a query requires a version of a data block to maintain read consistency, but that version is no longer available in the undo tablespace. This typically happens when the undo information has been overwritten by newer transactions because the query's duration exceeded the configured UNDO_RETENTION period. [8, 10, 21]

Which initialization parameter must be set to 'AUTO' to enable Automatic Undo Management (AUM)?

Answer: UNDO_MANAGEMENT

The `UNDO_MANAGEMENT` parameter controls the undo mode for the instance. Setting it to `AUTO` enables Automatic Undo Management, where the database transparently manages undo segments within an undo tablespace. The legacy `MANUAL` mode requires manual DBA management of rollback segments. [4, 5, 6]

A database administrator needs to ensure that unexpired undo data is never overwritten, even if it means subsequent DML operations that require undo space will fail. Which action accomplishes this?

Answer: Enable `RETENTION GUARANTEE` on the active undo tablespace.

By default, `UNDO_RETENTION` is a target, not a strict guarantee. To prevent unexpired undo from being overwritten, you must enable `RETENTION GUARANTEE` on the undo tablespace using the `ALTER TABLESPACE ... RETENTION GUARANTEE` command. This forces the database to honor the retention period, causing new DML to fail if space runs out, rather than overwriting needed undo data. [1, 2, 16]

Which of the following are the three primary purposes of undo data in an Oracle database?

Answer: Transaction rollback, read consistency, and instance recovery.

Undo data is essential for three core database functions: 1) Rolling back uncommitted transactions (e.g., via a `ROLLBACK` statement), 2) Providing read consistency for queries so they see a consistent version of the data as it existed when the query began, and 3) Rolling back uncommitted changes during instance recovery after a crash. [14, 15, 22]

A DBA needs to analyze historical undo generation rates and identify the longest-running queries over the past few days to properly size the undo tablespace. Which dynamic performance view is best suited for this task?

Answer: V$UNDOSTAT

`V$UNDOSTAT` collects statistics on undo space usage over 10-minute intervals. It is the primary tool for monitoring undo generation (`UNDOBLKS`), transaction counts (`TXNCOUNT`), and identifying the duration of the longest query (`MAXQUERYLEN`) within each interval, making it ideal for tuning `UNDO_RETENTION` and sizing the undo tablespace. [2, 9, 20]

When using Automatic Undo Management (AUM), what happens if the instance starts and the undo tablespace specified in the `UNDO_TABLESPACE` parameter is not available?

Answer: The instance will start without an undo tablespace and use the SYSTEM tablespace for undo records.

If the specified undo tablespace is unavailable, or if none is specified and no undo tablespace is available, the instance will still start. However, it will store undo records in the `SYSTEM` tablespace. This is a non-recommended configuration, and an alert will be written to the alert log to warn the DBA. [1, 5]