PDB資料庫的建立可以從現存的資料庫中複製資料檔案，包括種子容器、可插拔資料庫、non-CDB資料庫，建立時可使用CREATE PLUGGABLE、RMAN、以及EM。

在12.1版本中在建立PDB時，SOURCE PDB必須處於read only狀態，在12.2版本中，因為undo local mode新特性的推出，在建立PDB時，SOURCE PDB在read write狀態，依然可以建立。
另外在12.2版本中Oracle推出了refresh PDB特性，具有對SOURCE PDB進行增量同步的功能。

使用CREATE PLUGGABLE命令可以使用以下資源建立PDB
1.CDB seed (PDB$SEED)
2.克隆已經存在的PDB
Local PDB
Remote PDB
3.non-CDB資料庫
4.拔下的PDB
使用DBCA可以使用以下資源建立PDB
1.CDB seed (PDB$SEED)
2.RMAN備份
3.拔下的PDB

示例：(這裡只演示使用create pluggable database命令方式建立PDB)

使用CDB seed建立PDB

1.在SQL*Plus中輸入CREATE PLUGGABLE DATABASE語句

(這裡建立未ypdb1的pdb，管理使用者為ypdb1)

CREATE PLUGGABLE DATABASE ypdb1 ADMIN USER ypdb1 IDENTIFIED BY oracle

STORAGE (MAXSIZE 2G)

DEFAULT TABLESPACE ypdb1

DATAFILE '/u01/app/oracle/oradata/ora12c/ypdb1/ypdb01.dbf' SIZE 100M AUTOEXTEND ON

PATH_PREFIX = '/u01/app/oracle/oradata/ora12c/ypdb1/'

FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/ora12c/pdbseed', '/u01/app/oracle/oradata/ora12c/ypdb1');

ADMIN USER	用於執行管理任務的本地使用者
STORAGE (MAXSIZE 2G)	指定了PDB可用的最大空間
DEFAULT TABLESPACE	為PDB指定預設的Tablespace
PATH_PREFIX	用來限制directory objects/Oracle XML/Create pfile/Oracle wallets所在的目錄
FILE_NAME_CONVERT	設定子容器和資料檔案副本的位置

2.語句執行完畢之後檢視建立完成的PDB：

sys. ora12c>show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 YPDB1 MOUNTED

sys. ora12c>

克隆已經存在的PDB

這種方式可以透過使用當前CDB中的其他PDB，以及複製遠端CDB中的PDB，還可以複製non-CDB資料庫建立成PDB。

Clone a Local PDB

在Clone本地PDB時需要注意一下幾點：

1.使用的使用者必須擁有’CREATE PLUGGABLE DATABASE’的許可權(測試裡使用sys使用者)

2.源PDB不可以是關閉狀態

3.如果CDB為shared undo，PDB必須為READ-ONLY狀態

4.如果CDB不是歸檔模式，那麼PDB必須為READ-ONLY狀態

PS:如果是Oracle Database 版本為12.1，那麼PDB只能為READ-ONLY狀態，因在12.1中undo模式，還只能選擇shared undo。

sys. ora12c>show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 YPDB1 READ WRITE NO

sys. ora12c>

這裡使用YPDB1做為源PDB，建立的PDB為YPDB2。

(1) 檢查是否為shared undo模式

COL PROPERTY_NAME FOR A30

COL PROPERTY_VALUE FOR A30

SELECT property_name, property_value

FROM database_properties

WHERE property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE

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

LOCAL_UNDO_ENABLED TRUE

(2) 檢查是否未archivelog模式

sys. ora12c>ARCHIVE LOG LIST

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/recovery/ora12c/arch

Oldest online log sequence 21

Next log sequence to archive 23

Current log sequence 23

sys. ora12c>

(3) 建立驗證資料

yangyuhang. ypdb1>CONN /AS SYSDBA

Connected.

sys. ora12c>conn yangyuhang/yangyuhang@ypdb1

Connected.

yangyuhang. ypdb1>SELECT COUNT(*) FROM t;

COUNT(*)

----------

22198

Elapsed: 00:00:00.01

yangyuhang. ypdb1>

(4) 執行CREATE PLUGGABLE DATABASE語句(這裡使用sys使用者連線根容器)

CREATE PLUGGABLE DATABASE ypdb2 FROM ypdb1

PATH_PREFIX = '/u01/app/oracle/oradata/ora12c/ypdb2/'

FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/ora12c/ypdb1/', '/u01/app/oracle/oradata/ora12c/ypdb2/')

SERVICE_NAME_CONVERT = ('ypdb1t','ypdb2t');

(5) 檢視建立完成的YPDB2

sys. ora12c>show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 YPDB1 READ WRITE NO

4 YPDB2 MOUNTED

sys. ora12c>SELECT pdb_id, pdb_name, con_uid, status, creation_scn, con_id FROM cdb_pdbs;

PDB_ID PDB_NAME CON_UID STATUS CREATION_SCN CON_ID

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

2 PDB$SEED 1453953285 NORMAL 213 2

3 YPDB1 2376019304 NORMAL 697945 3

4 YPDB2 3908707960 NEW 707501 4

Elapsed: 00:00:00.02

sys. ora12c>

(6) 檢查資料檔案

sys. ora12c>ALTER PLUGGABLE DATABASE YPDB2 OPEN;

Pluggable database altered.

Elapsed: 00:00:14.02

sys. ora12c>SELECT con_id, tablespace_name, file_name FROM cdb_data_files WHERE con_id=4;

CON_ID TABLESP FILE_NAME

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

4 SYSTEM /u01/app/oracle/oradata/ora12c/ypdb2/system01.dbf

4 SYSAUX /u01/app/oracle/oradata/ora12c/ypdb2/sysaux01.dbf

4 DEFTBS /u01/app/oracle/oradata/ora12c/ypdb2/deftbs01.dbf

4 USERTBS /u01/app/oracle/oradata/ora12c/ypdb2/usertbs01.dbf

4 UNDO_1 /u01/app/oracle/oradata/ora12c/ypdb2/system01_i1_undo.dbf

4 YPDB1 /u01/app/oracle/oradata/ora12c/ypdb2/ypdb01.dbf

6 rows selected.

Elapsed: 00:00:00.01

sys. ora12c>

(7) 檢查service_name

sys. ora12c>SELECT service_id, name, network_name, enabled, pdb, con_id FROM cdb_services;

SERVICE_ID NAME NETWORK_NAME ENA PDB CON_ID

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

1 SYS$BACKGROUND NO CDB$ROOT 1

2 SYS$USERS NO CDB$ROOT 1

3 ora12c.linux.com ora12c.linux.com NO CDB$ROOT 1

6 ypdb1.linux.com ypdb1.linux.com NO YPDB1 3

1 ypdb1t ypdb1t NO YPDB1 3

1 ypdb2t ypdb2t NO YPDB2 4

2 ypdb2.linux.com ypdb2.linux.com NO YPDB2 4

7 rows selected.

Elapsed: 00:00:00.00

sys. ora12c>

源PDB中的service_name已經被更改指定的service_name.

(8) 檢查驗證資料

sys. ora12c>conn yangyuhang/yangyuhang@ypdb2

Connected.

yangyuhang. ypdb2>SELECT COUNT(*) FROM t;

COUNT(*)

----------

22198

Elapsed: 00:00:00.11

yangyuhang. ypdb2>

Creating a PDB by Cloning a Remote PDB

Clone遠端PDB時需要注意以下幾點：

1.如果PDB被Clone到的CDB的字符集不是AL32UTF8，那麼源與目標字符集必須相容。

2.源端與目標端的位元組順序必須相同.

3.連線的使用者在CDB中必須擁有'CREATE PLUGGABLE DATABASE'的許可權

4.源PDB不可以是關閉狀態

5.如果遠端CDB為shared undo，源PDB必須為READ-ONLY狀態

6.如果遠端CDB不是歸檔模式，源PDB必須為READ-ONLY狀態

這裡測試將win平臺ORA12CW中的PDB(ORA12CWPDB)複製到linux的，目標PDB為PDB2。

(1) 檢視源端與目標的字符集

col parameter for a30

col value for a30

select * from nls_database_parameters where parameter='NLS_CHARACTERSET'

or parameter='NLS_LANGUAGE' or parameter='NLS_NCHAR_CHARACTERSET';

(2) 檢視源端與目標端位元組順序

col platform_name for a40

