UNDO_RETENTION not inherited to PDBs anymore since 19.9.0

Behavior changes introduced via a bug fix may not be something you like a lot. And thanks to Sreedhar from one of our most important customers I learned on the weekend: UNDO_RETENTION not inherited to PDBs anymore since 19.9.0.

Photo by Ben Wicks on Unsplash

What is happening before 19.9.0?

Until Oracle 19.8.0 you could change the UNDO_RETENTION in the CDB$ROOT, and it applied to all PDBs automatically. You may or may not have cared. But there are cases when you’d like to change an undo related parameter in the CDB$ROOT without its propagation into all PDBs.

This behavior has been tracked as BUG 30577591 – LOCAL UNDO RELATED PARAMETER VALUES AUTOMATICALLY PROPAGATING FROM ROOT TO PDBS.

What is happening from 19.9.0 onward?

Now with the fix for BUG 30577591 – LOCAL UNDO RELATED PARAMETER VALUES AUTOMATICALLY PROPAGATING FROM ROOT TO PDBS being included in 19.9.0 and all future bundle patches for Oracle Database 19c on:

alter session set container=CDB$ROOT;
alter system set UNDO_RETENTION=5000;

will apply only to CDB$ROOT now. In the PDBs at this point the default of 900 (unless you changed it beforehand already) will be valid.

If you’d like the change to be rolled forward to all PDBs, too, then you could issue instead:

alter system set UNDO_RETENTION=5000 container=all;

This makes more sense and gives you more control. But it is a change you should be aware of.

There is not much information available on MOS except for a one-off patch available for SPARC on top of 19.8.0.

Local vs Shared Undo?

This change of course applies to (the default) local undo only. When you use shared undo as it was the only available option in Oracle 12.1 with Multitenant, then a change will implicitly apply to the PDBs as well. But I don’t think that anybody is using shared undo from Oracle 12.2.0.1 on anymore since this would limit the use of key features.

To which parameters does this apply?

This is the list of parameters where the behavior has been changed from 19.9.0 onward.

_rollback_segment_count
_smu_timeouts
_smu_debug_mode
_undo_debug_mode
_highthreshold_undoretention
_undo_autotune
undo_retention
_collect_tempundo_stats

Hence, if you change any of them with an ALTER SYSTEM command within the root container be aware that it won’t be propagated to the PDBs unless you use the CONTAINER=ALL clause in your ALTER SYSTEM statement. Please be aware, too, that the CONTAINER clause allows only two values: ALL and CURRENT but no list of PDBs.

Further Information and Links

• UNDO_RETENTION Parameter in Oracle 19c

Thanks Sreedhar!

–Mike