Startup PDB databases automatically when a container is started – good idea?
Posted by John Hallas on July 27, 2015
I posed a note on the Oracle-L Mailing list around pluggable database and why they were not opened automatically by default when the container database was opened. The post is below
I am trying to get my head around the thing about how pluggable databases react after the container database is restarted.
Pre 220.127.116.11 it was necessary to put a startup trigger in to run a ‘alter pluggable database all open;’ command to move them from mounted to open.
Now 18.104.22.168 allows you to save a state in advance using ‘alter pluggable database xxx save state’ which does seem a step forward
However why would the default not be to start all the pluggable databases (or services as they are seen) not leave them in a mounted state. Obviously Oracle have thought about this and changed the trigger method, maybe due to customer feedback but I wonder why they have not gone the whole hog and started the services automatically.
I would much prefer to have the default to be up and running rather than relying on the fact that I have saved the state previously
I did get some interesting and very helpful responses. Jared Still made a couple of good points. The first being that the opening time for all the pluggable databases might be very long if you had 300 of them. That blew my mind a little and I must admit that I had considered scenarios where you might have half a dozen maximum, not into the hundreds.
I did a little test on a virtual 2 CPU, 16Gb server, already loaded with 6 running non container databases. I created 11 pluggables (I have created a new word there) from an existing one – each one took less than 2 minutes
create pluggable database JH3PDB from JH2PDB;
create pluggable database JH4PDB from JH2PDB;
create pluggable database JH5PDB from JH2PDB;
create pluggable database JH6PDB from JH2PDB;
create pluggable database JH7PDB from JH2PDB;
create pluggable database JH8PDB from JH2PDB;
create pluggable database JH9PDB from JH2PDB;
create pluggable database JH10PDB from JH2PDB;
create pluggable database JH11PDB from JH2PDB;
create pluggable database JH12PDB from JH2PDB;
create pluggable database JH13PDB from JH2PDB;
They all default to the MOUNTED state so I then opened them all
SQL> alter pluggable database all open
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 99081426 PDB$SEED READ ONLY
3 3520566857 JHPDB READ WRITE
4 1404400467 JH1PDB READ WRITE
5 1704082268 JH2PDB READ WRITE
6 3352486718 JH3PDB READ WRITE
7 2191215773 JH4PDB READ WRITE
8 3937728224 JH5PDB READ WRITE
9 731805302 JH6PDB READ WRITE
10 1651785020 JH7PDB READ WRITE
11 769231648 JH8PDB READ WRITE
12 3682346625 JH9PDB READ WRITE
13 2206923020 JH10PDB READ WRITE
14 281114237 JH11PDB READ WRITE
15 2251469696 JH12PDB READ WRITE
16 260312931 JH13PDB READ WRITE
So 6:30 to open 11 PDBs might lead to a very long time for a very large number. That really answered the question I had asked. However there were more valuable nuggets to come.
Stefan Koehler pointed to a OTN community post where he advocated that the new (22.214.171.124) ‘save state’ for PDBs should also be extended to PDB services so that the service is started when the PDB is opened rather than having to use a custom script or a trigger. That seems a very reasonable proposal to me and will get my vote
Jared had an enhancement idea, instead of having a saved state, which I must admit is a bit messy, then why not a pdb control table with a START_MODE column?
– NEVER: Never open the pdb at startup
– ALWAYS: Always start this pdb.
– ON_DEMAND: Start this pdb when someone tries to connect to it.
And then some mechanism to override this.
‘startup open cdb nomount nopdb’ for instance.
It does sound an interesting idea, especially the ON_DEMAND option. I would have thought that if you were thinking along those lines a logical extension might be to auto unmount PDBs when they have not been used for a while, again controllable by a table.