12c Pluggable Database Container Database可插拔数据库特性专题

Oracle Database 12c中带来一种全新的架构,允许用户在一个独立的Oracle数据库中拥有多个pluggable可拔插的数据库。这种Pluggable 可拔插数据库的出现是为了对应 用户目前使用RDBMS数据库的现状,即有一些用户拥有大量的部门级应用构建于Oracle RDBMS数据库之上。

 

以下几个场景适合于使用pluggable database:

  1. 在产品系统中的某些应用实际仅使用十分少量的硬件资源。但是如果存在大量这样的应用,则还是需要构造大量的数据库实例并为这些小规模的数据库分配存储空间
  2. 对于那些并不十分复杂或重要,需要全职DBA花费大量时间管理的数据库
  3. 为了更好地利用硬件和DBA资源,用户有必要将大量的部门级应用整合到少数几个oracle RDBMS数据库中以便部署和管理

 

 

Pluggable Database 可拔插数据库允许DBA整合大量的小的部门级数据库到一个更庞大的数据库中。

 

Pluggable Database 带来的好处

 

在一个集中化的平台上操作多个数据库将有效降低成本:

  1. 更少的实例损耗
  2. 更低的存储成本

 

减少对DBA资源的使用,以及便于维护安全性:

  • 无需应用修改
  • 更快和简便的配置
  • 节省了打patch和升级的时间
  • 分离了以下责任:
    • 不同应用的管理员
    • 应用程序管理员和DBA
    • 应用用户
  • 提供isolation
  • 保证与非CDB 完整的向后兼容性
  • 完整的RAC操作使用
  • 与Oracle Enterprise Manager和Resource Manager整合在一起
  • 可以集中化管理多个数据库
    • 备份和灾难恢复
    • 补丁和升级

 

 

cdb1

 

 

上图给出了一个Container Database 并带有4个Containers, ROOT(CDB$ROOT)和其他的三个pluggable Database。每一个Pluggable Database均拥有自己的独立应用程序,这些Pluggable Database要么有自己的DBA, 要么被Container Administrator即SYS用户管理;SYS用户属于root(CDB$ROOT) Container 是一个common user。 SYS用户还是像之前的版本那样权威,他可以管理root container 和其他所有的pluggable database。

 

一个Pluggable Database可拔插数据库是 一堆database schemas数据库模式的集合以一个分离的数据库逻辑形象呈现给用户和应用程序。但是在物理级别,Container database拥有一个数据库实例以及多个数据文件,和普通的非CDB是一样的。 不管是客户端程序还是数据库对象均无需修改,以适应CDB。

用户将很方便地将一个非CDB 插入到一个CDB中, CDB避免了以下的冗余:

  • 后台进程
  • 内存分配
  • 多分数据字典源数据

 

一个CDB将多个应用合并到单一的一个实例中, 这导致 仅使用 一套后台进程,一份SGA内存分配和root container中的一份数据字典。当然PDB还将在自己的数据字典中维护部分数据。

 

cdb2

 

关于CDB 数据字典实际情况的测试:

 

通过下面的实验我们可以得出如下结论:

  1. 每一个Container 维护自己的一套底层数据字典 数据实际存放在C_OBJ#(C_OBJ#  的SHARING为NONE,意味着本质上 PDB还是有自己独立的数据字典的。)这样的cluster中,而TAB$、IND$等字典基表则成为METADATA LINK
  2. _CURRENT_EDITION_OBJ用作current container的OBJ视图
  3. _NEXT_OBJECT 和 _default_auditing_options_ 是2个特殊的对象

 

SQL> col owner for a20
SQL> col table_name for a30
SQL> l
  1* select CON_ID,OWNER,TABLE_NAME from cdb_tables where table_name='TAB$'
SQL> /

    CON_ID OWNER                TABLE_NAME
---------- -------------------- ------------------------------
         1 SYS                  TAB$
         4 SYS                  TAB$
         2 SYS                  TAB$

  1* select con_id,OWNER,SEGMENT_NAME,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BLOCKS from cdb_segments where segment_name='C_OBJ#'
SQL> /

    CON_ID OWNER                SEGMENT_NAME         TABLESPACE_NAME      HEADER_FILE HEADER_BLOCK     BLOCKS
---------- -------------------- -------------------- -------------------- ----------- ------------ ----------
         2 SYS                  C_OBJ#               SYSTEM                         5          144       1536
         4 SYS                  C_OBJ#               SYSTEM                        13          144       1536
         1 SYS                  C_OBJ#               SYSTEM                         1          144       1536

SQL> select name from v$datafile where file# in ( 5,1,13);

NAME
---------------------------------------------------------------
/u01/app/oracle/oradata/MAC/datafile/o1_mf_system_8y2rbkgw_.dbf
/u01/app/oracle/oradata/MAC/datafile/o1_mf_system_8y2rfs2q_.dbf
/u01/app/oracle/oradata/MAC/E173A736A1463DE1E0430100007F649A/datafile/o1_mf_system_8y4jfqb4_.dbf

SQL> alter session set container=macp3;

Session altered.

10046 TRACE

select count(*) from obj$
WAIT #139859066258680: nam='SQL*Net message to client' ela= 17 driver id=1650815232 #bytes=1 p3=0 obj#=442 tim=4096211739
WAIT #139859066258680: nam='db file sequential read' ela= 35 file#=13 block#=336 blocks=1 obj#=36 tim=4096211908
WAIT #139859066258680: nam='db file scattered read' ela= 8700 file#=13 block#=337 blocks=7 obj#=36 tim=4096220804
WAIT #139859066258680: nam='db file scattered read' ela= 3720 file#=13 block#=3088 blocks=8 obj#=36 tim=4096225083
WAIT #139859066258680: nam='db file scattered read' ela= 9735 file#=13 block#=4336 blocks=8 obj#=36 tim=4096235498
WAIT #139859066258680: nam='db file scattered read' ela= 5368 file#=13 block#=6424 blocks=8 obj#=36 tim=4096241580
WAIT #139859066258680: nam='db file scattered read' ela= 10721 file#=13 block#=10800 blocks=8 obj#=36 tim=4096253219
WAIT #139859066258680: nam='db file scattered read' ela= 11036 file#=13 block#=12952 blocks=8 obj#=36 tim=4096265197
WAIT #139859066258680: nam='db file scattered read' ela= 5056 file#=13 block#=16920 blocks=8 obj#=36 tim=4096270912
WAIT #139859066258680: nam='db file scattered read' ela= 7946 file#=13 block#=19216 blocks=8 obj#=36 tim=4096279469
WAIT #139859066258680: nam='db file scattered read' ela= 229 file#=13 block#=19288 blocks=8 obj#=36 tim=4096280915
WAIT #139859066258680: nam='db file scattered read' ela= 31 file#=13 block#=19344 blocks=8 obj#=36 tim=4096281765
WAIT #139859066258680: nam='db file scattered read' ela= 376 file#=13 block#=19400 blocks=8 obj#=36 tim=4096282653
WAIT #139859066258680: nam='db file scattered read' ela= 428 file#=13 block#=19440 blocks=8 obj#=36 tim=4096283541
WAIT #139859066258680: nam='db file scattered read' ela= 5414 file#=13 block#=19880 blocks=8 obj#=36 tim=4096290068
WAIT #139859066258680: nam='db file scattered read' ela= 1507 file#=13 block#=19920 blocks=8 obj#=36 tim=4096292248
WAIT #139859066258680: nam='db file scattered read' ela= 2551 file#=13 block#=19960 blocks=8 obj#=36 tim=4096295316
WAIT #139859066258680: nam='db file scattered read' ela= 9331 file#=13 block#=20400 blocks=8 obj#=36 tim=4096305568
WAIT #139859066258680: nam='db file scattered read' ela= 15208 file#=13 block#=20608 blocks=69 obj#=36 tim=4096321520
WAIT #139859066258680: nam='db file scattered read' ela= 3599 file#=13 block#=20677 blocks=59 obj#=36 tim=4096329323
WAIT #139859066258680: nam='db file scattered read' ela= 12489 file#=13 block#=23552 blocks=65 obj#=36 tim=4096346301
FETCH #139859066258680:c=26996,e=136587,p=321,cr=327,cu=0,mis=0,r=1,dep=0,og=1,plh=3951003077,tim=4096348379

