Oracle 12C引入了CDB与PDB的新特性,在ORACLE 12C数据库引入的多租用户环境(Multitenant Environment)中,允许一个数据库容器(CDB)承载多个可插拔数据库(PDB)。CDB全称为Container Database,中文翻译为数据库容器,PDB全称为Pluggable Database,即可插拔数据库。在ORACLE 12C之前,实例与数据库是一对一或多对一关系(RAC):即一个实例只能与一个数据库相关联,数据库可以被多个实例所加载。而实例与数据库不可能是一对多的关系。当进入ORACLE 12C后,实例与数据库可以是一对多的关系。下面是官方文档关于CDB与PDB的关系
一个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和名称。
现在就是pdb相关的一些简单的功能的测试
查看数据库是否是cdb的数据库,只要cdb的数据库才能够在里面创建pdb
20:54:21 sys@stldb> 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
--------- -------------------------- -------------------- -------------
STLDB Multitenant Option enabled READ WRITE 0
Multitenant Option enabled 可以创建pdb
查看当前连接的container的名字
20:56:04 sys@stldb> show con_name
CON_NAME
------------------------------
CDB$ROOT
查看当前有哪些pdb
20:58:35 sys@stldb> select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
------------- ------------- -------------------------------- ------------------------------ ----------
2 4117202806 117BCA51FD151564E0536506A8C0D708 PDB$SEED READ ONLY
3 3382304421 117C1A2739A394A2E0536506A8C0E86A PDB1 READ WRITE
4 3940876746 117C20359794C040E0536506A8C0B458 PDB2 READ WRITE
连接pdb
alter session set container=pdb1;
打开pdb,我们可以单独的打开一个pdb也可以打开所有的pdb
alter pluggable database pdb1 open;
alter pluggable database all open;
关闭pdb
alter pluggable database pdb1 close;
手动创建pdb
其中file_name_convert 是根据PDB$SEED的file来进行转换的
CREATE PLUGGABLE DATABASE pdb4
ADMIN USER pdb_adm IDENTIFIED BY oracle
DEFAULT TABLESPACE "USERS" DATAFILE '+data/pdb4/pdb4_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON
file_name_convert=(
'+DATA/STLDB/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/system.267.874613169',
'+data/pdb4/pdb4_system01.dbf',
'+DATA/STLDB/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/sysaux.266.874613169',
'+data/pdb4/pdb4_sysaux01.dbf',
'+DATA/STLDB/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/pdbseed_temp01.dbf',
'+data/pdb4/pdb4_temp01.dbf');
手工建库创建的pdb默认是mount 状态,需要手动把他启动
19:22:30 sys@stldb> select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
------------- ------------- -------------------------------- ------------------------------ ----------
2 4117202806 117BCA51FD151564E0536506A8C0D708 PDB$SEED READ ONLY
3 3382304421 117C1A2739A394A2E0536506A8C0E86A PDB1 READ WRITE
4 3940876746 117C20359794C040E0536506A8C0B458 PDB2 READ WRITE
5 286448482 118073E7A685F068E0536506A8C0A25E PROD READ WRITE
6 3885371043 118F19327FCA760FE0536506A8C05BDF PDB4 MOUNTED
Elapsed: 00:00:00.01
19:22:35 sys@stldb> alter pluggable database pdb4 open read only;
Pluggable database altered.
Elapsed: 00:00:04.91
19:23:01 sys@stldb> select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
------------- ------------- -------------------------------- ------------------------------ ----------
2 4117202806 117BCA51FD151564E0536506A8C0D708 PDB$SEED READ ONLY
3 3382304421 117C1A2739A394A2E0536506A8C0E86A PDB1 READ WRITE
4 3940876746 117C20359794C040E0536506A8C0B458 PDB2 READ WRITE
5 286448482 118073E7A685F068E0536506A8C0A25E PROD READ WRITE
6 3885371043 118F19327FCA760FE0536506A8C05BDF PDB4 READ ONLY
Elapsed: 00:00:00.02
克隆pdb
Elapsed: 00:00:18.37
19:36:27 sys@stldb> CREATE PLUGGABLE DATABASE pdb5 FROM pdb4
19:38:28 2 FILE_NAME_CONVERT=('pdb4','pdb5');
CREATE PLUGGABLE DATABASE pdb5 FROM pdb4
*
ERROR at line 1:
ORA-65081: database or pluggable database is not open in read only mode
由此,如果一个数据库在clone的时候必须是在read only的状态
19:40:10 sys@stldb> CREATE PLUGGABLE DATABASE pdb5 FROM pdb4
19:43:27 2 FILE_NAME_CONVERT=('pdb4','pdb5');
Pluggable database created.
从种子库创建pdb
CREATE PLUGGABLE DATABASE pdb6 ADMIN USER kiwi IDENTIFIED BY kiwi;
19:45:10 sys@stldb> CREATE PLUGGABLE DATABASE pdb6 ADMIN USER kiwi IDENTIFIED BY kiwi;
Pluggable database created.
Elapsed: 00:00:20.67
19:47:59 sys@stldb> select name from v$datafile where con_id=8;
NAME
----------------------------------------------------------------------------------------------------------------------------------
+DATA/STLDB/118F19327FCF760FE0536506A8C05BDF/DATAFILE/system.323.874698473
+DATA/STLDB/118F19327FCF760FE0536506A8C05BDF/DATAFILE/sysaux.322.874698459
Elapsed: 00:00:00.01
19:48:10 sys@stldb> select name from v$tempfile where con_id=8;
NAME
----------------------------------------------------------------------------------------------------------------------------------
+DATA/STLDB/118F19327FCF760FE0536506A8C05BDF/TEMPFILE/temp.324.874698471
PDB的删除
19:23:05 sys@stldb> drop pluggable database pdb4;
drop pluggable database pdb4
*
ERROR at line 1:
ORA-65025: Pluggable database PDB4 is not closed on all instances.
19:23:05 sys@stldb> drop pluggable database pdb4;
drop pluggable database pdb4
*
ERROR at line 1:
ORA-65025: Pluggable database PDB4 is not closed on all instances.
删除pdb的时候,这个数据库必须是close的
19:24:49 sys@stldb> select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
------------- ------------- -------------------------------- ------------------------------ ----------
2 4117202806 117BCA51FD151564E0536506A8C0D708 PDB$SEED READ ONLY
3 3382304421 117C1A2739A394A2E0536506A8C0E86A PDB1 READ WRITE
4 3940876746 117C20359794C040E0536506A8C0B458 PDB2 READ WRITE
5 286448482 118073E7A685F068E0536506A8C0A25E PROD READ WRITE
6 3885371043 118F19327FCA760FE0536506A8C05BDF PDB4 MOUNTED
Elapsed: 00:00:00.01
19:25:06 sys@stldb> drop pluggable database pdb4;
Pluggable database dropped.
顺利删除
19:52:19 sys@stldb> drop pluggable database pdb5 including datafiles;
Pluggable database dropped.
Elapsed: 00:00:03.15
是包括当前pdb的所有的数据文件都进行删除