Active Data Guard and PDB Parameters

In an earlier post it was shown how undo_retention value in a PDB that is part of an ADG standby could only be changed by updating the value on the primary PDB. In the case of undo_retention parameter Oracle introduced change 30577591 which prevented it from inheriting the value from the standby CDB.
This post looks at the effects of setting parameter values at PDB level in a ADG enviornment. It seems that the PDB in a standby CDB will inherit the init values from cdb$root spfile as long as the parameter is not set at PDB level in the primary.
open_cursors parameter is used as the test case here. The initial values at primary and standby are same and no value has been set at PDB level.

SQL> show parameter open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
open_cursors                         integer     3000

On the primary the value is changed at CDB level.

SQL> alter system set open_cursors=3500 scope=both;

System altered.

SQL> show parameter open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
open_cursors                         integer     3500

As no value has been set at PDB level it will inherit the value from CDB$root.

SQL> alter session set container=dgpdb;

Session altered.

SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
open_cursors                         integer     3500

Next the value is changed at PDB level.

SQL> alter session set container=dgpdb;

SQL>  alter system set open_cursors=2500;

System altered.

SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
open_cursors                         integer     2500

At this stage primary cdb$root has value 3500 and PDB has 2500. No changes has been made to standby and value there remains 3000.



On the standby checking the value in the PDB shows the initial value.

SQL>  alter session set container=dgpdb;

Session altered.

SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
open_cursors                         integer     3000

Changing the parameter value at CDB level and check value in PDB.

SQL>  alter system set open_cursors=4500 scope=both;

System altered.

SQL>  show parameter open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
open_cursors                         integer     4500

Value checked at PDB level.

SQL> alter session set container=dgpdb;

Session altered.

SQL> show parameter open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
open_cursors                         integer     4500

This shows value being inherited from standby cdb$root.
Next the PDB in the standby instance is restarted and parameter value checked.

SQL> shutdown ;
Pluggable Database closed.

SQL> startup;
Pluggable Database opened.

SQL> show parameter open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
open_cursors                         integer     2500

It not longer shows the 4500 value inherited from standby but the value set at primary PDB. It appears during PDB start the table PDB_SPFILE$ is read for PDB level parameter values. Since PDB_SPFILE$ is a database table, any writes to it will generate redo. As such changes made in primary PDB will cascade to all standby instances and to all relevant PDBs. When values are read from this table they are set for PDB overwriting values inherited from cdb$root spfile.
Any subsequent change to the init parameter at cdb$root doesn't override the value set at PDB level.

SQL> show con_name

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

SQL> alter system set open_cursors=4600 scope=both;

System altered.

SQL> alter session set container=dgpdb;

Session altered.

SQL>  show parameter open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
open_cursors                         integer     2500

This behaviour, inheriting from local cdb$root spfile vs primary PDB could have consequences for asymmetric data guard deployments where primary and standy differ in the amount of resources (memory,cpu) and user requirments (BI vs OLTP, user counts, etc). If for whatever an init value is set on PDB level at primary, remember that will get applied on all standby PDBs next time those are restarted.