This was first published on https://blog.dbi-services.com/12c-multitenant-pdb-spfile-parameters-for-standby-database (2016-03-16)
Republishing here for new followers. The content is related to the the versions available at the publication date
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 0As 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 0Interesting 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
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 2This 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 integerThat’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 0Of 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.
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 8This 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
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 8If I try it again, I’ll get a “ORA-32010: cannot find entry to delete in SPFILE”
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.
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…
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.
Have you been able to file a bug? What is the bug number?
Hi Alexei, Bug 22967827 – ora-32010 during alter system reset pdb parameter against active data guard cdb Regards, Franck.