SPFILE for a CDB vs. a PDB

Recently, I have had conversations with users about Oracle Database 12c.  One question that often comes up is, how does the database manage the parameters between a container database (CDB) and a pluggable database (PDB)?  In order to answer this question, I had to work with my pluggable database setup in a test environment.  Before changing anything within my database setup I first made a backup of the current parameters using a tired and true method.

SQL> create pfile=’init12ctest.ora’ from spfile;

Before diving into change and storage of parameters, it is important that you understand the concept of inheritance.  Once you under stand inheritance within the Oracle Database 12c, you will grasp how parameters can be changed and stored for PDBs.

Inheritance

Inheritance in dealing with Oracle Database 12c means that the value of a particular parameter in the root (CDB) is inherited by one or more PDBs.  There are parameters that can be changed at the PDB level and override what is being inherited from the CDB.  To identify which parameters can be modified for a PDB, the ISPDB_MODIFIABLE column in the V$SYSTEM_PARAMETER view must be TRUE.  If the ISPDB_MODIFIABLE is TRUE then the parameter inherits the value from the CDB until it is changed with an ALTER SYSTEM SET command at the PDB.

Setting Parameters

Now, let’s work on changing parameters for a PDB.  In order to do this, you needed to navigate into a PDB using the ALTER SESSION SET CONTAINER command. 

SQL> ALTER SESSION SET CONTAINER=bcpdb1;

Once inside of the PDB, you needed to find what parameters are modifiable.  These parameters can be found using the following SQL statement.  Remember, the ISPDB_MODIFIABLE has to be TRUE.

SQL> SELECT NAME FROM V$SYSTEM_PARAMETER
     WHERE ISPDB_MODIFIABLE = ‘TRUE’
     ORDER BY NAME;

With this list of parameters, pick one at random to change.  Lets change the statistics_level from typical to all for bcpdb1.

SQL> ALTER SYSTEM SET STATISTICS_LEVEL = ALL SCOPE = BOTH;

By using BOTH, you are setting the parameter in memory and in the spfile.  This way on a reboot of the database, the PDB will retain the setting.

Now that the parameter has been changed, can you see this change in the parameters for the CDB?  Not at the CDB level, but you can at the PDB level from the V$PARAMETER.  If I want to see the parameters that are set system wide (CDB & PDB), you needed to use the view V$SYSTEM_PARAMETER.  The following SQL will display the statistics_level parameter for both CDB and PDB (make note of the con_id column).

SQL> SELECT NAME, VALUE, DISPLAY_VALUE, CON_ID FROM V$SYSTEM_PARAMETER
     WHERE NAME = ‘statistics_level’
     ORDER BY NAME;

At this point, you have two different settings for statistics_level depending on which container you are in.  Now, you’ll want to make a backup of the spfile and verify that your settings for the PDB is in fact stored in the spfile.

SQL> CREATE PFILE=’init12ctest1.ora’ FROM SPFILE;

Let’s take a look at the pfile you just created.

bc12c.__data_transfer_cache_size=0
bc12c.__db_cache_size=1442840576
bc12c.__java_pool_size=16777216
bc12c.__large_pool_size=33554432
bc12c.__oracle_base=’/oracle/app’#ORACLE_BASE set from environment
bc12c.__pga_aggregate_target=1073741824
bc12c.__sga_target=2147483648
bc12c.__shared_io_pool_size=117440512
bc12c.__shared_pool_size=503316480
bc12c.__streams_pool_size=16777216
bc12c._common_user_prefix=’CU’##
*.audit_file_dest=’/oracle/app/admin/bc12c/adump’
*.audit_trail=’db’
*.compatible=’12.1.0.0.0′
*.control_files=’/oracle/app/oradata/BC12C/controlfile/o1_mf_91pqwlwo_.ctl’,’/oracle/app/fast_recovery_area/BC12C/controlfile/o1_mf_91pqwm7g_.ctl’
*.db_block_size=8192
*.db_create_file_dest=’/oracle/app/oradata’
*.db_domain=’acme.com’
*.db_name=’bc12c’
*.db_recovery_file_dest=’/oracle/app/fast_recovery_area’
*.db_recovery_file_dest_size=16106127360
*.diagnostic_dest=’/oracle/app’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=bc12cXDB)’
*.enable_pluggable_database=true
*.local_listener=’LISTENER_BC12C’
*.log_archive_format=’%t_%s_%r.dbf’
*.open_cursors=300
*.pga_aggregate_target=1073741824
*.processes=300
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_max_size=2147483648
*.sga_target=2147483648
*.undo_tablespace=’UNDOTBS1′

Notice that the parameter statistics_level is not included in the output for the pfile.  Why is this?  If you go back to the documentation on Oracle Database 12c (here), you will find a note stating:

Note: A text initialization parameter file (PFILE) cannot contain PDB-specific parameter values.

Where are the parameters stored

Since the parameters for the PDB are not stored in the spfile, where are they stored then?  In order to find this, you need to take the V$SYSTEM_PARAMETER view apart using GV$FIXED_VIEW_DEFINITION.

SQL> SELECT VIEW_DEFINITION FROM GV$FIXED_VIEW_DEFINITION
         WHERE VIEW_NAME = ‘V$SYSTEM_PARAMETER’;

The V$SYSTEM_PARAMETER view points you to the GV$SYSTEM_PARAMETER view.  Let’s grab the definition for this view.

SQL> SELECT VIEW_DEFINITION FROM GV$FIXED_VIEW_DEFINITION
         WHERE VIEW_NAME = ‘GV$SYSTEM_PARAMETER’;

