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>