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