Oracle 12c, 12.1.0.2.0 Basic commands & PDB save state feature

How to check the current container and set container database:

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> show pdbs

    CON_ID CON_NAME  OPEN MODE  RESTRICTED

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

         2 PDB$SEED  READ ONLY  NO

         3 TEST1     READ WRITE NO

SQL> alter session set container='&CON_NAME';

SQL> show pdbs

How to create pluggable(PDB) database:

CREATE PLUGGABLE DATABASE  <PDB_NAME> ADMIN USER <Username> IDENTIFIED BY <Password>;

How to drop PDB database:

DROP PLUGGABLE DATABASE <PDB_NAME> INCLUDING DATAFILES;

How to open pluggable instance wise:

ALTER PLUGGABLE DATABASE <PDB_NAME>  OPEN INSTANCES=('instance_name’); 

ALTER PLUGGABLE DATABASE <PDB_NAME>  CLOSE INSTANCES=('instance_name’); 

How to open PDB in all instances:

       ALTER PLUGGABLE DATABASE <PDB_NAME> close instances =all; 

To check status PDB status: 

SQL>  SELECT INST_ID,NAME,OPEN_MODE,RESTRICTED FROM GV$PDBS;  

    INST_ID NAME          OPEN_MODE  RESTRICTED

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

         2 PDB$SEED      READ ONLY  NO

         2 TEST1         READ WRITE NO

         1 PDB$SEED      READ ONLY  NO

         1 TEST1         READ WRITE NO

        

PDB save state feature: 

In earlier releases of 12c (before 12.1.0.2.0) whenever container database was restarted all the pluggable databases associated with container database remains in MOUNT state.

From 12c release 12.1.0.2.0 this functionality can be achieved with new PDB save state feature.

       

Note: By default all the PDB's would be in MOUNTED state.

Example scenario:  

In this case node 2 has rebooted and observed on second instance went to mounted state and also pluggable database was not register in DBA_PDB_SAVED_STATES View, so after staring PDB on 2nd instance, I have registered the second instance into save state.

set pages 1200 lines 120

col CON_NAME for a7

col STATE for a7

col INSTANCE_NAME for a11

col RESTRICTED for a4

select CON_ID,CON_NAME,INSTANCE_NAME,STATE,RESTRICTED from DBA_PDB_SAVED_STATES;

    CON_ID CON_NAM INSTANCE_NA STATE   REST

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

         3 TEST1   testa1     OPEN    NO

TEST1 Pluggable database was not present in DBA_PDB_SAVED_STATES view , so have added using below commands.

SQL> SELECT INST_ID,NAME,OPEN_MODE,RESTRICTED FROM GV$PDBS;

   INST_ID NAME                           OPEN_MODE  RES

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

         1 PDB$SEED                       READ ONLY  NO

         1 TEST1                          READ WRITE NO

         2 PDB$SEED                       READ ONLY  NO

         2 TEST1                          MOUNTED          >>>Issue one >>>

SQL> ALTER PLUGGABLE DATABASE TEST1 OPEN INSTANCES=('test1a2');

SQL> alter pluggable database TEST1 save state;

Pluggable database altered.

SQL> SELECT INST_ID,NAME,OPEN_MODE,RESTRICTED FROM GV$PDBS;

  INST_ID NAME                           OPEN_MODE  RES

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

         1 PDB$SEED                       READ ONLY  NO

         1 TEST1                          READ WRITE NO

         2 PDB$SEED                       READ ONLY  NO

         2 TEST1                          READ WRITE NO

SQL> select CON_ID,CON_NAME,INSTANCE_NAME,STATE,RESTRICTED from DBA_PDB_SAVED_STATES;

    CON_ID CON_NAM INSTANCE_NA STATE   REST

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

         3 TEST1   test1a1     OPEN    NO

         

!! Hope this help you !! Cheers😊