Have you ever wondered why we, (DBAs) keep using the words “init.ora parameters”, when referring to instance parameter. When in fact, nobody is actually using an init.ora file to startup an Oracle instance anymore.
Most likely all databases use spfiles to start an Oracle instance. It is the year
2016.
So why is it, when we refer to instance parameters, we call them init.ora parameters?
It’s because we are creatures of habit. We got used to the lingo “init.ora parameters”. Also it’s shorter and easier to say it, plus it sounds better too.
Try saying it out
loud: “I am going to change the spfile.ora parameters” vs “I am going to change the init.ora parameters.”
Which one sounds better? The latter.
Now, back to business.
With each new Oracle database version, new init.ora parameters are introduced.
12c is no
different.
It is out of scope for this post to look at the new init.ora parameters. Instead, we’ll look at behavior of parameters in a PDB vs CDB.
What’s new in 12c, for memory parameters?
Remember the CDB concepts from my previous post, 12c Multitenant Architecture Explained With Mind Maps?
In 12c Multitenant Architecture, there is only ONE instance. The PDBs don’t have their own
instance, the PDBs are sharing the instance with the root container and other PDBs. Thus, there is only ONE spfile or pfile for the whole CDB.
Any parameter that is set in the spfile, applies to the root container. These values are also default values for all the PDBs.
Some instance
parameters can be customized in each PDB, if the parameter is defined as PDB Modifiable.
To find out, check V$PARAMETER – ISPDB_MODIFIABLE column for the parameters that can be customized at the PDB level.
select ispdb_modifiable, count(*) from v$parameter
group by ispdb_modifiable;
ISPDB COUNT(*)
----- ----------
TRUE 171
FALSE 196
Remember that entries in V$PARAMETER only apply to the container you are connected to.
select name, value, con_id
from v$parameter
where name='optimizer_mode';
NAME VALUE CON_ID
-------------------- -------------------- ----------
optimizer_mode ALL_ROWS 3
If you want to see the values of a modified parameter across all PDBs, while connected to the CDB, query V$SYSTEM_PARAMETER
SQL> alter session set container=pdb;
Session altered.
SQL> alter system set optimizer_mode='FIRST_ROWS';
System altered.
SQL> alter session set container=cdb$root;
Session altered.
select name, value, con_id
from v$system_parameter
where name='optimizer_mode';
NAME VALUE CON_ID
-------------------- -------------------- ----------
optimizer_mode ALL_ROWS 0
optimizer_mode FIRST_ROWS 3
SGA and PGA parameters cannot be customized at the PDB level. Same rule applies for any parameters that affect the instance itself, those parameters cannot be customized at the PDB level.
An interesting thing: when altering the parameter for a PDB, even if you omit “scope=spfile”, the changes survive the closing of the PDB. Why?
Because, the instance stores the changes in memory after the PDB is closed, and it writes the changes to disk when the whole instance is shutdown.
In a nutshell, check out the mind map below.
