oracle cdb、pdb參考

CDB、PDB概念介紹

CDB與PDB是Oracle 12C引入的新特性，在ORACLE 12C數(shù)據(jù)庫(kù)引入的多租用戶環(huán)境（Multitenant Environment）中，允許一個(gè)數(shù)據(jù)庫(kù)容器（CDB）承載多個(gè)可插拔數(shù)據(jù)庫(kù)（PDB）。CDB全稱(chēng)為Container Database，中文翻譯為數(shù)據(jù)庫(kù)容器，PDB全稱(chēng)為Pluggable Database，即可插拔數(shù)據(jù)庫(kù)。在ORACLE 12C之前，實(shí)例與數(shù)據(jù)庫(kù)是一對(duì)一或多對(duì)一關(guān)系（RAC）：即一個(gè)實(shí)例只能與一個(gè)數(shù)據(jù)庫(kù)相關(guān)聯(lián)，數(shù)據(jù)庫(kù)可以被多個(gè)實(shí)例所加載。而實(shí)例與數(shù)據(jù)庫(kù)不可能是一對(duì)多的關(guān)系。當(dāng)進(jìn)入ORACLE 12C后，實(shí)例與數(shù)據(jù)庫(kù)可以是一對(duì)多的關(guān)系。下面為CDB與PDB的關(guān)系圖：

一個(gè)CDB容器數(shù)據(jù)庫(kù)通常包含CDB$ROOT、PDB$SEED組件：
a. CDB$ROOT存儲(chǔ)著ORACLE提供的元數(shù)據(jù)和Common User，Common User 是指在每個(gè)容器中都存在的用戶，查看：

show con_name;

b.PDB$SEED是創(chuàng)建PDB數(shù)據(jù)庫(kù)的模板。一個(gè)CDB中有且只能有一個(gè)Seed，查看：

show pdbs

c.PDB可插拔數(shù)據(jù)庫(kù)，CDB中可以有一個(gè)或多個(gè)PDB，查看：

show pdbs

創(chuàng)建CDB容器數(shù)據(jù)庫(kù)

dbca在創(chuàng)建數(shù)據(jù)庫(kù)的時(shí)候，可以選擇是創(chuàng)建容器數(shù)據(jù)庫(kù)還是傳統(tǒng)的數(shù)據(jù)庫(kù)，如下圖選擇為創(chuàng)建CDB：

創(chuàng)建pdb：

a.命令create pluggable database創(chuàng)建：

create pluggable database app1 admin user pdba identified by pdba123;

指定路徑創(chuàng)建pdb:

mkdir -p /u01/app/oracle/oradata/prod/data

create pluggable database app1 admin user pdba identified by pdba123 FILE_NAME_CONVERT=('/u01/app/oracle/oradata/prod/pdbseed','/u01/app/oracle/oradata/prod/data');

b.dbca創(chuàng)建pdb：

確認(rèn)：

啟動(dòng)pdb：

alter pluggable database APP1 open;

c. 克隆pdb數(shù)據(jù)庫(kù)：

alter system set db_create_file_dest='/u01/app/oracle/oradata/prod/';

create pluggable database app3 from app2;

刪除pdb

先關(guān)閉對(duì)應(yīng)的pdb，然后刪除pdb：

alter pluggable database app3 close immediate;

drop pluggable database app3 including datafiles;

只是刪除庫(kù)，不刪除文件：

drop pluggable database app3 keep datafiles;

alter pluggable database app3 unplug into '/home/oracle/app3.xml';

drop pluggable database app3 keep datafiles;

show pdbs

create pluggable database app5 using '/home/oracle/app3.xml' nocopy;

切換容器

切換到app1 pdb下面：

alter session set container=app1;

切換到CDB容器

alter session set container=CDB$ROOT;???

查看當(dāng)前屬于哪個(gè)容器：

show con_name

select sys_context('USERENV','CON_NAME') from dual;

啟動(dòng)、關(guān)閉pdb

a. 指定pdb進(jìn)行關(guān)閉和啟動(dòng)：

alter pluggable database app1 open;

startup pluggable database app1;

啟動(dòng)到只讀模式，新建的pdb必須啟動(dòng)一次后才可以設(shè)置為read only，否則報(bào)

ERROR at line 1:

ORA-65085: cannot open pluggable database in read-only mode：

可以從dba_pdbs視圖進(jìn)行確認(rèn)，對(duì)于NEW的pdb，需要先open：

SQL> col pdb_name for a10

SQL> select pdb_name,status from dba_pdbs;

PDB_NAME STATUS

---------- ----------

APP1 NORMAL

PDB$SEED NORMAL

APP2 NORMAL

APP3 NORMAL

APP4 NEW

新建一個(gè)app3 數(shù)據(jù)庫(kù)，然后執(zhí)行啟動(dòng)到read only：

create pluggable database app3 from app2;

alter pluggable database app3 open read only;

alter pluggable database app3 open read only;

show pdbs;

alter pluggable database app1 close immediate;

b．切換到對(duì)應(yīng)的pdb進(jìn)行關(guān)閉啟動(dòng)：
alter session set container= app1;?

startup；

alter session set container= app1;

shutdown immediate;

c．集中操作pdb：

開(kāi)啟所有pdb：

alter pluggable database all open;?

關(guān)閉所有pdb：

alter pluggable database all close immediate;

pdb自動(dòng)啟動(dòng)：

oracle 12.1和oracle12.2版本，默認(rèn)情況下PDB不會(huì)隨著CDB啟動(dòng)而啟動(dòng)，oracle 12.1只能通過(guò)觸發(fā)器實(shí)現(xiàn)：

CREATE TRIGGER open_all_pdbs

AFTER STARTUP ON DATABASE

BEGIN

EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';

END ;

/

刪除觸發(fā)器：

drop trigger open_all_pdbs;

oracle12.2之后可以使用save state來(lái)保存pdb數(shù)據(jù)庫(kù)的當(dāng)前狀態(tài)。

show pdbs;

alter pluggable database app1 save state;

startup force;

show pdbs;

查看state狀態(tài)：

col con_name for a10

select con_name, state from dba_pdb_saved_states;

刪除discard state狀態(tài)：

alter pluggable database app1 discard state;

查看pdb信息

查看所有pdb：
show pdbs

col name for a20

select con_id,dbid,name,open_mode from v$pdbs;

select name,cause ,type,message,status from pdb_plug_in_violations;