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
1
PDB12102
3
1
202375680
12.1.0.2.0
12.1.0.2.0
12.1.0.2.0
0.0.0.0.22
22
8.0.0.0.0
3315196027
0
4042264014
FF01D0A2814B36AEE043E7A8A8C0AA58
3098687
0
4194824
SYSTEM
0
0
1
0
/u01/app/oracle/oradata/ora12102/pdb12102/system01.dbf
8
1
1605041
0
1
34560
8192
202375680
3315196027
0
3098683
0
1594143
853865809
...
processes=300
shared_pool_size=486539264
large_pool_size=83886080
java_pool_size=33554432
streams_pool_size=0
sga_target=0
db_block_size=8192
db_cache_size=1073741824
_shared_io_pool_size=117440512
compatible='12.1.0.2.0'
_catalog_foreign_restore=FALSE
_data_transfer_cache_size=0
pga_aggregate_target=836763648
enable_pluggable_database=TRUE
*.db_securefile='PREFERRED'
*.inmemory_size=524288000
*.job_queue_processes=2
*.open_cursors=100
*.optimizer_index_cost_adj=40
*.sessions=300
...
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时会被遗弃。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
