Things You Didn’t Know About 12c init.ora Parameters!

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.

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana