Oracle 12c 建立與刪除CDB、PDBs

panpong發表於2019-03-27
Oracle

一、 計劃多租戶環境

準備足夠的記憶體，磁碟空間；需要考慮的問題包括如下 16 個方面：

1.        PDB 數量 (max 253) 與空間計劃

2.        後臺程式數量（ processes 引數）： PDB+CDB 使用者

3.        DB_NAME ， global db name

4.        Spfile 為 CDB 和 PDB 共用， pdb 可單獨設定引數，操作 spfile 必須在 root

5.        字符集為 CDB 和 PDB 共用，推薦 al32utf8/al16utf16( 國家字符集 )

6.        時區可以 CDB 和 PDB 統一，也可以 PDB 單獨設定

7.        Db_block_size 為 CDB 內統一且不能修改

8.        Online redo log 的 block size ， redo log 和 control file 都是例項級共用

9.        設計合適的 sysaux ，分別計劃 CDB 和 PDB 的

10.     預設表空間，可分別計劃 CDB 和 PDB 預設表空間，共享臨時表空間

11.     預設臨時表空間，可為分別計劃 CDB 和 PDB

12.     Undo 表空間，每個例項只有一個 active undo 表空間

13.     規劃 Service

14.     熟悉 CDB 和 PDB 的啟動與關閉

15.     是否使用 RAC 環境

16.     不支援的特徵需要計劃避開

二、建立與刪除CDB&PDB

1.    建立 CDB

DBCA （推薦）、 create database

 

CREATE DATABASE 必須包含 ENABLE PLUGGABLE DATABASE 從句，資料庫建立時會預設建立 root 和 seed ；

 

資料檔案的位置和名字：

1.     The SEED FILE_NAME_CONVERT clause

2.     Oracle Managed Files

3.     The PDB_FILE_NAME_CONVERT initialization parameter

 

建立 CDB

CREATE DATABASE cdb_name

DATAFILE '/u01/app/oracle/oradata/newcdb/system01.dbf' SIZE 325M REUSE

SYSAUX DATAFILE '/u01/app/oracle/oradata/newcdb/sysaux01.dbf' SIZE 325M REUSE

SEED #seed container's tbs

SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

SYSAUX DATAFILES SIZE 100M

 

Example 1: Creating a CDB Without Using Oracle Managed Files

CREATE DATABASE newcdb

  USER SYS IDENTIFIED BY sys_password

  USER SYSTEM IDENTIFIED BY system_password

  LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log')

             SIZE 100M BLOCKSIZE 512,

          GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log')

             SIZE 100M BLOCKSIZE 512,

          GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log')

             SIZE 100M BLOCKSIZE 512

  MAXLOGHISTORY 1

  MAXLOGFILES 16

  MAXLOGMEMBERS 3

  MAXDATAFILES 1024

  CHARACTER SET AL32UTF8

  NATIONAL CHARACTER SET AL16UTF16

  EXTENT MANAGEMENT LOCAL

  DATAFILE '/u01/app/oracle/oradata/newcdb/system01.dbf'

    SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

  SYSAUX DATAFILE '/u01/app/oracle/oradata/newcdb/sysaux01.dbf'

    SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

  DEFAULT TABLESPACE deftbs

     DATAFILE '/u01/app/oracle/oradata/newcdb/deftbs01.dbf'

     SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

  DEFAULT TEMPORARY TABLESPACE tempts1

     TEMPFILE '/u01/app/oracle/oradata/newcdb/temp01.dbf'

     SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

  UNDO TABLESPACE undotbs1

     DATAFILE '/u01/app/oracle/oradata/newcdb/undotbs01.dbf'

     SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED

  ENABLE PLUGGABLE DATABASE

    SEED

    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/newcdb/',

                         '/u01/app/oracle/oradata/pdbseed/')

    SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

    SYSAUX DATAFILES SIZE 100M

  USER_DATA TABLESPACE usertbs

    DATAFILE '/u01/app/oracle/oradata/pdbseed/usertbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

 

Example 2: Creating a CDB Using Oracle Managed Files

parameter file:

DB_CREATE_FILE_DEST ='/u01/app/oracle/oradata'

This example sets the parameter Oracle ASM storage:

DB_CREATE_FILE_DEST = +data

 

CREATE DATABASE newcdb

USER SYS IDENTIFIED BY sys_password

USER SYSTEM IDENTIFIED BY system_password

EXTENT MANAGEMENT LOCAL

DEFAULT TABLESPACE users

DEFAULT TEMPORARY TABLESPACE temp

UNDO TABLESPACE undotbs1

ENABLE PLUGGABLE DATABASE

   SEED

   SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

   SYSAUX DATAFILES SIZE 100M;

 

執行 catcdb.sql ，安裝 CDB 元件 

@?/rdbms/admin/catcdb.sql

  SYSDBA   執行如下指令碼：

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

@?/rdbms/admin/utlrp.sql

SYSTEM 使用者執行：

@?/sqlplus/admin/pupbld.sql

 

2.    配置 EM express

SYSDBA  許可權執行：

exec DBMS_XDB_CONFIG.SETHTTPPORT( http_port_number );

exec DBMS_XDB_CONFIG.SETHTTPSPORT( https_port_number );

每個 container 必須使用一個唯一的埠，訪問方法：

 

3.    建立 PDB

Figure 38-1 Options for Creating a PDB

                                             

 

Technique

Description

Create a PDB by using the seed

Create a PDB in a CDB using the files of the seed. This   technique copies the files associated with the seed to a new location and   associates the copied files with the new PDB.

Create a PDB by cloning an existing PDB or non-CDB

Create a PDB by cloning a source PDB or non-CDB and plugging   the clone into the CDB. A source can be a PDB in the local CDB, a PDB in a   remote CDB, or a non-CDB. This technique copies the files associated with the   source to a new location and associates the copied files with the new PDB.

Create a PDB by plugging an unplugged PDB into a CDB

Create a PDB by using the XML metadata file that describes the   PDB and the files associated with the PDB to plug it into the CDB.

Create a PDB by using a non-CDB

Create a PDB by moving a non-CDB into a PDB. You can use   the  DBMS_PDB  package   to create an unplugged PDB from an Oracle Database 12 c  non-CDB.   You can then plug the unplugged PDB into the CDB.

 

1)      CREATE PLUGGABLE DATABASE 語句

a.       儲存限制

STORAGE (MAXSIZE 2G)

STORAGE (MAXSIZE UNLIMITED)

b.       PDB 檔案位置

FILE_NAME_CONVERT

DB_CREATE_FILE_DEST

PDB_FILE_NAME_CONVERT

PATH_PREFIX #pdb 檔案位置限制

SOURCE_FILE_NAME_CONVERT 、 SOURCE_FILE_DIRECTORY # 插拔 pdb 的

c.       其他

SERVICE_NAME_CONVERT # 重新命名服務名

TEMPFILE REUSE # 臨時檔案重用

USER_TABLESPACES #move non-cdb to pdb 時指定 TBS,ALL /ALL EXCEPT /NONE/list('tbs1','tbs4','tbs5')

LOGGING/ NOLOGGING # 表空間日誌記錄

STANDBYS=ALL/NONE #standby CDB

NO DATA # 克隆 pdb 時，指定不克隆資料

2)      建立 PDB 前的準備

在建立 PDB 之前，必須滿足先決條件。確保在建立 PDB 之前滿足以下先決條件 :

•CDB 必須存在。參見建立和配置 CDB 。

•CDB 必須處於讀 / 寫模式。

• 當前使用者必須是 root 容器的 Common User 。

• 當前使用者必須具有 CREATE PLUGGABLE DATABASE 系統許可權。

• 您必須為每個 PDB 確定唯一的 PDB 名稱。每個 PDB 名稱在單個 CDB 中必須是惟一的，並且每個 PDB 名稱在其例項透過特定偵聽器到達的所有 CDBs 的範圍內必須是惟一的。

3)      用 seed 建立 PDB

在使用 SNAPSHOT COPY 子句時，源 PDB 的所有資料檔案必須儲存在相同的儲存型別中。

當使用 SNAPSHOT COPY 子句建立源 PDB 的克隆且 CLONEDB 初始化引數設定為 FALSE 時，源 PDB 檔案的底層檔案系統必須支援儲存快照。這些檔案系統包括 Oracle 自動儲存管理叢集檔案系統 (Oracle ACFS) 和直接 NFS 客戶端儲存。

當你使用 SNAPSHOT COPY 來建立一個克隆， CLONEDB 初始化引數設定為 TRUE ，底層檔案系統原始碼 PDB 檔案可以是任何的本地檔案系統、網路檔案系統 (NFS) ，或叢集檔案系統，直接啟用 NFS 。但是，只要存在克隆，源 PDB 必須保持開放只讀模式。

Example 38-18 Creating a PDB Using No Clauses

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password;

Example 38-19 Creating a PDB and Granting Predefined Oracle Roles to the PDB Administrator

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password

  ROLES=(DBA);

Example 38-20 Creating a PDB Using the STORAGE, DEFAULT TABLESPACE, PATH_PREFIX, and FILE_NAME_CONVERT Clauses

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password

  STORAGE (MAXSIZE 2G)

  DEFAULT TABLESPACE sales

    DATAFILE '/disk1/oracle/dbs/salespdb/sales01.dbf' SIZE 250M AUTOEXTEND ON

  PATH_PREFIX = '/disk1/oracle/dbs/salespdb/'

  FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdbseed/', '/disk1/oracle/dbs/salespdb/');

 

4)      克隆 PDB 或者 non-CBD 建立 PDB

A.        克隆 Local PDB

Example 38-21 Cloning a Local PDB Using No Clauses

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1;

Example 38-22 Cloning a Local PDB With the PATH_PREFIX, FILE_NAME_CONVERT, and SERVICE_NAME_CONVERT Clauses

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1

  PATH_PREFIX = '/disk2/oracle/pdb2'

  FILE_NAME_CONVERT = ('/disk1/oracle/pdb1/', '/disk2/oracle/pdb2/')

  SERVICE_NAME_CONVERT = ('salesrep','salesperson','orders','orderentry')

  NOLOGGING;

Example 38-23 Cloning a Local PDB Using the FILE_NAME_CONVERT, STORAGE, and SERVICE_NAME_CONVERT Clauses

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1

  FILE_NAME_CONVERT = ('/disk1/oracle/pdb1/', '/disk2/oracle/pdb2/')

  STORAGE (MAXSIZE 2G)

  SERVICE_NAME_CONVERT = ('salesrep','salesperson','orders','orderentry');

Example 38-24 Cloning a Local PDB Without Cloning Its Data

ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 NO DATA;

ALTER PLUGGABLE DATABASE pdb2 OPEN;

 

B.        克隆遠端 PDB 或者 non-CDB

Example 38-25 Creating a PDB by Cloning a Remote PDB Using No Clauses 

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1@pdb1_link;

Example 38-26 Creating a PDB by Cloning a Remote Non-CDB 

CREATE PLUGGABLE DATABASE pdb2 FROM mydb@mydb_link;

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

5)      插入 PDB 的方法建立 PDB

 

BEGIN

  DBMS_PDB.DESCRIBE(

    pdb_descr_file => '/disk1/oracle/salespdb.xml',

    pdb_name       => 'SALESPDB');

END;

/

 

SET SERVEROUTPUT ON

DECLARE

  compatible CONSTANT VARCHAR2(3) :=

    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

           pdb_descr_file => '/disk1/usr/salespdb.xml',

           pdb_name       => 'SALESPDB')

    WHEN TRUE THEN 'YES'

    ELSE 'NO'

END;

BEGIN

  DBMS_OUTPUT.PUT_LINE(compatible);

END;

/

Example 38-27 Plugging In an Unplugged PDB Using the NOCOPY Clause

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml'

  NOCOPY

  TEMPFILE REUSE;

Example 38-28 Plugging In an Unplugged PDB Using the AS CLONE and NOCOPY Clauses

CREATE PLUGGABLE DATABASE salespdb AS CLONE USING '/disk1/usr/salespdb.xml'

  NOCOPY

  TEMPFILE REUSE;

Example 38-29 Plugging In an Unplugged PDB Using the SOURCE_FILE_NAME_CONVERT, NOCOPY, and STORAGE Clauses

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml'

  SOURCE_FILE_NAME_CONVERT = ('/disk1/oracle/sales/', '/disk2/oracle/sales/')

  NOCOPY

  STORAGE (MAXSIZE 2G)

  TEMPFILE REUSE;

Example 38-30 Plugging In an Unplugged PDB With the COPY, PATH_PREFIX, and FILE_NAME_CONVERT Clauses

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml'

  COPY

  PATH_PREFIX = '/disk2/oracle/sales/'

  FILE_NAME_CONVERT = ('/disk1/oracle/sales/', '/disk2/oracle/sales/');

 

Example 38-31 Plugging In an Unplugged PDB Using the SOURCE_FILE_NAME_CONVERT, MOVE, FILE_NAME_CONVERT, and STORAGE Clauses

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml'

  SOURCE_FILE_NAME_CONVERT = ('/disk1/oracle/sales/', '/disk2/oracle/sales/')

  MOVE

  FILE_NAME_CONVERT = ('/disk2/oracle/sales/', '/disk3/oracle/sales/')

  STORAGE (MAXSIZE 2G);

Example 38-32 Plugging In an Unplugged PDB Using the SOURCE_FILE_DIRECTORY, MOVE, FILE_NAME_CONVERT, and STORAGE Clauses

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml'

  SOURCE_FILE_DIRECTORY = '/disk2/oracle/sales/' 

  MOVE

  FILE_NAME_CONVERT = ('/disk2/oracle/sales/', '/disk3/oracle/sales/')

  STORAGE (MAXSIZE 2G);

 

6)      用 Non-CDB 建立 PDB

 

BEGIN

  DBMS_PDB.DESCRIBE(

    pdb_descr_file => '/disk1/oracle/ncdb.xml');

END;

/

 

SET SERVEROUTPUT ON

DECLARE

  compatible CONSTANT VARCHAR2(3) :=

    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

           pdb_descr_file => '/disk1/oracle/ncdb.xml',

           pdb_name       => 'NCDB')

    WHEN TRUE THEN 'YES'

    ELSE 'NO'

END;

BEGIN

  DBMS_OUTPUT.PUT_LINE(compatible);

END;

/

 

CREATE PLUGGABLE DATABASE ncdb USING '/disk1/oracle/ncdb.xml'

  COPY

  FILE_NAME_CONVERT = ('/disk1/oracle/dbs/', '/disk2/oracle/ncdb/')

  USER_TABLESPACES=('tbs3');

 

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

 

4.    Unplugging PDB

Example 38-33 Unplugging PDB salespdb

ALTER PLUGGABLE DATABASE salespdb UNPLUG INTO '/oracle/data/salespdb.xml';

5.    刪除 PDB

刪除 PDB 時，將修改 CDB 的控制檔案，以消除對刪除的 PDB 的所有引用。 與 PDB 關聯的歸檔重做日誌檔案和備份不會被刪除，但是您可以使用 Oracle Recovery Manager (RMAN) 刪除它們。

Example 38-34 Dropping PDB salespdb While Keeping Its Data Files

DROP PLUGGABLE DATABASE salespdb KEEP DATAFILES;

Example 38-35 Dropping PDB salespdb and Its Data Files

DROP PLUGGABLE DATABASE salespdb INCLUDING DATAFILES;



【參考】

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

相關文章