select count(*) from tab$
END OF STMT
PARSE #139859066258680:c=1998,e=23437,p=1,cr=1,cu=0,mis=1,r=0,dep=0,og=1,plh=3205310162,tim=4168864808
EXEC #139859066258680:c=0,e=45,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3205310162,tim=4168864929
WAIT #139859066258680: nam='SQL*Net message to client' ela= 13 driver id=1650815232 #bytes=1 p3=0 obj#=36 tim=4168864966
WAIT #139859066258680: nam='db file sequential read' ela= 23 file#=13 block#=144 blocks=1 obj#=4 tim=4168865101
WAIT #139859066258680: nam='direct path read' ela= 226 file number=13 first dba=145 block cnt=23 obj#=4 tim=4168865805
WAIT #139859066258680: nam='direct path read' ela= 61 file number=13 first dba=1408 block cnt=8 obj#=4 tim=4168865960
WAIT #139859066258680: nam='direct path read' ela= 55 file number=13 first dba=1512 block cnt=8 obj#=4 tim=4168866508
WAIT #139859066258680: nam='direct path read' ela= 26 file number=13 first dba=1920 block cnt=8 obj#=4 tim=4168866618
WAIT #139859066258680: nam='direct path read' ela= 28 file number=13 first dba=2040 block cnt=8 obj#=4 tim=4168866696
WAIT #139859066258680: nam='direct path read' ela= 24 file number=13 first dba=2064 block cnt=8 obj#=4 tim=4168866763
WAIT #139859066258680: nam='direct path read' ela= 26 file number=13 first dba=2088 block cnt=8 obj#=4 tim=4168866835
WAIT #139859066258680: nam='direct path read' ela= 165 file number=13 first dba=6016 block cnt=64 obj#=4 tim=4168871396
WAIT #139859066258680: nam='direct path read' ela= 159 file number=13 first dba=6080 block cnt=64 obj#=4 tim=4168871744
WAIT #139859066258680: nam='direct path read' ela= 136 file number=13 first dba=9344 block cnt=64 obj#=4 tim=4168872071
WAIT #139859066258680: nam='direct path read' ela= 77 file number=13 first dba=9408 block cnt=64 obj#=4 tim=4168872307
WAIT #139859066258680: nam='direct path read' ela= 184 file number=13 first dba=11520 block cnt=64 obj#=4 tim=4168872622
WAIT #139859066258680: nam='direct path read' ela= 105 file number=13 first dba=11584 block cnt=64 obj#=4 tim=4168872864
WAIT #139859066258680: nam='direct path read' ela= 137 file number=13 first dba=13056 block cnt=64 obj#=4 tim=4168873160
WAIT #139859066258680: nam='direct path read' ela= 640 file number=13 first dba=13120 block cnt=64 obj#=4 tim=4168873926
WAIT #139859066258680: nam='direct path read' ela= 174 file number=13 first dba=17536 block cnt=64 obj#=4 tim=4168874284
WAIT #139859066258680: nam='direct path read' ela= 161 file number=13 first dba=17600 block cnt=64 obj#=4 tim=4168874611
WAIT #139859066258680: nam='direct path read' ela= 138 file number=13 first dba=26240 block cnt=64 obj#=4 tim=4168874970
WAIT #139859066258680: nam='direct path read' ela= 450 file number=13 first dba=26304 block cnt=64 obj#=4 tim=4168875558
WAIT #139859066258680: nam='direct path read' ela= 195 file number=13 first dba=29696 block cnt=64 obj#=4 tim=4168876097
WAIT #139859066258680: nam='direct path read' ela= 156 file number=13 first dba=29760 block cnt=64 obj#=4 tim=4168876425
WAIT #139859066258680: nam='direct path read' ela= 11356 file number=13 first dba=30848 block cnt=35 obj#=4 tim=4168888004

SQL> select SHARING,count(*) from dba_objects group by SHARING;

SHARING         COUNT(*)
------------- ----------
METADATA LINK      84206
NONE                6424
OBJECT LINK          147

可以看到PDB中大部分的对象其实是METADATA LINK 和OBJECT LINK

  1* select object_name,object_type,SHARING from dba_objects where object_name in ('C_OBJ#','OBJ$','USER$','SEG$')
SQL> /

OBJECT_NAME          OBJECT_TYPE             SHARING
-------------------- ----------------------- -------------
C_OBJ#               CLUSTER                 NONE
OBJ$                 TABLE                   METADATA LINK
USER$                TABLE                   METADATA LINK
SEG$                 TABLE                   METADATA LINK

C_OBJ#  的SHARING为NONE,意味着本质上 PDB还是有自己独立的数据字典的。

SHARING	VARCHAR2(13)	 	Values:
METADATA LINK - If the object is metadata-linked or a metadata link to an object in the root
OBJECT LINK - If the object is object-linked or an object link to an object in the root
NONE - If none of the above applies

可以看到 PDB 中DBA_OBJECTS的数据来主要源于sys."_CURRENT_EDITION_OBJ" o, sys.user$ u

"select u.name, o.name, o.subname, o.obj#, o.dataobj#,
       decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, NVL((SELECT 'REWRITE EQUIVALENCE'
                               FROM sum$ s
                               WHERE s.obj#=o.obj#
                                     and bitand(s.xpflags, 8388608) = 8388608),
                              'MATERIALIZED VIEW'),
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'EDITION', 59, 'RULE',
                      60, 'CAPTURE', 61, 'APPLY',
                      62, 'EVALUATION CONTEXT',
                      66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
                      72, 'SCHEDULER GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
                      81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY',
                      90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE',
                      94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
                      100, 'FILE WATCHER', 101, 'DESTINATION',
                      114, 'SQL TRANSLATION PROFILE',
                      115, 'UNIFIED AUDIT POLICY',
                     'UNDEFINED'),
       o.ctime, o.mtime,
       to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
       decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
       decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
       decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
       decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
       o.namespace,
       o.defining_edition,
       decode(bitand(o.flags, 196608),
              65536, 'METADATA LINK', 131072, 'OBJECT LINK', 'NONE'),
       case when o.type# in (4,5,7,8,9,11,12,13,14,22,87,114) then
           decode(bitand(o.flags, 1048576), 0, 'Y', 1048576, 'N', 'Y')
         else null end,
       decode(bitand(o.flags, 4194304), 4194304, 'Y', 'N')
from sys."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.user#
  and o.linkname is null
  and o.type# !=  10 /* NON-EXISTENT */
  and o.name != '_NEXT_OBJECT'
  and o.name != '_default_auditing_options_'
  and bitand(o.flags, 128) = 0
union all
select u.name, l.name, NULL, to_number(null), to_number(null),
       'DATABASE LINK',
       l.ctime, to_date(null), NULL, 'VALID','N','N', 'N', NULL, NULL,
       'NONE', NULL, 'N'
from sys.link$ l, sys.user$ u
where l.owner# = u.user#"