SELECT d.inst_id, t.platform_id, t.platform_name, t.endian_format, d.name FROM v$transportable_platform t, gv$database d

WHERE t.platform_name = d.platform_name;

(3) 檢視源端歸檔模式

archive log list

(4) 檢視源端undo模式

SELECT property_name, property_value

FROM database_properties

WHERE property_name='LOCAL_UNDO_ENABLED';

(5) 目標端建立dblink

CREATE DATABASE LINK ora12cw CONNECT TO system IDENTIFIED BY oracle USING 'ORA12CW';

(6) 執行CREATE PLUGGABLE DATABASE語句進行復制PDB

CREATE PLUGGABLE DATABASE pdb2 FROM ORA12CWPDB@ora12cw

PATH_PREFIX = '/u01/app/oracle/oradata/ora12cl/pdb2/'

FILE_NAME_CONVERT = ('D:\U01\APP\ORACLE\ORADATA\ORA12CW\ORA12CWPDB\', '/u01/app/oracle/oradata/ora12cl/pdb2/');

(7) 建立完成後目標端檢視PDB2

sys. ora12cl>show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

4 PDB2 MOUNTED

5 PDB1 MOUNTED

sys. ora12cl>SELECT pdb_id, pdb_name, con_uid, status, creation_scn, con_id FROM cdb_pdbs;

PDB_ID PDB_NAME CON_UID STATUS CREATION_SCN CON_ID

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

2 PDB$SEED 3409233005 NORMAL 1408788 2

4 PDB2 2218727525 NEW 1824437 4

5 PDB1 1058019921 NORMAL 1535445 5

Elapsed: 00:00:00.00

sys. ora12cl>

(8) 目標檢視資料檔案

sys. ora12cl>alter pluggable database pdb2 open;

Pluggable database altered.

Elapsed: 00:00:14.02

sys. ora12cl>SELECT con_id, tablespace_name, file_name FROM cdb_data_files WHERE con_id=4;

CON_ID TABLESPACE_NAME FILE_NAME

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

4 SYSTEM /u01/app/oracle/oradata/ora12cl/pdb2/SYSTEM01.DBF

4 SYSAUX /u01/app/oracle/oradata/ora12cl/pdb2/SYSAUX01.DBF

4 UNDOTBS1 /u01/app/oracle/oradata/ora12cl/pdb2/UNDOTBS01.DBF

4 USERS /u01/app/oracle/oradata/ora12cl/pdb2/USERS01.DBF

Elapsed: 00:00:00.00

sys. ora12cl>

克隆遠端的PDB成功。

Creating a PDB by Cloning a Non-CDB

Clone遠端的non-CDB時需要注意以下幾點：

1.連線的使用者必須擁有'CREATE PLUGGABLE DATABASE'的許可權

2.non-CDB不可以是關閉狀態

4.如果遠端non-CDB不是歸檔模式，那麼non-CDB必須為READ-ONLY狀態

5.源端與目標端的位元組序必須相同.

6.如果建立的PDB來自於non-CDB，那麼non-CDB的版本必須在12.1.0.2之後。

7.如果建立的PDB來自於non-CDB，當PDB建立完成後使用SYSDBA許可權使用者登入PDB執行noncdb_to_pdb.sql指令碼，將

這裡將源端non-CDB資料庫orcl12複製到ora12cl中，命名為PDB3。

(1)檢視non-CDB資料庫版本

SELECT * FROM v$version;

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

(2)檢視遠端non-CDB資料庫模式

SELECT dbid, name, open_mode, cdb FROM v$database;

DBID NAME OPEN_MODE CDB

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

3544959965 ORCL12 READ WRITE NO

(3)檢視non-CDB資料庫是否為歸檔模式

ARCHIVE LOG LIST

(4)檢視源端與目標端位元組順序

col platform_name for a40

SELECT d.inst_id, t.platform_id, t.platform_name, t.endian_format, d.name FROM v$transportable_platform t, gv$database d

WHERE t.platform_name = d.platform_name;

(5)目標端建立dblink

CREATE DATABASE LINK orcl12 CONNECT TO system IDENTIFIED BY oracle USING 'ORCL12';

(6)執行CREATE PLUGGABLE DATABASE語句

CREATE PLUGGABLE DATABASE pdb3 FROM orcl12@orcl12

PATH_PREFIX = '/u01/app/oracle/oradata/ora12cl/pdb3/'

FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/orcl12/', '/u01/app/oracle/oradata/ora12cl/pdb3/');

(7)PDB建立成功後檢視PDB

sys. ora12cl>show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB3 MOUNTED

4 PDB2 READ WRITE NO

5 PDB1 MOUNTED

sys. ora12cl>SELECT pdb_id, pdb_name, con_uid, status, creation_scn, con_id FROM cdb_pdbs;

PDB_ID PDB_NAME CON_UID STATUS CREATION_SCN CON_ID

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

3 PDB3 3564415452 NEW 3014651 3

2 PDB$SEED 3409233005 NORMAL 1408788 2

4 PDB2 2218727525 NORMAL 1824437 4

5 PDB1 1058019921 NORMAL 1535445 5

Elapsed: 00:00:00.00

(8)執行noncdb_to_pdb.sql指令碼(必須使用擁有SYSDBA許可權的使用者在PDB中執行)

$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

(9)檢視資料檔案

sys. ora12cl>alter pluggable database pdb3 open;

Pluggable database altered.

Elapsed: 00:00:52.88

sys. ora12cl>SELECT con_id, tablespace_name, file_name FROM cdb_data_files WHERE con_id=3;

CON_ID TABLESPACE_NAME FILE_NAME

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

3 SYSTEM /u01/app/oracle/oradata/ora12cl/pdb3/system01.dbf

3 SYSAUX /u01/app/oracle/oradata/ora12cl/pdb3/sysaux01.dbf

3 UNDOTBS1 /u01/app/oracle/oradata/ora12cl/pdb3/undotbs01.dbf

3 USERS /u01/app/oracle/oradata/ora12cl/pdb3/users01.dbf

Elapsed: 00:00:00.00

sys. ora12cl>

Cloning a PDB Without Cloning Its Data

在克隆已有的PDB或者non CDB時，我們也可以將克隆語句中加入NO DATA子句，在使用NO DATA子句時，僅僅會克隆源PDB中的模型定義，並不會克隆PDB中的資料。（Oracle資料庫內自動建立的schemas下的物件會被成功克隆，而使用者建立的schemas下的物件僅僅會克隆物件的定義結構）。

1. 測試克隆本地PDB時使用no data子句

這裡將WOQUPDB克隆為PDBTEST.

(1)源PDB:

sys. woqu>show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 WOQUPDB READ WRITE NO

sys. woqu>COL PROPERTY_NAME FOR A30

sys. woqu>COL PROPERTY_VALUE FOR A30

sys. woqu>

(2)檢查UNDO模式及歸檔模式

sys. woqu>SELECT property_name, property_value

2 FROM database_properties

3 WHERE property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE

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

LOCAL_UNDO_ENABLED TRUE

Elapsed: 00:00:00.04

sys. woqu>sys. woqu>archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 7

Next log sequence to archive 9

Current log sequence 9

sys. woqu>

(3)建立測試資料表t，注意這裡的t表位於sys使用者下：

05:51:10 sys. woqupdb>create table t as select * from dba_objects where rownum<=10;

Table created.

Elapsed: 00:00:00.10

05:52:41 sys. woqupdb>select count(*) from t;

COUNT(*)

----------

10

Elapsed: 00:00:00.01

05:52:50 sys. woqupdb>

表t中共有10條資料。

(4)建立測試資料表t_tab，注意這裡的t表位於yangyuhang使用者下：

05:51:46 yangyuhang. woqupdb>create table t_tab as select * from dba_objects where rownum<=999;

Table created.

Elapsed: 00:00:01.84

05:52:15 yangyuhang. woqupdb>select count(*) from t_tab;

COUNT(*)

----------

999

Elapsed: 00:00:00.01

05:52:19 yangyuhang. woqupdb>

(5)使用create pluggable database + NO DATA子句建立pdb:pdbtest:

sys. woqu>CREATE PLUGGABLE DATABASE pdbtest FROM woqupdb NO DATA

2 PATH_PREFIX = '/u01/app/oracle/oradata/woqu/pdbtest'

3 FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/woqu/woqupdb', '/u01/app/oracle/oradata/woqu/pdbtest');

Pluggable database created.

Elapsed: 00:00:09.35

sys. woqu>

(6)檢查克隆完成的pdbtest:

sys. woqu>show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 WOQUPDB READ WRITE NO

5 PDBTEST MOUNTED

sys. woqu>SELECT pdb_id, pdb_name, con_uid, status, creation_scn, con_id FROM cdb_pdbs;

PDB_ID PDB_NAME CON_UID STATUS CREATION_SCN CON_ID

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

3 WOQUPDB 1167267009 NORMAL 1443334 3

2 PDB$SEED 399989944 NORMAL 1408751 2

5 PDBTEST 3356573055 NEW 1956581 5

Elapsed: 00:00:00.05

sys. woqu>alter pluggable database pdbtest open;

Pluggable database altered.

Elapsed: 00:00:07.09

sys. woqu>show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 WOQUPDB READ WRITE NO

5 PDBTEST READ WRITE NO

sys. woqu>SELECT pdb_id, pdb_name, con_uid, status, creation_scn, con_id FROM cdb_pdbs;

PDB_ID PDB_NAME CON_UID STATUS CREATION_SCN CON_ID

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

3 WOQUPDB 1167267009 NORMAL 1443334 3

2 PDB$SEED 399989944 NORMAL 1408751 2

5 PDBTEST 3356573055 NORMAL 1956581 5

Elapsed: 00:00:00.02

sys. woqu>

(7)將從測試資料

sys. woqu>alter session set container=pdbtest;

Session altered.

Elapsed: 00:00:00.07

sys. woqu>show user con_id

USER is "SYS"

CON_ID

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

5

sys. woqu>select count(*) from t;

COUNT(*)

----------

10

Elapsed: 00:00:00.00

sys. woqu>select count(*) from yangyuhang.t_tab;

COUNT(*)

----------

0

Elapsed: 00:00:00.02

sys. woqu>

透過測試可以看到sys下的物件中的資料依然克隆成功，而自建使用者下的物件中資料並沒有跟著一個克隆。

2.測試克隆遠端PDB時使用no data子句

這裡將遠端PDB：PDBTEST克隆到本地，命名為ORA12CPDB2：

(1)建立DBLINK

sys. ora12c>CREATE DATABASE LINK woqu CONNECT TO system IDENTIFIED BY oracle USING 'WOQU';

Database link created.

Elapsed: 00:00:00.15

sys. ora12c>

(2)克隆PDBTEST

sys. ora12c>CREATE PLUGGABLE DATABASE ORA12CPDB2 FROM PDBTEST@WOQU NO DATA

2 PATH_PREFIX = '/u01/app/oracle/oradata/ora12c/ora12cpdb2'

3 FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/woqu/pdbtest', '/u01/app/oracle/oradata/ora12c/ora12cpdb2');

Pluggable database created.

Elapsed: 00:00:10.44

sys. ora12c>

(3)檢查克隆完成後的ORA12CPDB2

sys. ora12c>show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 ORA12CPDB READ WRITE NO

5 ORA12CPDB2 MOUNTED

sys. ora12c>SELECT pdb_id, pdb_name, con_uid, status, creation_scn, con_id FROM cdb_pdbs;

PDB_ID PDB_NAME CON_UID STATUS CREATION_SCN CON_ID

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

3 ORA12CPDB 1788418398 NORMAL 1442734 3

2 PDB$SEED 2375660446 NORMAL 1408749 2

5 ORA12CPDB2 2003741801 NEW 1959473 5

Elapsed: 00:00:00.01

sys. ora12c>

(4)驗證資料

sys. ora12c>alter pluggable database ora12cpdb2 open;

Pluggable database altered.

Elapsed: 00:00:05.24

sys. ora12c>alter session set container=ora12cpdb2;

Session altered.

Elapsed: 00:00:00.04

sys. ora12c>select count(*) from t;

COUNT(*)

----------

10

Elapsed: 00:00:00.01

sys. ora12c>select count(*) from yangyuhang.t_tab;

COUNT(*)

----------

0

Elapsed: 00:00:00.00

sys. ora12c>

Oracle 12c系列(二)｜PDB的建立

使用CDB seed建立PDB

克隆已經存在的PDB

Clone a Local PDB

Creating a PDB by Cloning a Remote PDB

Creating a PDB by Cloning a Non-CDB

Cloning a PDB Without Cloning Its Data

相關文章