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.
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
Thanks Sreedhar!
–Mike
Thanks for this notice. How did you find the 8 parameters in the section “To which parameters does this apply?”? Are they in an Oracle document? You checked them manually and found them? If the latter, could there be any other? Thanks.
From the bug – not from the docs. Underscores don’t get documented.
Cheers,
Mike
Hi Mike,
This change has made it impossible to change the undo_retention in a persistent way on ADG standbys where you have it open for read only to run BI systems and etc.
Since PDB level parameters are kept in PDB_SPFILE$ which is in system tablespace writes as disallowed on it.
As such trying to change with spfile or both results in following.
Since PDB is on a standby database specifying “container=all” option which is the way to get the parameter value set across PDBs doesn’t work.
SQL> alter system set undo_retention=901 container=all scope=both;
alter system set undo_retention=901 container=all scope=both
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database or pluggable database open for read-only access
Even trying to change it at PDB level (login into pdb as sys) results in
SQL> alter system set undo_retention=902;
alter system set undo_retention=902
*
ERROR at line 1:
ORA-32000: write to SPFILE requested but SPFILE is not modifiable
Have to explicitly specify the memory.
SQL> alter system set undo_retention=902 scope=memory;
System altered.
Which means anytime standby is restarted then it must be set again.
regards,
Asanga
I clearly see this problem as well 🙁
Cheers,
Mike
After a SR, Oracle provided solution was to set the value on primary’s PDB and switch log file to sync with standbys.
As it turns out PDB parameter changes do get captured on redo and get transported and applied on standby (you have to bounce the standby PDB to see the effect).
Still a deviation from previous behaviour where standby’s could have their own values different to that of primary. But now if you want to change one standby PDB’s undo_retention then all the PDBs in the DG configuration will get the same value.
regards,
Asanga
I agree – and thanks for investigating and the update!
Cheers,
Mike
I had noticed this issue after applying the patch and opened a case, as the container=all syntax still did not worked and I needed to manually change the undo inside PDB.
SR 3-24752232981 : undo_retention for Oracle PDB after applying OCt 2020 19.9 patch in Oracle cloud
The case owner noted below.
I am pleased to inform you that the Bug 32368480 – UNDO_RETENTION VALUE IS NOT KEEP IN PDB AFTER APPLYING OCT 2020 19.9 PATCH
has been created, analyzed and fixed by development area. This fix is going to be included in a future RU, for now it has been fixed for Ver: 23.1
Thanks for your observations and for your patience.
Thanks a lot – this is good to know.
Actually the request to include the fix for Bug 32368480 – UNDO_RETENTION VALUE IS NOT KEEP IN PDB AFTER APPLYING OCT 2020 19.9 PATCH into a future RU has been just filed end of July 2021. So it won’t be included into the Oct 2021 RU for sure.
Cheers,
Mike
Thanks Mike for the great blog. This is my first stop to know about issues before applying the Quarterly patch in addition to known issues in MOS and twitter feed of Roy Swonger. I think oracle can use some Machine learning tools on patching issues and make this available as a feed for customers to make informed decision. When I looked into the change made by this fix by examining the back end tables, it was apparent to me that some one has done some mistake in understanding how these parameters work for PDBs and also how this will work with ADG read only instance. UNDO_RETENTION is a key parameter and if not correctly set can result in ORA-01555 errors. I hope we have a official way to document all the changes and bug fixes that are rolled up in a quarterly release and also a real time updates with information posted by customers. Having a dedicated twitter feed will be ideal.
Hi,
thanks for the feedback – and I would fully agree.
Regarding the ADG issue, I recommend that you push an SR forward which should lead to a bug. And it has more power if a customer does this.
I see your points here.
Regarding AI and the twitter feed:
This would be great and I will bring this up internally for sure when I have the chance.
Just be aware that not everybody wants to share as much information as we do via the blog 😉 That is always kind of a struggle 😉
Mike
Hi Mike,
Thanks for this great article. Came in Handy.
I noticed however that even after setting the undo_retention using
alter system set UNDO_RETENTION=36000 container=all;
The values for the PDB do not persist across reboots. I looked at our PDBs and the values had been reset to 900 after a reboot.
How do we get this value to persist across reboots?
SQL>show con_name
CON_NAME
——————————
TEST
SQL>alter system set undo_retention=21600 scope=both;
System SET altered.
Elapsed: 00:00:00.127
SQL>
SQL> col db_uniq_name format a10
SQL>col name format a15
SQL>col “value$” format a10
SQL>select db_uniq_name, pdb_uid, name, value$ from pdb_spfile$;
0 rows selected.
Elapsed: 00:00:00.062
The values don’t appear in the pdb_spfile$ view
Hi Tunde,
I hear from customers here and there complaining about it – but I have no ultimate solution.
I fear you may need to go through Oracle Support and insist on a solution please.
Thanks,
Mike
How can I get a list of all the parameters that are not inherited by the PDBs ?
I don’t know – please check with Oracle Support.
I know that parameters have attributes such as ISPDB_MODIFIABLE, but I guess this is not what you are looking for.
Cheers
Mike