LATEST TOPICS

Oracle 12c (12.1.0.2): Now we can preserve the PDB state between instance startup

As we know, Oracle has introduced the multitanent architecture with Oracle version 12c, where we can have multiple pluggable databases (PDB) within a single container database (CDB). However, when we start up a container database (CDB), all of the associated pluggable databases (PDB) by default comes up in MOUNT state (except the SEED database, which comes up in READ-ONLY mode). In Oracle version 12.1.0.1, there was no mechanism to define the start up mode for individual pluggable databases and we had to write startup trigger (an example is shown below) to allow pluggable database (s) to start up in READ-WRITE/READ-ONLY mode.

---//
---// startup trigger to open pluggable database //---
---//
create or replace
trigger ST_PDBS after startup
on database
begin
execute immediate 'alter pluggable database MYPDB_01 open';
end;
/

With Oracle version 12.1.0.2; this is now history. Oracle 12.1.0.2 allows us to save the state of a pluggable database which in turn lets Oracle start up the respective pluggable databases in that saved state upon subsequent CDB startup.

Oracle has introduced a new clause pdb_save_or_discard_state for the ALTER PLUGGABLE DATABASE statement, which lets us save/discard the open state of a pluggable database within a CDB. Upon CDB startup, Oracle will bring up the pluggable databases in the saved state. If a save state is not defined for a pluggable database, Oracle will by default bring it in MOUNT state. The complete syntax associated with this new clause is as follows

---//
---// syntax for pdb_save_or_discard_state clause //---
---//
ALTER PLUGGABLE DATABASE 
(pdb_name,[pdb_name], ...) | ALL | ALL EXCEPT (pdb_name [,pdb_name] ...)
[INSTANCES = ('instance_name' [,'instance_name'] … ) | ALL | ALL EXCEPT ('instance_name' [,'instance_name'] … )]]
SAVE | DISCARD 
STATE;

where

ALL Will save/discard the state for all of the pluggable databases
ALL EXCEPT Will save/discard the state for all of the pluggable databases except the ones listed in the EXCEPT clause
INSTANCES Enables us to specify whether to save/discard the state for a specific container instance(s) or for all instances (applicable for RAC)
SAVE Will save the current open state of pluggable database (s) for subsequent container startup
DISCARD Will discard a already saved state of pluggable database (s). This will result in restoring the default behaviour (MOUNT state) for the specified pluggable database (s)

Let us go through few demonstrations to understand, how this SAVE/DISCARD state works for pluggable databases.

I have following list of pluggable databases with my container database.

---//
---// current state of all pluggable databases //---
---//
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYPDB_01                       READ WRITE NO
         4 MYPDB_02                       MOUNTED
         5 MYPDB_03                       MOUNTED
         6 MYPDB_04                       MOUNTED
         7 MYPDB_05                       MOUNTED

As of now only MYPDB_01 is in READ-WRITE state and other PDBs are in MOUNT state. Lets save the state of all the pluggable databases and see how it works in the subsequent container startup.

---//
---// saving current open state for all pluggable databases //---
---//
SQL> ALTER PLUGGABLE DATABASE ALL SAVE STATE;

Pluggable database altered.

---//
---// restarting container to verify the impact of saved state //---
---//
SQL> startup force
ORACLE instance started.

Total System Global Area 1459617792 bytes
Fixed Size                  2924496 bytes
Variable Size             452984880 bytes
Database Buffers          989855744 bytes
Redo Buffers               13852672 bytes
Database mounted.
Database opened.

---//
---// pluggable database state after container startup //---
---//
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYPDB_01                       READ WRITE NO
         4 MYPDB_02                       MOUNTED
         5 MYPDB_03                       MOUNTED
         6 MYPDB_04                       MOUNTED
         7 MYPDB_05                       MOUNTED

MYPDB_01 came up in READ-WRITE mode. However, the other pluggable databases remained in the MOUNT state even though we had saved the state for all the pluggable databases. The reason is, the save state works only based on the current open state of a pluggable database. Remember there is no option in the pdb_save_or_discard_state clause to define the open state of a pluggable database. This is why since MYPDB_01 was in READ-WRITE mode, Oracle was able to save its state and it came up in the same state following the container startup. Where as the other pluggable databases were in the default MOUNT mode when we saved the pluggable database (s) state and hence there was no point of saving their state by Oracle, which is why they started in the default MOUNT state following the container start up.

Whenever, we save the state of pluggable database (s), Oracle maintains an entry for the respective pluggable database in the dictionary. We can query the DBA_PDB_SAVED_STATES view to check the pluggable databases for which the open state is saved.

---//
---// list of pluggable databases with saved state //---
---//
SQL> select CON_NAME,INSTANCE_NAME,STATE from DBA_PDB_SAVED_STATES;

CON_NAME        INSTANCE_NAME   STATE
--------------- --------------- --------------
MYPDB_01        orlcdb01        OPEN

As we can observe, only the state of pluggable database MYPDB_01 was saved where as the state for other pluggable databases were ignored as those were in the default (MOUNT) open state. This implies that to be able to save the state of a pluggable database (for auto open in subsequent container startup) we must first open the pluggable database in the desired state and then save that state.

Lets open up other pluggable databases (in different modes) and save their state to examine the behaviour.

---//
---// opening othe pluggable databases in different state //---
---//
SQL> alter pluggable database MYPDB_02 open read only restricted;

