Strange behaviours on setting PDB parameters 3


[Tests performed on Oracle 19.8]

All started when I wanted to create a query to check which parameters are set on a PDB and the difference from the CDB$ROOT container.

col pdb_name for a10 
col value for a20
col pdb_value for a20
col root_value for a20
select a.pdb_name, a.name, a.value PDB_VALUE, b.value ROOT_VALUE from
  (select pdb_name,name,value 
   from v$system_parameter a left join dba_pdbs b on (a.CON_ID=b.pdb_id)
   where a.con_id>2 and isdefault='FALSE') a,
  (select 'CDB$ROOT' pdb_name,name,value 
   from v$system_parameter where con_id=0) b
where a.name=b.name
order by 1,2;

But I know there is also one view called pdb_spfile$ that would show the parameters on the PDB pseudo-spfiles:

col pdb_name for a10 
col name for a20
col value$ for a20
select pdb_name,name,value$ 
from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid) 
where con_id>2 order by name;

The V$SYSTEM_PARAMETER is well documented, while the PDB_SPFILE$ is not.

Now, the set or unset parameters do not work the same way, as I expected, it trigger some strange behaviours.

Strange behaviours

ALTER SYSTEM … default SCOPE on PDB

Oracle Documentation 19c only mentions the default SCOPE on CDB root:

"If a server parameter file was used to start up the database, then BOTH is the default. If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify."

On a PDB, the default SCOPE depends(!) on the action:

Setting a parameter, then default is SCOPE=BOTH :

TVDCDB1.PDB01 SQL> show parameter cursor_sharing
NAME             TYPE        VALUE
---------------- ----------- ----------------
cursor_sharing   string      EXACT

TVDCDB1.PDB01 SQL> show spparameter cursor_sharing
SID      NAME             TYPE        VALUE
-------- ---------------- ----------- ----------------
*        cursor_sharing   string

TVDCDB1.PDB01 SQL> alter system set cursor_sharing=FORCE;
System altered.

TVDCDB1.PDB01 SQL> show parameter cursor_sharing
NAME             TYPE        VALUE
---------------- ----------- ----------------
cursor_sharing   string      FORCE

TVDCDB1.PDB01 SQL> show spparameter cursor_sharing
SID      NAME             TYPE        VALUE
-------- ---------------- ----------- ----------------
*        cursor_sharing   string      FORCE

Resetting a parameter, then default is SCOPE=SPFILE :

TVDCDB1.PDB01 SQL> alter system reset cursor_sharing;
System altered.

TVDCDB1.PDB01 SQL> show parameter cursor_sharing
NAME             TYPE        VALUE
---------------- ----------- ----------------
cursor_sharing   string      FORCE

TVDCDB1.PDB01 SQL> show spparameter cursor_sharing
SID      NAME             TYPE        VALUE
-------- ---------------- ----------- ----------------
*        cursor_sharing   string

TVDCDB1.PDB01 SQL> alter system reset cursor_sharing scope=both;
System altered.

TVDCDB1.PDB01 SQL> show parameter cursor_sharing
NAME             TYPE        VALUE
---------------- ----------- ----------------
cursor_sharing   string      EXACT

PDB_SPFILE$ not updated on parameter reset

When going back to the CDB root, I would expect to not see anymore the parameter on PDB_SPFILE$ view. Well, it is still there:

SQL> connect / as sysdba
Connected.

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> select pdb_name,name,value$ from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid) where name='cursor_sharing' order by name;

PDB_NAME   NAME                      VALUE$
---------- ------------------------- --------------------
PDB01      cursor_sharing            'FORCE'
1 row selected.

And this remains even after restart the PDB or the complete instance.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2634018904 bytes
Fixed Size                  9138264 bytes
Variable Size            1526726656 bytes
Database Buffers          822083584 bytes
Redo Buffers                7634944 bytes
In-Memory Area            268435456 bytes
Database mounted.
Database opened.

SQL> select pdb_name,name,value$ from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid) where name='cursor_sharing' order by name;

PDB_NAME   NAME                      VALUE$
---------- ------------------------- --------------------
PDB01      cursor_sharing            'FORCE'
1 row selected.

And also after unplug and plug the PDB back!

SQL> alter pluggable database pdb01 close;
Pluggable database altered.

SQL> alter pluggable database pdb01 unplug into '/tmp/pdb01.xml';
Pluggable database altered.

SQL> select pdb_name,name,value$ from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid) where name='cursor_sharing' order by name;

PDB_NAME   NAME                      VALUE$
---------- ------------------------- --------------------
PDB01      cursor_sharing            'FORCE'
1 row selected.

SQL> drop pluggable database pdb01 keep datafiles;
Pluggable database dropped.

SQL> select pdb_name,name,value$ from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid) where name='cursor_sharing' order by name;

no rows selected

SQL> create pluggable database pdb01 using '/tmp/pdb01.xml' nocopy;
Pluggable database created.

SQL> select pdb_name,name,value$ from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid) where name='cursor_sharing' order by name;
PDB_NAME   NAME                      VALUE$
---------- ------------------------- --------------------
PDB01      cursor_sharing            'FORCE'
1 row selected.

SQL> alter pluggable database pdb01 open;
Pluggable database altered.

SQL> select pdb_name,name,value$ from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid) where name='cursor_sharing' order by name;
PDB_NAME   NAME                      VALUE$
---------- ------------------------- --------------------
PDB01      cursor_sharing            'FORCE'
1 row selected.

But the value is not used on the PDB itself:

SQL> alter session set container=pdb01;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
PDB01

SQL> show spparameter cursor_sharing

SID      NAME             TYPE        VALUE
-------- ---------------- ----------- ----------------
*        cursor_sharing   string

SQL> show parameter cursor_sharing

NAME             TYPE        VALUE
---------------- ----------- ----------------
cursor_sharing   string      EXACT

Default values shown on V$SYSTEM_PARAMETER

The “annoying” part of the using the V$SYSTEM_PARAMETER is that it will show always the SEED non-default parameters, even if these are not specified on the PDB pseudo-spfile:

SQL> create pluggable database pdb02 admin user pdb_admin identified by pdb_admin file_name_convert=('pdbseed','pdb02');
Pluggable database created.

SQL> alter pluggable database pdb02 open;
Pluggable database altered.

select a.pdb_name, a.name, a.value PDB_VALUE, b.value ROOT_VALUE from
  (select pdb_name,name,value 
   from v$system_parameter a left join dba_pdbs b on (a.CON_ID=b.pdb_id)
   where a.con_id>2 and isdefault='FALSE') a,
  (select 'CDB$ROOT' pdb_name,name,value 
   from v$system_parameter where con_id=0) b
where a.name=b.name and a.pdb_name='PDB02'
order by 1,2;


PDB_NAME   NAME                 PDB_VALUE            ROOT_VALUE
---------- -------------------- -------------------- --------------------
PDB02      db_securefile        PREFERRED            PREFERRED
PDB02      sga_target           0                    2634022912
PDB02      shared_pool_size     0                    637534208
PDB02      undo_tablespace      UNDOTBS1             UNDOTBS1

4 rows selected.

SQL> select 'PDB$SEED' pdb_name, name,value from v$system_parameter where con_id=2 and isdefault='FALSE';

PDB_NAME   NAME                 VALUE
---------- -------------------- --------------------
PDB$SEED   shared_pool_size     0
PDB$SEED   sga_target           0
PDB$SEED   undo_tablespace
PDB$SEED   db_securefile        PREFERRED

4 rows selected.

Summary: I would prefer to use the PDB_SPFILE$, as it tries to imitate a real SPFILE (at least name-wise), but seems it is not really working.


Leave a comment

Your email address will not be published. Required fields are marked *

3 thoughts on “Strange behaviours on setting PDB parameters

  • Martin Berger

    I did a quick&dirty test:
    when I set cursor_sharing in my PDB, the column pdb_spfile$.SPARE2 is 0,
    when I RESET cursor_sharing, pdb_spfile$.SPARE2 changes to 1.
    hth

    • Miguel Anjo Post author

      Hi Martin,

      Thanks for the comment. So I would believe the right way to use pdb_spfile$ would be:

      select pdb_name,name,value$
      from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid)
      where name=’cursor_sharing’
      and spare2=0
      order by name;

      Do you agree?

      • Martin Berger

        Hi Miguel,
        Yes, kind of 😉
        I’d use the filter
        bitand(nvl(spare2,0),1)=0
        as Oracle is also using it internally.

        I’d prefer using something like CONTAINERS(v$spparameterfile) but I failed in my sandbox for unknown reasons.