oracle 12c 容器数据库

最新推荐文章于 2024-05-24 16:10:05 发布

daf380

最新推荐文章于 2024-05-24 16:10:05 发布

阅读量1.7k

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/daf380/article/details/82997622

版权

数据库专栏收录该内容

28 篇文章 0 订阅

订阅专栏

一、Oracle 12c中的CDB和PDB

CDB：CDB全称为Container Database，容器数据库。（公用用户）
PDB：PDB全称为Pluggable Database，可插拔数据库。（本地用户）

1、ROOT：ROOT又叫CDB$ROOT, 存储着ORACLE提供的元数据和Common User（公用用户。 一个CDB只能有一个根。

如：

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> 


2、SEED：Seed又叫PDB$SEED（我们常说的种子数据库）,这个是你创建PDBS数据库的模板，你不能在Seed中添加或修改一个对象。一个CDB中有且只能有一个Seed. 


3、PDB：PDB展现给用户和应用的形象就像是一个没有CDB的普通数据库一样。例 如，一个PDB可以包括支持一个特定应用程序所需的所有数据和代码。PDB 完全向后兼容Oracle12c之前版本的所有数据库。

如：

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 OR12C1PDB			  MOUNTED
SQL> 


这些组件中的每一个都可以被称为一个容器。因此，ROOT(根)是一个容器，Seed(种子)是一个容器，每个PDB是一个容器。每个容器在CDB中都有一个独一无二的的ID和名称。



我们可以很轻松的向CDB中插入一个PDB或者从CDB中拔出一个PDB。当我们将一个PDB插入CDB中时，相当于将这个PDB与CDB连接起来。反之则解除关系。



这大大方便了数据的迁移。我们可以很方便的将一个特定的PDB从一个CDB挪到另一个CDB上面而不改变里面的任何数据和架构。当然，一个PDB只能在同一时间内插入一个CDB而不是多个。每一个PDB都有自己独一无二的全局唯一标识符(GUID)来预防PDB的错乱使用。

如：

SQL> set linesize 200
SQL> col name format a20
SQL> select con_id, dbid, guid, name , open_mode from v$pdbs;

    CON_ID	 DBID GUID			       NAME		    OPEN_MODE
---------- ---------- -------------------------------- -------------------- ----------
	 2 4032990220 939A639B0BD44954E053427FA8C01B0D PDB$SEED 	    READ ONLY
	 3  577406254 939B33F21ED7AC64E053427FA8C02F03 OR12C1PDB	    MOUNTED

SQL>


查询CDB中各容器信息：

SQL> set linesize 200
SQL> col CON_UID format 99999999999999999999999
SQL> col DBID format 999999999999999999999999999
SQL> col GUID format a50
SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

NAME			     CON_ID			    DBID		  CON_UID GUID
-------------------- -------------- ---------------------------- ------------------------ --------------------------------------------------
CDB$ROOT			  1		       533000142			1 4700A987085A3DFAE05387E5E50A8C7B
PDB$SEED			  2		      3095243514	       3095243514 952FEDEE46538487E053427FA8C02859
OR12C1PDB			  3		      2761727959	       2761727959 9530033A049F9866E053427FA8C0F1F8

SQL>

查询在CDB中的PDB数据/临时文件信息：

SQL> set linesize 200
SQL> col file_name format a80
SQL> col PDB_NAME format a30
SQL> SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID,d.TABLESPACE_NAME, d.FILE_NAME FROM DBA_PDBS p, CDB_DATA_FILES d WHERE p.PDB_ID= d.CON_ID ORDER BY p.PDB_ID;

    PDB_ID PDB_NAME			     FILE_ID TABLESPACE_NAME	  FILE_NAME
---------- ------------------------------ ---------- -------------------- --------------------------------------------------------------------------------
	 3 OR12C1PDB				  10 SYSTEM		  +DATA/OR12C/9530033A049F9866E053427FA8C0F1F8/DATAFILE/system.302.1022015239
	 3 OR12C1PDB				  11 SYSAUX		  +DATA/OR12C/9530033A049F9866E053427FA8C0F1F8/DATAFILE/sysaux.301.1022015239
	 3 OR12C1PDB				  14 USERS		  +DATA/OR12C/9530033A049F9866E053427FA8C0F1F8/DATAFILE/users.312.1022015253
	 3 OR12C1PDB				  13 UNDO_2		  +DATA/OR12C/9530033A049F9866E053427FA8C0F1F8/DATAFILE/undo_2.316.1022015239
	 3 OR12C1PDB				  12 UNDOTBS1			+DATA/OR12C/9530033A049F9866E053427FA8C0F1F8/DATAFILE/undotbs1.309.1022015239

