Monday, December 30, 2013

Oracle Database 12c: Managing Parameters in Container Database

Oracle Database 12c: Managing Parameters in Container database

Last week I attended the 'Oracle Database 12c New features course' at Oracle University and found that Oracle database 12c new features are really good. Especially the new concept of Container database and Pluggable Databases, restore of tables from RMAN backup, and many such facinating features.

Here I write my first simple article about managing parameters in the contrainer database.

In a container architecture, the parameters for Pluggable Database (PDB) will inherit from the root database. That means if statistics_level=all in the root that will cascade to the PDB databases.

You can over ride this by using Alter system set, if that parameter is pdb modifiable, there is a new column in v$system_parameter for the same.

The inheritance property for some parameters must be true.

For other parameters, you can change the inheritance property by running the ALTER SYSTEM SET statement to set the parameter when the current container is the PDB.

If ISPDB_MODIFIABLE is TRUE for an initialization parameter in the V$SYSTEM_PARAMETER view, then the inheritance property can be false for the parameter.


SQL> select NAME,ISSES_MODIFIABLE,ISSYS_MODIFIABLE,ISINSTANCE_MODIFIABLE from V$SYSTEM_PARAMETER where name='open_cursors';

NAME ISSES ISSYS_MOD ISINS ISPDB
----------------- ----- --------- ----- -------
open_cursors FALSE IMMEDIATE TRUE TRUE

SQL>The above example of open_cursors for ISPDB_MODIFIABLE is true means that this parameter can be inherited for PDB’s from root database

Further the parameters can be changed all together for all databases or for specific container (PDB) by using again a CONTAINER Clause in ALTER SYSTEM

The following changes the open_cursor parameter to 200 in root and all the PDB's

ALTER SYSTEM SET OPEN_CURSORS = 200 CONTAINER = ALL;


If you logged in root, The following changes the open_cursor parameter to 200 in root and all the PDB's as the instance inheritance parameter is true, if you change in root it will applicable to all.

ALTER SYSTEM SET OPEN_CURSORS = 200 CONTAINER = CURRENT;


If you logged in PDB, The following changes the open_cursor parameter to 200 in PDB only.

ALTER SYSTEM SET OPEN_CURSORS = 200 CONTAINER = CURRENT;

Important Note: PDB parameters cannot be changed if you are using PFILE, You must use SPFILE