In oracle 12c , when we startup the CDB, the PDBS will be in MOUNTED stage. We need to open them manually using alter pluggable PDB open. To make the PDBS open automatically, we can use the saved state option . This feature is available from Oracle 12.1.0.2 onwards
For more information about CDB and PDB , refer Oracle 12c multitenent architecture
DEMO:
1. Restart the container database:
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1.4529E+10 bytes Fixed Size 7649368 bytes Variable Size 8489273256 bytes Database Buffers 5939134464 bytes Redo Buffers 93011968 bytes Database mounted. Database opened.
2. Check the status of PDBS:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
5 NONCDB MOUNTED
We can see the PDBS are in MOUNTED stage after starting CDB. Lets open them
3. Open the PDBS:
SQL> alter pluggable database NONCDB open;
Pluggable database altered.
SQL> alter pluggable database PDB1 open;
Pluggable database altered.
SQL> alter pluggable database PDB2 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 NONCDB READ WRITE NO
Now use save state command to save the states, so that next time ,when container db restarts, PDBs will in READ WRITE MODE automatically.
4. Save the PDB state:
SQL> alter pluggable database NONCDB save state; Pluggable database altered. SQL> alter pluggable database PDB1 save state; Pluggable database altered. SQL> alter pluggable database PDB2 save state; Pluggable database altered.
5. check the saved state in dba_pdb_saved_states
SQL> select con_name, state from dba_pdb_saved_states; CON_NAME STATE ----------------------- -------------- NONCDB OPEN PDB1 OPEN PDB2 OPEN
6.Bounce the container database, to check the PDB state:
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1.4529E+10 bytes Fixed Size 7649368 bytes Variable Size 8489273256 bytes Database Buffers 5939134464 bytes Redo Buffers 93011968 bytes Database mounted. Database opened. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE NO 5 NONCDB READ WRITE NO
We can observed that PDBs are in READ WRITE mode automatically.
DISCARD STATE OPTION:
We can discard the saved state, so that next time CDB restarts, PDBs will start with MOUNT STATE only.
Lets discard state for PDB2 .
SQL> select con_name, state from dba_pdb_saved_states; CON_NAME STATE ----------------------- -------------- NONCDB OPEN PDB1 OPEN PDB2 OPEN SQL> alter pluggable database PDB2 discard state; Pluggable database altered. SQL> select con_name, state from dba_pdb_saved_states; CON_NAME STATE ----------------------- -------------- NONCDB OPEN PDB1 OPEN
1. Bounce the CDB to check the PDB State:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 NONCDB READ WRITE NO
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1.4529E+10 bytes
Fixed Size 7649368 bytes
Variable Size 8489273256 bytes
Database Buffers 5939134464 bytes
Redo Buffers 93011968 bytes
Database mounted.
Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
5 NONCDB READ WRITE NO
We can observe that, only PDB2 started in MOUNTED stage, because we have discarded the saved state for PDB2 .
As this feature is not available in 12.1.0.1 , we can create a trigger to open the PDBs automatically, with container database startup.
CREATE TRIGGER open_all_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE ALL OPEN’;
END ;
/
SEE ALSO:
How to convert non-cdb to pdb in oracle 12c
