转自：http://www.xifenfei.com/2013/06/oracle-12c-cdb%E4%B8%ADpdb%E5%8F%82%E6%95%B0%E7%AE%A1%E7%90%86%E6%9C%BA%E5%88%B6.html
在ORACLE 12C中参数文件只是记录了cdb的参数信息,没有记录任何的pdb的信息,那ORACLE是如何管理使得各个pdb有自己的参数,这里通过试验的出来ORACLE 12C CDB环境中是通过参数文件结合PDB_SPFILE$来实现参数管理
数据库版本
SQL>
select
*
from
v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle
Database
12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
PL/SQL Release 12.1.0.1.0 - Production 0
CORE 12.1.0.1.0 Production 0
TNS
for
Linux: Version 12.1.0.1.0 - Production 0
NLSRTL Version 12.1.0.1.0 - Production 0
|
pdb信息
SQL>
select
PDB_NAME,CON_UID,pdb_id,status
from
dba_pdbs;
PDB_NAME CON_UID PDB_ID STATUS
---------- ---------- ---------- -------------
PDB1 3313918585 3 NORMAL
PDB$SEED 4048821679 2 NORMAL
PDB2 3872456618 4 NORMAL
SQL>
select
con_id,dbid,
NAME
,OPEN_MODE
from
v$pdbs;
CON_ID DBID
NAME
OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED
READ
ONLY
3 3313918585 PDB1
READ
WRITE
4 3872456618 PDB2 MOUNTED
|
CDB$ROOT中修改参数
--指定container=all
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
alter
system
set
open_cursors=500 container=
all
;
System altered.
SQL> show parameter open_cursors;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors
integer
500
SQL>
alter
session
set
container=pdb1;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> show parameter open_cursors;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors
integer
500
--在CDB$ROOT中修改不指定container参数表示全部pdb生效
SQL>
alter
session
set
container=CDB$ROOT;
Session altered.
SQL>
alter
system
set
open_cursors=100;
System altered.
SQL> show parameter open_cursors;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors
integer
100
SQL>
alter
session
set
container=pdb1;
Session altered.
SQL> show parameter open_cursors;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors
integer
100
--指定container=current
SQL>
alter
system
set
open_cursors=120 container=
current
;
System altered.
SQL> show parameter open_cursors;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors
integer
120
SQL>
alter
session
set
container=pdb2 ;
Session altered.
SQL> show parameter open_cursors;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors
integer
120
|
这里可以看出来,在ROOT中修改参数,默认情况和指定container=all/current均是所有open的pdb都生效.
这里有个疑问ORACLE的参数文件只是记录的cdb的sid的参数,并未记录各个pdb的参数,那是如何实现cdb中各个pdb参数不一致的呢？继续分析
修改pdb参数做10046
SQL> show con_name;
CON_NAME
------------------------------
PDB1
SQL> oradebug setmypid
Statement processed.
SQL> oradebug EVENT 10046 TRACE
NAME
CONTEXT FOREVER,
LEVEL
12
Statement processed.
SQL> oradebug TRACEFILE_NAME
/u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_18377.trc
SQL>
alter
system
set
sessions=100;
System altered.
SQL> oradebug EVENT 10046 trace
name
context
off
Statement processed.
--继续修改pdb参数
SQL>
alter
session
set
container=pdb1;
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug EVENT 10046 TRACE
NAME
CONTEXT FOREVER,
LEVEL
12
Statement processed.
SQL> oradebug TRACEFILE_NAME
/u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_20275.trc
SQL>
alter
system
set
sessions=101;
System altered.
SQL> oradebug EVENT 10046 trace
name
context
off
Statement processed.
|
分析trace文件
--第一次修改pdb参数值
insert into pdb_spfile$(db_uniq_name, pdb_uid, sid, name, value$, comment$) values(:1,:2,:3,:4,:5,:6)
END OF STMT
PARSE
#140085118752824:c=3999,e=3397,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=99767937623
BINDS
#140085118752824:
Bind
#0
oacdty=01 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=7fffcfaa5842 bln=32 avl=03 flg=09
value=
"cdb"
Bind
#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f681bbb2170 bln=22 avl=06 flg=05
value=3313918585
Bind
#2
oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=7fffcfaa46f8 bln=32 avl=01 flg=09
value=
"*"
Bind
#3
oacdty=01 mxl=32(08) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=0bc220d8 bln=32 avl=08 flg=09
value=
"sessions"
Bind
#4
oacdty=01 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=7fffcfaa474c bln=32 avl=03 flg=09
value=
"100"
Bind
#5
oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
--第二次修改pdb参数值(相同参数)
update pdb_spfile$
set
value$=:5, comment$=:6 where name=:1 and pdb_uid=:2 and db_uniq_name=:3 and sid=:4
BINDS
#140603847818408:
Bind
#0
oacdty=01 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=7ffff6477dcc bln=32 avl=03 flg=09
value=
"101"
Bind
#1
oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind
#2
oacdty=01 mxl=32(08) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=0bc220d8 bln=32 avl=08 flg=09
value=
"sessions"
Bind
#3
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7fe0e2638320 bln=22 avl=06 flg=05
value=3313918585
Bind
#4
oacdty=01 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=7ffff6478ec2 bln=32 avl=03 flg=09
value=
"cdb"
Bind
#5
oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=7ffff6477d78 bln=32 avl=01 flg=09
value=
"*"
|
通过这里我们发现在独立修改pdb参数之时,其本质是在pdb_spfile$基表中插入或者修改相关记录(第一次修改插入,后续修改是更新)
关于pdb_spfile$基表分析
SQL> SHOW CON_NAME;
CON_NAME
------------------------------
CDB$ROOT
SQL> COL OWNER
FOR
A10
SQL>
select
con_id,owner,object_type
from
cdb_objects
where
object_name=
'PDB_SPFILE$'
;
CON_ID OWNER OBJECT_TYPE
---------- ---------- -----------------------
2 SYS
TABLE
1 SYS
TABLE
3 SYS
TABLE
SQL> COL DB_UNIQ_NAME
FOR
A10
SQL> COL
NAME
FOR
A15
SQL> COL VALUE$
FOR
A10
SQL>
SELECT
DB_UNIQ_NAME,PDB_UID,
NAME
,VALUE$
FROM
PDB_SPFILE$;
DB_UNIQ_NA PDB_UID
NAME
VALUE$
---------- ---------- --------------- ----------
cdb 3313918585 sessions 101
SQL>
ALTER
SESSION
SET
CONTAINER=pdb1;
Session altered.
SQL>
SELECT
DB_UNIQ_NAME,PDB_UID,
NAME
,VALUE$
FROM
PDB_SPFILE$;
no
rows
selected
|
证明pdb中不同于root的参数是记录在root的PDB_SPFILE$基表中.
整个CDB的工作原理是如果在PDB_SPFILE$中无相关参数记录,则继承cdb的参数文件中值,如果PDB_SPFILE$中有记录则使用该值覆盖cdb参数文件值.
删除PDB_SPFILE$验证
SQL> SHOW CON_NAME;
CON_NAME
------------------------------
CDB$ROOT
SQL> show parameter open_cursors;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors
integer
100
SQL>
select
con_id,dbid,
NAME
,OPEN_MODE
from
v$pdbs;
CON_ID DBID
NAME
OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED
READ
ONLY
3 3313918585 PDB1 MOUNTED
4 3872456618 PDB2
READ
WRITE
SQL>
alter
session
set
container=pdb2;
Session altered.
SQL> show parameter open_cursors;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors
integer
100
SQL>
alter
system
set
open_cursors=110;
System altered.
SQL> show parameter open_cursors;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors
integer
110
SQL> conn /
as
sysdba
Connected.
SQL>
select
value$
from
pdb_spfile$
where
name
=
'open_cursors'
;
VALUE$
--------------------------------------------------------------------------------
110
SQL>
delete
from
pdb_spfile$
where
name
=
'open_cursors'
;
1 row deleted.
SQL>
commit
;
Commit
complete.
SQL> startup
ORACLE instance started.
Total System
Global
Area 597098496 bytes
Fixed
Size
2291072 bytes
Variable
Size
272632448 bytes
Database
Buffers 314572800 bytes
Redo Buffers 7602176 bytes
Database
mounted.
Database
opened.
SQL>
select
value$
from
pdb_spfile$
where
name
=
'open_cursors'
;
no
rows
selected
SQL> show parameter open_cursors;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors
integer
100
SQL>
alter
session
set
container=pdb2 ;
Session altered.
SQL>
alter
database
open
;
Database
altered.
SQL> show parameter open_cursors;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors
integer
100
|
删除PDB_SPFILE$中相关记录,pdb的参数值会自动继续继承cdb中参数值
总结说明:通过上述的一些列试验证明cdb中参数关系,在cdb中修改,会默认所有pdb均自动继承;如果在pdb中修改值会覆盖cdb参数,而且只对当前pdb生效,并记录在PDB_SPFILE$
