How to open the PDBS automatically when CDB restarts

In oracle 12c , when we startup the CDB, the PDBS will be in MOUNTED stage.
We need to open them manually using alter pluggable PDB open.

To make the PDBS open automatically, we can use the saved state option .

This feature is available from Oracle 12.1.0.2  onwards

1.  Restart the container database:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.

Total System Global Area 1.4529E+10 bytes
Fixed Size                  7649368 bytes
Variable Size            8489273256 bytes
Database Buffers         5939134464 bytes
Redo Buffers               93011968 bytes
Database mounted.
Database opened.

2. Check the status of PDBS:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
         5 NONCDB                         MOUNTED


 We can see the PDBS are in MOUNTED stage after starting CDB. Lets open them

3. Open the PDBS:

SQL> alter pluggable database NONCDB open;

Pluggable database altered.

SQL>  alter pluggable database PDB1 open;

Pluggable database altered.

SQL> alter pluggable database PDB2 open;

Pluggable database 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 NONCDB                         READ WRITE NO

 Now use save state command to save the states, so that next time ,when container db restarts,
PDBs will in READ WRITE MODE automatically.

 4. Save the PDB state:

SQL> alter pluggable database NONCDB save state;

Pluggable database altered.

SQL>  alter pluggable database PDB1 save state;

Pluggable database altered.

SQL> alter pluggable database PDB2 save state;

Pluggable database altered.


5. check the saved state in dba_pdb_saved_states

SQL> select con_name, state from dba_pdb_saved_states;

CON_NAME                STATE
----------------------- --------------
NONCDB                  OPEN
PDB1                    OPEN
PDB2                    OPEN


6.Bounce the container database, to check the PDB state:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.

Total System Global Area 1.4529E+10 bytes
Fixed Size 7649368 bytes
Variable Size 8489273256 bytes
Database Buffers 5939134464 bytes
Redo Buffers 93011968 bytes
Database mounted.
Database opened.

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 NONCDB READ WRITE NO


We can observed that PDBs are in READ WRITE mode automatically.

DISCARD STATE OPTION:

  We can discard the saved state, so that next time CDB restarts, PDBs will start with MOUNT STATE only.

Lets discard state for PDB2 .

SQL> select con_name, state from dba_pdb_saved_states;

CON_NAME                STATE
----------------------- --------------
NONCDB                  OPEN
PDB1                    OPEN
PDB2                    OPEN


SQL> alter pluggable database PDB2 discard state;

Pluggable database altered.

SQL>   select con_name, state from dba_pdb_saved_states;

CON_NAME                STATE
----------------------- --------------
NONCDB                  OPEN
PDB1                        OPEN


1. Bounce the CDB to check the PDB State:

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 NONCDB                         READ WRITE NO

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1.4529E+10 bytes
Fixed Size                  7649368 bytes
Variable Size            8489273256 bytes
Database Buffers         5939134464 bytes
Redo Buffers               93011968 bytes
Database mounted.
Database opened.

SQL>  show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           MOUNTED
         5 NONCDB                         READ WRITE NO




We can observe that, only PDB2 started in MOUNTED stage, because we have discarded the saved state for PDB2 .

As this feature is not available in 12.1.0.1 , we can create a trigger to open the PDBs automatically, with container database startup.

CREATE TRIGGER open_all_pdbs
  AFTER STARTUP ON DATABASE
BEGIN
   EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE ALL OPEN’;
END ;
/


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

Reference :-

https://dbaclass.com/article/how-to-open-the-pdbs-automatically-when-cdb-restarts/

https://community.oracle.com/thread/4086167?start=0&tstart=0