Where is stored Initialization Parameter(spfile) for PDB in Oracle 12c (PDB参数文件存在哪里)

原创 Anbob 2014-09-12

1248

12c引入的可插拔的数据库容器(Multitenant Database)框架，在CDB中容器中的有1个或多个(Pluggable Databases)PDB，同时有一些参数是可以在PDB级别修改，我们暂时称为PDB local parameter, PDB 级的参数对于PDB自身影响会覆盖(优先)从CDB继承的参数, 可以从 V$SYSTEM_PARAMETER document view 中ISPDB_MODIFIABLE=TRUE的记录中找出哪些参数可以在PDB级修改，然后可以通过ALTER SYSTEM SET 命令在PDB 中进行修改如下：

alter session set container=pdbxx;
alter system set inmemory_size=500M;

昨天在测试12.1.0.2 的新组件(Oracle In-Memory Option)时,用上面的命令,发现PDB级修改了INMEMORY_SIZE后SEGMENT不能成功Inmemory,并且SGA查看IN Memory area还是0时注意到了这个问题，当然最后是通过设置CDB的INMEMORY_SIZE并重启DB后问题解决。带着问题研究了一个CDB和PDB 是Parameter 或者 SPfile 是如何区分？又是存在哪里？会不会像之前的RAC Spfile用sid 的方式把PDB参数区分呢？
首先在CDB中创建PFILE

sys@ORA12102>create pfile from spfile;
File created.
[oracle@db231 ~]$ cd $ORACLE_HOME/dbs
[oracle@db231 dbs]$ vi initora12102.ora 
ora12102.__data_transfer_cache_size=0
ora12102.__db_cache_size=1073741824
ora12102.__java_pool_size=33554432
ora12102.__large_pool_size=83886080
ora12102.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ora12102.__pga_aggregate_target=838860800
ora12102.__sga_target=2516582400
ora12102.__shared_io_pool_size=117440512
ora12102.__shared_pool_size=486539264
ora12102.__streams_pool_size=0
*._catalog_foreign_restore=FALSE
*._data_transfer_cache_size=0
*._shared_io_pool_size=117440512
*.audit_file_dest='/u01/app/oracle/admin/ora12102/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/ora12102/control01.ctl','/u01/app/oracle/fast_recovery_area/ora12102/control02.ctl'
*.db_block_size=8192
*.db_cache_size=1073741824
*.db_domain=''
*.db_name='ora12102'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora12102XDB)'
*.enable_pluggable_database=true
*.java_pool_size=33554432
*.large_pool_size=83886080
*.open_cursors=300
*.pga_aggregate_target=798m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=0
*.shared_pool_size=486539264
*.streams_pool_size=0
*.undo_tablespace='UNDOTBS1'

Note:
可以看到 parameter inmemory_size 并没有包含在pfile中, 而且也看不出格式中有PDB的身影. 我们查看12C 的官方文档 (here ), 我们会找到这样一段话"

A text initialization parameter file (PFILE) cannot contain PDB-specific parameter values.

在PDB中创建PFILE

sys@ORA12102>alter session set container=PDB12102;
sys@ORA12102>create pfile='/tmp/pfile.init' from spfile;
File created.
[oracle@db231 ~]$ vi /tmp/pfile.init 
*.job_queue_processes=2
*.inmemory_size=524288000
*.db_securefile='PREFERRED'

Note:
pdb 的pfile中只有刚配置的inmemory_size 和从CDB 继承的两项。
下面我们在CDB和PDB级分别配置不同的参数值，从V$SYSTEM_PARAMETER 观察一下区别。

SQL> alter session set container=cdb$root;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter system set optimizer_index_cost_adj=60;
System altered.
SQL> alter session set container=pdb12102;
Session altered.
SQL> alter system set optimizer_index_cost_adj=40;
System altered.
SQL> select name,value,con_id from v$system_parameter where name='optimizer_index_cost_adj';
NAME                      VALUE                    CON_ID
------------------------- -------------------- ----------
optimizer_index_cost_adj  60                            0
optimizer_index_cost_adj  40                            3

NOTE:
可以看到我们修改了optimizer_index_cost_adj分别在CDB和PDB指定了不同的值， PDB级查看参数可以从V$PARAMETER,如果查询CDB & PDB 可以从V$SYSTEM_PARAMETER，用上面的SQL可以列出。但是注意V$SYSTEM_PARAMETER中只列出已OPENED的pluggable database . 从v$fixed_view_definITION中可以查到V$SYSTEM_PARAMETER 取的是X$的表中取的数据, 关于X$ VIEW可以查看here，而且 V$SYSTEM_PARAMETER数据来自于UGA, X$数据来源参考TanelPoder的文章, 其实我们可能从10046 trace中不难找到PDB的修改参数修改的是PDB_SPFILE$ 字典表。V$SYSTEM_PARAMETER值也许是在PDB OPEN时加载到内存中。那PDB_SPFILE$ 是在CDB还是私有的PDB中呢？下面接着测试

