Terraform Modules Simplified
Terraform is probably already the de-facto standard for cloud deployment. I use it on a daily basis deploying and destroying my tests and demo setups ... Read More
Découvrez pourquoi Eclipsys a été nommée 2023 Best Workplaces in Technology, Great Place to Work® Canada et Canada's Top 100 SME !
En savoir plus !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.
Terraform is probably already the de-facto standard for cloud deployment. I use it on a daily basis deploying and destroying my tests and demo setups ... Read More
If you’ve been following the recent changes in the Linux world you probably remember how Red Hat and Centos announced in December 2020 that the ... Read More