cat

 

Playing lately with PDBs and CDBs I have encountered interesting problem. While doing some investigations I found out that it is possible to affect the CDB$ROOT parameters from the PDB level what shouldn’t be possible in my humble opinion. Let’s go with me through the testcase below.

At the beginning I am going to show you how my spfile looks like. I am going to show the steps with optimizer_adaptive_features parameter, but it could be any other that can be modified from the pdb.

SYS@CDB1> create pfile='/tmp/initCDB1.ora' from spfile;
File created.

SYS@CDB1> exit 
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management and Unified Auditing options 

[oracle@server ~]$ cat /tmp/initCDB1.ora 
*._ash_size=266338304 
*._gc_element_percent=200 
*.archive_lag_target=3600 
*.audit_file_dest='/u01/app/oracle/admin/CDB1/adump' 
*.audit_trail='DB' 
*.cluster_database=TRUE 
*.compatible='12.1.0.2.0' 
*.control_files='+DATA/CDB1/CONTROLFILE/current.389.895664823','+FRA/CDB1/CONTROLFILE/current.286.895664823' 
*.db_block_checking='MEDIUM' 
*.db_block_checksum='FULL' 
*.db_block_size=8192 
*.db_create_file_dest='+DATA' 
*.db_create_online_log_dest_1='+DATA' 
*.db_create_online_log_dest_2='+FRA' 
*.db_domain='' 
*.db_name='CDB1' 
*.db_recovery_file_dest='+FRA' 
*.db_recovery_file_dest_size=107374182400 
*.diagnostic_dest='/u01/app/oracle' 
*.enable_pluggable_database=true 
*.fast_start_mttr_target=180 
*.global_names=FALSE 
*.inmemory_force='OFF' 
*.log_archive_format='%t_%s_%r.dbf' 
*.nls_language='AMERICAN' 
*.nls_territory='AMERICA' 
*.open_cursors=800 
*.os_authent_prefix='' 
*.pga_aggregate_target=1048576000 *.processes=1000 
*.remote_login_passwordfile='exclusive' 
*.sec_protocol_error_trace_action='LOG' 
*.sga_max_size=6442450944 
*.sga_target=6442450944 
*.sql92_security=TRUE 
*.undo_retention=3600 
*.use_large_pages='ONLY' 

OPTIMIZER_ADAPTIVE_FEATURES parameter hasn’t been set explicitly, CDB is using default setting for optimizer_adaptive_features which is TRUE.

 

As you can see below settings both in CDB and PDB equal to TRUE.

SYS@CDB1> show parameter optimizer_adaptive_features;

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
optimizer_adaptive_features          boolean                          TRUE
SYS@PDB1> show parameter optimizer_adaptive_features;

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
optimizer_adaptive_features          boolean                          TRUE
 

List of my containers below.

SYS@CDB1> select con_id,name from v$containers;

CON_ID	 NAME
-------  ---------
1	     CDB$ROOT
2	     PDB$SEED
3	     PDB1
 

Now let’s check if PDB_SPFILE$ has got any value specified

SYS@CDB1> select * from  PDB_SPFILE$ where name='optimizer_adaptive_features'
	no rows selected
SYS@PDB1> select * from  PDB_SPFILE$ where name='optimizer_adaptive_features'
	no rows selected
 

Now let’s check settings in spfile visible in the v$spparameter view. As you can see no value has been specified.

SYS@CDB1> select inst_id,name,value,isspecified,con_id from gv$spparameter where name='optimizer_adaptive_features'
INST_ID    NAME                         VALUE      ISSPECIFIED   CON_ID                                                                                                                                                                                                                                                                               
2          optimizer_adaptive_features             FALSE         0   
SYS@PDB1> select inst_id,name,value,isspecified,con_id from gv$spparameter where 
INST_ID    NAME                           VALUE         ISSPECIFIED   CON_ID                              
2          optimizer_adaptive_features                  FALSE         0   
 

Checking instance parameters in CDB and PDB using v$system_parameter – they are the same set to default

SYS@CDB1> select inst_id,name,value,con_id from gv$system_parameter where name='optimizer_adaptive_features';
INST_ID NAME                            VALUE   CON_ID
2       optimizer_adaptive_features     TRUE    0
SYS@PDB1> select inst_id,name,value,con_id from gv$system_parameter where name='optimizer_adaptive_features';
INST_ID NAME                            VALUE   CON_ID
2       optimizer_adaptive_features     TRUE    0
 

Checking how optimizer settings look like inside the CDB and PDB – they are both set to default

SYS@CDB1> select inst_id,name,value,con_id from gV$SYS_OPTIMIZER_ENV  where name='optimizer_adaptive_features';
INST_ID NAME                            VALUE   CON_ID
2       optimizer_adaptive_features     true    1
SYS@PDB1> select inst_id,name,value,con_id from gV$SYS_OPTIMIZER_ENV  where name='optimizer_adaptive_features';
INST_ID NAME                            VALUE   CON_ID
2       optimizer_adaptive_features     true    3
 

Now let’s change the parameter inside the PDB as it has got ISPDB_MODIFIABLE = TRUE

SYS@PDB1> alter system set optimizer_adaptive_features=false scope=both;
System altered.
 

Let’s run through the views again to verify current settings after the change both in CDB and PDB

SYS@CDB1> show parameter optimizer_adaptive_features;

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
optimizer_adaptive_features          boolean                          TRUE
SYS@PDB1> show parameter optimizer_adaptive_features;

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
optimizer_adaptive_features          boolean                          FALSE

