Oracle 12C引入了CDB与PDB的新特性,在ORACLE 12C数据库引入的多租用户环境(Multitenant Environment)中,允许一个数据库容器(CDB)承载多个可插拔数据库(PDB)。CDB全称为Container Database,中文翻译为数据库容器,PDB全称为Pluggable Database,即可插拔数据库。在ORACLE 12C之前,实例与数据库是一对一或多对一关系(RAC):即一个实例只能与一个数据库相关联,数据库可以被多个实例所加载。而实例与数据库不可能是一对多的关系。当进入ORACLE 12C后,实例与数据库可以是一对多的关系。下面是官方文档关于CDB与PDB的关系图。
其实大家如果对SQL SERVER比较熟悉的话,这种CDB与PDB是不是感觉和SQL SERVER的单实例多数据库架构是一回事呢。像PDB$SEED可以看成是master、msdb等系统数据库,PDBS可以看成用户创建的数据库。而可插拔的概念与SQL SERVER中的用户数据库的分离、附加其实就是那么一回事。看来ORACLE也“抄袭”了一把SQL SERVER的概念,只是改头换面的包装了一番。
CDB组件(Components of a CDB)
一个CDB数据库容器包含了下面一些组件:
ROOT组件
ROOT又叫CDB$ROOT, 存储着ORACLE提供的元数据和Common User,元数据的一个例子是ORACLE提供的PL/SQL包的源代码,Common User 是指在每个容器中都存在的用户。
SEED组件
Seed又叫PDB$SEED,这个是你创建PDBS数据库的模板,你不能在Seed中添加或修改一个对象。一个CDB中有且只能有一个Seed. 这个感念,个人感觉非常类似SQL SERVER中的model数据库。
PDBS
CDB中可以有一个或多个PDBS,PDBS向后兼容,可以像以前在数据库中那样操作PDBS,这里指大多数常规操作。
这些组件中的每一个都可以被称为一个容器。因此,ROOT(根)是一个容器,Seed(种子)是一个容器,每个PDB是一个容器。每个容器在CDB中都有一个独一无二的的ID和名称。
1)连接到CDB数据库
连接到CDB数据库容器非常简单,跟以前连接数据库是一样的
[oracle@get-orasvr02 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Oct 20 23:41:36 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
[oracle@get-orasvr02 ~]$ sqlplus sys/password as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Oct 20 23:43:17 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
2)查看数据库是否为CDB
SQL> select name, decode(cdb, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option" , open_mode, con_id from v$database;
NAME Multitenant Option OPEN_MODE CON_ID
--------- ----------------------------- -------------------- ----------
EPPS Multitenant Option enabled READ WRITE 0
YES表示该数据库是CDB,如果是NO表示是NO-CDB(普通数据库)
3)查看当前容器(Container)
3.1
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
3.2
SQL> select sys_context('userenv', 'con_name') "Container DB" from dual;
Container DB
----------------------------------------------------
CDB$ROOT
SQL>
4)查看CDB容器中的PDBS信息
查看CDB中有多少个pluggable database
SQL> select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
---------- ---------- -------------------------------- ------------------------------ ----------
2 4071321146 E89E8DA2866E3157E043DE07A8C09238 PDB$SEED READ ONLY
3 1930201447 E89E9418B882350CE043DE07A8C092B6 PDBEPPS MOUNTED
SQL>
5)启动PDB数据库
方式1:
SQL> alter pluggable database PDBEPPS open;
Pluggable database altered.
SQL> select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
---------- ---------- -------------------------------- ------------------------------ ----------
2 4071321146 E89E8DA2866E3157E043DE07A8C09238 PDB$SEED READ ONLY
3 1930201447 E89E9418B882350CE043DE07A8C092B6 PDBEPPS READ WRITE
方式2:
SQL> alter session set container=PDBEPPS;
Session altered.
SQL> startup
Pluggable Database opened.
SQL>
6)关闭PDB数据库
SQL> alter pluggable database PDBEPPS close;
Pluggable database altered.
SQL> select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
---------- ---------- -------------------------------- ------------------------------ ----------
2 4071321146 E89E8DA2866E3157E043DE07A8C09238 PDB$SEED READ ONLY
3 1930201447 E89E9418B882350CE043DE07A8C092B6 PDBEPPS MOUNTED
SQL>
7)在容器间切换
SQL> alter session set container=PDBEPPS;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
PDBEPPS
SQL>
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL>
###################################################
Oracle 12.1发布至今已有多年,但国内Oracle 12C的用户并不多,随着12.2在去年的发布,选择安装Oracle 12c的客户量明显增加,在接下来的几年中,Oracle 12c将逐步得到普及。
目前关于12C新特性的文章很多,但大多都不成体系,本次的文章是一个非常完整、连贯的系列,将带你全面的从基础到深入全方位的理解Oracle 12C。
本篇为Oracle 12c系列的开篇文章《Oracle 12c系列(1)Multitenant Container》。
可插入数据库的概念
Oracle Multitenant Container Database(CDB),即多租户容器数据库,是Oracle 12C引入的特性,指的是可以容纳一个或者多个可插拔数据库的数据库,这个特性允许在CDB容器数据库中创建并且维护多个数据库,在CDB中创建的数据库被称为PDB,每个PDB在CDB中是相互独立存在的,在单独使用PDB时,与普通数据库无任何区别。
CDB根容器数据库的主要作用就是容纳所有相关的PDB的元数据,以及在CDB中对所有的PDB进行管理。
多租户环境的组成
-
ROOT
Root容器数据库,是CDB环境中的根数据库,在根数据库中含有主数据字典视图,其中包含了与Root容器有关的元数据和CDB中所包含的所有的PDB信息。在CDB环境中被标识为CDB$ROOT
,每个CDB环境中只能有一个Root容器数据库。 -
CDB seed
CDB seed为PDB的种子,其中提供了数据文件,在CDB环境中被标识为PDB$SEED
,是创建新的 PDB的模板,你可以连接PDB$SEED
,但是不能执行任何事物,因为PDB$SEED
是只读的,不可进行修改。 -
PDBs
PDB数据库,在CDB环境中每个PDB都是独立存在的,与传统的Oracle数据库基本无差别,每个PDB拥有自己的数据文件和objects,唯一的区别在于PDB可以插入到CDB中,以及在CDB中拔出,并且在任何一个时间点之上PDB必须拔出或者插入到一个CDB中,当用户链接PDB时不会感觉到根容器和其他PDB的存在。
CDBwith PDBs
<span style="color:#000000"><code><span style="color:#880000 !important"><em>sys</em></span><span style="color:#009900 !important">.</span> <span style="color:#880000 !important"><em>ora12c</em></span>><span style="color:#880000 !important"><em>show</em></span> <span style="color:#880000 !important"><em>pdbs</em></span>
<span style="color:#880000 !important"><em>CON_ID</em></span> <span style="color:#880000 !important"><em>CON_NAME</em></span> <span style="color:#880000 !important"><em>OPEN</em></span> <span style="color:#880000 !important"><em>MODE</em></span> <span style="color:#880000 !important"><em>RESTRICTED</em></span>
<span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span> <span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span> <span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span> <span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span>
<span style="color:#880000 !important"><em>2</em></span> <span style="color:#880000 !important"><em>PDB$SEED</em></span> <span style="color:#880000 !important"><em>READ</em></span> <span style="color:#880000 !important"><em>ONLY</em></span> <span style="color:#880000 !important"><em>NO</em></span>
<span style="color:#880000 !important"><em>3</em></span> <span style="color:#880000 !important"><em>ORA12CPDB</em></span> <span style="color:#880000 !important"><em>READ</em></span> <span style="color:#880000 !important"><em>WRITE</em></span> <span style="color:#880000 !important"><em>NO</em></span>
<span style="color:#880000 !important"><em>4</em></span> <span style="color:#880000 !important"><em>PDB2</em></span> <span style="color:#880000 !important"><em>MOUNTED</em></span>
<span style="color:#880000 !important"><em>5</em></span> <span style="color:#880000 !important"><em>PDB1</em></span> <span style="color:#880000 !important"><em>MOUNTED</em></span>
<span style="color:#880000 !important"><em>6</em></span> <span style="color:#880000 !important"><em>PDB3</em></span> <span style="color:#880000 !important"><em>READ</em></span> <span style="color:#880000 !important"><em>WRITE</em></span> <span style="color:#880000 !important"><em>NO</em></span>
<span style="color:#880000 !important"><em>sys</em></span><span style="color:#009900 !important">.</span> <span style="color:#880000 !important"><em>ora12c</em></span>></code></span>
PDB$SEED
为CDB seed,ORA12CPDB/PDB1/PDB2/PDB3为PDB数据库。
Application Containers
在12cR2版本中,Oracle对多租户功能进行了增强,在CDB root容器中可以创建一个叫做Application root的容器,可在其内创建多个依赖于Application root的Application PDBs,架构图如下:
ApplicationContainers in a CDB
Application
更多的关于Application Containers相关内容请参考之前的ApplicationContainers系列:
CDB环境中的用户
CDB环境中包含两类用户,公用用户和本地用户。
公用用户
公用用户是在root数据库中和所有的PDB数据库中都存在的用户,公用用户必须在根容器中创建,然后此用户会在所有的现存的PDB中自动创建,公用用户标识必须以c##或者C##开头,sys和system用户是Oracle在CDB环境中自动创建的公用用户。
<span style="color:#000000"><code><span style="color:#000088 !important">create</span> <span style="color:#000088 !important">user</span> c##yyh identified <span style="color:#000088 !important">by</span> yyh;</code></span>
- 1
创建完成公用用户,需要为公用用户赋予所有可插拔数据库的权限,公用用户才可以访问其他PDB,如果在连接根容器时仅仅为公用用户赋予了相关权限,那么该权限不会被传递到所有的可插拔数据库中,必须为公用用户赋予能够传递到PDB中的权限,可以创建公用角色然后赋予公用用户,或者在为公共用户付权时指定子句container=ALL;
例如:
<span style="color:#000000"><code> 或者
<span style="color:#000088 !important">grant</span> dba <span style="color:#000088 !important">to</span> c##yyh container=<span style="color:#000088 !important">all</span>;
<span style="color:#000088 !important">create</span> role c##dbaprivs container=<span style="color:#000088 !important">all</span>;
<span style="color:#000088 !important">grant</span> dba <span style="color:#000088 !important">to</span> c##dbaprivs container=<span style="color:#000088 !important">all</span>;
<span style="color:#000088 !important">grant</span> c##dbaprivs <span style="color:#000088 !important">to</span> c##yyh container=<span style="color:#000088 !important">all</span>;
或者
<span style="color:#000088 !important">grant</span> dba <span style="color:#000088 !important">to</span> c##yyh container=<span style="color:#000088 !important">all</span>;</code></span>
本地用户
本地用户指的是在PDB中创建的普通用户,只有在创建它的PDB中才会存在该用户,并且PDB中只能创建本地用户。
CDB中你需要再次了解的基础知识
SYSTEM/SYSAUX
在CDB的数据库环境中,SYSTEM/SYSAUX表空间并不是公用,CDB$ROOT以及每个PDB都拥有自己的SYSTEM和SYSAUX表空间。
REDO文件
在CDB环境中所有的PDB共用CDB$ROOT中的REDO文件,REDO中的条目标识REDO来自那个PDB。
在PDB中无法执行ALTERSYSTEM SWITCH LOGFILE命令,只有公用用户在ROOT容器中才可以执行该命令。
另外ALTER SYSTEM CHECKPOINT命令是可以在PDB中执行的。
归档
在CDB环境中所有的PDB共用CDB的归档模式,以及归档文件,不可以单独为PDB设置自己的归档模式,只有特权用户连接根容器之后才可以启动归档模式。
UNDO MODE
在12.2之前的版本中,所有的PDB共用CDB$ROOT
中的UNDO文件,在12.2之后的版本中UNDO的使用模式有两种:SHARED UNDO MODE和LOCAL UNDO MODE,顾名思义,LOCAL UNDO MODE就是每个PDB使用自己的UNDO表空间,但当PDB中没有自己的UNDO表空间时,会使用CDB$ROOT
中的公共UNDO表空间。
查看UNDO表空间的使用模式(CDB$ROOT
):
<span style="color:#000000"><code>COL PROPERTY_NAME <span style="color:#000088 !important">FOR</span> A50
COL PROPERTY_VALUE <span style="color:#000088 !important">FOR</span> A50
COL DESCRIPTION <span style="color:#000088 !important">FOR</span> A50
<span style="color:#000088 !important">SELECT</span> property_name, property_value
<span style="color:#000088 !important">FROM</span> database_properties
<span style="color:#000088 !important">WHERE</span> property_name=<span style="color:#880000 !important"><em>'LOCAL_UNDO_ENABLED';</em></span>
PROPERTY_NAME PROPERTY_VALUE
-------------------- --------------------
LOCAL_UNDO_ENABLED <span style="color:#006666 !important">TRUE</span></code></span>
在创建CDB时使用了SHARED UNDO MODE方式,如果后续想更改为LOCAL UNDO MODE,我们可以使用如下命令更改UNDO MODE为LOCAL UNDO MODE:
<span style="color:#000000"><code>startup upgrade
<span style="color:#000088 !important">alter</span> <span style="color:#000088 !important">database</span> <span style="color:#000088 !important">local</span> undo <span style="color:#000088 !important">on</span>;
shutdown immediate
startup</code></span>
更改为local undo后CDB中的所有的PDB会自动创建自己的UNDO表空间。
临时文件
每个PDB都有自己的临时表空间,如果PDB没有自己的临时表空间文件,那么,PDB可以使用CDB$ROOT中的临时表空间。
<span style="color:#000000"><code>08<span style="color:#000000 !important">:42</span><span style="color:#000000 !important">:46</span> <span style="color:#000000 !important">sys</span>. <span style="color:#000000 !important">woqu</span>><span style="color:#000000 !important">select</span> <span style="color:#000000 !important">con_id</span>, <span style="color:#000000 !important">tablespace_name</span> <span style="color:#000000 !important">from</span> <span style="color:#000000 !important">cdb_temp_files</span>;
<span style="color:#000000 !important">CON_ID</span> <span style="color:#000000 !important">TABLESPACE_NAME</span>
<span style="color:#000000 !important">----------</span> <span style="color:#000000 !important">--------------------</span>
1 <span style="color:#000000 !important">TEMP</span>
3 <span style="color:#000000 !important">TEMP</span>
<span style="color:#000000 !important">Elapsed</span>: 00<span style="color:#000000 !important">:00</span><span style="color:#000000 !important">:00</span><span style="color:#9b703f !important">.11</span>
08<span style="color:#000000 !important">:43</span><span style="color:#000000 !important">:02</span> <span style="color:#000000 !important">sys</span>. <span style="color:#000000 !important">woqu</span>></code></span>
参数文件
参数文件中只记录了根容器的参数信息,没有记录PDB级别的参数信息,在根容器中修改初始化参数,会被继承到所有的PDB中,在PDB中修改参数后,PDB的参数会覆盖CDB级别的参数,PDB级别的参数记录在根容器的pdb_spfile$
视图中,但并不是所有的参数都可以在PDB中修改,可以通过v$system_parameter
视图查看PDB中可修改的参数:
<span style="color:#000000"><code><span style="color:#000088 !important">SELECT</span> name FROM v<span style="color:#4f4f4f !important">$system_parameter</span>
<span style="color:#000088 !important">WHERE</span> ispdb_modifiable <span style="color:#4f4f4f !important">=</span> <span style="color:#009900 !important">'TRUE'</span>
<span style="color:#000088 !important">ORDER</span> <span style="color:#000088 !important">BY</span> name;
pdb_spfile$:
<span style="color:#006666 !important">08</span>:<span style="color:#006666 !important">37</span>:<span style="color:#006666 !important">58</span> sys<span style="color:#4f4f4f !important">.</span> yyh<span style="color:#4f4f4f !important">></span>show parameter open_cursors
NAME <span style="color:#000088 !important">TYPE</span> VALUE
<span style="color:#4f4f4f !important">------------------------------------</span> <span style="color:#4f4f4f !important">-----------</span> <span style="color:#4f4f4f !important">-----</span>
open_cursors <span style="color:#4f4f4f !important">integer</span> <span style="color:#006666 !important">999</span>
<span style="color:#006666 !important">08</span>:<span style="color:#006666 !important">38</span>:<span style="color:#006666 !important">04</span> sys<span style="color:#4f4f4f !important">.</span> yyh<span style="color:#4f4f4f !important">></span>show parameter local_listener
NAME <span style="color:#000088 !important">TYPE</span> VALUE
<span style="color:#4f4f4f !important">------------------------------------</span> <span style="color:#4f4f4f !important">-----------</span> <span style="color:#4f4f4f !important">----------------</span>
local_listener <span style="color:#4f4f4f !important">string</span> PDB_YYH
<span style="color:#006666 !important">08</span>:<span style="color:#006666 !important">38</span>:<span style="color:#006666 !important">08</span> sys<span style="color:#4f4f4f !important">.</span> yyh<span style="color:#4f4f4f !important">></span><span style="color:#000088 !important">select</span> pdb_uid, name, value$ from pdb_spfile$;
PDB_UID NAME VALUE$
<span style="color:#4f4f4f !important">----------</span> <span style="color:#4f4f4f !important">------------------</span> <span style="color:#4f4f4f !important">------------------</span>
<span style="color:#006666 !important">1167267009</span> open_cursors <span style="color:#006666 !important">999</span>
<span style="color:#006666 !important">1167267009</span> local_listener <span style="color:#009900 !important">'PDB_YYH'</span>
Elapsed: <span style="color:#006666 !important">00</span>:<span style="color:#006666 !important">00</span>:<span style="color:#006666 !important">00.00</span>
<span style="color:#006666 !important">08</span>:<span style="color:#006666 !important">38</span>:<span style="color:#006666 !important">11</span> sys<span style="color:#4f4f4f !important">.</span> yyh<span style="color:#4f4f4f !important">></span></code></span>
这里在PDB:1167267009中更改了open_cursors/ local_listener两个参数。
控制文件
CDB环境中只有一组控制文件,所有的PDB共用这组公共的控制文件,从任何PDB中添加数据文件都会记录到公共控制文件当中,公用用户连接根容器时,可对控制文件进行管理。
<span style="color:#000000"><code><span style="color:#006666 !important">20</span>:<span style="color:#006666 !important">51</span>:<span style="color:#006666 !important">03</span> sys. woqu>show parameter control_files
NAME <span style="color:#000088 !important">TYPE</span> VALUE
<span style="color:#880000 !important"><em>------------------------------------ -----------------------------------------</em></span>
control_files <span style="color:#4f4f4f !important">string</span> /u01/app/oracle/oradata/woqu/c
ontrol01.ctl, /u01/app/oracle/
oradata/woqu/control02.ctl
<span style="color:#006666 !important">20</span>:<span style="color:#006666 !important">51</span>:<span style="color:#006666 !important">06</span> sys. woqu>
<span style="color:#006666 !important">20</span>:<span style="color:#006666 !important">50</span>:<span style="color:#006666 !important">55</span> sys. woqupdb>show parameter control_files
NAME <span style="color:#000088 !important">TYPE</span> VALUE
<span style="color:#880000 !important"><em>------------------------------------ -----------------------------------------</em></span>
control_files <span style="color:#4f4f4f !important">string</span> /u01/app/oracle/oradata/woqu/c
ontrol01.ctl,/u01/app/oracle/
oradata/woqu/control02.ctl
<span style="color:#006666 !important">20</span>:<span style="color:#006666 !important">51</span>:<span style="color:#006666 !important">23</span> sys.woqupdb></code></span>
告警日志以及跟踪文件
在CDB中所有的PDB共用一个告警日志和一组跟踪文件,所有的PDB告警信息都会写入同一个告警日志中。
时区
在CDB环境中可以为CDB以及所有的PDB设置相同的时区,也可以为每个PDB设置单独的时区。
<span style="color:#000000"><code>20:51:50 sys. woqu>show pdbs
CON<em>_ID CON_</em>NAME OPEN MODE RESTRICTED
<span style="color:#4078f2">---------- </span>------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 WOQUPDB READ WRITE NO
20:52:00 sys. woqu>select dbtimezone from dual;
<span style="color:#009900 !important">DBTIME
------</span>
+00:00
Elapsed: 00:00:00.00
20:52:01 sys. woqu>
20:51:53 sys. woqupdb>select dbtimezone from dual;
<span style="color:#009900 !important">DBTIME
------</span>
+08:00
Elapsed: 00:00:00.00
20:52:02 sys. woqupdb></code></span>
字符集
在CDB中定义字符集也可以应用于它所含有的PDB中,每个PDB也可以有自己的字符集设置。
<span style="color:#000000"><code>20:52:33 sys. woqu>SELECT a.value || <em>'_'</em> || b.value || <em>'.'</em>|| c.value NLS<em>_LANG
20:52:33 2 FROM nls_</em>database<em>_parameters a,nls_</em>database<em>_parameters b, nls_</em>database<em>_parameters c
20:52:33 3 WHERE a.parameter = 'NLS_</em>LANGUAGE' ANDb.parameter = <em>'NLS_TERRITORY'</em> AND c.parameter = <em>'NLS_CHARACTERSET'</em>;
<span style="color:#009900 !important">NLS_LANG
----------------------------------------</span>
AMERICAN<em>_AMERICA.AL32UTF8
Elapsed: 00:00:00.06
20:52:33 sys. woqu>20:52:33 sys. woqu>
20:52:36 sys. woqupdb>SELECT a.value || '_</em>' || b.value || <em>'.'</em>|| c.value NLS<em>_LANG
20:52:36 2 FROM nls_</em>database<em>_parameters a,nls_</em>database<em>_parameters b, nls_</em>database<em>_parameters c
20:52:36 3 WHERE a.parameter = 'NLS_</em>LANGUAGE' ANDb.parameter = <em>'NLS_TERRITORY'</em> AND c.parameter = <em>'NLS_CHARACTERSET'</em>;
<span style="color:#009900 !important">NLS_LANG
----------------------------------------</span>
AMERICAN<em>_AMERICA.ZHS16GBK
Elapsed: 00:00:00.08
20:52:36 sys. woqupdb>20:52:36 sys. woqupdb></em></code></span>
数据字典视图与动态性能视图
在CDB环境中引入了CDB级别的数据字典视图,它的级别高于DBA_/ALL_/USER_
,CDB级别的数据字典视图含有所有PDB的元数据信息,其中增加了con_id
列,con_id
为CDB中所有容器唯一标识符,其中con_id
为0的是CDB$ROOT
,con_id
为2的是PDB$SEED
,每个PDB在CDB中都会分配一个唯一的con_id
。如果要想查看CDB级别的数据字典视图,必须使用公用用户在跟容器中查看,并且要查看的PDB必须处于open状态,才可以看到PDB中的信息。
<span style="color:#000000"><code><span style="color:#880000 !important"><em>20:53:42</em></span> <span style="color:#880000 !important"><em>sys</em></span><span style="color:#009900 !important">.</span> <span style="color:#880000 !important"><em>woqu</em></span>><span style="color:#880000 !important"><em>showpdbs</em></span>
<span style="color:#880000 !important"><em>CON_ID</em></span> <span style="color:#880000 !important"><em>CON_NAME</em></span> <span style="color:#880000 !important"><em>OPEN</em></span> <span style="color:#880000 !important"><em>MODE</em></span> <span style="color:#880000 !important"><em>RESTRICTED</em></span>
<span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span> <span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span> <span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span> <span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span>
<span style="color:#880000 !important"><em>2</em></span> <span style="color:#880000 !important"><em>PDB$SEED</em></span> <span style="color:#880000 !important"><em>READ</em></span> <span style="color:#880000 !important"><em>ONLY</em></span> <span style="color:#880000 !important"><em>NO</em></span>
<span style="color:#880000 !important"><em>3</em></span> <span style="color:#880000 !important"><em>WOQUPDB</em></span> <span style="color:#880000 !important"><em>READ</em></span> <span style="color:#880000 !important"><em>WRITE</em></span> <span style="color:#880000 !important"><em>NO</em></span>
<span style="color:#880000 !important"><em>20:53:45</em></span> <span style="color:#880000 !important"><em>sys</em></span><span style="color:#009900 !important">.</span> <span style="color:#880000 !important"><em>woqu</em></span>>
<span style="color:#880000 !important"><em>20:54:40</em></span> <span style="color:#880000 !important"><em>sys</em></span><span style="color:#009900 !important">.</span> <span style="color:#880000 !important"><em>woqu</em></span>><span style="color:#880000 !important"><em>select</em></span> <span style="color:#880000 !important"><em>con_id</em></span><span style="color:#009900 !important">,</span> <span style="color:#880000 !important"><em>pdb_id</em></span><span style="color:#009900 !important">,</span> <span style="color:#880000 !important"><em>pdb_name</em></span><span style="color:#009900 !important">,</span> <span style="color:#880000 !important"><em>dbid</em></span><span style="color:#009900 !important">,</span> <span style="color:#880000 !important"><em>status</em></span> <span style="color:#880000 !important"><em>fromcdb_pdbs;</em></span>
<span style="color:#880000 !important"><em>CON_ID</em></span> <span style="color:#880000 !important"><em>PDB_ID</em></span> <span style="color:#880000 !important"><em>PDB_NAME</em></span> <span style="color:#880000 !important"><em>DBID</em></span> <span style="color:#880000 !important"><em>STATUS</em></span>
<span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span> <span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span> <span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span> <span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span>
<span style="color:#880000 !important"><em>2</em></span> <span style="color:#880000 !important"><em>2</em></span> <span style="color:#880000 !important"><em>PDB$SEED</em></span> <span style="color:#880000 !important"><em>2834527297</em></span> <span style="color:#880000 !important"><em>NORMAL</em></span>
<span style="color:#880000 !important"><em>3</em></span> <span style="color:#880000 !important"><em>3</em></span> <span style="color:#880000 !important"><em>WOQUPDB</em></span> <span style="color:#880000 !important"><em>3238406520</em></span> <span style="color:#880000 !important"><em>NORMAL</em></span>
<span style="color:#880000 !important"><em>20:54:43</em></span> <span style="color:#880000 !important"><em>sys</em></span><span style="color:#009900 !important">.</span> <span style="color:#880000 !important"><em>woqu</em></span>></code></span>
CDB的创建
如果要使用可插拔数据库的功能,需要在创建数据库时专门指定启用可插拔数据库,创建CDB通常有如下两种方式:
-
使用DBCA图形工具创建CDB
这里需要注意的是Oracle 12.2之后支持LOCAL UNDO,这里注意需要手动要勾选LOCAL UNDO选项。
-
CREATE DATABASE语句创建CDB
在使用脚本创建CDB时Oracle提供了两种方法,一种是使用OMF,另外一种是非OMF的方式,注意参数文件中需要将
ENABLE_PLUGGABLE_DATABASE
设置为TRUE。
这里演示使用非OMF方式创建CDB,数据名称为ora12c:
1.设置ENABLE_PLUGGABLE_DATABASE
参数为TRUE,参数文件如下:
<span style="color:#000000"><code><span style="color:#009900 !important">audit_file_dest</span>='/u01/app/oracle/admin/ora12c/adump'
<span style="color:#009900 !important">audit_trail</span>='none'
<span style="color:#009900 !important">compatible</span>='12.2.0'
<span style="color:#009900 !important">control_files</span>='/u01/app/oracle/oradata/ora12c/control01.ctl','
/u01/app/oracle/oradata/ora12c/control02.ctl'
<span style="color:#009900 !important">db_block_size</span>=8192
<span style="color:#009900 !important">db_domain</span>='linux.com'
<span style="color:#009900 !important">db_name</span>='ora12c'
<span style="color:#009900 !important">diagnostic_dest</span>='/u01/app/oracle'
<span style="color:#009900 !important">enable_pluggable_database</span>=true
<span style="color:#009900 !important">memory_target</span>=801m
<span style="color:#009900 !important">nls_language</span>='AMERICAN'
<span style="color:#009900 !important">nls_territory</span>='AMERICA'
<span style="color:#009900 !important">remote_login_passwordfile</span>='EXCLUSIVE'
<span style="color:#009900 !important">undo_tablespace</span>='UNDOTBS1'</code></span>
2.创建相应目录以及设置环境变量
<span style="color:#000000"><code><span style="color:#000088 !important">export</span> ORACLE_SID=ora12c
<span style="color:#000088 !important">export</span> ORACLE_HOME=<span style="color:#009900 !important">$ORACLE_BASE</span>/product/<span style="color:#006666 !important">12.2</span>.<span style="color:#006666 !important">0</span>/dbhome_1
<span style="color:#000088 !important">export</span> PATH=<span style="color:#009900 !important">$ORACLE_HOME</span>/bin:<span style="color:#009900 !important">$ORACLE_HOME</span>/OPatch:<span style="color:#009900 !important">$ORACLE_HOME</span>/perl/bin:<span style="color:#009900 !important">$ORACLE_HOME</span>/jdk/bin:<span style="color:#009900 !important">$PATH</span></code></span>
3.CREATE DATABASE脚本createdb.sql如下:
<span style="color:#000000"><code><span style="color:#000088 !important">CREATE</span> <span style="color:#000088 !important">DATABASE</span> ora12c
<span style="color:#000088 !important">USER</span> SYS IDENTIFIED <span style="color:#000088 !important">BY</span> oracle
<span style="color:#000088 !important">USER</span> SYSTEM IDENTIFIED <span style="color:#000088 !important">BY</span> oracle
LOGFILE <span style="color:#000088 !important">GROUP</span> <span style="color:#006666 !important">1</span>(<span style="color:#009900 !important">'/u01/app/oracle/oradata/ora12c/redo01a.log'</span>,<span style="color:#009900 !important">'/u01/app/oracle/oradata/ora12c/redo01b.log'</span>)
<span style="color:#000088 !important">SIZE</span> <span style="color:#006666 !important">100</span>M BLOCKSIZE <span style="color:#006666 !important">512</span>,
<span style="color:#000088 !important">GROUP</span> <span style="color:#006666 !important">2</span>(<span style="color:#009900 !important">'/u01/app/oracle/oradata/ora12c/redo02a.log'</span>,<span style="color:#009900 !important">'/u01/app/oracle/oradata/ora12c/redo02b.log'</span>)
<span style="color:#000088 !important">SIZE</span> <span style="color:#006666 !important">100</span>M BLOCKSIZE <span style="color:#006666 !important">512</span>,
<span style="color:#000088 !important">GROUP</span> <span style="color:#006666 !important">3</span>(<span style="color:#009900 !important">'/u01/app/oracle/oradata/ora12c/redo03a.log'</span>,<span style="color:#009900 !important">'/u01/app/oracle/oradata/ora12c/redo03b.log'</span>)
<span style="color:#000088 !important">SIZE</span> <span style="color:#006666 !important">100</span>M BLOCKSIZE <span style="color:#006666 !important">512</span>
MAXLOGHISTORY <span style="color:#006666 !important">1</span>
MAXLOGFILES <span style="color:#006666 !important">16</span>
MAXLOGMEMBERS <span style="color:#006666 !important">3</span>
MAXDATAFILES <span style="color:#006666 !important">1024</span>
<span style="color:#000088 !important">CHARACTER</span> <span style="color:#000088 !important">SET</span> AL32UTF8
<span style="color:#000088 !important">NATIONAL</span> <span style="color:#000088 !important">CHARACTER</span> <span style="color:#000088 !important">SET</span> AL16UTF16
EXTENT MANAGEMENT <span style="color:#000088 !important">LOCAL</span>
DATAFILE <span style="color:#009900 !important">'/u01/app/oracle/oradata/ora12c/system01.dbf'</span>
<span style="color:#000088 !important">SIZE</span> <span style="color:#006666 !important">700</span>M REUSE AUTOEXTEND <span style="color:#000088 !important">ON</span> NEXT10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE <span style="color:#009900 !important">'/u01/app/oracle/oradata/ora12c/sysaux01.dbf'</span>
<span style="color:#000088 !important">SIZE</span> <span style="color:#006666 !important">550</span>M REUSE AUTOEXTEND <span style="color:#000088 !important">ON</span> NEXT10240K MAXSIZE UNLIMITED
<span style="color:#000088 !important">DEFAULT</span> TABLESPACE deftbs
DATAFILE<span style="color:#009900 !important">'/u01/app/oracle/oradata/ora12c/deftbs01.dbf'</span>
<span style="color:#000088 !important">SIZE</span> <span style="color:#006666 !important">500</span>M REUSE AUTOEXTEND ONMAXSIZE UNLIMITED
<span style="color:#000088 !important">DEFAULT</span> <span style="color:#000088 !important">TEMPORARY</span> TABLESPACE tempts1
TEMPFILE <span style="color:#009900 !important">'/u01/app/oracle/oradata/ora12c/temp01.dbf'</span>
<span style="color:#000088 !important">SIZE</span> <span style="color:#006666 !important">20</span>M REUSE AUTOEXTEND <span style="color:#000088 !important">ON</span> NEXT640K MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
DATAFILE<span style="color:#009900 !important">'/u01/app/oracle/oradata/ora12c/undotbs01.dbf'</span>
<span style="color:#000088 !important">SIZE</span> <span style="color:#006666 !important">200</span>M REUSE AUTOEXTEND ONNEXT <span style="color:#006666 !important">5120</span>K MAXSIZE UNLIMITED
ENABLE PLUGGABLE <span style="color:#000088 !important">DATABASE</span>
SEED
FILE_NAME_CONVERT =(<span style="color:#009900 !important">'/u01/app/oracle/oradata/ora12c/'</span>,
<span style="color:#009900 !important">'/u01/app/oracle/oradata/ora12c/pdbseed/'</span>)
SYSTEM DATAFILES <span style="color:#000088 !important">SIZE</span> <span style="color:#006666 !important">125</span>MAUTOEXTEND <span style="color:#000088 !important">ON</span> <span style="color:#000088 !important">NEXT</span> <span style="color:#006666 !important">10</span>M MAXSIZE UNLIMITED
SYSAUX DATAFILES <span style="color:#000088 !important">SIZE</span> <span style="color:#006666 !important">100</span>M
USER_DATA TABLESPACE usertbs
DATAFILE<span style="color:#009900 !important">'/u01/app/oracle/oradata/ora12c/pdbseed/usertbs01.dbf'</span>
<span style="color:#000088 !important">SIZE</span> <span style="color:#006666 !important">200</span>M REUSE AUTOEXTEND ONMAXSIZE UNLIMITED; </code></span>
这段代码中”ENABLE PLUGGABLE DATABASE”之后部分与PDB有关,其他部分与创建传统的Oracle数据库语句均相同。
FILE_NAME_CONVERT
子句指定了使用'/u01/app/oracle/oradata/ora12c/'
中的文件名在'/u01/app/oracle/oradata/ora12c/pdbseed/'
中生产CDBseeds。
USER_DATA_TABLESPACE
子句可以在容器中创建额外的表空间,这个表空间在后续使用PDB$SEED创建PDB时会被复制到PDB中。
4.将数据库启动到nomount状态,然后执行上面createdb.sql脚本创建CDB
<span style="color:#000000"><code>startup nomount;
<span style="color:#4f4f4f !important">@createdb</span>.sql</code></span>
当数据库创建成功之后可以看到新建立的数据库被加入到/etc/oratab文件中。
<span style="color:#000000"><code><span style="color:#880000 !important"><em>#cat/etc/oratab</em></span>
+<span style="color:#009900 !important">ASM</span><span style="color:#009900 !important">:/u01/app/</span><span style="color:#006666 !important">11.2</span>.<span style="color:#006666 !important">0</span>/<span style="color:#009900 !important">grid:</span><span style="color:#009900 !important">N</span>
<span style="color:#009900 !important">ora12c:</span>/u01/app/oracle/product/<span style="color:#006666 !important">12.2</span>.<span style="color:#006666 !important">0</span>/<span style="color:#009900 !important">dbhome_1:</span><span style="color:#009900 !important">N</span>
<span style="color:#009900 !important">orcl:</span>/u01/app/oracle/product/<span style="color:#006666 !important">11.2</span>.<span style="color:#006666 !important">0</span>/<span style="color:#009900 !important">dbhome_1:</span><span style="color:#009900 !important">N</span> <span style="color:#880000 !important"><em># line added by Agent</em></span>
<span style="color:#009900 !important">newdb:</span>/u01/app/oracle/product/<span style="color:#006666 !important">12.2</span>.<span style="color:#006666 !important">0</span>/<span style="color:#009900 !important">dbhome_1:</span><span style="color:#009900 !important">N</span>
<span style="color:#009900 !important">yyh:</span>/u01/app/oracle/product/<span style="color:#006666 !important">12.2</span>.<span style="color:#006666 !important">0</span>/<span style="color:#009900 !important">dbhome_1:</span><span style="color:#009900 !important">N</span></code></span>
5.查看监听,根容器的服务ora12c.linux.com已经被注册到监听中。
<span style="color:#000000"><code>Service<span style="color:#009900 !important">"ora12c.linux.com"</span> has <span style="color:#006666 !important">1</span> instance(s).
Instance <span style="color:#009900 !important">"ora12c"</span>, status READY,has <span style="color:#006666 !important">1</span> handler(s) <span style="color:#000088 !important">for</span> <span style="color:#000088 !important">this</span> service...</code></span>
6.运行catcdb.sql脚本创建CDB相关视图:
<span style="color:#000000"><code>@?<span style="color:#008800 !important">/rdbms/</span>admin/catcdb.sql</code></span>
注意事项:
1.在catcdb.pl执行过中需要输入两个参数,文档中并没有指出是什么参数.
第一个参数应输入:
<span style="color:#000000"><code><span style="color:#009900 !important">$ORACLE_HOME</span>/rdbms/admin</code></span>
第二个参数应输入:
<span style="color:#000000"><code>catcdb<span style="color:#009900 !important">.pl</span></code></span>
2.环境变量中必须将$ORACLE_HOME/perl/bin
指定到PATH中,否则报如下错误:
<span style="color:#000000"><code>Can<span style="color:#880000 !important"><em>'tlocate Term/ReadKey.pm</em></span></code></span>
3.需要将$ORACLE_HOME/perl/lib/5.22.0/x86_64-linux-thread-multi/Hash
中的catcdb.pl文件内容:
<span style="color:#000000"><code><span style="color:#000088 !important">use</span> Data::Dumper;
<span style="color:#000088 !important">use</span> util <span style="color:#009900 !important">qw(trim, splitToArray)</span>;
<span style="color:#000088 !important">use</span> catcon <span style="color:#009900 !important">qw(catconSqlplus)</span>;
更改为
<span style="color:#000088 !important">use</span> Data::Dumper;
<span style="color:#000088 !important">use</span> Util<span style="color:#009900 !important">qw(trim, splitToArray)</span>;
<span style="color:#000088 !important">use</span> catcon <span style="color:#009900 !important">qw(catconSqlplus)</span>;</code></span>
4.切换到$ORACLE_HOME/perl/lib/5.22.0/x86_64-linux-thread-multi/Hash
目录中,然后sqlplus连接到数据,在执行catcdb.sql,否则报如下错误:
<span style="color:#000000"><code>Can<span style="color:#880000 !important"><em>'tlocate Util.pm</em></span></code></span>
7.开启localundo mode(因为上面再创建CDB时没有加入localundo mode子句)
<span style="color:#000000"><code>sys<span style="color:#4f4f4f !important">.</span> ora12c<span style="color:#4f4f4f !important">></span>shutdown immediate
sys<span style="color:#4f4f4f !important">.</span> ora12c<span style="color:#4f4f4f !important">></span>startup upgrade
sys<span style="color:#4f4f4f !important">.</span> ora12c<span style="color:#4f4f4f !important">></span>alter database <span style="color:#4f4f4f !important">local</span> undo <span style="color:#000088 !important">on</span>;
Database altered<span style="color:#4f4f4f !important">.</span>
Elapsed: <span style="color:#006666 !important">00</span>:<span style="color:#006666 !important">00</span>:<span style="color:#006666 !important">00.72</span>
查看是否为<span style="color:#4f4f4f !important">local</span> undo mode:
<span style="color:#000088 !important">SELECT</span> property_name, property_value
FROM database_properties
<span style="color:#000088 !important">WHERE</span> property_name<span style="color:#4f4f4f !important">=</span><span style="color:#009900 !important">'LOCAL_UNDO_ENABLED'</span>;
PROPERTY_NAME PROPERTY_VALUE
<span style="color:#4f4f4f !important">--------------------</span> <span style="color:#4f4f4f !important">--------------------</span>
LOCAL_UNDO_ENABLED <span style="color:#006666 !important">TRUE</span>
Elapsed: <span style="color:#006666 !important">00</span>:<span style="color:#006666 !important">00</span>:<span style="color:#006666 !important">00.02</span>
sys<span style="color:#4f4f4f !important">.</span> ora12c<span style="color:#4f4f4f !important">></span> </code></span>
重启数据库:
<span style="color:#000000"><code><span style="color:#000088 !important">shutdown</span> immediate
startup</code></span>
启动数据库的过程中会在PDBSEED中自动创建UNDO表空间,提供给后来采用PDBSEED中自动创建UNDO表空间,提供给后来采用PDBSEED方式创建PDB所使用。
PS:如果CDB中已经存在多个PDB,当在根容器中使用alter database local undo on;开启local undo mode时会自动为每个PDB创建local undo。
8.检查CDB是否已经创建成功
<span style="color:#000000"><code><span style="color:#880000 !important"><em>sys</em></span><span style="color:#009900 !important">.</span> <span style="color:#880000 !important"><em>ora12c</em></span>><span style="color:#880000 !important"><em>SELECT</em></span> <span style="color:#880000 !important"><em>dbid</em></span><span style="color:#009900 !important">,</span> <span style="color:#880000 !important"><em>name</em></span><span style="color:#009900 !important">,</span> <span style="color:#880000 !important"><em>open_mode</em></span><span style="color:#009900 !important">,</span> <span style="color:#880000 !important"><em>cdb</em></span><span style="color:#009900 !important">,</span> <span style="color:#880000 !important"><em>con_id</em></span> <span style="color:#880000 !important"><em>FROM</em></span> <span style="color:#880000 !important"><em>v$database;</em></span>
<span style="color:#880000 !important"><em>DBID</em></span> <span style="color:#880000 !important"><em>NAME</em></span> <span style="color:#880000 !important"><em>OPEN_MODE</em></span> <span style="color:#880000 !important"><em>CDB</em></span> <span style="color:#880000 !important"><em>CON_ID</em></span>
<span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span> <span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span> <span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span> <span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span> <span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span><span style="color:#006666 !important">-</span>
<span style="color:#880000 !important"><em>323027068</em></span> <span style="color:#880000 !important"><em>ORA12C</em></span> <span style="color:#880000 !important"><em>READ</em></span> <span style="color:#880000 !important"><em>WRITE</em></span> <span style="color:#880000 !important"><em>YES</em></span> <span style="color:#880000 !important"><em>0</em></span>
<span style="color:#880000 !important"><em>Elapsed:</em></span> <span style="color:#880000 !important"><em>00:00:00</em></span><span style="color:#009900 !important">.</span><span style="color:#880000 !important"><em>11</em></span>
<span style="color:#880000 !important"><em>sys</em></span><span style="color:#009900 !important">.</span> <span style="color:#880000 !important"><em>ora12c</em></span>> </code></span>
此时CDB中含有两个容器:根容器CDB$ROOT
和种子容器PDB$SEED
,如下:
<span style="color:#000000"><code>sys. ora12c>SELECT con<em>_id, dbid, con_</em>uid, guid, name FROM v$containers;
CON<em>_ID DBID CON_</em>UID GUID NAME
<span style="color:#4078f2">------ </span>---------- ---------- --------------------------------- ----------
1 323027068 150A57CDEFD150C96E0530838A8C0206E CDB$ROOT
2 1453953285 145395328550A57CDEFD160C96E0530838A8C0206E PDB$SEED
Elapsed: 00:00:00.00
sys. ora12c>SELECT con<em>_id, tablespace_</em>name, file<em>_id, file_</em>name
FROM cdb<em>_data_</em>files;
CON<em>_ID TABLESPACE_</em>NAME FILE<em>_ID FILE_</em>NAME
<span style="color:#4078f2">------ </span>--------------- ---------------------------------------------------
1 SYSTEM 1/u01/app/oracle/oradata/ora12c/system01.dbf
1 SYSAUX 3/u01/app/oracle/oradata/ora12c/sysaux01.dbf
1 UNDOTBS1 5/u01/app/oracle/oradata/ora12c/undotbs01.dbf
1 DEFTBS 6/u01/app/oracle/oradata/ora12c/deftbs01.dbf
Elapsed: 00:00:00.01
sys. ora12c>show con<em>_id con_</em>name user
<span style="color:#009900 !important">CON_ID
------------------------------</span>
1
<span style="color:#009900 !important">CON_NAME
------------------------------</span>
CDB$ROOT
USER is "SYS"
sys. ora12c></code></span>
CDB的管理
管理CDB时,通常需要使用sys用户连接根容器数据库,在操作方式上与非CDB数据库同样。
当前连接容器的信息:
<span style="color:#000000"><code>1.
23:03:31 sys. woqu>show con<em>_id con_</em>name user
<span style="color:#009900 !important">CON_ID
------------------------------</span>
1
<span style="color:#009900 !important">CON_NAME
------------------------------</span>
CDB$ROOT
USER is "SYS"
23:03:33 sys. woqu>
2.
COL CON<em>_ID FOR A10
COL CUR_</em>CONTAINER FOR A25
COL CUR<em>_USER FOR A25
SELECT
sys_</em>context(<em>'USERENV'</em>,<em>'CON_ID'</em>)con<em>_id,
sys_</em>context(<em>'USERENV'</em>,<em>'CON_NAME'</em>) cur<em>_container,
sys_</em>context(<em>'USERENV'</em>,<em>'session_user'</em>) cur<em>_user
FROM dual;
CON_</em>ID CUR<em>_CONTAINER CUR_</em>USER
<span style="color:#4078f2">---------- </span>------------------------- -------------------------
1 CDB$ROOT SYS </code></span>
启动和停止CDB
只有公用用户才可以连接根容器,并且启动和停止CDB,停止和启动与普通数据库的处理过程相同,当公用用户连接PDB时,无法启动和停止CDB:
<span style="color:#000000"><code>sys. ora12c>startup</code></span>
- 默认情况下启动CDB时不会自动启动PDBs,我们可以使用手工的方式启动PDB:
在关闭CDB时,CDB中正在运行PDSs会也会关闭,连接CDB与PDBs的session均会断开。
查看CDB环境中表空间使用情况:
<span style="color:#000000"><code>with generator0 as
(<span style="color:#000088 !important">select</span> cf.con_id,cf.tablespace_name, <span style="color:#009900 !important">sum</span>(cf.bytes) / <span style="color:#006666 !important">1024</span> / <span style="color:#006666 !important">1024</span> frm
<span style="color:#000088 !important">from</span> cdb_free_space cf
<span style="color:#000088 !important">group</span> <span style="color:#000088 !important">by</span> cf.con_id,cf.tablespace_name),
generator1 <span style="color:#000088 !important">as</span>
(<span style="color:#000088 !important">select</span> cd.con_id,cd.tablespace_name, <span style="color:#009900 !important">sum</span>(cd.bytes) / <span style="color:#006666 !important">1024</span> / <span style="color:#006666 !important">1024</span> usm
<span style="color:#000088 !important">from</span> cdb_data_files cd
<span style="color:#000088 !important">group</span> <span style="color:#000088 !important">by</span> cd.con_id,cd.tablespace_name),
generator2 <span style="color:#000088 !important">as</span>(
<span style="color:#000088 !important">select</span> g0.con_id, c.name con_name, g0.tablespace_name, g0.frm, g1.usm
<span style="color:#000088 !important">from</span> generator0 g0, generator1 g1,v$containers c
<span style="color:#000088 !important">where</span> g0.con_id = g1.con_id
<span style="color:#000088 !important">and</span> g0.tablespace_name =g1.tablespace_name
<span style="color:#000088 !important">and</span> c.con_id = g1.con_id
<span style="color:#000088 !important">union</span>
<span style="color:#000088 !important">select</span> c.con_id,
c.name,
ct.tablespace_name,
<span style="color:#000088 !important">null</span>,
<span style="color:#009900 !important">sum</span>(ct.bytes) / <span style="color:#006666 !important">1024</span> / <span style="color:#006666 !important">1024</span>
<span style="color:#000088 !important">from</span> v$containers c,cdb_temp_files ct
<span style="color:#000088 !important">where</span> c.con_id = ct.con_id
<span style="color:#000088 !important">group</span> <span style="color:#000088 !important">by</span> c.con_id, c.name,ct.tablespace_name)
<span style="color:#000088 !important">select</span> con_id,
<span style="color:#000088 !important">case</span> <span style="color:#000088 !important">when</span> con_name = LAG(con_name, <span style="color:#006666 !important">1</span>) OVER(PARTITION <span style="color:#000088 !important">BY</span> con_name <span style="color:#000088 !important">ORDER</span> BYtablespace_name) <span style="color:#000088 !important">THEN</span> <span style="color:#000088 !important">null</span> <span style="color:#000088 !important">ELSE</span> con_name <span style="color:#000088 !important">END</span>
con_name, tablespace_name, frm freemb, usm usemb
<span style="color:#000088 !important">from</span> generator2
<span style="color:#000088 !important">order</span> <span style="color:#000088 !important">by</span> con_id;
CON_ID CON_NAME TABLESPACE_NAME FREEMB USEMB
<span style="color:#880000 !important"><em>------ ------------ --------------- ---------- ----------</em></span>
1 CDB$ROOT SYSAUX 28 470
1 SYSTEM 6.5625 810
1 TEMP 33
1 UNDOTBS1 1.9375 60
1 USERS 4 5
3 ORA12CPDB SYSAUX 18.125 360
3 SYSTEM 1.25 250
3 TEMP 64
3 UNDOTBS1 33 100
3 USERS 4 5 </code></span>
切换容器
使用公用用户连接CDB后可以使用alter session的方式切换不同的容器:
<span style="color:#000000"><code><span style="color:#000088 !important">alter</span> <span style="color:#000088 !important">session</span> <span style="color:#000088 !important">set</span> container=pdb1;
<span style="color:#000088 !important">alter</span> <span style="color:#000088 !important">session</span> <span style="color:#000088 !important">set</span> container = cdb$root;</code></span>
- 在切换容器时无需运行监听器和密码文件。只要公用用户拥有相关权限就可以切换到另外的容器中。
后文转自https://blog.csdn.net/dev_csdn/article/details/79412617