Undo_retention For Oracle PDB After Applying Oct 2020 19.9 Patch In Oracle Cloud (Doc ID 2797924.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

--> In version 19.9 it is mandatory to change it for every PDB. And it has to be a manually change. One by one PDB.

--> It seems that container=all clause is not working correctly.

--> Parameter undo_retention not getting persisted after restart the PDB. It goes to default value again undo_retention = 900

VERIFICATION EXAMPLE:

SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;

Session altered.

SQL> select name,value,default_value,con_id from GV$system_parameter where name = 'undo_retention';

NAME VALUE DEFAULT_VALUE
------------------------- ------------------------- -------------------------
CON_ID
----------
undo_retention 6000 900
0

undo_retention 900 900
2

undo_retention 3000 900
3

SQL> ALTER SYSTEM SET undo_retention=4000 CONTAINER=ALL scope=BOTH; <----change of parameter at level CDB$ROOT using container=all

System altered.

SQL> select name,value,default_value,con_id from GV$system_parameter where name = 'undo_retention';

NAME VALUE DEFAULT_VALUE
------------------------- ------------------------- -------------------------
CON_ID
----------
undo_retention 4000 900
0

undo_retention 4000 900
2

undo_retention 4000 900
3

SQL> shutdown immediate
SQL> startup

SQL> select name,value,default_value,con_id from GV$system_parameter where name = 'undo_retention'; <----- Verifying since CDB$ROOT

NAME VALUE DEFAULT_VALUE
------------------------- ------------------------- -------------------------
CON_ID
----------
undo_retention 4000 900
0

undo_retention 900 900
2

undo_retention 900 900 <-------Changed value was NOT preserved
3

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> ALTER SESSION SET CONTAINER=pdb199; <----------------Verifying since PDB

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB199 READ WRITE NO
SQL> show con_name

CON_NAME
------------------------------
PDB199
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900 <----------------Changed value was NOT preserved
undo_tablespace string UNDOTBS1

Changes

-->The patch for  Database Release Update : 19.9.0.0.201020 was applied in Oracle Cloud

-->Before to apply the 19.9 patch the parameter undo_retention can be set in CDB$ROOT for all PDB:

ALTER SYSTEM SET undo_retention=21600 CONTAINER=ALL scope=BOTH;

NAME VALUE DEFAULT_VALUE CON_ID
-------------- ----- ------------- ------
undo_retention 21600 900 0

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.