一個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和名稱

關於多租戶環境：
1、多個PDB可以可以插入一個CDB中去
2、需要有足夠的資源支撐CDB
3 Configuration options that apply to the entire CDB and configuration options that apply to each PDB

可以用以下方法建立一個CDB:
可以用 DBCA to Create a CDB

也可以用 CREATE DATABASE Statement to Create a CDB

1 配置新的可插入資料
一個在存在期間，只能是一個CDB或者一個非CDB，不能將非CDB轉換為CDB，反之亦然。必須在建立時指定為CDB，然後在其中建立PDB
SQL語句CREATE DATABASE ... ENABLE PLUGGABLE DATABASE可以建立一個新的CDB。如果沒有指定ENABLE PLUGGABLE DATABASE從句，那麼新建的資料庫為非CDB，並且不能包含PDB。

檢視當前資料庫是否為多租戶資料庫，v$database的cdb欄位顯示yes，表明當前資料庫為一個CDB資料庫

SQL> select name,cdb from v$database;
NAME CDB
--------- ---
ORCL YES

檢視當前CDB資料庫中的pdb的狀態
SQL> select con_id,dbid,name,open_mode from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 2972678483 PDB$SEED READ ONLY
3 1145215341 PDBORCL READ WRITE

SQL> select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs;
PDB_ID PDB_NAME DBID STATUS CREATION_SCN
---------- ------------------------------ ---------- --------- ------------
3 PDBORCL 1145215341 NORMAL 1740054
2 PDB$SEED 2972678483 NORMAL 1594415

三種方式，建立PDB
1、從種子PDB建立新的PDB
create pluggable database pdb2
admin user boswll identified by roles=(connect)
file_name_convert=('ORCL','PDB2');

2、將非CDB插入或者克隆到CDB中

克隆
將本地的PDB克隆到同一個CDB中
講遠端的PDB克隆島CDB中

3、將移走的PDB插入到另外一個CDB中

alter pluggable database pdborcl open;

下面舉例：
1、從種子PDB建立新的PDB
create pluggable database pdb2
admin user boswll identified by oracle roles=(connect)
file_name_convert=('ORCL','PDB2');

從 pdb$seed 資料庫複製資料檔案，建立system和sysaux表空間
建立完整目錄，包括向ORACLE提供的物件後設資料
建立臨時表空間TEMP,建立公共使用者（超級使用者sys system）
建立授予本地 pdb_dbs 角色的本地使用者
建立新的預設服務

建立PDB使用者
多租戶環境下的資料庫狀態檢視，啟停，開啟，關閉等，操作如下
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_c6pmswtl_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_c6pmq6xf_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_c6pmxkfs_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_c6pmzfpf_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_c6pmxj8s_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_c6pmzfpb_.dbf
/u01/app/oracle/oradata/ORCL/26A8333E18924AF2E0530210A8C01D68/datafile/o1_mf_system_c6po12ls_.dbf
/u01/app/oracle/oradata/ORCL/26A8333E18924AF2E0530210A8C01D68/datafile/o1_mf_sysaux_c6po12o8_.dbf
/u01/app/oracle/oradata/ORCL/26A8333E18924AF2E0530210A8C01D68/datafile/o1_mf_users_c6po76sc_.dbf

1、從種子PDB建立新的PDB
create pluggable database pdb2
admin user boswll identified by oracle roles=(connect)
file_name_convert=('ORCL','PDB2');

SQL> create pluggable database pdb1
admin user boswll identified by oracle roles=(connect)
file_name_convert=('pdbseed','PDB1');
2 3
create pluggable database pdb1
*
ERROR at line 1:
ORA-65005: missing or invalid file name pattern for file - /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_c6pmzfpf_.dbf

SQL> create pluggable database pdb2
admin user boswll identified by oracle roles=(connect)
file_name_convert=('ORCL','PDB2'); 2 3
create pluggable database pdb2
*
ERROR at line 1:
ORA-01276: Cannot add file /u01/app/oracle/oradata/PDB2/datafile/o1_mf_system_c6pmzfpf_.dbf. File has an Oracle Managed Files file name.

原因是之前建立種子資料庫時用的omf建立的，需要用路徑轉換file_name_convert

檢視種子用資料庫檔案
alter session set =pdb$seed;
SQL> show con_name
CON_NAME
------------------------------
PDB$SEED

SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_c6pmxkfs_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_c6pmzfpf_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_c6pmzfpb_.dbf

