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.