Oracle Multitenant - PDB State Management Across CDB Restart (12.1.0.2.0)

Oracle Database 12c Release 1 (12.1.0.2.0) already released at July 22, 2014.
You can download new release from here.

Oracle Multitenant option also supports some news in Oracle Database 12c Release 1 (12.1.0.2.0).
This article covers PDB State Management in Oracle Multitenant option of Oracle Database 12c Release 1 (12.1.0.2) .

By default, any pluggable database (PDB) are not open in READ WRITE mode, when Multitenant Container Database (CDB) restarts. You can see this also from following example:

[oracle@oel62-ora12c2 ~]$ export ORACLE_SID=prmcdb
[oracle@oel62-ora12c2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 1 14:14:59 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> select cdb,name from v$database;        

CDB NAME
--- ---------
YES PRMCDB

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PRMPDB01         READ WRITE
PRMPDB02         READ WRITE
PRMPDB03         READ WRITE

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

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size      2932632 bytes
Variable Size    666894440 bytes
Database Buffers   398458880 bytes
Redo Buffers      5455872 bytes
Database mounted.
Database opened.

SQL> select name, open_mode from v$database;

NAME   OPEN_MODE
--------- --------------------
PRMCDB   READ WRITE

SQL> select name, open_mode from  v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PRMPDB01         MOUNTED
PRMPDB02         MOUNTED
PRMPDB03         MOUNTED

Prior to Oracle Database 12c Release 1 (12.1.0.2) somebody using system trigger (ON STARTUP) for open all (or some) pluggable databases like as below:

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

You can preserve the open mode of one or more PDBs when the CDB restarts after Oracle Database 12c Release 1 (12.1.0.2). So, the SAVE STATE clause and DISCARD STATE clause are now available with the ALTER PLUGGABLE DATABASE SQL statement to preserve the open mode of a pluggable database (PDB) across multitenant container database (CDB) restarts.

SQL> alter pluggable database all close;

Pluggable database alter

SQL> alter pluggable database prmpdb03 open; 

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PRMPDB01         MOUNTED
PRMPDB02         MOUNTED
PRMPDB03         READ WRITE

SQL> alter pluggable database prmpdb03 save state;

Pluggable database altered.

SQL> select con_id, con_name, state from dba_pdb_saved_states;

    CON_ID CON_NAME STATE
---------- ----------- ---------- 
     5     PRMPDB03     OPEN

As you seen, PDB's (prmpdb03) state are saved as OPEN. It means when CDB restarts, only state saved PDB (prmpdb03) will be opened automatically.

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

SQL> startup 
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size      2932632 bytes
Variable Size    666894440 bytes
Database Buffers   398458880 bytes
Redo Buffers      5455872 bytes
Database mounted.
Database opened.
SQL> 
SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PRMPDB01         MOUNTED
PRMPDB02         MOUNTED
PRMPDB03         READ WRITE   

Only pluggable database in READ WRITE or READ ONLY open mode, we can save state of PDB. So, in MOUNT mode cannot save state.

SQL> alter pluggable database prmpdb02 open read only;

Pluggable database altered.

SQL> alter pluggable database prmpdb02 save state;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PRMPDB01         MOUNTED
PRMPDB02         READ ONLY
PRMPDB03         READ WRITE

SQL> alter pluggable database prmpdb01 save state; 

Pluggable database altered.

SQL> select con_id, con_name, state from  dba_pdb_saved_states;

    CON_ID CON_NAME STATE
---------- ----------- ---------------- 
  5      PRMPDB03    OPEN
  4      PRMPDB02    OPEN READ ONLY
  

If you want ignore the PDBs' open mode when the CDB is restarted, then you must use DISCARD STATE in ALTER PLUGGABLE DATABASE statement for one or more PDBs.
When DISCARD STATE is specified for a PDB, the PDB is always mounted after the CDB is restarted.

SQL> alter pluggable database prmpdb03 discard state; 

Pluggable database altered.

SQL> select con_id, con_name, state from  dba_pdb_saved_states;

     CON_ID CON_NAME STATE
---------- ----------- ---------- 
  4      PRMPDB02    OPEN READ ONLY

SQL> select name, open_mode from v$pdbs; 

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PRMPDB01         MOUNTED
PRMPDB02         READ ONLY
PRMPDB03         READ WRITE

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

Total System Global Area 1073741824 bytes
Fixed Size      2932632 bytes
Variable Size    666894440 bytes
Database Buffers   398458880 bytes
Redo Buffers      5455872 bytes
Database mounted.
Database opened.
SQL> 
SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PRMPDB01         MOUNTED
PRMPDB02         READ ONLY
PRMPDB03         MOUNTED

As you seen, only PDB prmpdb02 opened in read-only mode. Because only this PDB's state are saved as OPEN READ ONLY.
We can use ALL SAVE STATE clause for save all PDB's state.

SQL> alter pluggable database all discard state;

Pluggable database altered.

SQL> select con_id, con_name, state from  dba_pdb_saved_states;

no rows selected

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PRMPDB01         MOUNTED
PRMPDB02         READ ONLY
PRMPDB03         MOUNTED

SQL> alter pluggable database all close;

Pluggable database altered.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PRMPDB01         READ WRITE
PRMPDB02         READ WRITE
PRMPDB03         READ WRITE

SQL> select con_id, con_name, state from  dba_pdb_saved_states;

no rows selected

SQL> alter pluggable database all save state; 

Pluggable database altered.

If you are save all PDB's state as OPEN, then across CDB restarts all PBDs will open READ WRITE mode in CDB as below:

SQL> select con_id, con_name, state from  dba_pdb_saved_states;

     CON_ID  CON_NAME STATE
---------- ----------- ---------- 
  5      PRMPDB03    OPEN
  4      PRMPDB02    OPEN
  3 PRMPDB01    OPEN

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

SQL> startup 
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size      2932632 bytes
Variable Size    666894440 bytes
Database Buffers   398458880 bytes
Redo Buffers      5455872 bytes
Database mounted.
Database opened.
SQL> 
SQL> select name, open_mode from  v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PRMPDB01         READ WRITE
PRMPDB02         READ WRITE
PRMPDB03         READ WRITE

For an Oracle RAC CDB, you can use the instances clause in the PDB SAVE or DISCARD  STATE clause to specify the instances on which a PDB's open mode is preserved in the following ways:

List one or more instances in the instances clause in the following form:

INSTANCES = ('instance_name' [,'instance_name'] … )

Specify ALL in the instances clause to modify the PDB in all running instances, as in the following example:

INSTANCES = ALL

Specify ALL EXCEPT in the instances clause to modify the PDB in all of the instances, except for the instances listed, in the following form:

INSTANCES = ALL EXCEPT('instance_name' [,'instance_name'] … )

For a PDB in an Oracle RAC CDB, SAVE STATE and DISCARD STATE only affect the mode of the current instance. They do not affect the mode of other instances, even if more than one instance is specified in the instances clause.


Regards
Mahir M. Quluzade