SQL> select FILE_NAME,TABLESPACE_NAME,STATUS from dba_data_files;
FILE_NAME TABLESPACE_NAMESTATUS
-------------------------------------------------------------------------------- ------------------------------ ---------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_c6pmzfpf_.dbfSYSTEM AVAILABLE
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_c6pmzfpb_.dbfSYSAUX AVAILABLE

show parameter SOURCE_FILE_NAME_CONVERT
show parameter FILE_NAME_CONVERT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
log_file_name_convert string
pdb_file_name_convert string
SQL> show parameter SOURCE_FILE_NAME_CONVERT

SQL> create pluggable database pdb2
admin user boswll identified by oracle roles=(connect)
DEFAULT TABLESPACE pdb2 DATAFILE '/u01/app/oracle/oradata/ORCL/datafile/pdb2/pdb201.dbf' SIZE 50M
file_name_convert=('/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_c6pmzfpf_.dbf','/u01/app/oracle/oradata/ORCL/datafile/pdb2/pdb2_mf_system_c6pmzfpf_.dbf','/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_c6pmzfpb_.dbf','/u01/app/oracle/oradata/ORCL/datafile/pdb2/pdb2_o1_mf_sysaux_c6pmzfpb_.dbf');

2 3 4 create pluggable database pdb2
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
必須用cdb$root下建立

SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> alter session set container=cdb$root;
Session altered.

create pluggable database pdb2
admin user boswll identified by oracle roles=(connect)
DEFAULT TABLESPACE pdb2 DATAFILE '/u01/app/oracle/oradata/ORCL/datafile/pdb2/pdb201.dbf' SIZE 50M
file_name_convert=('/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_c6pmzfpf_.dbf','/u01/app/oracle/oradata/ORCL/datafile/pdb2/system.dbf',
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_c6pmzfpb_.dbf','/u01/app/oracle/oradata/ORCL/datafile/pdb2/sysaux.dbf',
'/u01/app/oracle/oradata/ORCL/datafile/pdbseed_temp012015-12-12_06-37-39-AM.dbf','/u01/app/oracle/oradata/ORCL/datafile/pdb2/temp.dbf'
);

create pluggable database pdb3
admin user boswll identified by oracle roles=(connect)
file_name_convert=('/o1_mf_system_c6pmzfpf_.dbf','/pdb3/system.dbf',
'/o1_mf_sysaux_c6pmzfpb_.dbf','/pdb3/sysaux.dbf',
'/pdbseed_temp012015-12-12_06-37-39-AM.dbf','/pdb3/temp.dbf'
);

alter pluggable database pdb2 open;
alter pluggable database pdb3 open;

SQL> select NAME,CON_ID,OPEN_MODE from v$pdbs;
NAME CON_ID OPEN_MODE
------------------------------------------------------------------------------------------------------------------------------------------------------ ---------- ----------
PDB$SEED 2 READ ONLY
PDBORCL 3 READ WRITE
PDB1 4 READ WRITE
PDB2 5 READ WRITE
PDB3 6 READ WRITE

如果PDB$SEED 不是omf 自動管理的，那麼可以用下面的方式直接建庫

[oracle@test12c orcl]$ ls
control01.ctl pdborcl pdbseed redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

create pluggable database pdb4
admin user boswll identified by oracle roles=(connect)
file_name_convert=('pdbseed','PDB4');

CONN / AS SYSDBA
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb2/');

CONN / AS SYSDBA
ALTER SESSION SET PDB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb3/';
CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm IDENTIFIED BY Password1;

---刪除
DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;

[oracle@test12c PDB4]$ ls -lrt
總用量 839760
-rw-r----- 1 oracle oinstall 20979712 12月 15 22:01 PDB4_temp012015-12-15_07-45-14-PM.dbf
-rw-r----- 1 oracle oinstall 262152192 12月 15 22:08 system01.dbf
-rw-r----- 1 oracle oinstall 597696512 12月 15 22:08 sysaux01.dbf

2、將非CDB插入或者克隆到CDB中

克隆
將本地的PDB克隆到同一個CDB中
將遠端的PDB克隆到CDB中

---設定 db_create_file_dest 或者配置使用 db_file_name_convert
---停頓要複製的PDB資料庫
alter session set container=cdb$root
alter pluggable database pdb4 close immediate;
alter pluggable database pdb4 open read only;

----從pdb4 克隆pdb5
----此方式建立的omf管理
create pluggable database pdb5 from pdb4 create_file_dest='/u01/app/oracle/oradata/orcl/PDB5';
---此方式建立
create pluggable database pdb6 from pdb4 FILE_NAME_CONVERT=('PDB4','PDB6');
---在讀寫模式下開啟pdb5
alter pluggable database pdb5 open;

