SPFILE Parameter: max_pdbs – a must for Single Tenant

Sometimes my job has a aspect making me smile at the end of the day 😉

I sat together with Johannes Ahrends during a talk at the OUGN Conference on the boat from Oslo towards Kiel. And we were discussing afterwards why there’s no official way to limit the number of PDBs which will be essential for customers wanting to go the Single Tenant track. I had my Hands-On environment up and we played a bit in the break recognizing that a constraint on CONTAINER$ won’t be the correct solution as unplug/plug operations leave leftovers in it unless you DROP PLUGGABLE DATABASE. And even if you drop the remains the constraint solution does not work.

At the breakfast the next morning Johannes mentioned a trigger – and he published one soon after. But it is not a good idea in terms of keeping support for your database when you fiddle around with the data dictionary.

So I did ask my contacts internally and received a message saying clearly
“Somebody doesn’t want this.“. 🙂
Well, I work long enough for Oracle to know how to read it. It’s a common thing to blame it on “Somebody” when you don’t want to discuss things further. One can hide perfectly well behind “Somebody“.

The higher my surprise was when I started testing and playing with Oracle Database 12.2 – and collecting init.ora parameters between release labels to detect changes and additions. And apparently, this one here appeared:

• MAX_PDBS

as a new parameter in Oracle Database 12.2 saying “max number of pdbs allowed in CDB or Application ROOT” in its parameter description.
I was (a) surprised and (b) happy and (c) had to try it out immediately in my environment where I had already 3 PDBs (the PDB$SEED does not count):

SQL> alter system set max_pdbs=3;
System altered.

SQL> show pdbs

CON_ID CON_NAME               OPEN MODE  RESTRICTED
------ ---------------------- ---------- ----------
2      PDB$SEED               READ ONLY  NO
3      PDB1                   READ WRITE NO
4      PDB2                   READ WRITE NO
5      CLONE_PDB              MOUNTED

SQL> alter system set max_pdbs=2;
alter system set max_pdbs=2
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-65331: DDL on a data link table is outside an application action.
.

So even though the error message is a bit rough (at least in my release drop) the parameter does what we want. In a single tenant environment you’ll set it to “1” and prevent the creation or plugin of a 2nd PDB in this container database..

Another test with a fresh container database:

SQL> show pdbs

CON_ID CON_NAME      OPEN MODE  RESTRICTED
------ ------------- ---------- ----------
2      PDB$SEED      READ ONLY  NO


SQL> alter system set max_pdbs=1;
System altered.

SQL> show parameter max_pdbs

NAME                     TYPE     VALUE
------------------------ -------- ----- 
max_pdbs                 integer      1


SQL> create pluggable database pdb1 admin user adm identified by adm file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb1');
Pluggable database created.

SQL> create pluggable database pdb2 admin user adm identified by adm file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb2');

create pluggable database pdb2 admin user adm identified by adm file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb2')
*
ERROR at line 1:
ORA-65010: maximum number of pluggable databases created

SQL> drop pluggable database pdb1 including datafiles;
Pluggable database dropped.

SQL> create pluggable database pdb2 admin user adm identified by adm file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb2');
Pluggable database created.

Looks like a solid solution to me.

But please see also this blog post by Oracle ACE Director Franck Pachot about an issue with the parameter:
http://blog.dbi-services.com/oracle-12cr2-max_pdbs/

–Mike