Pluggable Database save state (automatic start)

On this post I will show one interesting feature on 12c at version 12.1.0.2, save state of a pluggable database.
After restart a container database all pluggables databases stays on mount state, imagine if you are a DBA with more than 100 PDBs.
You may need to write a trigger event or a script to put the PDBs on write mode, this operations could be complexs.
Considering that not all PDBs don’t have to stay on write mode.

Let’s start:

For PDBs stay accessible, they need to stay on read / write mode:

SQL> SHOW PDBS

    CON_ID CON_NAME   OPEN MODE
    ---------------   ----------
	 2 PDB$SEED   READ ONLY
	 3 DBUPGR     READ WRITE
	 4 PRDB1      READ WRITE
	 5 PDB3       READ WRITE

After a startup of CDB we notice that PDBs are on mount state:

SQL> startup
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size		 2923920 bytes
Variable Size		 788529776 bytes
Database Buffers	 452984832 bytes
Redo Buffers		 13852672 bytes
Database mounted.
Database opened.

SQL> show pdbs

    CON_ID CON_NAME	 OPEN MODE
------------------------ ----------
	 2 PDB$SEED	  READ ONLY
	 3 DBUPGR	  MOUNTED
	 4 PRDB1	  MOUNTED
	 5 PDB3 	  MOUNTED

We will bring all PDBs UP with manual intervention:

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
Pluggable database altered.	 

SQL> SHOW PDBS

    CON_ID CON_NAME   OPEN MODE
    ---------------   ----------
	 2 PDB$SEED   READ ONLY
	 3 DBUPGR     READ WRITE
	 4 PRDB1      READ WRITE
	 5 PDB3       READ WRITE

Now this is the command that will save the state of PDB:

SQL> alter pluggable database PDB3 SAVE STATE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE PRDB1 SAVE STATE;

Pluggable database altered.

After this command, when the container was started the PDBs, will stay UP automatic.

NOTE: On RAC or RAC One node environments you need to run this command to reflect in all instances:

SQL> ALTER PLUGGABLE DATABASE PDB3 SAVE STATE INSTANCES=ALL;

We can check the DBA_PDB_SAVED_STATES dictionary tables to view what PDBs are with save states,

SQL> DESC DBA_PDB_SAVED_STATES
 Name			                Null?       Type
 ------------------------------------- --------- --------
 CON_ID 			        NOT NULL    NUMBER
 CON_NAME			        NOT NULL    VARCHAR2(128)
 INSTANCE_NAME			        NOT NULL    VARCHAR2(128)
 CON_UID			        NOT NULL    NUMBER
 GUID					            RAW(16)
 STATE					            VARCHAR2(14)
 RESTRICTED				            VARCHAR2(3)

SQL> SELECT * FROM DBA_PDB_SAVED_STATES;

CON_ID CON_NAME   INSTANCE_N  CON_UID      GUID				  STATE		RES
------ ---------- ---------- ------------ -------------------------------- ----------- ---
     5 PDB3	  CDB1	     2035629807   315B16CA3DAE0C57E0530F02000A953B OPEN	        NO
     4 PRDB1      CDB1	     1036549335   315B16CA3DAD0C57E0530F02000A953B OPEN	        NO

In case that you do not need of the actual state of the PDB you can remove from them

SQL> ALTER PLUGGABLE DATABASE PDB3 DISCARD STATE;

You can run this command for all PDBs:

SQL> ALTER PLUGGABLE DATABASE ALL SAVE STATE;
Pluggable database altered.

After that all PDBs will stay UP when the container started:

I hope that you enjoy.
Cheers
Felipe.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s