---重新以讀寫模式開啟pdb4
alter pluggable database pdb4 close immediate;
alter pluggable database pdb4 open;

---建好PDB5,檢視資料檔案
alter session set container=pdb5
select FILE_NAME,TABLESPACE_NAME,STATUS from dba_data_files;
FILE_NAME TABLESPACE_NAMESTATUS
-------------------------------------------------------------------------------- ------------------------------ ---------
/u01/app/oracle/oradata/orcl/PDB5/ORCL/270349FEACBF115BE0530210A8C0B69A/datafile/o1_mf_system_c72mqpfl_.dbf SYSTEMAVAILABLE
/u01/app/oracle/oradata/orcl/PDB5/ORCL/270349FEACBF115BE0530210A8C0B69A/datafile/o1_mf_sysaux_c72mqpft_.dbf SYSAUXAVAILABLE

alter session set container=pdb6
SQL> select FILE_NAME,TABLESPACE_NAME,STATUS from dba_data_files;
FILE_NAME TABLESPACE_NAMESTATUS
-------------------------------------------------------------------------------- ------------------------------ ---------
/u01/app/oracle/oradata/orcl/PDB6/system01.dbf SYSTEM AVAILABLE
/u01/app/oracle/oradata/orcl/PDB6/sysaux01.dbf SYSAUX AVAILABLE


將non-CDB插入到CDB中
可用三種方法： tts (transportable tablespace)或者tdb (Transportable Database) 或完全匯出/匯入
使用 dbms_pdb 構造XML檔案定義
複製

新的PDB 中會建立以下實體：表空間：system sysaux，temp
完整目錄
公共使用者：sys system
本地管理員 pdba
新的預設服務

--- 使用dbms_pdb包進行遷移
Basically it is very straight forward:
(1) Upgrade your stand-alone database to 12c
(2) Put it into Read-Only mode
(3) DBMS_PDB.DESCRIBE will create an XML manifest file
(4) Shut your database down IMMEDIATE
(5) Connect into the CDB$ROOT
(6) CREATE PLUGGABLE DATABASE pdb1 USING (pdb.xml) NOCOPY TEMPFILE REUSE;
(7) Start @noncdb_to_pdb.sql

--匯出非cdb中的資料
select name ,cdb from v$database;
NAME CDB
--------- ---
dog NO

shut immediate;
conn /as sysdba
startup mount;
alter database open read only;
exec dbms_pdb.describe(PDB_DESCR_FILE=>'/home/oracle/dog.xml');

---匯入到cdb中去作為pdb5
export ORACLE_SID=orcl
sqlplus sys/oracle as sysdba
create pluggable database pdb5 using '/home/oracle/dog.xml'
file_name_convert=('DOG','ORCL/PDB6');
ALTER SESSION SET CONTAINER=pdb5;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb;
select name,open_mode from v$pdbs;
alter session set container=pdb5;
select FILE_NAME,TABLESPACE_NAME,STATUS from dba_data_files;

----dblink此方式可以是把cdb中的pdb插入到其他cdb中去，也可以是non-cdb，插入到其他cdb中去，如果是non-cdb，必須要執行一個指令碼noncdb_to_pdb

Source database must be at least Oracle 12.1.0.1
Source database must be on the same OS platform
Source database must be at the same (equal) version as the container database
Script noncdb_to_pdb.sql needs to be run

--連線到非CDB上
sqlplus sys/oracle as sysdba
shut immediate；
alter database open read only;

---連線到CDB資料庫，建立dblink，在監聽檔案中要配置non-cdb
non-cdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = your_instance.your_host)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = non-cdb)
)
)
create database link orcl connect to system identified by oracle using 'non-cdb';
mkdir -p /u01/app/oracle/oradata/test/pdb6
create pluggable database pdb6 from non$cdb@orcl create_file_dest='/u01/app/oracle/oradata/test/pdb6' TEMPFILE REUSE;;

--執行指令碼，如果是non-cdb,如果不是non-cdb，可以跳過指令碼，直接開啟庫
---But when you check the status of the new PDB you will realize it is OPEN but only in RESTRICTED mode. Therefore noncdb_to_pdb,sql needs to be run. Connect to the new PDB and start the script:
ALTER SESSION SET CONTAINER=pdb6;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb;

---或者透過下列方式執行指令碼，總之要在pdb6上執行指令碼

