Oracle Database 12c中带来一种全新的架构，允许用户在一个独立的Oracle数据库中拥有多个pluggable可拔插的数据库。这种Pluggable 可拔插数据库的出现是为了对应 用户目前使用RDBMS数据库的现状，即有一些用户拥有大量的部门级应用构建于Oracle RDBMS数据库之上。
以下几个场景适合于使用pluggable database:
- 在产品系统中的某些应用实际仅使用十分少量的硬件资源。但是如果存在大量这样的应用，则还是需要构造大量的数据库实例并为这些小规模的数据库分配存储空间
- 对于那些并不十分复杂或重要，需要全职DBA花费大量时间管理的数据库
- 为了更好地利用硬件和DBA资源，用户有必要将大量的部门级应用整合到少数几个oracle RDBMS数据库中以便部署和管理
Pluggable Database 可拔插数据库允许DBA整合大量的小的部门级数据库到一个更庞大的数据库中。
Pluggable Database 带来的好处
在一个集中化的平台上操作多个数据库将有效降低成本：
- 更少的实例损耗
- 更低的存储成本
减少对DBA资源的使用，以及便于维护安全性：
- 无需应用修改
- 更快和简便的配置
- 节省了打patch和升级的时间
- 分离了以下责任：
- 不同应用的管理员
- 应用程序管理员和DBA
- 应用用户
- 提供isolation
- 保证与非CDB 完整的向后兼容性
- 完整的RAC操作使用
- 与Oracle Enterprise Manager和Resource Manager整合在一起
- 可以集中化管理多个数据库
- 备份和灾难恢复
- 补丁和升级
上图给出了一个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还将在自己的数据字典中维护部分数据。
关于CDB 数据字典实际情况的测试：
通过下面的实验我们可以得出如下结论：
- 每一个Container 维护自己的一套底层数据字典 数据实际存放在C_OBJ#(C_OBJ# 的SHARING为NONE，意味着本质上 PDB还是有自己独立的数据字典的。)这样的cluster中,而TAB$、IND$等字典基表则成为METADATA LINK
- _CURRENT_EDITION_OBJ用作current container的OBJ视图
- _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
------------------------------------------------ ----------
[email protected] (PMON) 0
[email protected] (PSP0) 0
[email protected] (VKTM) 0
[email protected] (GEN0) 0
[email protected] (MMAN) 0
[email protected] (RECO) 0
[email protected] (DIAG) 0
[email protected] (DBRM) 0
[email protected] (DIA0) 0
[email protected] (DBW0) 0
[email protected] (LGWR) 0
[email protected] (CKPT) 0
[email protected] (SMON) 0
[email protected] (LREG) 0
[email protected] (MMON) 0
[email protected] (MMNL) 0
[email protected] (CJQ0) 0
[email protected] (TMON) 0
[email protected] (TT00) 0
[email protected] (SMCO) 0
[email protected] (AQPC) 0
[email protected] (W000) 0
[email protected] (TNS V1-V3) 1
[email protected] (QM02) 0
[email protected] (Q002) 0
[email protected] (Q003) 0
[email protected] (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
------------------------------------------------ ----------
[email protected] (TNS V1-V3) 3
而此时若实际使用CDB$ROOT去查 V$SESSION则可以发现：
SQL> SELECT PROGRAM,CON_ID FROM V$SESSION where CON_ID!=0;
PROGRAM CON_ID
------------------------------------------------ ----------
[email protected] (TNS V1-V3) 3
[email protected] (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.
//
Leave a Reply to 美女总裁 Cancel reply