一、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