Automatic PDB Start in 12c

Hi

I would like to tell about a new feature that came with 12.1.0.2.0. This feature help us  about PDB state after CDB restart. As you now before this release when you restart CDB,  PDB in the CDB is still mount state unless a trigger that  is start pluggable database. So when you restart CDB you start also pluggable database manually or you create a trigger and  when you restart CDB your PDB’s also start. Now we have a feature that came with 12.1.0.2.0.Let’s do an example;

We have 3 pluggable database.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
UPPDB                          READ WRITE
UPPDB2                         READ WRITE
UPPDB3                         READ WRITE

We have no startup triggers. Normally when you restart CDB. Your pluggable databases are mount state.

SQL> startup
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size                  2923872 bytes
Variable Size             452985504 bytes
Database Buffers          771751936 bytes
Redo Buffers               13852672 bytes
Database mounted.
Database opened.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
UPPDB                          MOUNTED
UPPDB2                         MOUNTED
UPPDB3                         MOUNTED

All of them will start with manual intervention.

SQL> alter pluggable database all open;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
UPPDB                          READ WRITE
UPPDB2                         READ WRITE
UPPDB3                         READ WRITE

As I mentioned in the previous my post we should have a trigger to start pluggable databases automaticly before 12.1.0.2.0 version.But now we have the parameters that called save state and discard state. Actually we will use save state parameter.This provide that pluggable database will be state that before CDB restart.So if pluggable database is open and after you restart CDB, pluggable database restart also. Let’s do an example. Our pluggable databases are as following.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
UPPDB                          READ WRITE
UPPDB2                         READ WRITE
UPPDB3                         READ WRITE

We set save state parameter for UPPDB3.

SQL> alter pluggable database UPPDB3 save state;
Pluggable database altered.

Now we restart CDB.

SQL> startup
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size                  2923872 bytes
Variable Size             452985504 bytes
Database Buffers          771751936 bytes
Redo Buffers               13852672 bytes
Database mounted.
Database opened.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
UPPDB                          MOUNTED
UPPDB2                         MOUNTED
UPPDB3                         READ WRITE

As you see UPPDB3 started as well. Other PDB’s are still mount mode. Because we did not set save state parameter for these PDB’s.We can use save state clause for pdbs that open read write mode and read only mode.If we set save state parameter for database that in mount state this is meaningless. Already that will be mount mode when you restart  CDB.

We set save state for read only pluggable database.We open UPPDB2 pluggable database as read only.

SQL> alter pluggable database UPPDB2 open read only;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
UPPDB                          MOUNTED
UPPDB2                         READ ONLY
UPPDB3                         READ WRITE

And we set save state parameter for UPPDB2 pluggable database.

SQL> alter pluggable database UPPDB2 save state;
Pluggable database altered.

We again restart container database.

SQL> startup
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size                  2923872 bytes
Variable Size             452985504 bytes
Database Buffers          771751936 bytes
Redo Buffers               13852672 bytes
Database mounted.
Database opened.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
UPPDB                          MOUNTED
UPPDB2                         READ ONLY
UPPDB3                         READ WRITE

we can see PDB that saved state with dba_pdb_saved_states tables.

SQL> select * from dba_pdb_saved_states;
CON_ID      CON_NAME    INSTANCE_NAME     CON_UID           GUID                          STATE           RES
—–     ———–   ————-    ———-   ——————————–   ————–   —
5        UPPDB3          upcdb         298154765    001EA54E4B075894E0531C30640A53A6   OPEN             NO
4        UPPDB2          upcdb         1147292449   001EA54E4B065894E0531C30640A53A6   OPEN READ ONLY   NO

we have the another option is discard state.We need set discard state for PDB that set save state parameter before.In our example we have set save state for UPPDB3.Now we set discard state for UPPDB3.

SQL> alter pluggable database UPPDB3 discard state;
Pluggable database altered.

Now we check dba_pdb_saved_states table.

SQL> select * from dba_pdb_saved_states;
CON_ID      CON_NAME    INSTANCE_NAME     CON_UID           GUID                          STATE           RES
—–     ———–   ————-    ———-   ——————————–   ————–   —
4        UPPDB2          upcdb         1147292449   001EA54E4B065894E0531C30640A53A6   OPEN READ ONLY   NO

Now if we restart CDB  we except that UPDPB3 don’t start.

SQL> startup
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size                  2923872 bytes
Variable Size             452985504 bytes
Database Buffers          771751936 bytes
Redo Buffers               13852672 bytes
Database mounted.
Database opened.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
UPPDB                          MOUNTED
UPPDB2                         READ ONLY
UPPDB3                         MOUNTED

NOTE:When we use discard state for mount state while CDB is open.It will not start after CDB restart. So When we use discard state for a PDB It is to be always mount after CDB is restarted.If you want to set save state parameter for all PDB. You can do this with one command as following.

SQL> alter pluggable database all save state;
Pluggable database altered.

SQL> select * from dba_pdb_saved_states;
CON_ID    CON_NAME     INSTANCE_NAME    CON_UID         GUID                             STATE    RES
——- ———– —————— ———-  ——————————–   ———– —
3        UPPDB         upcdb          2195545011   001ACD22788C4690E0531C30640A1891     OPEN      NO
5        UPPDB3        upcdb          298154765    001EA54E4B075894E0531C30640A53A6     OPEN      NO
4        UPPDB2        upcdb          1147292449   001EA54E4B065894E0531C30640A53A6     OPEN      NO

Now if we restart container database all pdbs are open also.