SQL> select con_id,name,state from x$con;
    CON_ID NAME                           STATE
---------- ------------------------- ----------
         1 CDB$ROOT                           1
         2 PDB$SEED                           2
         3 PDB12102                           1
sys@ORA12102>alter session set container=PDB12102;
sys@ORA12102>alter system set open_cursors=100 ;
System altered.
sys@ORA12102>show parameter open_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     100
...
sys@ORA12102>select pdb_uid,name,value$ from PDB_SPFILE$;
no rows selected
SQL> alter session set container=cdb$root;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
sys@ORA12102>show parameter open_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     120
...
sys@ORA12102>select pdb_uid,name,value$ from PDB_SPFILE$;
             PDB_UID NAME                           VALUE$
-------------------- ------------------------------ -------------------------
          3315196027 job_queue_processes            2
          3315196027 inmemory_size                  524288000
          3315196027 sessions                       300
          3315196027 db_securefile                  'PREFERRED'
          3315196027 optimizer_index_cost_adj       40
          3315196027 open_cursors                   100
sys@ORA12102>select con_id,dbid,con_uid,guid from v$pdbs;
              CON_ID                 DBID              CON_UID GUID
-------------------- -------------------- -------------------- --------------------------------
                   2           2671850633           2671850633 FF01C4255E4533B6E043E7A8A8C0E14E
                   3           3315196027           3315196027 FF01D0A2814B36AEE043E7A8A8C0AA58

Note:
答案是CDB中。
Unplug a PDB
如果PDB UN-PLUG,参PDB的参数是如何传递的呢？

sys@ORA12102>ALTER PLUGGABLE DATABASE PDB12102 CLOSE;
Pluggable database altered.
sys@ORA12102>ALTER PLUGGABLE DATABASE pdb12102 UNPLUG INTO '/tmp/pdb12102.xml';
Pluggable database altered.

[oracle@db231 tmp]$ ll -rth
total 40K
drwx------ 2 root root 16K Apr 15 2013 lost+found
srw------- 1 root root 0 Apr 22 12:32 scim-panel-socket:0-root
srwxr-xr-x 1 root root 0 Apr 22 12:32 mapping-root
srwxrwxrwx 1 mongo mongo 0 Sep 9 10:43 mongodb-27017.sock
-rw-r--r-- 1 oracle oinstall 35 Sep 11 13:58 sql_tmp.sql.sql
-rw-r--r-- 1 oracle oinstall 2.2K Sep 11 13:58 env_tmp.sql.sql
-rw-r--r-- 1 oracle oinstall 78 Sep 11 16:58 pfile.init
-rw-r--r-- 1 oracle oinstall 6.6K Sep 12 10:52 pdb12102.xml
drwxr-xr-x 2 oracle oinstall 4.0K Sep 12 10:52 hsperfdata_oracle
[oracle@db231 tmp]$ vi pdb12102.xml

<PDB>
  <xmlversion>1</xmlversion>
  <pdbname>PDB12102</pdbname>
  <cid>3</cid>
  <byteorder>1</byteorder>
  <vsn>202375680</vsn>
  <vsns>
    <vsnnum>12.1.0.2.0</vsnnum>
    <cdbcompt>12.1.0.2.0</cdbcompt>
    <pdbcompt>12.1.0.2.0</pdbcompt>
    <vsnlibnum>0.0.0.0.22</vsnlibnum>
    <vsnsql>22</vsnsql>
    <vsnbsv>8.0.0.0.0</vsnbsv>
  </vsns>
  <dbid>3315196027</dbid>
  <ncdb2pdb>0</ncdb2pdb>
  <cdbid>4042264014</cdbid>
  <guid>FF01D0A2814B36AEE043E7A8A8C0AA58</guid>
  <uscnbas>3098687</uscnbas>
  <uscnwrp>0</uscnwrp>
  <rdba>4194824</rdba>
  <tablespace>
    <name>SYSTEM</name>
    <type>0</type>
    <tsn>0</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/u01/app/oracle/oradata/ora12102/pdb12102/system01.dbf</path>
      <afn>8</afn>
      <rfn>1</rfn>
      <createscnbas>1605041</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>34560</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375680</vsn>
      <fdbid>3315196027</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>3098683</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>1594143</frlsb>
      <frlt>853865809</frlt>
    </file>
  </tablespace>
  ...
    <parameters>
      <parameter>processes=300</parameter>
      <parameter>shared_pool_size=486539264</parameter>
      <parameter>large_pool_size=83886080</parameter>
      <parameter>java_pool_size=33554432</parameter>
      <parameter>streams_pool_size=0</parameter>
      <parameter>sga_target=0</parameter>
      <parameter>db_block_size=8192</parameter>
      <parameter>db_cache_size=1073741824</parameter>
      <parameter>_shared_io_pool_size=117440512</parameter>
      <parameter>compatible='12.1.0.2.0'</parameter>
      <parameter>_catalog_foreign_restore=FALSE</parameter>
      <parameter>_data_transfer_cache_size=0</parameter>
      <parameter>pga_aggregate_target=836763648</parameter>
      <parameter>enable_pluggable_database=TRUE</parameter>
      <spfile>*.db_securefile='PREFERRED'</spfile>
      <spfile>*.inmemory_size=524288000</spfile>
      <spfile>*.job_queue_processes=2</spfile>
      <spfile>*.open_cursors=100</spfile>
      <spfile>*.optimizer_index_cost_adj=40</spfile>
      <spfile>*.sessions=300</spfile>
    </parameters>
