Alter Pluggable Database Open Automatically

ALTER PLUGGABLE DATABASE OPEN

By default, the pluggable databases (PDB) are at MOUNT state after you startup the container database (CDB).


SQL> conn / as sysdba

Connected.

SQL> show pdbs



    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 ORCLPDB                        MOUNTED

For using pluggable databases, we should open PDB to READ WRITE like this:


SQL> alter pluggable database all open;



Pluggable database altered.

Please note that, you can always change ALL into any specific PDB.


SQL> show pdbs



    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 ORCLPDB                        READ WRITE NO

If you feel annoying to open those PDB every time after starting up the database, you can configure it and make all PDB open automatically.

Solution

The solution is to save the state of every PDB before DB shutdown. I'll talk about it in two sections:

Single-instance Database
RAC Database

1. Single-instance Database

For specific PDB:


SQL> alter pluggable database <PDB_NAME> save state;



Pluggable database altered.

For all PDBs:


SQL> alter pluggable database all save state;



Pluggable database altered.

Let's restart the database to verify the settings.


SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.



...

Database mounted.

Database opened.


SQL> show pdbs



    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 ORCLPDB                        READ WRITE NO

2. RAC Database

Issuing ALTER PLUGGABLE DATABASE without specifying instance clause affects only current instance for a RAC database. To affect all instances, you have to specify INSTANCES=ALL.


SQL> alter pluggable database all open instances=all;



Pluggable database altered.



SQL> alter pluggable database all save state instances=all;



Pluggable database altered.

We list all of saved state below.


SQL> column con_name format a10;

SQL> column instance_name format a10;

SQL> select instance_name, con_id, con_name from dba_pdb_saved_states order by 1;



INSTANCE_N     CON_ID CON_NAME

---------- ---------- ----------

ORCLCDB1            3 ORCLPDB

ORCLCDB2            3 ORCLPDB

This is very important concept, if you found only a few connections to the PDB are successful after opening a Container Database (CDB). That's because you didn't save the OPEN state of the PDB on all instances.

Alter Pluggable Database Open Automatically