By Franck Pachot

.
In multitenant, the spfile parameters at pluggable database level are not stored in the spfile but in the CDB$ROOT table SYS.PDB_SPFILE$
If you open the standby database in read only for reporting, which is one reason to get Active Data Guard option, you may want different optimizer settings suited for reporting. In non-CDB it’s easy: the standby has its own spfile where parameters can be changed. But for a pluggable database, it’s stored in a table which is impossible to update in a read-only database, but there’s a solution.

Let’s say I’m not a big fan of Adaptive Dynamic Sampling for my OLTP application and set the optimizer_dynamic_sampling to 0 in my PDB:


SQL> alter session set container=PDB001;
Session altered.
SQL> alter system set optimizer_dynamic_sampling=0 scope=spfile;
System altered.
SQL> show spparameter optimizer_dynamic_sampling;
SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- -------------------------
*        optimizer_dynamic_sampling    integer     0

As I said this is stored in the CDB$ROOT:


SQL> select db_uniq_name,pdb_uid,sid,name,value$ from pdb_spfile$;
DB_UNIQ    PDB_UID SID NAME                       VALUE
------- ---------- --- -------------------------- -----
CDB     4058593923 *   optimizer_dynamic_sampling 0

Interesting things here. First, the pluggable database is identified by its PDB_UID which do not change on unplug/plug rather than the CON_ID which is related to the CDB.
Second there is a db_unique_name here which is the one of the CDB

Ignored in Standby

Now in my standby, which value do I have?


SQL> alter session set container=PDB001;
Session altered.
SQL> show parameter optimizer_dynamic_sampling;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2

This is the default value. Actually, no spfile parameter is set in the standby:


SQL> show spparameter optimizer_dynamic_sampling;
SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        optimizer_dynamic_sampling    integer

That’s not bad here but I want more. I want to set the level to 8 for my reporting activity here.
If I want to set it I’ll get: “ORA-65099: Operation cannot be performed when the CDB is not open” because it’s stored in the PDB_SPFILE$ table and we cannot update a table in a read-only database. I can change the value at CDB level, but this is not what I want to do. Let’s have a look at that PDB_SPFILE$ in the standby:


SQL> select db_uniq_name,pdb_uid,sid,name,value$ from pdb_spfile$;
DB_UNIQ    PDB_UID SID NAME                       VALUE
------- ---------- --- -------------------------- -----
CDB     4058593923 *   optimizer_dynamic_sampling 0

Of course, the table is replicated. It’s the same than in the primary database. This is where the DB_UNIQ_NAME columns is used: the parameter is not used here because the standby CDB has a different unique name.

Change in Standby

So how to change the parameter in the standby? Just change it in the primary but specify which db_unique_name it belongs to.
Back in the primary:


SQL> alter session set container=PDB001;
Session altered.
SQL> alter system set optimizer_dynamic_sampling=8 scope=spfile db_unique_name='CDB_ADG';
System altered.
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> select db_uniq_name,pdb_uid,sid,name,value$ from pdb_spfile$;
DB_UNIQ    PDB_UID SID NAME                       VALUE
------- ---------- --- -------------------------- -----
CDB     4058593923 *   optimizer_dynamic_sampling 0
CDB_ADG 4058593923 *   optimizer_dynamic_sampling 8

This is perfect. The database has both entries, and when in the standby I open the PDB read only it get the right value:


SQL> show spparameter optimizer_dynamic_sampling;
SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        optimizer_dynamic_sampling    integer     8

Reset

If I want to remove a parameter from spfile, I can use the ALTER SYSTEM RESET for it. If I want to remove it on the primary, then no problem no need to specify the db_unique_name as the current one is the default. But let’s try to reset the one for the standby:


SQL> alter session set container=PDB001;
Session altered.
SQL> alter system reset optimizer_dynamic_sampling scope=spfile db_unique_name='CDB_ADG';
System altered.

But there’s a bug. The db_unique_name is ignored so the wrong one has been removed:


SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> select db_uniq_name,pdb_uid,sid,name,value$ from pdb_spfile$;
DB_UNIQ    PDB_UID SID NAME                       VALUE
------- ---------- --- -------------------------- -----
CDB_ADG 4058593923 *   optimizer_dynamic_sampling 8

If I try it again, I’ll get a “ORA-32010: cannot find entry to delete in SPFILE”

Bug

This is a bug and I’ll put the bug number as soon as My Oracle Support engineer dares to reproduce that 3 lines test-case.

Workaround

The workaround is easy. It’s stored in a table so you can just remove the row and commit. I’ve sql_traced it, it’s just a delete except that it uses the wrong db_unique_name. That trace is my Plan B to get a bug opened for that. The 4 lines test case in case of the 3 lines one is not sufficient…

Documentation

You don’t find that db_unique_name clause in the ALTER SYSTEM documentation. However, it’s documented in MOS Doc ID 2101638.1
The reset is not documented at all, but there’s no reason to have a SET command without a RESET. Ok, I know for scope=memory you have a SET without RESET, but that’s another problem. And Enhancement Request to fill.