...

sys@ORA12102>select pdb_uid,name,value$ from PDB_SPFILE$;
             PDB_UID NAME                           VALUE$
-------------------- ------------------------------ ------------------------------
          3315196027 job_queue_processes            2
          3315196027 inmemory_size                  524288000
          3315196027 sessions                       300
          3315196027 db_securefile                  'PREFERRED'
          3315196027 optimizer_index_cost_adj       40
          3315196027 open_cursors                   100
sys@ORA12102>DROP PLUGGABLE DATABASE pdb12102 KEEP DATAFILES;
Pluggable database dropped.
sys@ORA12102>select con_id,dbid,con_uid,guid,name,open_mode from v$pdbs;
   CON_ID                 DBID              CON_UID NAME                           OPEN_MODE
--------- -------------------- -------------------- ------------------------------ ----------
        2           2671850633           2671850633 PDB$SEED                       READ ONLY
sys@ORA12102>select pdb_uid,name,value$ from PDB_SPFILE$;
no rows selected

Note:
PDB un-plug后pdb parameter and spfile会先进xml文件, 当drop pluggable database后，pdb信息和PDB_SPFILE$记录也会被清除。(当然可以手动delete PDB_SPFILE$ 只是在pdb 重启时，参数不存在从CDB继承). 如PDB 再PLUG-IN 时会怎么样呢？
Plug-in PDB

sys@ORA12102>show con_name
CON_NAME
------------------------------
CDB$ROOT
sys@ORA12102>create pluggable database pdbanbob using '/tmp/pdb12102.xml' nocopy;
Pluggable database created.
sys@ORA12102>alter pluggable database pdbanbob open;
Pluggable database altered.
sys@ORA12102>select con_id,dbid,con_uid,name,open_mode from v$pdbs;
CON_ID DBID CON_UID NAME OPEN_MODE
-------------------- -------------------- -------------------- ----------------- ----------
2 2671850633 2671850633 PDB$SEED READ ONLY
3 3315196027 2910323056 PDBANBOB READ WRITE
sys@ORA12102>select pdb_uid,name,value$ from PDB_SPFILE$;
PDB_UID NAME VALUE$
-------------------- ------------------------------ ------------------------------
2910323056 job_queue_processes 2
2910323056 open_cursors 100
2910323056 optimizer_index_cost_adj 40
2910323056 sessions 300
2910323056 db_securefile 'PREFERRED'

NOTE:
注意到大部分参数是从XML中重新启用到新PDB, 但是注意到inmemory_size参数丢失了,In-Memory option disabled.其实想想也是合理的。

When a PDB is unplugged from a CDB, the values of the initialization parameters that were specified for the PDB with SCOPE=BOTH or SCOPE=SPFILE are added to the PDB's XML metadata file. These values are restored for the PDB when it is plugged in to a CDB.

Summary:
CDB的参数文件依然使用以前的SPIFLE，pdb的参数文件不会出现在SPFILE中，而是直接从CDB中继承，如果PDB中有privete Local parameter 会存在CDB的PDB_SPFILE$字典表中以con_id区别，当PDB UN-Plug时，PDB参数会写入PDB的XML文件中，再当PDB重新Plug-in到CDB时会重新加载回PDB, 但是由于一些DB参数特殊原因在plug-in时会被遗弃。

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Where is stored Initialization Parameter(spfile) for PDB in Oracle 12c (PDB参数文件存在哪里)

评论