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:
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
Hi Mike, some days ago I faced a strange behavior in context with max_pdbs. After setting it to 3 on a single tenant 2-node-RAC as recommended I wasnt able to restart the second node, I got an ORA-29707 telling me that a value of 3 is inconsistent with other instances. Of course I changed the parameter using “scope=both” and “sid=’*'”. During additional tests it seems that its possible to solve the issue by bringing down both the instances and then restarting. Ive opened SR 3-24374037861 for this issue, up to now with no sufficient explanation. May you have some experience with this kind of problem?
Warm regards
Axel D.
Hi, forgot to say that its a 18.8.0 (shame on me 😉 and that we have only one custom pdb of course!
Hi Mike,
strange, I already posted a comment some weeks ago but its gone 🙁
Anyway: it seems that changing max_pdbs in a RAC environment (2-node at least) requires a total shutdown of the database. I opened a SR for this question (as I couldnt find anything in the documentation nor in any blog), 3-24390729934, your colleagues say this is handled within “Bug 27434010 – OPC_RAC: FAIL TO BRING DB UP DUE TO ORA-29707” which I cant see as its internal.
Best regards
Axel D.
Hi Alex,
I’m not fully convinced yet – especially as the bug is marked as “not a bug” and has no real solution.
Also the base bug associated with it does not seem to be reproducible.
Hence, this information does not bring you further.
Background of the bug seems that a parameter change (e.g. alter system set max_pdbs=3 scope=spfile sid=’*’) does not get propagated to the other instances on the other nodes correctly.
Please insist in the SR for a solution and more information.
Cheers,
Mike