Oracle DBA – A lifelong learning experience

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 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 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

Elapsed: 00:06:23.54

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 ( ‘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?

Possible values

– 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.



One Response to “Startup PDB databases automatically when a container is started – good idea?”

  1. jkstill said

    Nice idea for the ‘auto unmount’ option. That too should be something that can be controlled, possibly with a default of ‘NEVER’ and then change as needed.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: