The fix 30577591 which is included in the DBRU 19.9 changed how the undo_retention parameter value is set in a CDB. Prior to this the value was set at CDB root level and all PDBs inherited this value. But with this change this is not the case. The value set at CDB root is only assigned to CDB root. For PDB this must be explicityly set either by login into the PDB or using "container=all" clause if setting at CDB root level and want the value to be inherited by all PDBs. This change has been mentioned
here.
It seems Oracle has not foreseen the unintended consequence of this change. Mainly, how this would affect the standby databases. The database used in this case is using local undo.
SQL> select property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';
PROPERTY_VALUE
--------------------------------------------------------------------------------
TRUE
SQL> select ispdb_modifiable from v$parameter where name='undo_retention';
ISPDB
-----
TRUE
To demonstrate the issue at hand, assume the standby databases is in mount mode.
SQL> select open_mode from v$database;
OPEN_MODE
---------
MOUNTED
Current value for undo_retention is same for both cdb root and pdb and is the default 900.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
-- value at PDB
SQL> alter session set container=dgpdb;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
DGPDB
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
Set the undo_retention parameter at CDB root level.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter system set undo_retention=901 scope=both;
System altered.
This change appears to be applied on both CDB root and PDB level.
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 901
SQL> alter session set container=dgpdb;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
DGPDB
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 901
Value appears to be set at PDB because at mounted state the PDB seem to inherit it from the CDB.
Now open the CDB in read only mode.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 DGPDB READ ONLY NOThe change reamins on the CDB root but is lost on the PDB.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 901
SQL> alter session set container=dgpdb;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
DGPDB
-- change not inherited by pdb
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
Trying to set the value with container=all fails with the following.
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
Trying to set it by login into PDB also fails.
SQL> alter session set container=dgpdb;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
DGPDB
SQL> alter system set undo_retention=901;
alter system set undo_retention=901
*
ERROR at line 1:
ORA-32000: write to SPFILE requested but SPFILE is not modifiable
Reason for this is as undo_retention value is not inherited, it is kept as a local parameter of the PDB. To change this the new value must be written to the data dictionary (PDB_SPFILE$ table) this means writing it to the system tablespace where the pdb_spfile$ table resides. But for standby databases this cannot be done as writes are not allowed on standby.
Only workaround found so far is to set the value on the PDB to memory.
SQL> alter system set undo_retention=901 scope=memory;
System altered.
However, this means everytime there's a restart of the standby the change is lost and must be set again.
SR has been raised.
Update on 2021-08-13
After SR Oracle support provided solution was to set the undo_retention on the primary's PDB. As PDB parameters are written to table, redo gets generated and gets transported and applied on standby PDB. The standby PDB must be restarted in order for new value to take effect.
This is a major change for system parameters as it was not captured on redo. With this standby PDBs cannot have their own undo_retention values different to that of primary DB (if ADG primaryly used for BI and has different undo retention targets than primary).
Update on 2021-09-07
As a result of the SR filed an enhancement request (33283856) has been created.