SQL> desc sys."_CURRENT_EDITION_OBJ"
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#                                      NOT NULL NUMBER
 DATAOBJ#                                           NUMBER
 DEFINING_OWNER#                           NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(128)
 NAMESPACE                                 NOT NULL NUMBER
 SUBNAME                                            VARCHAR2(128)
 TYPE#                                     NOT NULL NUMBER
 CTIME                                     NOT NULL DATE
 MTIME                                     NOT NULL DATE
 STIME                                     NOT NULL DATE
 STATUS                                    NOT NULL NUMBER
 REMOTEOWNER                                        VARCHAR2(128)
 LINKNAME                                           VARCHAR2(128)
 FLAGS                                              NUMBER
 OID$                                               RAW(16)
 SPARE1                                             NUMBER
 SPARE2                                             NUMBER
 SPARE3                                             NUMBER
 SPARE4                                             VARCHAR2(1000)
 SPARE5                                             VARCHAR2(1000)
 SPARE6                                             DATE
 SIGNATURE                                          RAW(16)
 SPARE7                                             NUMBER
 SPARE8                                             NUMBER
 SPARE9                                             NUMBER
 OWNER#                                             NUMBER
 DEFINING_EDITION                                   VARCHAR2(128)

SQL> 
SQL> select name from obj$ where name like '%CURRENT%EDITION%';

NAME
--------------------------------------------------------------------------------
_CURRENT_EDITION_OBJ

_CURRENT_EDITION_OBJ 的定义如下

create or replace view "_CURRENT_EDITION_OBJ"
 (    obj#,
      dataobj#,
      defining_owner#,
      name,
      namespace,
      subname,
      type#,
      ctime,
      mtime,
      stime,
      status,
      remoteowner,
      linkname,
      flags,
      oid$,
      spare1,
      spare2,
      spare3,
      spare4,
      spare5,
      spare6,
      owner#,
      defining_edition
 )
as
select o.obj#, o.dataobj#, o.owner#, o.name, o.namespace, o.subname,
       o.type#, o.ctime, o.mtime, o.stime, o.status, o.remoteowner, o.linkname,
       o.flags, o.oid$, o.spare1, o.spare2, o.spare3, o.spare5, o.spare5,
       o.spare6, o.owner#, NULL
from obj$ o
/

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump library_cache 11;
Statement processed.

ucket: #=120119 Mutex=0x8c5bd0d0(206158430208, 3, 0, 6)
  LibraryHandle:  Address=0x75221240 Hash=4461d537 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=MACP3.SYS.P_TEST
      FullHashValue=797823f3f7ec6f4726e5ce5a4461d537 Namespace=TABLE/PROCEDURE(01) Type=PROCEDURE(07) ContainerId=4 ContainerUid=15133
68950 Identifier=91611 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=1 TotalPinCount=2
    Counters:  BrokenCount=3 RevocablePointer=2 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x752212f0(0, 0, 0, 0) Mutex=0x75221388(48, 17, 0, 6)
    Flags=TIM/[00002801]
    WaitersLists:
      Lock=0x752212d0[0x752212d0,0x752212d0]
      Pin=0x752212b0[0x752212b0,0x752212b0]
      LoadLock=0x75221328[0x75221328,0x75221328]
    Timestamp:  Current=07-21-2013 01:08:33
    HandleReference:  Address=0x75221408 Handle=0x7514b8a8 Flags=OWN[200]
    LibraryObject:  Address=0x775f87e0 HeapMask=0000-001d-0001-0000 Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=NST[0001]
      DataBlocks:
        Block:  #='0' name=KGLH0^4461d537 pins=0 Change=NONE
          Heap=0x747dcef0 Pointer=0x775f88a8 Extent=0x775f8750 Flags=I/-/-/A/-/-
          FreedLocation=0 Alloc=1.617188 Size=3.976562 LoadTime=4295713190
        Block:  #='1' name= pins=0 Change=NONE
		   Heap=0x775f8c10 Pointer=(nil) Extent=(nil) Flags=-/-/-/-/-/-
          FreedLocation=4 Alloc=0.000000 Size=0.000000 LoadTime=0
        Block:  #='2' name=PLDIA^4461d537 pins=0 Change=NONE
          Heap=0x775f8c98 Pointer=0x7435d0c0 Extent=0x7435d030 Flags=I/-/-/A/-/-
          FreedLocation=0 Alloc=2.945312 Size=4.000000 LoadTime=0
        Block:  #='4' name=PLMCD^4461d537 pins=0 Change=NONE
          Heap=0x775f8d20 Pointer=0x73be80c0 Extent=0x73be8030 Flags=I/-/-/A/-/-
          FreedLocation=0 Alloc=0.929688 Size=4.000000 LoadTime=4295713200
        Block:  #='7' name= pins=0 Change=NONE
          Heap=0x775f8b88 Pointer=(nil) Extent=(nil) Flags=-/-/-/-/-/-
          FreedLocation=4 Alloc=0.000000 Size=0.000000 LoadTime=0
        Block:  #='13' name= pins=0 Change=NONE
          Heap=0x775f8da8 Pointer=(nil) Extent=(nil) Flags=-/-/-/-/-/-
          FreedLocation=4 Alloc=0.000000 Size=0.000000 LoadTime=0

Bucket: #=2480 Mutex=0x8ca8a440(214748364800, 91, 0, 6)
  LibraryHandle:  Address=0x74470858 Hash=9b609b0 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=CDB$ROOT.SYS._CURRENT_EDITION_OBJ
      FullHashValue=5a511b0783bb8e12f23e418309b609b0 Namespace=TABLE/PROCEDURE(01) Type=VIEW(04) ContainerId=1 ContainerUid=1 Identifi
er=3760 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=4 ActiveLocks=0 TotalLockCount=28 TotalPinCount=28
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=40 HandleInUse=40 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x74470908(0, 49, 0, 0) Mutex=0x744709a0(50, 465, 0, 6)
    Flags=PIN/TIM/[00002801]
    WaitersLists:
      Lock=0x744708e8[0x744708e8,0x744708e8]
      Pin=0x744708c8[0x744708c8,0x744708c8]
      LoadLock=0x74470940[0x74470940,0x74470940]
    Timestamp:  Current=05-24-2013 11:53:03
    HandleReference:  Address=0x74470a30 Handle=0x8aecd138 Flags=OWN[200]

Bucket: #=13882 Mutex=0x8caf99d0(214748364800, 23, 0, 6)
  LibraryHandle:  Address=0x746610b0 Hash=e9a2363a LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=CDB$ROOT.SYS.ALL_OBJECTS
      FullHashValue=55e63fcf740ca4bde5b35b4ee9a2363a Namespace=TABLE/PROCEDURE(01) Type=VIEW(04) ContainerId=1 ContainerUid=1 Identifi
er=3884 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=1 HandleInUse=1 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x74661160(0, 3, 0, 0) Mutex=0x746611f8(50, 25, 0, 6)
    Flags=PIN/TIM/[00002801]
    WaitersLists:
      Lock=0x74661140[0x74661140,0x74661140]
      Pin=0x74661120[0x74661120,0x74661120]
      LoadLock=0x74661198[0x74661198,0x74661198]
    Timestamp:  Current=05-24-2013 11:53:06
    HandleReference:  Address=0x74661280 Handle=0x8aecd138 Flags=OWN[200]
    ReferenceList:
      Reference:  Address=0x75f81970 Handle=0x772ea120 Flags=DEP[01]
       Timestamp=05-24-2013 11:53:06 InvalidatedFrom=0
      Reference:  Address=0x74e2e398 Handle=0x74ce7a88 Flags=DEP[01]
        Timestamp=05-24-2013 11:53:06 InvalidatedFrom=0
    LibraryObject:  Address=0x75f7a190 HeapMask=0000-0101-0101-0000 Flags=EXS/LOC[0004] Flags2=/COM[0400] PublicFlags=[0000]
      Dependencies:  count='15' size='16' table='0x75f7b050'
        Dependency:  num='0'
          Reference=0x75f7a4f8 Position=0 Flags=DEP[0001]
          Handle=0x72a62808 Type=TABLE(02) Parent=CDB$ROOT.SYS.USER$
        Dependency:  num='1'
          Reference=0x75f7a560 Position=0 Flags=DEP[0001]
          Handle=0x74470858 Type=VIEW(04) Parent=CDB$ROOT.SYS._CURRENT_EDITION_OBJ
        Dependency:  num='2'
          Reference=0x75f7a5c8 Position=0 Flags=DEP[0001]
          Handle=0x8af92d38 Type=TABLE(02) Parent=CDB$ROOT.SYS.SUM$
        Dependency:  num='3'
          Reference=0x75f7a630 Position=0 Flags=DEP[0001]
          Handle=0x72a700a8 Type=TABLE(02) Parent=CDB$ROOT.SYS.IND$
        Dependency:  num='4'

 

在Oracle 12c之前 一个数据库实例只能与一个单独的数据库相对应。而在RAC环境中,多个实例可以对应一个单独的数据库。

在Oracle Database 12c 中,一个实例可以与一个CDB container Database 相对应。

 

 

环境描述: 12.1.0.1 单机 DB_NAME=MAC有2个Pluggable Database MACP1和 MACP2

oracle@localhost:~$ ps -ef|grep MAC
oracle    4491     1  0 10:21 ?        00:00:00 ora_pmon_MAC
oracle    4495     1  0 10:21 ?        00:00:00 ora_psp0_MAC
oracle    4499     1  8 10:21 ?        00:00:07 ora_vktm_MAC
oracle    4505     1  0 10:21 ?        00:00:00 ora_gen0_MAC
oracle    4509     1  0 10:21 ?        00:00:00 ora_mman_MAC
oracle    4517     1  0 10:21 ?        00:00:00 ora_diag_MAC
oracle    4521     1  0 10:21 ?        00:00:00 ora_dbrm_MAC
oracle    4525     1  0 10:21 ?        00:00:00 ora_dia0_MAC
oracle    4529     1  0 10:21 ?        00:00:00 ora_dbw0_MAC
oracle    4533     1  0 10:21 ?        00:00:00 ora_lgwr_MAC
oracle    4537     1  0 10:21 ?        00:00:00 ora_ckpt_MAC
oracle    4541     1  0 10:21 ?        00:00:00 ora_smon_MAC
oracle    4545     1  0 10:21 ?        00:00:00 ora_reco_MAC
oracle    4549     1  0 10:21 ?        00:00:00 ora_lreg_MAC
oracle    4553     1  1 10:21 ?        00:00:01 ora_mmon_MAC
oracle    4557     1  0 10:21 ?        00:00:00 ora_mmnl_MAC
oracle    4561     1  0 10:21 ?        00:00:00 ora_d000_MAC
oracle    4565     1  0 10:21 ?        00:00:00 ora_s000_MAC
oracle    4591     1  0 10:21 ?        00:00:00 ora_tmon_MAC
oracle    4595     1  0 10:21 ?        00:00:00 ora_tt00_MAC
oracle    4599     1  0 10:21 ?        00:00:00 ora_smco_MAC
oracle    4603     1  0 10:21 ?        00:00:00 ora_w000_MAC
oracle    4607     1  0 10:21 ?        00:00:00 ora_aqpc_MAC
oracle    4616     1  3 10:21 ?        00:00:03 ora_p000_MAC
oracle    4620     1  5 10:21 ?        00:00:03 ora_p001_MAC
oracle    4624     1  1 10:21 ?        00:00:01 ora_p002_MAC
oracle    4628     1  0 10:21 ?        00:00:00 ora_p003_MAC
oracle    4684     1  0 10:21 ?        00:00:00 ora_qm02_MAC
oracle    4692     1  0 10:21 ?        00:00:00 ora_q002_MAC
oracle    4696     1  0 10:21 ?        00:00:00 ora_q003_MAC
oracle    4704     1  2 10:21 ?        00:00:01 ora_cjq0_MAC
oracle    4713     1  0 10:21 ?        00:00:00 ora_vkrm_MAC
oracle    4721     1  0 10:21 ?        00:00:00 ora_p004_MAC
oracle    4725     1  0 10:21 ?        00:00:00 ora_p005_MAC
oracle    4760     1  0 10:22 ?        00:00:00 ora_p006_MAC
oracle    4764     1  0 10:22 ?        00:00:00 ora_p007_MAC
oracle    4768     1  0 10:22 ?        00:00:00 ora_p008_MAC
oracle    4772     1  2 10:22 ?        00:00:00 ora_j000_MAC
oracle    4780     1  1 10:22 ?        00:00:00 ora_j002_MAC
oracle    4784     1  0 10:22 ?        00:00:00 ora_j003_MAC
oracle    4788     1  2 10:22 ?        00:00:00 ora_j001_MAC
oracle    4792     1  0 10:22 ?        00:00:00 ora_j004_MAC
oracle    4796     1  0 10:22 ?        00:00:00 ora_j005_MAC
oracle    4800     1  2 10:22 ?        00:00:00 ora_j006_MAC
oracle    4805     1  1 10:22 ?        00:00:00 ora_j007_MAC
oracle    4809     1  8 10:22 ?        00:00:01 ora_j008_MAC
oracle    4813     1  0 10:22 ?        00:00:00 ora_j009_MAC
oracle    4817     1  0 10:22 ?        00:00:00 ora_j010_MAC
oracle    4821     1  1 10:22 ?        00:00:00 ora_j011_MAC
oracle    4825     1  1 10:22 ?        00:00:00 ora_j012_MAC
oracle    4829     1  0 10:22 ?        00:00:00 ora_j013_MAC

oracle@localhost:~$ export ORACLE_SID=MAC
oracle@localhost:~$ sqlplus  / as sysdba 

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 13 10:24:04 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

// 首先检查该DB是否为CDB  Container Database

SQL> select name, cdb, con_id from v$database;

NAME      CDB     CON_ID
--------- --- ----------
MAC       YES          0

并检查实例名:

SQL> select INSTANCE_NAME, STATUS, CON_ID from v$instance;

INSTANCE_NAME    STATUS           CON_ID
---------------- ------------ ----------
MAC              OPEN                  0

使用V$PDBS检查实例相关的Pluggable Databases

select con_id,dbid,name,open_mode,total_size from v$PDBS;
    CON_ID       DBID NAME                           OPEN_MODE  TOTAL_SIZE
---------- ---------- ------------------------------ ---------- ----------
         2 4062078151 PDB$SEED                       READ ONLY   283115520
         3 1965483069 MACP1                          READ WRITE  288358400
         4 1550789943 MACP2                          MOUNTED             0

V$PDBS的数据来源于X$CON内部视图

SELECT inst_id,
       con_id,
       dbid,
       con_uid,
       guid,
       name,
       DECODE (state,
               0, 'MOUNTED',
               1, 'READ WRITE',
               2, 'READ ONLY',
               3, 'MIGRATE'),
       DECODE (restricted,  0, 'NO',  1, 'YES'),
       stime,
       create_scn,
       total_size
  FROM x$con
 WHERE con_id > 1

而X$CON的数据应当主要来源是控制文件CONTROLFILE中的PLUGGABLE DATABASE RECORDS部分:

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump controlf 3;
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/mac/MAC/trace/MAC_ora_4900.trc

***************************************************************************
PLUGGABLE DATABASE RECORDS
***************************************************************************
 (size = 684, compat size = 684, section max = 10, section in-use = 4,
  last-recid= 13, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 540, numrecs = 10)
