Preface
It’s been a long time since the last blog post, so today is the day to start sharing knowledge again. And the topic is quite an interresting one I think. It came up during the creation of my slides for DOAG Conference 2022 in Nuremberg. There I will be talking about my experiences during several Multitenant migration projects. A question that always occur is how the Oracle Database handles parameter changes.
The thing is, that in contrast to the old-fashioned architecture we can now set parameters at the CDB (e.g. CDB$ROOT container) level as well as at the PDB level. My initial assumption was, that paramter changes done at the CDB level will also affect all PDBs as long as they don’t have their own settings. But during the projects, it turned out that this is not rule of thumb.
So I read through the documentation starting with the Database Concepts going to the Administrators Guide which in turn led me to the Multitenant Administrators Guide and the topics of Modifying a CDB with ALTER SYSTEM and Modifying a PDB with ALTER SYSTEM. But for me that didn’t paint a clearer picture. That’s why I started investigating things further.
I will be using Oracle 19.16 for my testing. I started with the V$PARAMETER view which has a column called “ISPDB_MODIFIABLE”. This column may have values of either “FALSE” or “TRUE”.
SQL> select ispdb_modifiable, count(*) cnt# from v$parameter group by ispdb_modifiable;
ISPDB CNT#
----- ----------
TRUE 200
FALSE 265
Out of these 200 parameters, that can be modified from within a PDB I choose roughly 60 that made sense to change. My ideas was to create a table containing these selected parameter names, store the initial values of these parameters for the CDB as well as for one PDB, then change the values at CDB level and store the values again for the CDB and the PDB. That would then make up for a comparison.
Initial setup
SQL> create table params_in_question (name varchar2(100));
Table created.
##### Do the inserts #####
SQL> select * from params_in_question order by 1;
NAME
--------------------------------------------------------------------------------
approx_for_aggregation
approx_for_count_distinct
approx_for_percentile
aq_tm_processes
bitmap_merge_area_size
blank_trimming
cpu_count
cpu_min_count
create_bitmap_area_size
cursor_sharing
db_domain
db_file_multiblock_read_count
db_securefile
deferred_segment_creation
global_names
job_queue_processes
max_dump_file_size
open_cursors
open_links
optimizer_adaptive_plans
optimizer_adaptive_reporting_only
optimizer_adaptive_statistics
optimizer_capture_sql_plan_baselines
optimizer_dynamic_sampling
optimizer_features_enable
optimizer_ignore_hints
optimizer_ignore_parallel_hints
optimizer_index_caching
optimizer_index_cost_adj
optimizer_inmemory_aware
optimizer_mode
optimizer_real_time_statistics
optimizer_secure_view_merging
optimizer_use_invisible_indexes
optimizer_use_pending_statistics
optimizer_use_sql_plan_baselines
parallel_degree_limit
parallel_degree_policy
parallel_force_local
parallel_max_servers
parallel_min_degree
parallel_min_time_threshold
parallel_servers_target
plscope_settings
query_rewrite_enabled
query_rewrite_integrity
recyclebin
resource_limit
result_cache_max_result
result_cache_max_size
result_cache_mode
result_cache_remote_expiration
resumable_timeout
session_cached_cursors
sga_min_size
sga_target
shared_pool_size
skip_unusable_indexes
smtp_out_server
sort_area_retained_size
sort_area_size
star_transformation_enabled
undo_retention
63 rows selected.
Now I am going to save the initial values for the CDB and PDB.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MMIPDB1 READ WRITE NO
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> create table cdb_start_params as select name, value from v$parameter where name in (select name from params_in_question);
Table created.
SQL> alter session set container=mmipdb1;
Session altered.
SQL> create table cdb_start_params as select name, value from v$parameter where name in (select name from params_in_question);
Table created.Change the values
After setting that up, I altered all these parameters using the typical ALTER SYSTEM statement in the form
SQL> alter system set <parameter name> = <new value> scope=spfile;and restart the whole database instance. Note, if we do not specify any CONTAINER=XYZ clause, the default of CONTAINER=CURRENT will be used according to the documentation.
Check the values
After the reboot, I created another set of tables that hold the values after the change.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> create table cdb_end_params as select name, value from v$parameter where name in (select name from params_in_question);
Table created.
SQL> alter session set container=mmipdb1;
Session altered.
SQL> create table cdb_end_params as select name, value from v$parameter where name in (select name from params_in_question);
Table created.At this point I am now able to compare the settings from before and after the parameter changes.
SQL> select count(*)
2 from
3 (select * from containers(cdb_start_params) where con_id=1) b,
4 (select * from containers(cdb_end_params) where con_id=1) e
5 where e.name = b.name
6 and e.value <> nvl(b.value, '');
COUNT(*)
----------
60
SQL> select count(*)
2 from
3 (select * from containers(cdb_start_params) where con_id=3) b,
4 (select * from containers(cdb_end_params) where con_id=3) e
5 where e.name = b.name
6 and nvl(e.value, 'X') <> nvl(b.value, 'X');
COUNT(*)
----------
56
So 60 out 63 parameter changes made it in the CDB$ROOT but only 56 into the PDB. What parameters did not change in the CDB$ROOT? Let’s check that.
select b.name
from
(select * from containers(cdb_start_params) where con_id=1) b,
(select * from containers(cdb_end_params) where con_id=1) e
where e.name = b.name
and nvl(e.value, 'X') = nvl(b.value, 'X')
;
NAME
--------------------------------------------------------------------------------
parallel_max_servers
result_cache_max_size
parallel_servers_target
Ok, these parameters are all related to parallel executions. In my example I am using a Standard Edition 2 database which does not allow parallel operations at all. That might be the reason. Let’s check next, which parameters did not made it into the PDB.
SQL> select b.name
2 from
3 (select * from containers(cdb_start_params) where con_id=1) b,
4 (select * from containers(cdb_end_params) where con_id=1) e
5 where e.name = b.name
6 and e.value <> nvl(b.value, '')
7 minus
8 select b.name
9 from
10 (select * from containers(cdb_start_params) where con_id=3) b,
11 (select * from containers(cdb_end_params) where con_id=3) e
12 where e.name = b.name
13 and nvl(e.value, 'X') <> nvl(b.value, 'X');
NAME
--------------------------------------------------------------------------------
sga_min_size
sga_target
shared_pool_size
undo_retention
Even though I altered the parameters with the default of CONTAINER=CURRENT, only these 4 parameters where left unchanged inside the PDB. All other changes are picked up by the PDB. But are these parameters really persisted in the PDBs SPfile?
SQL> select name, con_id, con_uid from v$pdbs;
NAME CON_ID CON_UID
-------------- ---------- ----------
PDB$SEED 2 3913790623
PDB1 3 2275295767
PDB2 4 3431655691
SQL> select name from pdb_spfile$ where pdb_uid=2275295767;
NAME
--------------
db_securefile
Obviously not.
Modify again
So my next check is to alter all these parameters again, but this time using CONTAINER=ALL, bounce the instance and compare the parameter settings again.
SQL> select b.name
2 from
3 (select * from containers(cdb_start_params) where con_id=1) b,
4 (select * from containers(cdb_end_params) where con_id=1) e
5 where e.name = b.name
6 and nvl(e.value, 'X') <> nvl(b.value, 'X')
7 minus
8 select b.name
9 from
10 (select * from containers(cdb_start_params) where con_id=3) b,
11 (select * from containers(cdb_end_params) where con_id=3) e
12 where e.name = b.name
13 and nvl(e.value, 'X') <> nvl(b.value, 'X');
no rows selected
SQL> select name from pdb_spfile$ where pdb_uid=2275295767;
NAME
-------------------------
cpu_min_count
db_securefile
parallel_servers_target
sga_min_size
sga_target
shared_pool_size
undo_retention
7 rows selected.
This time all parameter changes made it into the PDB. That means, the CONTAINER=ALL does exactly what it says. But interrestingly only a few parameters are stored in the PDBs Spfile and not only the ones that were left unchanged in the first example but some others too.
Conclusion
I don’t really found a pattern which paramters might make it into the PDB even though I did not specify CONTAINER=ALL. At the end I have to be careful what I am doing, think twice before doing it and of cause, check the outcome afterwards if it really did what I wanted it to do.
Marco's DBA Blog 