The definition for GV$SYSTEM_PARAMTER shows you that the information is coming from the X$KSPPI and X$KSPPSV tables in the data dictionary.  The SQL that defines the GV$SYSTEM_PARAMETER view is listed below.

select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf, 
decode(bitand(ksppiflg/256,1),1,’TRUE’,’FALSE’), 
decode(bitand(ksppiflg/65536,3),1,’IMMEDIATE’,2,’DEFERRED’, 3,’IMMEDIATE’,’FALSE’), 
decode(bitand(ksppiflg/524288,1),1,’TRUE’,’FALSE’), 
decode(bitand(ksppiflg,4),4,’FALSE’,
decode(bitand(ksppiflg/65536,3), 0, ‘FALSE’, ‘TRUE’)),
decode(bitand(ksppstvf,7),1,’MODIFIED’,’FALSE’), 
decode(bitand(ksppstvf,2),2,’TRUE’,’FALSE’), 
decode(bitand(ksppilrmflg/64, 1), 1, ‘TRUE’, ‘FALSE’), 
decode(bitand(ksppilrmflg/268435456, 1), 1, ‘TRUE’, ‘FALSE’), 
ksppdesc, ksppstcmnt, ksppihash, y.con_id
from x$ksppi x, x$ksppsv y 
where (x.indx = y.indx)
and  bitand(ksppiflg,268435456) = 0
and  ((translate(ksppinm,’_’,’#’) not like ‘##%’)
and  ((translate(ksppinm,’_’,’#’) not like ‘#%’)     
or   (ksppstdf = ‘FALSE’)
or   (bitand(ksppstvf,5) > 0)))

Now that you know the X tables to use, let’s take a look and see if you can locate the statistics_level parameter for bcpdb1.

SQL> SELECT * FROM X$KSPPSV
         WHERE INDX IN (SELEC
T INDX FROM X$KSPPI WHERE CON_ID= 3 AND KSPPINM = ‘statistics_level’);

Notice that the CON_ID is 3.  If you query CDB_PDB, you will notice that bcpdb1 has  CON_ID of 3. At this point, you have located where the value of statistics_level for the PDB is stored.  If you go back to the documentation, you will find references that tell you if SCOPE=SPFILE or SCOPE=BOTH were used when setting the parameter; the parameter will be transferred and stored in the XML file when the PDB is unplugged from the CDB.  Let’s test this out.

Unplug a PDB

To test if the parameter (statistics_level) is stored in the XML file or data dictionary. What happens to the parameter when you unplug the PDB.  According to documentation when unplugged the value of statistics_level should be stored in the associated XML file for plugging into a CDB.  Let’s unplug BCPDB1.

SQL> ALTER PLUGGABLE DATABASE BCPDB1 CLOSE;

SQL> ALTER PLUGGABLE DATABASE BCPDB1 UNPLUG TO ‘/TMP/BCPDB1.XML’;

SQL> DROP PLUGGABLE DATABASE BCPDB1 KEEP DATAFILES;

With the pluggable database BCPDB1 unplugged from the CDB, lets see if the parameter values for statistics_level are still in the data dictionary.

SQL> SELECT * FROM X$KSPPSV
         WHERE INDX IN (SELECT INDX FROM X$KSPPI WHERE CON_ID= 3 AND KSPPINM = ‘statistics_level’);

Apparently, the parameter values for statistics_level are gone.  Let’s check the XML file that was created in the /tmp directory to see if the parameter is there.

 <parameters>
     <parameter>processes=300</parameter>
     <parameter>sga_max_size=2147483648</parameter>
     <parameter>sga_target=2147483648</parameter>
     <parameter>db_block_size=8192</parameter>
     <parameter>compatible=12.1.0.0.0</parameter>
     <parameter>open_cursors=300</parameter>
     <parameter>pga_aggregate_target=1073741824</parameter>
     <parameter>enable_pluggable_database=TRUE</parameter>
     <parameter>_common_user_prefix=CU</parameter>
   </parameters>

The values that are in the XML file appear to be just general settings.  The statistics_level parameter didn’t make it into the XML file either. Hmmm…  Let’s plug in the PDB and see if the value comes back to the data dictionary.

Plug-in the PDB

In order to plug-in the PDB make sure you still have the data files and the XML file needed.  What you are hoping for is that the statistic_level parameter comes back for the PDB and is set to a value of ALL. 

SQL> CREATE PLUGGABLE DATABASE BCPDB1 USING ‘/TMP/BCPDB1.XML’ NOCOPY;

SQL> ALTER PLUGGABLE DATABASE BCPDB1 OPEN;

With the PDB (BCPDB1) open, let’s check and see if the statistic_level parameter is in the data dictionary.

SQL> SELECT * FROM X$KSPPSV
         WHERE INDX IN (SELECT INDX FROM X$KSPPI WHERE CON_ID= 3 AND KSPPINM = ‘statistics_level’); 

Sure enough, the statistics_level parameter came back.  This means that the parameters for the PDB are stored in the PDB data dictionary. 🙂

Conclusion

When working with Oracle Database 12c, you have to understand how the initialization parameters are set for the CDB and each of the PDBs associated. The traditional ways of looking at an SPFILE will only be useful for a CDB database.  This is because the CDB is the root level that controls many of the items which are shared amongst the CDB and PDBs.  In order to fully understand how parameters are set for a PDB, you need to remember that PDBs inherit parameter values from a CDB; unless they are overridden from the PDB level. 

Enjoy!

twitter: @dbasolved

blog: http://dbasolved.com

Please follow and like: