oracle12c 学习之二——pdb的基本功能

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的所有的数据文件都进行删除