Pluggable DataBase record=1
  id=1
  dbid=797473714
  name=CDB$ROOT
  first datafile link=1
  pdbinc=0, pdbrdi=0, status=0x00000000, flags=0x00000000
  incrcv scn scn: 0x0000.00000000, clnscn scn: 0x0000.00000000, crescn scn: 0x0000.00000000
  dbrls scn: 0x0000.00000000, dbrlc=0
  iscn scn: 0x0000.00000000, itime=0
  bscn scn: 0x0000.00000000, btime=0
  escn scn: 0x0000.00000000, etime=0
Pluggable DataBase record=2
  id=2
  dbid=4062078151
  name=PDB$SEED
  first datafile link=7
  pdbinc=0, pdbrdi=0, status=0x00000001, flags=0x00000000
  incrcv scn scn: 0x0000.00000000, clnscn scn: 0x0000.001a80a5, crescn scn: 0x0000.001a41be
  dbrls scn: 0x0000.00000000, dbrlc=0
  iscn scn: 0x0000.00000000, itime=0
  bscn scn: 0x0000.00000000, btime=0
  escn scn: 0x0000.00000000, etime=0
Pluggable DataBase record=3
  id=3
  dbid=1965483069
  name=MACP1
  first datafile link=10
  pdbinc=0, pdbrdi=0, status=0x00000000, flags=0x00000000
  incrcv scn scn: 0x0000.00000000, clnscn scn: 0x0000.00000000, crescn scn: 0x0000.001a849c
  dbrls scn: 0x0000.00000000, dbrlc=0
  iscn scn: 0x0000.00000000, itime=0
  bscn scn: 0x0000.00000000, btime=0
  escn scn: 0x0000.00000000, etime=0
Pluggable DataBase record=4
  id=4
  dbid=1550789943
  name=MACP2
  first datafile link=13
  pdbinc=0, pdbrdi=0, status=0x00000001, flags=0x00000000
  incrcv scn scn: 0x0000.00000000, clnscn scn: 0x0000.001b04f4, crescn scn: 0x0000.001a897c
  dbrls scn: 0x0000.00000000, dbrlc=0
  iscn scn: 0x0000.00000000, itime=0
  bscn scn: 0x0000.00000000, btime=0
  escn scn: 0x0000.00000000, etime=0

DATA FILE #5:
  name #9: /u01/app/oracle/oradata/MAC/pdbseed/system01.dbf
creation size=32000 block size=8192 status=0x80 flg=0x5 head=9 tail=9 dup=1
 pdb_id 2, tablespace 0, index=6 krfil=1 prev_file_in_ts=0 prev_file_in_pdb=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:9 scn: 0x0000.001a80a5 06/30/2013 14:27:50
 Stop scn: 0x0000.001a80a5 06/30/2013 14:27:50
 Creation Checkpointed at scn:  0x0000.001a41be 06/30/2013 14:23:04
 thread:1 rba:(0x1.1eb.10)
  Hot Backup end marker scn: 0x0000.00000000
 aux_file is NOT DEFINED
 Plugged readony: NO
 Plugin scnscn: 0x0000.00000000
 Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
 Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
 Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
 Online move state: 0

DATA FILE #8:
  name #12: /u01/app/oracle/oradata/MAC/MACP1/system01.dbf
creation size=32000 block size=8192 status=0xe flg=0x1 head=12 tail=12 dup=1
 pdb_id 3, tablespace 0, index=9 krfil=1 prev_file_in_ts=0 prev_file_in_pdb=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:9 scn: 0x0000.001ed479 07/13/2013 10:22:14
 Stop scn: 0xffff.ffffffff 07/01/2013 03:41:49
 Creation Checkpointed at scn:  0x0000.001a849c 06/30/2013 14:28:40
 thread:1 rba:(0x6.2.10)
  Hot Backup end marker scn: 0x0000.00000000
 aux_file is NOT DEFINED
 Plugged readony: NO
 Plugin scnscn: 0x0000.00000000
 Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
 Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
 Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
 Online move state: 0

 透过对控制文件信息的探索可以发现 多出了以下几个属性:

1.	Plugged readony
2.	Plugin scnscn
3.	Plugin resetlogs scn/timescn
4.	Foreign creation scn/timescn
5.	Foreign checkpoint scn/timescn
6.	Online move state

SQL>  select TYPE FROM V$CONTROLFILE_RECORD_SECTION where type like '%PDB%';

TYPE
----------------------------
PDB RECORD
PDBINC RECORD

下面的测试可以证明V$PDBS的数据来源于控制文件,但是 TOTAL_SIZE需要OPEN PLUGGABLE DB后才能获得

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2293880 bytes
Variable Size             562040712 bytes
Database Buffers          268435456 bytes
Redo Buffers                2334720 bytes
Database mounted.
SQL> select con_id,dbid,name,open_mode,total_size from v$PDBS;

    CON_ID       DBID NAME                           OPEN_MODE  TOTAL_SIZE
---------- ---------- ------------------------------ ---------- ----------
         2 4062078151 PDB$SEED                       MOUNTED             0
         3 1965483069 MACP1                          MOUNTED             0
         4 1550789943 MACP2                          MOUNTED             

SQL> alter pluggable database  MACP1 open;
alter pluggable database  MACP1 open
*
ERROR at line 1:
ORA-01109: database not open

SQL> alter database open;

Database altered.

SQL> select con_id,dbid,name,open_mode,total_size from v$PDBS;

    CON_ID       DBID NAME                           OPEN_MODE  TOTAL_SIZE
---------- ---------- ------------------------------ ---------- ----------
         2 4062078151 PDB$SEED                       READ ONLY   283115520
         3 1965483069 MACP1                          MOUNTED             0
         4 1550789943 MACP2                          MOUNTED             0

SQL> alter pluggable database  MACP1 open;

Pluggable database altered.

SQL> select con_id,dbid,name,open_mode,total_size from v$PDBS;

    CON_ID       DBID NAME                           OPEN_MODE  TOTAL_SIZE
---------- ---------- ------------------------------ ---------- ----------
         2 4062078151 PDB$SEED                       READ ONLY   283115520
         3 1965483069 MACP1                          READ WRITE  288358400
         4 1550789943 MACP2                          MOUNTED             0

在一个PDB MACP1打开的情况下我们去了解LISTENER 服务注册的情况:

oracle@localhost:~$ lsnrctl service

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 13-JUL-2013 10:40:20

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "MAC" has 1 instance(s).
  Instance "MAC", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "MACXDB" has 1 instance(s).
  Instance "MAC", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=43229))
Service "macp1" has 1 instance(s).
  Instance "MAC", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "macp2" has 1 instance(s).
  Instance "MAC", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

可以看到MACP1这个PLUGGABLE DATABASE OPEN的情况下是有INSTANCE READY可用的,尝试连接,首先连接CDB$ROOT

可以通过SQLPLUS的SHOW CON_ID或者CON_NAME来获得当前的CONTAINER信息

也可以通过sys_context('userenv','CON_NAME') 、sys_context('userenv','CON_ID')来获得

