Thursday, January 11, 2018

Auto restart of PDB in multi tenant ( CDB) database




Most of the time , when we start/restart  the container database ( CDB) , we would like one ot more pluggable database ( PDBs) to start with the CDB.

In 12c release 1 , the option was to use the trigger (AFTER STARTUP ON DATABASE )  to open the pdbs .  In 12c release 2 , the other option was to save the current state  , so that upon restart of the CDB , the PDB initializes to the saved state ( in our example , READ_WRITE). 


Here is an example. 

As it can be seen below , the PDB is not in 'OPEN' state once the CDB is restarted. 


SQL> col name format a20

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB_SIS              READ WRITE

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 1.0335E+10 bytes
Fixed Size                 12468584 bytes
Variable Size            2751467160 bytes
Database Buffers         7549747200 bytes
Redo Buffers               21082112 bytes
Database mounted.
Database opened.

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY

PDB_SIS              MOUNTED


Now , let us open the PDB , confirm its status and save the state. 
The view 'dba_pdb_saved_states' shows the saved state of all PDBs.

SQL> alter pluggable database PDB_SIS open ;

Pluggable database altered.

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB_SIS              READ WRITE

SQL> alter pluggable database PDB_SIS save state ;

Pluggable database altered.

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY

PDB_SIS              READ WRITE

SQL> col con_name format a20
SQL> select con_name , state from dba_pdb_saved_states;

CON_NAME             STATE
-------------------- --------------

PDB_SIS               OPEN


Now , let us bounce the CDB and look at the status of the PDB. Now , the PDBs  re-initalized to the saved state. 

SQL>  shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1.0335E+10 bytes
Fixed Size                 12468584 bytes
Variable Size            2751467160 bytes
Database Buffers         7549747200 bytes
Redo Buffers               21082112 bytes
Database mounted.
Database opened.
SQL> select con_name , state from dba_pdb_saved_states;

CON_NAME             STATE
-------------------- --------------
PDB_SIS               OPEN

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB_SIS               READ WRITE



Thanks !


No comments:

Post a Comment