Working with Oracle multi-tenant architecture gives us some obvious benefits but also some challenges. What if we want to change a system parameter but only for a certain pluggable database (PDB) and keep it default for all others? Starting from 12.1 Oracle provides the ability to modify parameters on PDB level. If you look to a reference documentation for database parameters it states clearly whether it can be applied on CDB level or not. And with every new release we have more and more parameters which can be changed on PDB level. It has grown from 185 for 12.1.0.2 to 194 on 19.7.0.0.
The changing for a parameter is quite simple. You switch to the container you want to apply the parameter and run the standard “ALTER SYSTEM …” command or you can run it from the upper (CDB$ROOT) level and add clause container=all and apply it to all your containers.
testdb021> ALTER SESSION SET container=pdb02; SESSION altered. testdb021> ALTER system SET db_securefile='PREFERRED' sid='*' scope=spfile; System altered. testdb021>
Now we know how to modify a parameter. But where are those PDB scope parameters stored and how to verify it? If you try to read your spfile you will not be able to see the pdb scope parameters. The v$spparameter view doesn’t provide result either:
testdb021> SELECT COUNT(*) FROM v$spparameter WHERE con_id=2; COUNT(*) ---------- 0 testdb021>
But you can see the parameters in the v$system_parameter view filtering it by the con_id value:
testdb021> SELECT name,VALUE FROM v$system_parameter WHERE con_id=2 AND isdefault='FALSE'; NAME VALUE ----------------------------------- ---------------------------------------------------------------------- sga_target 0 undo_tablespace db_securefile PREFERRED testdb021>
So, we can check it in the v$system_parameter view. But how are they actually stored. We have a table pdb_spfile$ in the SYSTEM tablespace where all our PDB-scope parameters are defined.
estdb021>; SELECT sid,name,VALUE$,pdb_uid FROM pdb_spfile$; SID NAME VALUE$ PDB_UID ---------- ----------------------------------- -------------------------------------------------- ---------- * db_securefile 'PREFERRED' 2060554552 * db_securefile 'PREFERRED' 2007906006 testdb021 local_listener '(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.254.11)(PORT= 2007906006 1531))' testdb022 local_listener '(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.254.13)(PORT= 2007906006 1531))' * nls_date_format 'YYYY-MM-DD HH24:MI:SS' 2007906006 testdb021>
So that table is used to apply the parameters every time when you open the pluggable database. The PDB_UID value can be found in the v$pdb or dba_pdbs views.
testdb021> SELECT pdb_name,con_uid FROM dba_pdbs; PDB_NAME CON_UID -------------------------------------------------------------------------------------------------------------------------------- ---------- PDB$SEED 2060554552 PDB02 2007906006 testdb021>
Hope that small dive into the multitenant architecture can help you in your daily tasks or when you need to adjust your PDB according to requirements.
Happy tuning.