Unable to Set UNDO_RETENTION in Standby PDB

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  NO

The 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.

Local Undo in 12.2 PDB in RAC

In 12.1 all PDBs shared a single undo tablespace. With 12.2 oracle introduced local undo, where each container has it's own undo tablespace on each instance it is opened. Which mode is currently used could be checked with following.

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME        PROPERTY_VALUE  DESCRIPTION
-------------------- --------------- ------------------------------
LOCAL_UNDO_ENABLED   TRUE            true if local undo is enabled

It is possible to move from local undo mode to shared undo mode and vice versa.

In RAC instances this means there would be undo tablespaces per instance and per PDB. However, due to automatic naming convention used this may not be obvious until datafile path is examined. Below example was on a base release of 12.2 with Patch 26124078 applied [ID 2497491.1].

RAC CDB consists of two instances.

SQL> select inst_id,instance_name from gv$instance;

   INST_ID INSTANCE_NAME
---------- ----------------
         1 cdb12c1
         2 cdb12c2

Currently there are no PDBs except the seed.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO

The current undo tablespaces are their datafile locations are as follows

    CON_ID TBLSPACE   NAME
---------- ---------- --------------------------------------------------------------------------------
         1 UNDOTBS1   +DATA/ORCL/DATAFILE/undotbs1.279.1000054779
         1 UNDOTBS2   +DATA/ORCL/DATAFILE/undotbs2.289.1000054817
         2 UNDOTBS1   +DATA/ORCL/81B657D10A1B2EE8E0535D00A8C08053/DATAFILE/undotbs1.295.1000054779

Each instance has it's own undo tablespace which is the expected setup on RAC.

SQL> select inst_id,con_id,name,value from gv$parameter where name='undo_tablespace' order by 1;

   INST_ID     CON_ID NAME                      VALUE
---------- ---------- ------------------------- --------------------
         1          1 undo_tablespace           UNDOTBS1
         2          1 undo_tablespace           UNDOTBS2

When creating a PDB and opening it which will create the undo tablespace for remote instance's PDB.

SQL> create pluggable database pdb3 admin user pdb1 identified by pdb1 default tablespace users ;

Pluggable database created.

SQL> alter pluggable database pdb3 open instances=all;

Pluggable database altered.

The alert log on the remote instance shows the creation of this local undo tablespace for the PDB on that instance.

PDB3(3):Opening pdb with no Resource Manager plan active
PDB3(3):CREATE SMALLFILE UNDO TABLESPACE undo_2 DATAFILE '+DATA' SIZE 193986560 AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
2019-02-13T11:35:09.410262+00:00
PDB3(3):[4337] Successfully onlined Undo Tablespace 5.
PDB3(3):Completed: CREATE SMALLFILE UNDO TABLESPACE undo_2 DATAFILE '+DATA' SIZE 193986560 AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
Pluggable database PDB3 opened read write

Login to the PDB and query the instance specific local undo tablespaces.

SQL> alter session set container=pdb3;

Session altered.

SQL> select inst_id,con_id,name,value from gv$parameter where name='undo_tablespace' order by 1;

   INST_ID     CON_ID NAME                      VALUE
---------- ---------- ------------------------- ----------
         1          3 undo_tablespace           UNDOTBS1
         2          3 undo_tablespace           UNDO_2

It appears that the local undo tablespace for PDB on instance 1 uses the same as the CDB$root's undo tablespaces "UNDOTBS1" this is not the case. The datafile for this undo tablesapce shows it's different to root's undo tablespace.

SQL>  select t.con_id,t.name tblspace,d.name from  v$tablespace t, v$datafile d where  d.con_id=t.con_id and t.ts#=d.ts# and t.name like 'UND%' order by 1,2;

    CON_ID TBLSPACE   NAME
---------- ---------- -------------------------------------------------------------------------------
         3 UNDOTBS1   +DATA/ORCL/81C59E3616323828E0535D00A8C059DD/DATAFILE/undotbs1.288.1000121653
         3 UNDO_2     +DATA/ORCL/81C59E3616323828E0535D00A8C059DD/DATAFILE/undo_2.285.1000121707

This shows that each PDB in each instance gets a local UNDO tablespace.

Useful metalink notes
Undo Modes in 12.2 Multitenant Databases - Local and Shared Modes [ID 2169828.1]
Different levels of encrypted undo tablespace across instances PDB In a 12.2 RAC - OCI . [ID 2497491.1]