SQL> select name from v$services where pdb='PDB6';
NAME
----------------------------------------------------------------------------------------------------
pdb6

pdb6 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = your_instance.your_host)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PDB6)
)
)
sqlplus sys/pdb6 as sysdba
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb;

alter pluggable database pdb6 open;
---驗證
SELECT message, action FROM pdb_plug_in_violations WHERE name = 'PDB6';

3、將移走的PDB插入到另外一個CDB中
a 、將根容器orcl中的pdb5移走
以公共使用者的身份連結到pdb5上
驗證pdb5是否已經關閉
alter pluggable database pdb5 unplug into‘pdb5.xml’

以公共使用者的身份連結到test 根容器上
使用dbms_pdb 程式包檢查pdb5 與test 根容器的相容性
create pluggable database pdb1 using 'pdb5.xml' nocopy;
在讀寫模式下開啟pdb2



---拔出pdb

ALTER PLUGGABLE DATABASE pdb4 CLOSE;
ALTER PLUGGABLE DATABASE pdb4 UNPLUG INTO '/home/oracle/pdb4.xml';

[oracle@test12c dog]$ echo $ORACLE_SID
test
[oracle@test12c dog]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 17 05:08:08 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
--插入pdb
把PDB 插入到CDB 類似於新建一個PDB,首先做相容性檢測
First check the PBD is compatible with the CDB by calling the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function,
passing in the XML metadata file and the name of the PDB you want to create using it.
SET SERVEROUTPUT ON
DECLARE
l_result BOOLEAN;
BEGIN
l_result := DBMS_PDB.check_plug_compatibility(
pdb_descr_file => '/home/oracle/pdb4.xml',
pdb_name => 'pdb2');

IF l_result THEN
DBMS_OUTPUT.PUT_LINE('compatible');
ELSE
DBMS_OUTPUT.PUT_LINE('incompatible');
END IF;
END;
/
compatible

---Instead, we want to plug the database back into the same container, so we don't need to copy the files or recreate the temp file, so we can do the following'
CREATE PLUGGABLE DATABASE pdb2 USING '/home/oracle/pdb4.xml' NOCOPY TEMPFILE REUSE;

ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;

SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB MOUNTED
4 PDB2 READ WRITE NO

或者路徑轉換
---If the PDB is not compatible, violations are listed in the PDB_PLUG_IN_VIOLATIONS view. If the PDB is compatible, create a new PDB using it as the source.
--If we were creating it with a new name we might do something like this

CREATE PLUGGABLE DATABASE pdb5 USING '/home/oracle/pdb4.xml'
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2/','/u01/app/oracle/oradata/cdb1/pdb5/');

alter pluggable database pdb5 open;

原來的資料
SQL> alter pluggable database pdb4 open read write;
alter pluggable database pdb4 open read write
*
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database

SQL> SELECT pdb_id,pdb_name,status FROM dba_pdbs;

PDB_ID PDB_NAME STATUS
---------- -------------------------------------------------------------------------------------------------------------------------------- ---------
3 PDBORCL NORMAL
2 PDB$SEED NORMAL
4 PDB4 UNPLUGGED
5 PDB5 NORMAL
6 PDB6 NORMAL

解決方法：
SQL> DROP pluggable DATABASE pdb4;
Pluggable database dropped.

CREATE pluggable DATABASE pdb1 USING '/home/oracle/pdb4.xml' NOCOPY;
SQL> CREATE pluggable DATABASE pdb1 USING '/home/oracle/pdb4.xml' NOCOPY;
CREATE pluggable DATABASE pdb1 USING '/home/oracle/pdb4.xml' NOCOPY
*
ERROR at line 1:
ORA-65139: Mismatch between XML metadata file and data file /u01/app/oracle/oradata/orcl/PDB4/system01.dbf for value of afn (12 in the plug XML file, 11 in the data file)
原因是重新開啟原來的none-cdb後，會往裡面寫入一些資料，導致資料檔案大小和xml檔案中描述不一致。否則只能重新生成xml檔案了.

CREATE pluggable DATABASE pdb4 ;
ORA-65018: FILE_NAME_CONVERT or NOCOPY must be specified

CREATE pluggable DATABASE pdb4 USING '/home/oracle/pdb4.xml' FILE_NAME_CONVERT=('/home/oracle/','/u01/app/oracle/oradata/orcl/PDB4/')

CREATE pluggable DATABASE pdb4 USING '/home/oracle/pdb4.xml' NOCOPY;
ALTER pluggable DATABASE pdb4 OPEN;

12 可插入資料庫

相關文章