Automatic PDB startup after CDB Instance Bounce

Automatic PDB startup after CDB instance bounce:


In this article I will demonstrate that how we can preserve the PDBs state inside a Container database(CDB) which would enable PDBs to open in the same state upon CDB instance bounce. Earlier than patchset release 12.1.0.2, this feature was not available and we used to have our own scripts/database trigger to open all/required PDBs in read-write mode at next startup of container database.


In Oracle Database 12c Patchset (12.1.0.2), this new feature was introduced where we can save the current state of a PDB inside a CDB so that at next startup of CDB that Pluggable Database inside the CDB starts back automatically to the state that was preserved in.

By default, all PDBs(except PDB$SEED, this is a template pluggable database which remains in read-only mode and it is used to create a new pluggable database) inside the CDB start in MOUNT state upon Container Database Instance bounce.


Let's take a look at this new feature with following set of examples:

I have one container database, PROD1, running with one pluggable database, PDBORCL inside it.

c:\app\oracle\product\12.1.0\dbhome_3\BIN>sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Thu Feb 1 07:34:47 2018

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> set lines 300
SQL> select name, open_mode, database_role, cdb from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    CDB
--------- -------------------- ---------------- ---
PROD1     READ WRITE           PRIMARY          YES




You can see that current Open-Mode of pluggable database, PDBORCL is in MOUNTED state.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        MOUNTED

Let's bounce the container database instance and check its status.

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

SQL> startup
ORACLE instance started.

Total System Global Area 1006632960 bytes
Fixed Size                  3053104 bytes
Variable Size             905972176 bytes
Database Buffers           92274688 bytes
Redo Buffers                5332992 bytes
Database mounted.
Database opened.

Here, you see that after container database instance bounce, PDBORCL pluggable database started in MOUNTED state by default, in real world, you may have a number of pluggable databases running inside a single container database instance and you would want all or some of them to be started in READ-WRITE mode upon container database instance bounce, otherwise you would have to open all of them after CDB restart.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        MOUNTED
SQL>

Let's now use the new feature introduced in the 12.1.0.2 patchset, and remain a lazy DBA, so that our pluggable database starts back automatically in read-write mode after CDB instance bounce.

First, we will open the pluggable database PDBORCL and save its current state so that it starts back in read-write mode at next startup.

SQL> alter pluggable database pdborcl open;

Pluggable database altered.

SQL> show pdbs

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

Here we save the PDB current state i.e READ WRITE at the moment.

SQL> alter pluggable database pdborcl save state;

Pluggable database altered.

SQL>

Now, bounce the CDB instance.

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

SQL> startup
ORACLE instance started.

Total System Global Area 1006632960 bytes
Fixed Size                  3053104 bytes
Variable Size             905972176 bytes
Database Buffers           92274688 bytes
Redo Buffers                5332992 bytes
Database mounted.
Database opened.
SQL>

As expected, if you check the pluggable database state after container database instance bounce, then you would see that PDBORCL started back in READ WRITE mode automatically.

SQL> select name, open_mode, database_role, cdb from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    CDB
--------- -------------------- ---------------- ---
PROD1     READ WRITE           PRIMARY          YES

SQL> show pdbs

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

Any feedback or comments would highly be appreciated....!!