Oracle

PDB Saved state – 12.1.0.2

Posted Anand

Prior to 12.1.0.2 version, whenever container database was restarted,the pluggable databases within the container database remained in MOUNT state. Startup trigger were written to open the database in READ-WRITE/READ-ONLY mode.Starting from 12.1.0.2, this can be done with PDB save state feature

SQL> show pdbs
    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ----------------- ---------- ----------
	 2 PDB$SEED			     READ ONLY  NO
	 3 ALPDB			     READ WRITE NO

Lets create a new PDB

SQL>
SQL> create pluggable database hydb admin user anand identified by anand123 role = (dba)
  2  file_name_convert =('/u01/app/oracle/oradata/orcl/pdbseed/','/u01/app/oracle/oradata/orcl/hydb/')
  3  storage (maxsize 500M);
create pluggable database hydb admin user anand identified by anand123 role = (dba)
*
ERROR at line 1:
ORA-65113: value of MAX_PDB_STORAGE property for the PDB is too low
SQL> create pluggable database hydb admin user anand identified by anand123 role = (dba)
    file_name_convert =('/u01/app/oracle/oradata/orcl/pdbseed/','/u01/app/oracle/oradata/orcl/hydb/')
    storage (maxsize 1G);
  2    3
Pluggable database created.

Check status

SQL> @cdb_pdbs
    PDB_ID	 DBID PDB_NAME	     STATUS
---------- ---------- -------------- ----------
	 3 2221989451 ALPDB	     NORMAL
	 2  385653993 PDB$SEED	     NORMAL
	 4 4008421982 HYDB	     NEW
SQL> show pdbs
    CON_ID CON_NAME		  OPEN MODE  RESTRICTED
---------- -------------- ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ALPDB			  READ WRITE NO
	 4 HYDB 			  MOUNTED
SQL>
SQL> alter pluggable database hydb open;
Pluggable database altered.
SQL> show pdbs
    CON_ID CON_NAME	      OPEN MODE  RESTRICTED
---------- ------------   ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ALPDB			  READ WRITE NO
	 4 HYDB 			  READ WRITE NO 

SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b
  2  where a.con_id = b.con_id;
no rows selected

DBA_PDB_SAVED_STATES can be used to check PDBs in saved state. We have none PDBs in saved state as per the above output.

SQL> alter pluggable database hydb save state;
Pluggable database altered.
SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b
  2  where a.con_id = b.con_id;
NAME				 STATE
------------------------------- --------------
HYDB				 OPEN

Now lets, restart the container

SQL> shu abort
ORACLE instance shut down.
SQL> SQL>
SQL> startup
ORACLE instance started.
Total System Global Area  838860800 bytes
Fixed Size		    2929936 bytes
Variable Size		  562039536 bytes
Database Buffers	  268435456 bytes
Redo Buffers		    5455872 bytes
Database mounted.
Database opened.
SQL> show pdbs
    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------ ---------- ----------
	 2 PDB$SEED			       READ ONLY  NO
	 3 ALPDB			       MOUNTED    
	 4 HYDB 			       READ WRITE NO
SQL>

To discard the save state

SQL> alter pluggable database HYDB discard state;
Pluggable database altered.
SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b
  2  where a.con_id = b.con_id;
no rows selected
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  838860800 bytes
Fixed Size		    2929936 bytes
Variable Size		  562039536 bytes
Database Buffers	  268435456 bytes
Redo Buffers		    5455872 bytes
Database mounted.
Database opened.
SQL> show pdbs
    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ----------------- ---------- ----------
	 2 PDB$SEED			       READ ONLY  NO
	 3 ALPDB			       MOUNTED
	 4 HYDB 			       MOUNTED
SQL>

Leave a Reply