{"id":411,"date":"2020-11-19T14:52:33","date_gmt":"2020-11-19T13:52:33","guid":{"rendered":"https:\/\/anjo.pt\/keyword-oracle\/?p=411"},"modified":"2023-09-19T15:13:05","modified_gmt":"2023-09-19T13:13:05","slug":"strange-behaviours-on-setting-pdb-parameters","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2020\/11\/19\/strange-behaviours-on-setting-pdb-parameters\/","title":{"rendered":"Strange behaviours on setting PDB parameters"},"content":{"rendered":"\n

[Tests performed on Oracle 19.8]<\/p>\n\n\n\n

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<\/strong>.<\/p>\n\n\n

\nset pages 110\ncol pdb_name for a10 \ncol name for a30 \ncol value for a20\ncol pdb_value for a20\ncol root_value for a20\ncol source for a10\nselect a.pdb_name, a.name, a.value PDB_VALUE, b.value ROOT_VALUE,source from\n  (select pdb_name,name,value,a.con_id, decode(ismodified,'MODIFIED','PDB SPFILE','PDB$SEED') SOURCE \n   from v$system_parameter a left join dba_pdbs b on (a.CON_ID=b.pdb_id)\n   where a.con_id>2 and (ismodified='MODIFIED' or isdefault='FALSE')) a,\n  (select 'CDB$ROOT' pdb_name,name,value,con_id,null\n   from v$system_parameter where con_id=0) b\nwhere a.name=b.name and a.con_id>2\norder by 1,2;\n<\/pre><\/div>\n\n\n
But I know there is also one view called pdb_spfile$ that would show the parameters on the PDB pseudo-spfiles:<\/p>\n\n\n
\ncol pdb_name for a10 \ncol name for a20\ncol value$ for a20\nselect pdb_name,name,value$ \nfrom pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid) \nwhere con_id>2 \nand bitand(nvl(spare2,0),1)=0\norder by name;\n<\/pre><\/div>\n\n\n
The V$SYSTEM_PARAMETER is well documented<\/a>, while the PDB_SPFILE$ is not. <\/p>\n\n\n\n
A slight more complete query to check parameters which are not set the same between cdb$root and the PDB is this one:<\/p>\n\n\n
\nwith pdb_params as (select pdb_name,name,trim(both '''' from value$) value$ \n                                   from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid)\n                                   where con_id>1 and bitand(nvl(spare2,0),1)=0 ),\n        cdb_params as (select name, value,inst_id,con_id from gv$parameter ),\n        spfile_params as (select name,value, sid from v$spparameter where isspecified='TRUE')\n  select pdb_name, pdb_params.name, value$ pdb_value, cdb_params.value root_value, spfile_params.value spfile_value\n    from pdb_params, cdb_params, spfile_params\n    where pdb_params.name(+)=cdb_params.name and cdb_params.name(+)=spfile_params.name\n        and (value$!=cdb_params.value or value$!=spfile_params.value)\nunion all \n  select case when sys_context('USERENV','CON_NAME') !='CDB$ROOT' then 'CALL THIS SCRIPT FROM CDB$ROOT' end pdb_name,\n        null,null,null,null from dual\norder by 1,2;\n<\/pre><\/div>\n\n\n
Now, the set or unset parameters do not work the same way, as I expected, it trigger some strange behaviours.<\/p>\n\n\n\n\n\n\n\n
Strange behaviours<\/h2>\n\n\n\n
ALTER SYSTEM … default SCOPE on PDB<\/h3>\n\n\n\n
Oracle Documentation 19c<\/a> only mentions the default SCOPE on CDB root:<\/p>\n\n\n\n
\"If a server parameter file was used to start up the database, then BOTH<\/code> is the default. If a parameter file was used to start up the database, then MEMORY<\/code> is the default, as well as the only scope you can specify.\"<\/pre>\n\n\n\n
On a PDB, the default SCOPE depends(!) on the action:<\/p>\n\n\n\n
Setting<\/strong> a parameter, then default is SCOPE=BOTH<\/strong> :<\/p>\n\n\n
\nTVDCDB1.PDB01 SQL> show parameter cursor_sharing\nNAME             TYPE        VALUE\n---------------- ----------- ----------------\ncursor_sharing   string      EXACT\n\nTVDCDB1.PDB01 SQL> show spparameter cursor_sharing\nSID      NAME             TYPE        VALUE\n-------- ---------------- ----------- ----------------\n*        cursor_sharing   string\n\nTVDCDB1.PDB01 SQL> alter system set cursor_sharing=FORCE;\nSystem altered.\n\nTVDCDB1.PDB01 SQL> show parameter cursor_sharing\nNAME             TYPE        VALUE\n---------------- ----------- ----------------\ncursor_sharing   string      FORCE\n\nTVDCDB1.PDB01 SQL> show spparameter cursor_sharing\nSID      NAME             TYPE        VALUE\n-------- ---------------- ----------- ----------------\n*        cursor_sharing   string      FORCE\n<\/pre><\/div>\n\n\n
Resetting <\/strong>a parameter, then default is SCOPE=SPFILE<\/strong> :<\/p>\n\n\n
\nTVDCDB1.PDB01 SQL> alter system reset cursor_sharing;\nSystem altered.\n\nTVDCDB1.PDB01 SQL> show parameter cursor_sharing\nNAME             TYPE        VALUE\n---------------- ----------- ----------------\ncursor_sharing   string      FORCE\n\nTVDCDB1.PDB01 SQL> show spparameter cursor_sharing\nSID      NAME             TYPE        VALUE\n-------- ---------------- ----------- ----------------\n*        cursor_sharing   string\n\nTVDCDB1.PDB01 SQL> alter system reset cursor_sharing scope=both;\nSystem altered.\n\nTVDCDB1.PDB01 SQL> show parameter cursor_sharing\nNAME             TYPE        VALUE\n---------------- ----------- ----------------\ncursor_sharing   string      EXACT\n<\/pre><\/div>\n\n\n
PDB_SPFILE$ marks row as “deleted” on parameter reset<\/h3>\n\n\n\n
[EDIT: 03.01.2022, next paragraphs corrected followed helpful comments from Martin Albert Berger]<\/p>\n\n\n\n
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:<\/p>\n\n\n
\nSQL> connect \/ as sysdba\nConnected.\n\nSQL> show con_name\nCON_NAME\n------------------------------\nCDB$ROOT\n\nSQL> select pdb_name,name,value$ from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid) where name='cursor_sharing' order by name;\n\nPDB_NAME   NAME                      VALUE$\n---------- ------------------------- --------------------\nPDB01      cursor_sharing            'FORCE'\n1 row selected.\n\nSQL> -- Need to use "and bitand(nvl(spare2,0),1)=0", as row is just marked "deleted"\n\nSQL> select pdb_name,name,value$ from pdb_spfile$ left join dba_pdbs on (CON_UID=pdb_uid) where name='cursor_sharing' and bitand(nvl(spare2,0),1)=0 order by name;\n\nno rows selected\n<\/pre><\/div>\n\n\n
Use ISMODIFIED column on V$SYSTEM_PARAMETER<\/h3>\n\n\n\n
On V$SYSTEM_PARAMETER the “ISDEFAULT=’FALSE'” will show the SEED non-default parameters for all PDBs, even if these are not specified on the PDB pseudo-spfile:<\/p>\n\n\n
\nSQL> create pluggable database pdb02 admin user pdb_admin identified by pdb_admin file_name_convert=('pdbseed','pdb02');\nPluggable database created.\n\nSQL> alter pluggable database pdb02 open;\nPluggable database altered.\n\nselect a.pdb_name, a.name, a.value PDB_VALUE, b.value ROOT_VALUE from\n  (select pdb_name,name,value \n   from v$system_parameter a left join dba_pdbs b on (a.CON_ID=b.pdb_id)\n   where a.con_id>2 and isdefault='FALSE') a,\n  (select 'CDB$ROOT' pdb_name,name,value \n   from v$system_parameter where con_id=0) b\nwhere a.name=b.name and a.pdb_name='PDB02'\norder by 1,2;\n\n\nPDB_NAME   NAME                 PDB_VALUE            ROOT_VALUE\n---------- -------------------- -------------------- --------------------\nPDB02      db_securefile        PREFERRED            PREFERRED\nPDB02      sga_target           0                    2634022912\nPDB02      shared_pool_size     0                    637534208\nPDB02      undo_tablespace      UNDOTBS1             UNDOTBS1\n\n4 rows selected.\n\nSQL> select 'PDB$SEED' pdb_name, name,value from v$system_parameter where con_id=2 and isdefault='FALSE';\n\nPDB_NAME   NAME                 VALUE\n---------- -------------------- --------------------\nPDB$SEED   shared_pool_size     0\nPDB$SEED   sga_target           0\nPDB$SEED   undo_tablespace\nPDB$SEED   db_securefile        PREFERRED\n\n4 rows selected.\n<\/pre><\/div>\n\n\n
One needs to use the ISMODIFIED=’MODIFIED’ to get the modified values on a specific PDB:<\/p>\n\n\n
\nSQL> select a.pdb_name, a.name, a.value PDB_VALUE, b.value ROOT_VALUE from\n  (select pdb_name,name,value \n   from v$system_parameter a left join dba_pdbs b on (a.CON_ID=b.pdb_id)\n   where a.con_id>2 and isdefault='FALSE') a,\n  (select 'CDB$ROOT' pdb_name,name,value \n   from v$system_parameter where con_id=0) b\nwhere a.name=b.name and a.pdb_name='PDB02'\norder by 1,2;\n\nno rows selected\n\nSQL> alter session set container=PDB01;\nSession altered.\n\nTVDCDB1.PDB01 SQL> alter system set cursor_sharing=FORCE;\nSystem altered.\n\nTVDCDB1.PDB01 SQL> alter session set container=CDB$ROOT;\nSession altered.\n\nSQL> select a.pdb_name, a.name, a.value PDB_VALUE, b.value ROOT_VALUE from\n  (select pdb_name,name,value \n   from v$system_parameter a left join dba_pdbs b on (a.CON_ID=b.pdb_id)\n   where a.con_id>2 and ismodified='MODIFIED') a,\n  (select 'CDB$ROOT' pdb_name,name,value \n   from v$system_parameter where con_id=0) b\nwhere a.name=b.name and a.pdb_name='PDB02'\norder by 1,2;\n\nPDB_NAME   NAME                 PDB_VALUE            ROOT_VALUE\n---------- -------------------- -------------------- --------------------\nPDB01      cursor_sharing       FORCE                EXACT\n<\/pre><\/div>\n\n\n
Summary<\/strong>: Make sure you use the correct filter on V$SYSTEM_PARAMETER or PDB_SPFILE$ to have the parameters of a PDB.<\/p>\n","protected":false},"excerpt":{"rendered":"
[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. But I know there is also one view called pdb_spfile$ that would show the parameters on the PDB pseudo-spfiles: The V$SYSTEM_PARAMETER is well documented, while […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,39],"tags":[],"class_list":{"0":"post-411","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-oracle","7":"category-oracle-19c","8":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/411","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=411"}],"version-history":[{"count":10,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/411\/revisions"}],"predecessor-version":[{"id":865,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/411\/revisions\/865"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=411"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=411"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=411"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}