SQL> SELECT sys_context('userenv','CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

SQL> SELECT sys_context('userenv','CON_ID') from dual;

SYS_CONTEXT('USERENV','CON_ID')
--------------------------------------------------------------------------------
1

SQL> show CON_ID

CON_ID
------------------------------
1
SQL> show CON_NAME

CON_NAME
------------------------------
CDB$ROOT

oracle@localhost:~$ sqlplus  sys/oracle@localhost:1521/MAC as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 13 10:42:34 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SHOW CON_id

CON_ID
------------------------------
1

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select name from v$pdbs;

NAME
------------------------------
PDB$SEED
MACP1
MACP2

SQL> SELECT PROGRAM,CON_ID FROM V$SESSION
PROGRAM                                              CON_ID
------------------------------------------------ ----------
oracle@localhost.localdomain (PMON)                       0
oracle@localhost.localdomain (PSP0)                       0
oracle@localhost.localdomain (VKTM)                       0
oracle@localhost.localdomain (GEN0)                       0
oracle@localhost.localdomain (MMAN)                       0
oracle@localhost.localdomain (RECO)                       0
oracle@localhost.localdomain (DIAG)                       0
oracle@localhost.localdomain (DBRM)                       0
oracle@localhost.localdomain (DIA0)                       0
oracle@localhost.localdomain (DBW0)                       0
oracle@localhost.localdomain (LGWR)                       0
oracle@localhost.localdomain (CKPT)                       0
oracle@localhost.localdomain (SMON)                       0
oracle@localhost.localdomain (LREG)                       0
oracle@localhost.localdomain (MMON)                       0
oracle@localhost.localdomain (MMNL)                       0
oracle@localhost.localdomain (CJQ0)                       0
oracle@localhost.localdomain (TMON)                       0
oracle@localhost.localdomain (TT00)                       0
oracle@localhost.localdomain (SMCO)                       0
oracle@localhost.localdomain (AQPC)                       0
oracle@localhost.localdomain (W000)                       0
sqlplus@localhost.localdomain (TNS V1-V3)                 1
oracle@localhost.localdomain (QM02)                       0
oracle@localhost.localdomain (Q002)                       0
oracle@localhost.localdomain (Q003)                       0
oracle@localhost.localdomain (VKRM)                       0

27 rows selected.

SQL> select count(*) from tab$;

  COUNT(*)
----------
      2372

SQL> select count(*) from dba_tables;

  COUNT(*)
----------
      2325

SQL>select count(*) from cdb_tables;

  COUNT(*)
----------
      6957

ALTER SESSION 将当前容器设置为MACP1

SQL> alter session set container=MACP1;

Session altered.

SQL> show con_id

CON_ID
------------------------------
3

SQL> show con_name

CON_NAME
------------------------------
MACP1

SQL> select name from v$pdbs;

NAME
------------------------------
MACP1

SQL> select name from v$database;

NAME
---------
MAC

SQL> SELECT PROGRAM,CON_ID FROM V$SESSION where CON_ID!=0;

PROGRAM                                              CON_ID
------------------------------------------------ ----------
sqlplus@localhost.localdomain (TNS V1-V3)                 3

而此时若实际使用CDB$ROOT去查 V$SESSION则可以发现:

SQL>  SELECT PROGRAM,CON_ID FROM V$SESSION where CON_ID!=0;

PROGRAM                                              CON_ID
------------------------------------------------ ----------
sqlplus@localhost.localdomain (TNS V1-V3)                 3
sqlplus@localhost.localdomain (TNS V1-V3)                 1

可以看出CDB/Pluggable Database对于V$视图的设计是可以让用户察觉到这是一个Pluggable Database,但不能让用户了解到非本地的其他Pluggable Database的运行情况。

通过X$CON内部视图可以看到 ,PDB的这种V$视图的只能观察本Container信息的实现是通过X$视图底层实现的。

SQL> show con_name

CON_NAME
------------------------------
MACP1

SQL> select name from x$CON;

NAME
------------------------------
MACP1

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL>  select name from x$CON; 

NAME
------------------------------
CDB$ROOT
PDB$SEED
MACP1
MACP2

SQL>  select count(*) from tab$;

  COUNT(*)
----------
      2363

SQL> select count(*) from dba_tables;

  COUNT(*)
----------
      2316

SQL> select count(*) from cdb_tables;

  COUNT(*)
----------
      2316

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

oracle@localhost:~$ sqlplus  sys/oracle@localhost:1521/MACP1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 13 10:44:41 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------
MACP1
SQL> show con_id

CON_ID
------------------------------
3

oracle@localhost:~$ sqlplus  sys/oracle@localhost:1521/MACP2 as sysdba 

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 13 11:01:32 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_id

CON_ID
------------------------------
4
SQL> show con_name

CON_NAME
------------------------------
MACP2
SQL> select count(*) from tab;
select count(*) from tab
                     *
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only

oracle@localhost:~$ oerr ora 1219
01219, 00000, "database or pluggable database not open: queries allowed on fixed tables or views only"
// *Cause:  A query was issued against an object not recognized as a fixed
//          table or fixed view before the database or pluggable database has 
//          been opened.
// *Action: Re-phrase the query to include only fixed objects, or open the
//          database or pluggable database.

由于MACP2 这个Container由于尚未OPEN所以无法查表,遇到了ORA-1219错误

oracle@localhost:~$ sqlplus  / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 13 11:03:02 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name, con_id from v$services;

NAME                                                                 CON_ID
---------------------------------------------------------------- ----------
macp2                                                                     4
macp1                                                                     3
MACXDB                                                                    1
MAC                                                                       1
SYS$BACKGROUND                                                            1
SYS$USERS                                                                 1

6 rows selected.

视图cdb_pdbs也记录了PDB的信息

SQL> desc cdb_pdbs 
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PDB_ID                                    NOT NULL NUMBER
 PDB_NAME                                  NOT NULL VARCHAR2(128)
 DBID                                      NOT NULL NUMBER
 CON_UID                                   NOT NULL NUMBER
 GUID                                               RAW(16)
 STATUS                                             VARCHAR2(13)
 CREATION_SCN                                       NUMBER
 CON_ID                                             NUMBER

SQL> col PDB_NAME format a8
SQL> col CON_ID format 999999
SQL> select PDB_ID, PDB_NAME, DBID, GUID, CON_ID from cdb_pdbs;

    PDB_ID PDB_NAME       DBID GUID                              CON_ID
---------- -------- ---------- -------------------------------- -------
         3 MACP1    1965483069 E060EC0DEFDA2A4CE0430100007FADFA       1
         2 PDB$SEED 4062078151 E060D99341542648E0430100007F6B20       1
         4 MACP2    1550789943 E060EF86B0C22ABFE0430100007F6F3C       1

SQL> select text from dba_VIEWS where view_name='CDB_PDBS';

TEXT
--------------------------------------------------------------------------------
SELECT "PDB_ID","PDB_NAME","DBID","CON_UID","GUID","STATUS","CREATION_SCN","CON_
ID" FROM CDB$VIEW("SYS"."DBA_PDBS")

SQL> select text from dba_VIEWS where view_name='DBA_PDBS';

TEXT
--------------------------------------------------------------------------------
select c.con_id#, o.name, c.dbid, c.con_uid, o.oid$,
 decode(c.status, 0, 'UNUSABLE', 1, 'NEW', 2, 'NORMAL', 3, 'UNPLUGGED', 4, 'NEED
S SYNC',
                  5, 'NEEDS UPGRADE', 6, 'CONVERTING', 'UNDEFINED'),
c.create_scnwrp*power(2,32)+c.create_scnbas
 from sys.container$ c, sys.obj$ o
 where o.obj# = c.obj# and c.con_id# > 1

CDB_PDBS的数据来源于CDB$VIEW("SYS"."DBA_PDBS"),而DBA_PDBS的数据来源于字典对象container$, CONTAINER$的基表存放在 $ORACLE_HOME/rdbms/admin/dcore.bsq中

create table container$
(
 obj#            number not null,         /* Object number for the container */
 con_id#         number not null,                            /* container ID */
 dbid            number not null,                             /* database ID */
 con_uid         number not null,                               /* unique ID */
 status          number not null,                       /* active, plugged...*/
 create_scnwrp   number not null,                       /* creation scn wrap */
 create_scnbas   number not null,                       /* creation scn base */
 clnscnwrp       number,    /* clean offline scn - zero if not offline clean */
 clnscnbas       number,       /* clnscnbas - scn base, clnscnwrp - scn wrap */
 rdba            number not null,                 /*  r-dba of the container */
 flags           number,                                            /* flags */
 spare1          number,                                            /* spare */
 spare2          number,                                            /* spare */
 spare3          varchar2("M_IDEN"),                                /* spare */
 spare4          varchar2("M_IDEN")                                 /* spare */
)
/

CREATE UNIQUE INDEX i_container1 ON container$(obj#)
/

CREATE UNIQUE INDEX i_container2 ON container$(con_id#)
/

CREATE UNIQUE INDEX i_container3 ON container$(con_uid)
/
CREATE UNIQUE INDEX i_container3 ON container$(con_uid)
/

create table cdb_file$                    /* file table in a consolidated db */
(
 file#         number not null,                    /* file identifier number */
 con_id#       number not null,                              /* container ID */
 mtime         date,                        /* time it was created, modified */
 spare1        number,                                              /* spare */
 spare2        number,                                              /* spare */
 spare3        number,                                              /* spare */
 spare4        number,                                              /* spare */
 f_afn         number not null,              /* foreign absolute file number */
 f_dbid        number not null,                       /* foreign database id */
 f_cpswrp      number not null,                    /* foreign checkpoint scn */
 f_cpsbas      number not null,
 f_prlswrp     number not null,              /* foreign plugin resetlogs scn */
 f_prlsbas     number not null,
 f_prlstim     number not null              /* foreign plugin resetlogs time */
)
/

CREATE UNIQUE INDEX i_cdbfile1 ON cdb_file$(file#, con_id#)

REM to provide PDB lineage information.

create table pdb_history$
(
 name       varchar2("M_IDEN") not null,                  /* Name of the PDB */
 con_id#    number  not null,                                /* Container ID */
 dbid       number  not null,                                 /* DBID of PDB */
 guid       raw(16) not null,                                 /* GUID of PDB */
 scnbas     number  not null,             /* SCN base when operation occured */
 scnwrp     number  not null,             /* SCN wrap when operation occured */
 time       date    not null,                 /* time when operation occured */
 operation  varchar2(16)  not null,   /* CREATE, CLONE, UNPLUG, PLUG, RENAME */
 db_version number  not null,                            /* Database version */
 c_pdb_name varchar2("M_IDEN"),             /* Created, Cloned from PDB name */
 c_pdb_dbid number,                         /* Created, Cloned from PDB DBID */
 c_pdb_guid raw(16),                        /* Created, Cloned from PDB GUID */
 c_db_name  varchar2("M_IDEN_128"),            /* Created, Cloned in DB name */
 c_db_uname varchar2("M_IDEN"),         /* Created, Cloned in DB unique name */
 c_db_dbid  number,                               /* Created, Cloned in DBID */
 clonetag   varchar2(128),                                 /* Clone tag name */
 spare1     number,                                                 /* spare */
 spare2     number,                                                 /* spare */
 spare3     varchar2("M_IDEN"),                                     /* spare */
 spare4     varchar2("M_IDEN")                                      /* spare */
)
/

SQL> create table container_backup as select * from sys.container$;

Table created.

SQL> truncate table sys.container$;  

Table truncated.

SQL> select PDB_ID, PDB_NAME, DBID, GUID, CON_ID from cdb_pdbs;

no rows selected

虽然目前container$字典基表仍不算BOOTSTRAP对象,但是该基表的数据讹误仍导致无法OPEN CDB$ROOT

SQL> create table container_backup as select * from sys.container$;

Table created.

Insert into sys.container$ select * from container_backup;

SQL> truncate table sys.container$;  

Table truncated.

SQL> select PDB_ID, PDB_NAME, DBID, GUID, CON_ID from cdb_pdbs;

no rows selected

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2293880 bytes
Variable Size             562040712 bytes
Database Buffers          268435456 bytes
Redo Buffers                2334720 bytes
Database mounted.
SQL> show con_name      

CON_NAME
------------------------------
CDB$ROOT
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kpdbLoadCbk-bad-obj#], [185], [],
[], [], [], [], [], [], [], [], []
Process ID: 6250
Session ID: 1 Serial number: 5

Recovery sets nab of thread 1 seq 33 to 88 with 8 zeroblks
Count of ofsmtab$: 0 entries
 07/13/2013 11:16:12 07/13/2013 11:16:122013-07-13 11:16:12.705: [  OCRMSG]prom_waitconnect: CONN NOT ESTABLISHED (0,29,1,2)
2013-07-13 11:16:12.705: [  OCRMSG]GIPC error [29] msg [gipcretConnectionRefused]
2013-07-13 11:16:12.705: [  OCRMSG]prom_connect: error while waiting for connection complete [24]
2013-07-13 11:16:12.706: [  OCRMSG]prom_waitconnect: CONN NOT ESTABLISHED (0,29,1,2)
2013-07-13 11:16:12.706: [  OCRMSG]GIPC error [29] msg [gipcretConnectionRefused]
2013-07-13 11:16:12.706: [  OCRMSG]prom_connect: error while waiting for connection complete [24]
2013-07-13 11:16:12.708: [  OCRMSG]prom_waitconnect: CONN NOT ESTABLISHED (0,29,1,2)
2013-07-13 11:16:12.708: [  OCRMSG]GIPC error [29] msg [gipcretConnectionRefused]
2013-07-13 11:16:12.708: [  OCRMSG]prom_connect: error while waiting for connection complete [24]
2013-07-13 11:16:12.709: [  OCRMSG]prom_waitconnect: CONN NOT ESTABLISHED (0,29,1,2)
2013-07-13 11:16:12.709: [  OCRMSG]GIPC error [29] msg [gipcretConnectionRefused]
2013-07-13 11:16:12.709: [  OCRMSG]prom_connect: error while waiting for connection complete [24]

*** 2013-07-13 11:16:12.969
Incident 31361 created, dump file: /u01/app/oracle/diag/rdbms/mac/MAC/incident/incdir_31361/MAC_ora_6250_i31361.trc
ORA-00600: internal error code, arguments: [kpdbLoadCbk-bad-obj#], [185], [], [], [], [], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [kpdbLoadCbk-bad-obj#], [185], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kpdbLoadCbk-bad-obj#], [185], [], [], [], [], [], [], [], [], [], []

Stack call:
kpdbLockPdbSwitch=> kglget=> kglLock=> kgllkal=> kglLoadOnLock=> kpdbLoadCbk=>报错

可以看到内核新增了一个MODULE名字叫 KPDB

DAY 2

了解log file , 

SQL> col member format A45
SQL> select GROUP#, MEMBER, CON_ID from v$logfile;

    GROUP# MEMBER                                            CON_ID
---------- --------------------------------------------- ----------
         3 /u01/app/oracle/oradata/MAC/onlinelog/o1_mf_3          0
           _8y2rf931_.log

         3 /u01/app/oracle/fast_recovery_area/MAC/online          0
           log/o1_mf_3_8y2rf958_.log

         2 /u01/app/oracle/oradata/MAC/onlinelog/o1_mf_2          0
           _8y2rf74s_.log

         2 /u01/app/oracle/fast_recovery_area/MAC/online          0
           log/o1_mf_2_8y2rf771_.log

    GROUP# MEMBER                                            CON_ID
---------- --------------------------------------------- ----------

         1 /u01/app/oracle/oradata/MAC/onlinelog/o1_mf_1          0
           _8y2rf5c9_.log

         1 /u01/app/oracle/fast_recovery_area/MAC/online          0
           log/o1_mf_1_8y2rf5hl_.log

6 rows selected.

了解Pluggable Database的创建

SQL> show parameter pluggable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_pluggable_database            boolean     TRUE

SQL>  CREATE PLUGGABLE DATABASE MACP3 ADMIN USER maclean identified by oracle;
 CREATE PLUGGABLE DATABASE MACP3 ADMIN USER maclean identified by oracle

oracle@localhost:~$ oerr ora 65016
65016, 00000, "FILE_NAME_CONVERT must be specified"
// *Cause:  Data files, and possibly other files, needed to be copied as a
//          part of creating a pluggable database.  However, Oracle Managed 
//          Files (OMF) was not enabled, PDB_FILE_NAME_CONVERT was not defined,
//          and there was a failure to specify the FILE_NAME_CONVERT clause.
// *Action: Enable OMF or define PDB_FILE_NAME_CONVERT system parameter before
//          issuing CREATE PLUGGABLE DATABASE statement, or specify 
//          FILE_NAME_CONVERT clause as a part of the statement.

如果未指定db_create_file_dest  参数则需要手动在CREATE PLUGGABLE DATABASE时指定FILE_NAME_CONVERT,例如:
CREATE PLUGGABLE DATABASE test_pdb ADMIN USER admin IDENTIFIED
BY admin
file_name_convert=('/private/oracle_base/oradata/cdb2/pdbseed/sy
stem01.dbf','/shared/pdb_files/<server_name>/test_pdb/system01.d
bf','/private/oracle_base/oradata/cdb2/pdbseed/sysaux01.dbf','/s
hared/pdb_files/<server_name>/test_pdb/sysaux01.dbf','/private/o
racle_base/oradata/cdb2/pdbseed/temp01.dbf','/shared/pdb_files/< server_name>/test_pdb/temp01.dbf')

这里我们使用db_create_file_dest  

SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata';

System altered.

SQL> alter session set events '10046 trace name context forever , level 1';

Session altered.

SQL> CREATE PLUGGABLE DATABASE MACP3 ADMIN USER maclean identified by oracle;

Pluggable database created.

该CREATE PLUGGABLE DATABASE  DDL语句主要涉及到的字典变更:

delete from pdb_alert$ where name=:1

insert into cdb_file$
  (file#,
   con_id#,
   mtime,
   f_afn,
   f_dbid,
   f_cpswrp,
   f_cpsbas,
   f_prlswrp,
   f_prlsbas,
   f_prlstim)
values
  (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)

update cdb_file$
   set con_id#   = :2,
       mtime     = :3,
       f_afn     = :4,
       f_dbid    = :5,
       f_cpswrp  = :6,
       f_cpsbas  = :7,
       f_prlswrp = :8,
       f_prlsbas = :9,
       f_prlstim = :10
 where file# = :1

insert into container$
  (obj#,
   con_id#,
   dbid,
   con_uid,
   status,
   create_scnwrp,
   create_scnbas,
   clnscnwrp,
   clnscnbas,
   rdba,
   flags)
values
  (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11)

insert into cdb_service$
  (name, network_name, name_hash, flags, con_id#)
values
  (:1, :2, :3, :4, :5)

update /*+ RULE */ service$
   set pdb = UPPER(:1)
 where bitand(flags, 128) != 128

insert into pdb_history$
  (name,
   con_id#,
   dbid,
   guid,
   scnbas,
   scnwrp,
   time,
   operation,
   db_version,
   c_pdb_name,
   c_pdb_dbid,
   c_pdb_guid,
   c_db_name,
   c_db_uname,
   c_db_dbid)
values
  (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15)

DBA_PDB_HISTORY视图依赖于pdb_history$字典基表:

SQL> col pdb_name for a20
SQL> col db_name for a20
SQL> select pdb_name,pdb_id,db_name from dba_pdb_history;

no rows selected

SQL> select pdb_name,pdb_id,db_name from cdb_pdb_history;

PDB_NAME                 PDB_ID DB_NAME
-------------------- ---------- --------------------
PDB$SEED                      2 SEEDDATA
PDB$SEED                      2 MAC

SQL> select con_id,dbid,name,open_mode,total_size from v$PDBS;

    CON_ID       DBID NAME                           OPEN_MODE  TOTAL_SIZE
---------- ---------- ------------------------------ ---------- ----------
         2 4063312629 PDB$SEED                       READ ONLY   283115520
         3  284039876 MACP                           MOUNTED             0
         4 1513368950 MACP3                          MOUNTED             0

SQL> alter pluggable database macp3 open;

Pluggable database altered.

SQL> select con_id,dbid,name,open_mode,total_size from v$PDBS;

    CON_ID       DBID NAME                           OPEN_MODE  TOTAL_SIZE
---------- ---------- ------------------------------ ---------- ----------
         2 4063312629 PDB$SEED                       READ ONLY   283115520
         3  284039876 MACP                           MOUNTED             0
         4 1513368950 MACP3                          READ WRITE  283115520

SQL> select username, common, con_id from cdb_users where username ='SYSTEM';

USERNAME                       COM     CON_ID
------------------------------ --- ----------
SYSTEM                         YES          1
SYSTEM                         YES          2
SYSTEM                         YES          4

SYSTEM是一个COMMON USER

SQL> select username, common, con_id from cdb_users  where con_id!=1 and common!='YES';

USERNAME                       COM     CON_ID
------------------------------ --- ----------
MACLEAN                        NO           4

而PDB MACP3的ADMIN USER  MACLEAN不是COMMON USER

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

在CDB$ROOT中默认创建的user/role是 COMMON的,USERNAME必须以C##开头,否则报错ORA-65096;

SQL> create user maclean identified by oracle;
create user maclean identified by oracle
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> create user maclean1 identified by oracle container=ALL;
create user maclean1 identified by oracle container=ALL
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> ! oerr ora 65096
65096, 00000, "invalid common user or role name"
// *Cause:  An attempt was made to create a common user or role with a name
//          that wass not valid for common users or roles.  In addition to 
//          the usual rules for user and role names, common user and role 
//          names must start with C## or c## and consist only of ASCII 
//          characters.
// *Action: Specify a valid common user or role name.
//

SQL> create user maclean1 identified by oracle container=CURRENT;
create user maclean1 identified by oracle container=CURRENT
                                   *
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

SQL> ! oerr ora 65049
65049, 00000, "creation of local user or role is not allowed in CDB$ROOT"
// *Cause:   An attempt was made to create a local user or role in CDB$ROOT.
// *Action:  If trying to create a common user or role, specify CONTAINER=ALL.
//

同时在CDB$ROOT 中不允许创建 LOCAL USER/ROLE

SQL> create user maclean1 identified by oracle container=CURRENT;

User created.

SQL> create user c##maclean1 identified by oracle container=CURRENT;
create user c##maclean1 identified by oracle container=CURRENT
            *
ERROR at line 1:
ORA-65094: invalid local user or role name

LOCAL USER/ROLE不能以 C##开头

SQL>  create user maclean1 identified by oracle container=ALL;
 create user maclean1 identified by oracle container=ALL
                                    *
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT

SQL> ! oerr ora 65050
65050, 00000, "Common DDLs only allowed in CDB$ROOT"
// *Cause:   An attempt was made to issue a Common DDL in a pluggable database.
// *Action:  Switch to CDB$ROOT and issue the Common DDL there.
//

Comments

  1. Pluggable Database 相比shema 隔离有带来哪些好处哪

  2. rainier says

    你确定应用程序不用改动?第一JDBC驱动版本兼容问题,10G的不能访问12C的数据库第二JDBC连接串的问题,访问PDB不能用IP:PORT:SERVICENAME这样的URL了

  3. hello985 says

    如果采用11g ,建立不同的schema,每个schema对应自己的表空间。这样实现的效果,是不是和12c 类似呢?

  4. 第一幅图只有两个CDB吧,每个CDB有两个PDB

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号