Pluggable database altered.

SQL> alter pluggable database MYPDB_03 open restricted;

Pluggable database altered.

SQL> alter pluggable database MYPDB_04 open read only;

Pluggable database altered.

SQL> alter pluggable database MYPDB_05 open;

Pluggable database altered.

---//
---// current state of all pluggable databases //---
---//
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYPDB_01                       READ WRITE NO
         4 MYPDB_02                       READ ONLY  YES
         5 MYPDB_03                       READ WRITE YES
         6 MYPDB_04                       READ ONLY  NO
         7 MYPDB_05                       READ WRITE NO

As we can see, I have opened the rest of the pluggable databases in different open state (read only/read write/read only + restricted/read write + restricted). Lets say, I want all of these pluggable databases except MYPDB_03 to come up in this state in the subsequent container startup. We can do so using the pdb_save_or_discard_state clause as shown below

---//
---// saving current state of pluggable databases except for MYPDB_03 //---
---//		 
SQL> ALTER PLUGGABLE DATABASE ALL EXCEPT MYPDB_03 SAVE STATE;

Pluggable database altered.

We can further confirm, if the state was saved or not by querying DBA_PDB_SAVED_STATES view as shown below.

---//
---// validating if the current state of pluggable database are saved //---
---//
SQL> select CON_NAME,INSTANCE_NAME,STATE,RESTRICTED from DBA_PDB_SAVED_STATES;

CON_NAME        INSTANCE_NAME   STATE          RESTRICTED
--------------- --------------- -------------- ---------------
MYPDB_01        orlcdb01        OPEN           NO
MYPDB_02        orlcdb01        OPEN READ ONLY YES
MYPDB_04        orlcdb01        OPEN READ ONLY NO
MYPDB_05        orlcdb01        OPEN           NO

As we can see the current state of the pluggable databases except for MYPDB_03 are saved in the dictionary. Lets restart the container instance to confirm if the pluggable will come in the saved state.

---//
---// restarting container to verify the impact of saved state //---
---//
SQL> startup force
ORACLE instance started.

Total System Global Area 1459617792 bytes
Fixed Size                  2924496 bytes
Variable Size             469762096 bytes
Database Buffers          973078528 bytes
Redo Buffers               13852672 bytes
Database mounted.
Database opened.

---//
---// pluggable database state after startup //---
---//
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYPDB_01                       READ WRITE NO
         4 MYPDB_02                       READ ONLY  YES
         5 MYPDB_03                       MOUNTED
         6 MYPDB_04                       READ ONLY  NO
         7 MYPDB_05                       READ WRITE NO
SQL>

As expected all the pluggable databases came up in the desired state. MYPDB_03 came up in the default MOUNT state as we did not save the state for this pluggable database.

These saved pluggable database state will remain available in the dictionary enabling the pluggable databases to come up in the saved state until and unless we discard the saved state. We can discard a saved pluggable database state using the pdb_save_or_discard_state clause as shown below

---//
---// discarding saved state for pluggable database MYPDB_05 //---
---//
SQL> ALTER PLUGGABLE DATABASE MYPDB_05 DISCARD STATE;

Pluggable database altered.

---//
---// discarding saved state for all PDBs except MYPDB_01 and MYPDB_02 //---
---//
SQL> ALTER PLUGGABLE DATABASE ALL EXCEPT MYPDB_01,MYPDB_02 DISCARD STATE;

Pluggable database altered.

The moment we discard a saved pluggable database state, the respective entry would be wiped out from the dictionary as found below. This will restore the default open state behaviour (MOUNT) for the respective pluggable database.

---//
---// discarded pluggable database state is removed from dictionary //---
---//
SQL> select CON_NAME,INSTANCE_NAME,STATE,RESTRICTED from DBA_PDB_SAVED_STATES;

CON_NAME        INSTANCE_NAME   STATE          RESTRICTED
--------------- --------------- -------------- ---------------
MYPDB_01        orlcdb01        OPEN           NO
MYPDB_02        orlcdb01        OPEN READ ONLY YES

Now we have discarded the saved state for all pluggable databases except for MYPDB_01 and MYPDB_02. Upon container instance startup, Oracle will now open MYPDB_01 and MYPDB_02 in the saved state, where as all the other PDBs will come up in the default MOUNT state. Lets restart the container instance to validate this fact.

---//
---// restarting container to validate discard behaviour //---
---//
SQL> startup force
ORACLE instance started.

Total System Global Area 1459617792 bytes
Fixed Size                  2924496 bytes
Variable Size             469762096 bytes
Database Buffers          973078528 bytes
Redo Buffers               13852672 bytes
Database mounted.
Database opened.

---//
---// pluggable database state after startup //---
---//
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYPDB_01                       READ WRITE NO
         4 MYPDB_02                       READ ONLY  YES
         5 MYPDB_03                       MOUNTED
         6 MYPDB_04                       MOUNTED
         7 MYPDB_05                       MOUNTED

As expected only MYPDB_01 and MYPDB_02 came up in the saved state, where as other pluggable databases came in the default MOUNT state.

The PDB SAVE/DISCARD state feature is a little but note worthy enhancement in Oracle 12.1.0.2 release. It will help DBAs to avoid the task of maintaining startup triggers especially for environments with higher number of pluggable databases with a need of having different open state for different pluggable databases.

One Response
  1. Foued
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed