联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在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$