SQL> 

SQL> SELECT CON_ID, FILE_ID, TABLESPACE_NAME,FILE_NAME FROM CDB_TEMP_FILES ORDER BY CON_ID;

	CON_ID	  FILE_ID TABLESPACE_NAME      FILE_NAME
-------------- ---------- -------------------- --------------------------------------------------------------------------------
	     1		1 TEMP		       +DATA/OR12C/TEMPFILE/temp.307.1022014875
	     3		3 TEMP		       +DATA/OR12C/9530033A049F9866E053427FA8C0F1F8/TEMPFILE/temp.317.1022015247

SQL>

查询数据文件名、文件号和所属容器：

SQL> select con_id,file#,name from v$datafile order by con_id;

	CON_ID	    FILE# NAME
-------------- ---------- --------------------------------------------------------------------------------
	     1		9 +DATA/OR12C/DATAFILE/undotbs2.284.1022015039
	     1		7 +DATA/OR12C/DATAFILE/users.320.1022014809
	     1		4 +DATA/OR12C/DATAFILE/undotbs1.321.1022014807
	     1		1 +DATA/OR12C/DATAFILE/system.311.1022014741
	     1		3 +DATA/OR12C/DATAFILE/sysaux.300.1022014783
	     2		6 +DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/sysaux.303.1022014881
	     2		5 +DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/system.293.1022014881
	     2		8 +DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/undotbs1.285.1022014881
	     3	       10 +DATA/OR12C/9530033A049F9866E053427FA8C0F1F8/DATAFILE/system.302.1022015239
	     3	       11 +DATA/OR12C/9530033A049F9866E053427FA8C0F1F8/DATAFILE/sysaux.301.1022015239
	     3	       12 +DATA/OR12C/9530033A049F9866E053427FA8C0F1F8/DATAFILE/undotbs1.309.1022015239

	CON_ID	    FILE# NAME
-------------- ---------- --------------------------------------------------------------------------------
	     3	       13 +DATA/OR12C/9530033A049F9866E053427FA8C0F1F8/DATAFILE/undo_2.316.1022015239
	     3	       14 +DATA/OR12C/9530033A049F9866E053427FA8C0F1F8/DATAFILE/users.312.1022015253

13 rows selected.

SQL>

查看容器信息：

SQL> set linesize 300
SQL> col name format a80
SQL> col con_name format a20
SQL> col FILE# format 999
SQL> col GUID format a33
SQL> col CON_ID format 999
SQL> select c.CON_ID,c.GUID,d.FILE#,c.name con_name,d.name from v$containers c,v$datafile d where c.CON_ID=d.CON_ID order by c.CON_ID;

CON_ID GUID				 FILE# CON_NAME 	    NAME
------ --------------------------------- ----- -------------------- --------------------------------------------------------------------------------
     1 4700A987085A3DFAE05387E5E50A8C7B      9 CDB$ROOT 	    +DATA/OR12C/DATAFILE/undotbs2.284.1022015039
     1 4700A987085A3DFAE05387E5E50A8C7B      7 CDB$ROOT 	    +DATA/OR12C/DATAFILE/users.320.1022014809
     1 4700A987085A3DFAE05387E5E50A8C7B      4 CDB$ROOT 	    +DATA/OR12C/DATAFILE/undotbs1.321.1022014807
     1 4700A987085A3DFAE05387E5E50A8C7B      1 CDB$ROOT 	    +DATA/OR12C/DATAFILE/system.311.1022014741
     1 4700A987085A3DFAE05387E5E50A8C7B      3 CDB$ROOT 	    +DATA/OR12C/DATAFILE/sysaux.300.1022014783
     2 952FEDEE46538487E053427FA8C02859      6 PDB$SEED 	    +DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/sysaux.303.1022014881
     2 952FEDEE46538487E053427FA8C02859      5 PDB$SEED 	    +DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/system.293.1022014881
     2 952FEDEE46538487E053427FA8C02859      8 PDB$SEED 	    +DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/undotbs1.285.1022014881
     3 9530033A049F9866E053427FA8C0F1F8     10 OR12C1PDB	    +DATA/OR12C/9530033A049F9866E053427FA8C0F1F8/DATAFILE/system.302.1022015239
     3 9530033A049F9866E053427FA8C0F1F8     11 OR12C1PDB	    +DATA/OR12C/9530033A049F9866E053427FA8C0F1F8/DATAFILE/sysaux.301.1022015239
     3 9530033A049F9866E053427FA8C0F1F8     12 OR12C1PDB	    +DATA/OR12C/9530033A049F9866E053427FA8C0F1F8/DATAFILE/undotbs1.309.1022015239

