How to Auto Open PDB When Startup CDB

  • by

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

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:

  1. Single-instance Database
  2. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *