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

zhangsharp20發表於2018-03-26
Oracle

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>

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/196700/viewspace-2154399/，如需轉載，請註明出處，否則將追究法律責任。

相關文章