CON_ID GUID				 FILE# CON_NAME 	    NAME
------ --------------------------------- ----- -------------------- --------------------------------------------------------------------------------
     3 9530033A049F9866E053427FA8C0F1F8     13 OR12C1PDB	    +DATA/OR12C/9530033A049F9866E053427FA8C0F1F8/DATAFILE/undo_2.316.1022015239
     3 9530033A049F9866E053427FA8C0F1F8     14 OR12C1PDB	    +DATA/OR12C/9530033A049F9866E053427FA8C0F1F8/DATAFILE/users.312.1022015253

13 rows selected.

SQL>

二、根据PDB$SEED（PDB种子）创建一个新的PDB

查看当前CDB中的PDB：

[oracle1@test01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 12 22:25:18 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 TEST12CPDB			  MOUNTED
SQL>


切换到test12cpdb可插拔数据并启动它：

SQL> alter session set container=test12cpdb;

Session altered.

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 3 TEST12CPDB			  MOUNTED
SQL> 
SQL> alter database open;

Database altered.

SQL> shutdown immediate;
Pluggable Database closed.
SQL> 
SQL> 
SQL> startup;
Pluggable Database opened.
SQL>

查看当前PDB中的数据文件位置：

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/test12c/test12cpdb/system01.dbf
/u02/app/oracle/oradata/test12c/test12cpdb/sysaux01.dbf
/u02/app/oracle/oradata/test12c/test12cpdb/undotbs01.dbf
/u02/app/oracle/oradata/test12c/test12cpdb/users01.dbf

SQL> 

看来PDB数据文件的位置是在CDB数据文件的目录下

[oracle@test01 test12c]$ cd /u02/app/oracle/oradata/test12c/
[oracle@test01 test12c]$ ls
control01.ctl  pdbseed     redo02.log  sysaux01.dbf  temp01.dbf  undotbs01.dbf
control02.ctl  redo01.log  redo03.log  system01.dbf  test12cpdb  users01.dbf
[oracle@test01 test12c]$ 

其中pdbseed代表种子PDB的数据文件位置；test12cpdb代表test12cpdb 这个PDB数据文件的位置。

然后我们再来根据种子文件的数据文件模板来创建一个新的PDB：

SQL> create pluggable database test12cpdb1 admin user pdb1admin identified by oracle roles=(connect) file_name_convert=('/u02/app/oracle/oradata/test12c/pdbseed','/u02/app/oracle/oradata/test12c/test12cpdb1');
create pluggable database test12cpdb1 admin user pdb1admin identified by oracle roles=(connect) file_name_convert=('/u02/app/oracle/oradata/test12c/pdbseed','/u02/app/oracle/oradata/test12c/test12cpdb1')
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database


SQL> 

报错提示我们不允许在PDB下创建PDB数据库，那么我们再切换到CDB模式下看看能不能创建。


SQL> alter session set container=cdb$root;

Session altered.

SQL>

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 TEST12CPDB			  READ WRITE NO
SQL>

切换到CDB了

再来创建PDB：

SQL> create pluggable database test12cpdb1 admin user pdb1admin identified by oracle roles=(connect) file_name_convert=('/u02/app/oracle/oradata/test12c/pdbseed','/u02/app/oracle/oradata/test12c/test12cpdb1');

Pluggable database created.

SQL>

创建成功了！

查看新的PDB：

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 TEST12CPDB			  READ WRITE NO
	 4 TEST12CPDB1			  MOUNTED
SQL>

对于rac环境下创建新的PDB：

SQL> create pluggable database test12cpdb2 admin user pdb2admin identified by oracle roles=(connect) file_name_convert=('+DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/sysaux.303.1022014881','+data','+DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/system.293.1022014881','+data','+DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/undotbs1.285.1022014881','+data');
create pluggable database test12cpdb2 admin user pdb2admin identified by oracle roles=(connect) file_name_convert=('+DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/sysaux.303.1022014881','+data','+DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/system.293.1022014881','+data','+DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/undotbs1.285.1022014881','+data')
*
ERROR at line 1:
ORA-65005: missing or invalid file name pattern for file - +DATA/OR12C/952FEDEE46538487E053427FA8C02859/TEMPFILE/temp.294.1022014911



提示还缺少临时数据文件，我们先登录种子PDB查看这个临时数据文件是否存在：



SQL> alter session set container=PDB$SEED;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/system.293.1022014881
+DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/sysaux.303.1022014881
+DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/undotbs1.285.1022014881

SQL> select tablespace_name,file_name,bytes/1024/1024 mb from dba_temp_files;

TABLESPACE_NAME      FILE_NAME										      MB
-------------------- -------------------------------------------------------------------------------- ----------
TEMP		     +DATA/OR12C/952FEDEE46538487E053427FA8C02859/TEMPFILE/temp.294.1022014911		      64

SQL> 



我们再加上临时数据文件：



SQL> 
SQL> create pluggable database test12cpdb2 admin user pdb2admin identified by oracle roles=(connect) file_name_convert=('+DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/sysaux.303.1022014881','+data','+DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/system.293.1022014881','+data','+DATA/OR12C/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/undotbs1.285.1022014881','+data','+DATA/OR12C/952FEDEE46538487E053427FA8C02859/TEMPFILE/temp.294.1022014911','+data');

Pluggable database created.

SQL> 





或者：


SQL> create pluggable database or12cpdb2 admin user or12c2pdbadmin identified by oracle roles=(connect) file_name_convert=('+data','+data');

Pluggable database created.

SQL>

三、删除已存在的PDB

查看数据库中已存在的PDB：

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 TEST12CPDB			  READ WRITE NO
	 4 TEST12CPDB1			  MOUNTED
SQL>

我们现在来删除test12cpdb1这个PDB

SQL> drop pluggable database TEST12CPDB including datafiles;
drop pluggable database TEST12CPDB including datafiles
*
ERROR at line 1:
ORA-65025: Pluggable database TEST12CPDB is not closed on all instances.


SQL>

提示PDB没有关闭，我们首先关闭它：

SQL> alter session set container=TEST12CPDB1;

Session altered.

SQL> shutdown immediate;
ORA-65020: pluggable database TEST12CPDB1 already closed


SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 4 TEST12CPDB1			  MOUNTED
SQL> 
SQL> 

pdb处于mount状态，通过test12cpdb1登录后，执行关闭数据库，不成功。看来还是要切换到CDB模式：

SQL> alter session set container=cdb$root;

Session altered.

SQL> 
SQL>  alter pluggable database test12cpdb1 close;
 alter pluggable database test12cpdb1 close
*
ERROR at line 1:
ORA-65020: pluggable database TEST12CPDB1 already closed


SQL> drop pluggable database test12cpdb1 including datafiles;

Pluggable database dropped.

SQL> 

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 TEST12CPDB			  READ WRITE NO
SQL> 
SQL> 


test12cpdb这个PDB数据库已被删除

四、为PDB创建专用监听（即通过不同的端口来访问不同的PDB）

查看当前CDB中有多少个PDB：

SQL> show pdbs;         

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 OR12CPDB			  READ WRITE NO
	 4 TEST12CPDB1			  READ WRITE NO
SQL>

可以看到除了种子PDB外，还有两个PDB，分别为：OR12CCPDB和TEST12CPDB1


现在，我的要求是通过11521能连接到OR12CPDB，通过61521能够连接到TEST12CPDB1，然后CDB默认的监听名和端口不变分别为：listener和1521

首先，我们通过监听配置工具来分别创建这两个PDB的监听：listener_or12cpdb和listener_test12cpdb1

daf380

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
1
评论
oracle 12c 容器数据库

一、Oracle 12c中的CDB和PDBCDB：CDB全称为Container Database，容器数据库。（公用用户）PDB：PDB全称为Pluggable Database，可插拔数据库。（本地用户）1、ROOT：ROOT又叫CDB$ROOT, 存储着ORACLE提供的元数据和Common User（公用用户。一个CDB只能有一个根。如：SQL> show ...
复制链接

扫一扫