首页 » ORACLE [C]系列 » Where is stored Initialization Parameter(spfile) for PDB in Oracle 12c (PDB参数文件存在哪里)

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

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时会被遗弃。

 

 

打赏

对不起,这篇文章暂时关闭评论。