An initiative of Mobiliar IT

Where is my init parameter? Typical question in Multitenant environments

 

If you’re a regular reader of our blog, you know that we are more and more working with Oracle Multitenant. In fact more than 50% of our Oracle infrastructure already runs on it and the rest will follow later this year. The more you work with this technology, the more questions you get that a DBA only asks if he works in a Multitenant environment

“I set a parameter on pdb level and if I check the spfile, the entry isn’t available anymore. Why?”

“If I plug a pdb in a new container, how does Oracle know which parameter must be set for this pdb?”

So I did some research in the documentation and asked some Oracle insiders in order to find out how the parameter management is working.

 

Parameter in the spfile / init.ora of the cdb

  • All parameters that can be modified only at cdb level are always present in the spfile.ora / init.ora
  • All parameters that can be modified at pdb level display only the cdb value in the spfile.ora / init.ora.
  • The parameters on pdb level are managed in the data dictionary of the cdb not in the parameter file

Example

I have a pluggable database test01 that inherits all parameters from the cdb.
The parameter db_files is set to 2000 in the cdb.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 TEST01                         READ WRITE YES

SQL> show con_id
CON_ID
---------
1

SQL> show parameter db_files
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
db_files                             integer     2000

Now we modify the parameter on pdb level to the value 500. Although I write “scope=spfile” at the end, the value isn’t available on the spfile. You only find the value 2000.

SQL> alter session set container=test01;
Session altered.
SQL> alter system set db_files=500 scope=spfile;
System altered.
SQL> exit
strings spfileto30ms01.ora | grep db_files
*.db_files=2000

I can understand that on first sight this might look very strange. For years you could be sure that all parameters that were set were available in the spfile – except you set it with “scope=memory”

In the Multitenant environment you have to query the database to get to the truth. If you take a closer look into the architecture, it’s clear that the settings must be stored somewhere in the data dictionary. If you unplug / plug a pdb to a new container, the information over the maximum of 500 db files for that pdb must be stored somewhere in that pdb because the information in the spfile belongs only to one cdb.

So how does it work?

After setting the parameter for a pdb, this information is stored in the cdb view: “v$system_parameter”. If we query this view, we can see that the value is different for the container and for the pdb

SQL> select name,value,con_id from v$system_parameter where Name = 'db_files';

NAME               VALUE                                    CON_ID
-----------        --------------------------               -----------
db_files           2000                                     0
db_files           500                                      5

 

This view queries an internal and undocumented table called pdb_spfile$. The values in this table are checked and set every time before opening a pdb.

During the unplug operation the parameters are stored in the sysaux tablespace of the pdb and not in the xml file! This is not documented and may change in the future.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Basic HTML is allowed. Your email address will not be published.

Subscribe to this comment feed via RSS

%d bloggers like this: