Oracle DBA Lesson 64 Part 2 — Choose an Undo Remedy from the Failing Workload

A long report losing its required history and a transaction unable to allocate undo need different investigations. Use the affected workload, container, Oracle 19c Release Update (RU), incident interval, retention policy, and actual storage capacity together.

Oracle DBA Lesson 64 Part 2 — Choose an Undo Remedy from the Failing Workload

Follow the undo history

State	Meaning	Space implication
ACTIVE	Undo belongs to an uncommitted transaction	Needed for transaction rollback and recovery; cannot be overwritten to satisfy another transaction
UNEXPIRED	Committed history remains within the current undo retention period	Retained for consistent reads and flashback; NOGUARANTEE permits reuse under space pressure
EXPIRED	Committed history has aged beyond the current retention period	Eligible for reuse; expiration does not mean it has already been overwritten

Commit does not immediately free undo. The state progression in the video is a schematic for transaction history. Actual undo extents group records, and their aggregate status should be inspected rather than inferred from one transaction's commit.

Undo tablespaces have physical data files on disk. Undo blocks being accessed can also be cached in the database buffer cache in the SGA. The backward-moving marker represents a reader's logical request for an older version, not a physical block moving between memory and disk.

A statement that starts before an update commits may later need the older block version. If the required undo has been overwritten, the statement can fail with ORA-01555: snapshot too old. Undo age is measured after commit. Query duration is measured from the cursor's opening to its last fetch or execution. A long query can span multiple ten-minute statistics rows. Its duration alone does not establish the exact age or amount of undo it needs.

Check the scope and RU

These checks assume automatic undo management on an Oracle 19c primary database, a single instance, permanent-table work, and local undo for the application PDB. Record the database, instance, RU, and the original and affected containers before interpreting results.

In the affected application PDB, using SQL*Plus:

SHOW CON_NAME
SHOW PARAMETER undo_tablespace
SHOW PARAMETER undo_retention

Verify the actual container name. The selected UNDO_TABLESPACE identifies the instance's active undo selection. Recent switches can leave older transactions completing in the previous tablespace. Names alone do not establish the effective selection.

UNDO_RETENTION is expressed in seconds, with a default of 900. Automatic tuning can retain history longer. This setting does not create storage or guarantee success for every query.

• RU 19.7 and later: fixed-size and autoextending undo tablespaces honor UNDO_RETENTION when enough undo space is available. Automatic tuning may choose a larger interval.
• Earlier 19c RUs: fixed-size undo ordinarily ignores this setting unless retention guarantee is enabled. Verify the applicable version before applying a tuning prescription.
• RU 19.9 and later: changing UNDO_RETENTION in root does not automatically change it in every PDB. Inspect and manage the affected PDB value explicitly in local undo mode. Shared-undo PDBs cannot independently set this parameter. Establish the CDB's undo mode first.

See UNDO_RETENTION for the RU-specific behavior and scope.

Use the installed binary inventory and successful SQL patch application and rollback history to establish the actual patch state:

SELECT patch_id, action, status, action_time, description
FROM dba_registry_sqlpatch
ORDER BY action_time DESC;

A row records a patch action attempt. The last row alone is insufficient proof of the currently installed binary and SQL state. Check the relevant container and inventory. See DBA_REGISTRY_SQLPATCH.

Read the same incident interval

SELECT begin_time, end_time, maxquerylen,
       tuned_undoretention, ssolderrcnt, nospaceerrcnt
FROM v$undostat
ORDER BY begin_time DESC FETCH FIRST 6 ROWS ONLY;

Column	Interpretation
BEGIN_TIME / END_TIME	Boundaries for this statistics interval; normally ten minutes, with the current interval partial
MAXQUERYLEN	Longest query length in seconds for queries fetched/executed during the interval; a query can have started earlier
TUNED_UNDORETENTION	Tuned committed-undo retention in seconds; the latest value governs recycling eligibility
SSOLDERRCNT	Number of ORA-01555 occurrences in this interval
NOSPACEERRCNT	Undo space requests when all undo space is in use by active transactions

V$UNDOSTAT normally retains 576 rows, approximately four days. Match timestamps and the relevant database, instance, and container scope (CON_ID) to the application incident. For RAC, inspect the appropriate instance or use the corresponding global view with INST_ID. Do not combine unrelated instances. TUNED_UNDORETENTION is not meaningful for an Active Data Guard standby, which does not actively tune undo retention in the same way.

These counters are per interval, not cumulative totals since startup. They help prioritize investigation. They do not identify the exact failing query or establish every cause of ORA-01555. Correlate the actual error, query, transaction activity, and undo reuse. UNXPBLKREUCNT can support a reuse investigation.

See V$UNDOSTAT for intervals, durations, and counter definitions.

Compare two incident intervals

These two completed intervals are from the same application PDB and instance on the same day, with NOGUARANTEE as the baseline. They show different workloads. They are not a before-and-after tuning comparison. The video shows the rows as two interval columns. The SQL returns one row per interval.

BEGIN_TIME	END_TIME	MAXQUERYLEN	TUNED_UNDORETENTION	SSOLDERRCNT	NOSPACEERRCNT
10:00	10:10	1800	900	3	0
11:00	11:10	240	1800	0	2