The above looks like expected. Now let’s check if PDB_SPFILE$ has got any value specified

SYS@CDB1> select * from  PDB_SPFILE$ where name='optimizer_adaptive_features';
DB_UNIQ_NAME    PDB_UID         SID     NAME                            VALUE
CDB1            2831059444      *       optimizer_adaptive_features     FALSE
 

Changed as expected in the PDB’s internal configuration

SYS@PDB1> select * from  PDB_SPFILE$ where name='optimizer_adaptive_features'
	no rows selected

It looks like the spfile parameters for a specific pdb are visible only from the CDB.
I couldn’t find the parameter in the CDB’s spfile, it is being kept in the dictionary of PDB.

 

Now check spfile of the instance

SYS@CDB1>  select inst_id,name,value,isspecified,con_id from gv$spparameter where name='optimizer_adaptive_features'
INST_ID    NAME                         VALUE      ISSPECIFIED   CON_ID                                                                                                                                                                                                                                                                               
2          optimizer_adaptive_features             FALSE         0   
SYS@PDB1> select inst_id,name,value,isspecified,con_id from gv$spparameter where 
INST_ID    NAME                           VALUE         ISSPECIFIED   CON_ID                              
2          optimizer_adaptive_features    FALSE         TRUE         0   

I am not sure why the con_id is still 0 and not 3 in the PDB(bug?), but it shows that default value which was “TRUE” has been changed to “FALSE”

 

Let’s check system parameters. As you can see it looks as expected. Instead of the inherited value from the CDB we can see now the one set for PDB

SYS@CDB1> select inst_id,name,value,con_id from gv$system_parameter where name='optimizer_adaptive_features';
INST_ID NAME                            VALUE   CON_ID
2       optimizer_adaptive_features     TRUE    0
2       optimizer_adaptive_features     FALSE    3
SYS@PDB1> select inst_id,name,value,con_id from gv$system_parameter where name='optimizer_adaptive_features';
INST_ID NAME                            VALUE   CON_ID
2       optimizer_adaptive_features     FALSE    3
 

And the last one check of the system optimizer settings:

SYS@CDB1> select inst_id,name,value,con_id from gV$SYS_OPTIMIZER_ENV  where name='optimizer_adaptive_features';
INST_ID NAME                            VALUE   CON_ID
2       optimizer_adaptive_features     false    1
 

Wow as you can see below a change in PDB had changed optimizer setting in the root container – another Bug?
How come? I have changed it in PDB only, why is it showing the new value as current optimizer setting in CDB??

 

For PDB it has been changed as expected:

SYS@PDB1> select inst_id,name,value,con_id from gV$SYS_OPTIMIZER_ENV  where name='optimizer_adaptive_features';
INST_ID NAME                            VALUE   CON_ID
2       optimizer_adaptive_features     false    3
 

Another check for CDB$ROOT, let’s login with new session to check if PDB parameter change had influenced the CDB’s session environment

$ sqlplus / as sysdba
SYS@CDB1> select sys_context('USERENV','SID') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
722

SYS@CDB1> select sid,inst_id,name,value,con_id from gv$ses_optimizer_env where name='optimizer_adaptive_features' and sid=722;

       SID    INST_ID NAME					VALUE                         CON_ID
---------- ---------- ----------------------------------------  ------------------------- ----------
       722          2 optimizer_adaptive_features		false                              1


SYS@CDB1> show parameter optimizer_adaptive_features

NAME                                 TYPE				VALUE
------------------------------------ --------------------------------	------------------------------
optimizer_adaptive_features          boolean				TRUE

 

Wow, as you can see it still shows TRUE for the parameter with show parameter but not when quering the v$ses_optimizer_env

 

Let’s try to run a query using this session and then check optimizer settings used for it

SYS@CDB1> select /*+ my param magic test */ username from dba_users where username='DBSNMP';

USERNAME
--------------------------------------------------------------------------------
DBSNMP

SYS@CDB1> select name,value from v$sql_optimizer_env where name='optimizer_adaptive_features' and sql_id in (select sql_id from v$sql where sql_text like '%my param magic test%');

NAME                                     VALUE
---------------------------------------- -------------------------
optimizer_adaptive_features              false
 

Well, Am I missing something here guys? SR has been created – I need clarification for it.
Looks like a nice CDB param hack as of now :)

Please give me your feedback.

 

EXTRAS:

One interesting behaviour I have discovered is that you can dump the PDB settings with “create pfile from spfile” command as follows

SYS@PDB1> create pfile='/tmp/initCDB1.ora' from spfile;
File created.

SYS@PDB1> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Unified Auditing options
[oracle@server.dba24.pl ~]$ cat /tmp/initCDB1.ora
*.optimizer_adaptive_features=FALSE
 

UPDATE 2016-07.12
Franck Pachot found another similiar issue. Please find Franck’s blog note here:
http://blog.dbi-services.com/phantom-session-environment-in-multitenant/

About the author

 
maciej tokar
Maciej Tokar

An Oracle technology geek and crazy long distance runner, DBA24 Owner
Senior Oracle DBA / Consultant / [OCP10g, OCP12c, OCE RAC 10g] / [experience: 9y+]
Currently working for Bluegarden (Oslo Norway) by Miratech Group
Past: Mastercard / Trevica by Britenet, Citi International PLC, PZU

 
View Maciej Tokar's profile on LinkedIn         logoDB4
LinkedIn Auto Publish Powered By : XYZScripts.com