分类目录归档：数据库

在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$