10:00–10:10: the longest query is 1800 seconds, or 30 minutes, while tuned retention is 900 seconds, or 15 minutes. Three snapshot-too-old errors justify tracing the failing report's history requirement and actual undo reuse. The 30-minute query can have started before this ten-minute interval. The duration comparison alone does not prove that this query generated all three errors, or that 1800 seconds of retention would guarantee its success.

11:00–11:10: the no-space counter records two requests while all undo space was in active use. Inspect active transaction demand, undo generation, current allocation, and available growth. A higher retention setting cannot add physical capacity. This counter definition is not a unique diagnostic for retention-guarantee failures.

Retention policy and real capacity

SELECT tablespace_name, retention
FROM dba_tablespaces
WHERE contents = 'UNDO';

Match the policy to the selected undo tablespace. RETENTION reports GUARANTEE, NOGUARANTEE, or NOT APPLY where the property is inapplicable.

For the policy comparison, hold the condition constant: undo space is full, growth is unavailable, and a new writer needs space.

Policy	Effect on unexpired committed undo	Consequence under the stated condition
NOGUARANTEE	May be reused under space pressure	A writer may proceed, while a reader needing overwritten history may fail
GUARANTEE	Cannot be overwritten while unexpired	A new write may fail for lack of usable space

ACTIVE transaction undo remains protected in both cases. Guarantee preserves the retained unexpired history. It is not a promise that every query or flashback operation will succeed for all reasons and all ages. Assess writer impact before changing the policy.

See DBA_TABLESPACES for the retention policy, managing undo for retention, guarantee, and capacity, and ALTER TABLESPACE for the retention clause.

Useful additional capacity observations, substituting the verified selected tablespace:

SELECT tablespace_name, status, SUM(bytes) AS bytes
FROM dba_undo_extents
WHERE tablespace_name = 'UNDO_APP'
GROUP BY tablespace_name, status;

SELECT tablespace_name, file_id, bytes,
       autoextensible, maxbytes
FROM dba_data_files
WHERE tablespace_name = 'UNDO_APP'
ORDER BY file_id;

In RAC, inspect undo-extent distribution from the instance where that tablespace is active. Another instance's in-memory status information can differ. See DBA_UNDO_EXTENTS for undo states and instance reporting.

BYTES gives current allocation. AUTOEXTENSIBLE says whether growth is configured. MAXBYTES is its configured maximum. Maximum size is not a reservation of physical disk capacity. Check actual filesystem or ASM headroom, other consumers, file and platform limits, and growth increments separately. See DBA_DATA_FILES for current allocation and growth limits.

For readers, investigate history needs, duration, undo generation, and space to retain the history. Query or workload improvements may reduce demand. For writers, inspect active demand and allocatable capacity. Avoid a blind retention increase or a blanket commit-more-often prescription. Transaction boundaries must preserve application correctness.

Recap

Commit does not immediately free undo. ACTIVE undo belongs to an uncommitted transaction and cannot be overwritten to satisfy another transaction. UNEXPIRED committed history remains inside the current retention period for consistent reads and flashback, and NOGUARANTEE permits reuse under space pressure. EXPIRED history has aged beyond retention and is eligible for reuse. Extents group records, so inspect their aggregate status. Undo tablespaces store the blocks in data files on disk, and blocks being accessed can also sit in the database buffer cache in the SGA. If the required history has been overwritten, the statement can fail with ORA-01555: snapshot too old. Undo age is measured after commit. Query duration runs from cursor open to the last fetch or execution and can span several ten-minute statistics rows.

On an Oracle 19c primary with automatic undo and local undo, read UNDO_TABLESPACE and UNDO_RETENTION in the affected PDB, then confirm the RU. From RU 19.7, fixed-size and autoextending undo honor UNDO_RETENTION when enough space is available. Earlier 19c RUs ordinarily ignore that setting on fixed-size undo unless retention guarantee is enabled. From RU 19.9, changing UNDO_RETENTION in root does not update every PDB. V$UNDOSTAT counters are per interval. SSOLDERRCNT points toward snapshot-too-old errors and reader history. NOSPACEERRCNT points toward requests made when active transactions already hold all undo space. A higher retention setting does not add disk. With undo full and no growth, NOGUARANTEE may let a writer reuse unexpired committed undo, while GUARANTEE can make that writer fail. ACTIVE undo stays protected under both policies. MAXBYTES is a configured cap. Check autoextend, real filesystem or ASM headroom, and the other growth limits before relying on growth.

Quiz

1. On 19c RU 19.9 or later, does changing UNDO_RETENTION in root automatically update every PDB?

Yes, always Only when a report is running No. In local undo mode, inspect and manage the affected PDB value explicitly Only when undo is on disk

2. What happens to undo immediately after a transaction commits?

It becomes permanently unavailable to readers Its committed history can remain unexpired for consistent reads and flashback Every undo data file is deleted Its history is always instantly overwritten

3. What does NOSPACEERRCNT count?

All queries lasting more than ten minutes Successful commits Every configured autoextend limit Undo requests when all undo space is in use by active transactions

4. With full undo storage and no growth, how can GUARANTEE affect a new writer?

It can fail when no other usable space exists because unexpired undo is protected It gains unlimited disk capacity It can always overwrite active transaction undo It automatically shortens all reports

5. MAXBYTES exceeds current BYTES. What must be checked before relying on growth?

Only the tablespace's name Whether the report has a title Autoextend configuration, actual underlying free space, and applicable growth limits The number of columns selected

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily