在多租户（容器）数据库中如何创建PDB：方法3 克隆远程PDB

基于版本：19c （12.2.0.3） AskScuti

创建方法：克隆远程PDB（从非当前CDB中进行远程克隆）。将 CDB2 中的 ERP1 远程克隆为 CDB1 中的 PDB6

对应路径：Creating a PDB --> Cloning --> Remotely --> From a PDB

 在容器数据库CDB里面如何创建PDB，官方文档给出了一张创建PDB技术选项图，图片如下：

内容总览

1. 概述

2. 源PDB设置只读

3. 编辑TNSNAMES.ora

4. 创建DBLINK

5. 远程克隆PDB

6. 打开PDB6

 

1. 概述

 克隆远程PDB的意思是指：被克隆的源PDB不在当前CDB内，通过DBLink方式进行克隆。

整体过程为：

1）. 将源PDB置为只读模式

2）. 连接CDB创建DBLink

3）. 远程克隆PDB

4）. 读写模式打开PDB

 

2. 源PDB设置只读

SQL> show pdbs

    CON_ID CON_NAME  OPEN MODE  RESTRICTED
---------- --------- ---------- ----------
     2 PDB$SEED      READ ONLY  NO
     3 ERP1          READ WRITE NO
     4 ERP2          READ WRITE NO

SQL> alter pluggable database erp1 close immediate;

Pluggable database altered.

SQL> alter pluggable database erp1 open read only;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME  OPEN MODE  RESTRICTED
---------- --------- ---------- ----------
     2 PDB$SEED      READ ONLY  NO
     3 ERP1          READ ONLY  NO
     4 ERP2          READ WRITE NO

 

3. 编辑TNSNAMES.ora

ERP1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ERP1)
    )
  )

 

4. 创建DBLINK

SQL> show pdbs

    CON_ID CON_NAME  OPEN MODE  RESTRICTED
---------- --------- ---------- ----------
     2 PDB$SEED      READ ONLY  NO
     3 PDB1          MOUNTED
     4 PDB2          MOUNTED
     5 PDB3          MOUNTED
     6 PDB4          MOUNTED
     7 PDB5          MOUNTED

SQL> create database link henry connect to system identified by oracle using 'ERP1';

Database link created.

 

5. 远程克隆PDB

SQL> create pluggable database pdb6 from erp1@henry create_file_dest='/u01/app/oracle/oradata/CDB1/pdb6';

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME  OPEN MODE  RESTRICTED
---------- --------- ---------- ----------
     2 PDB$SEED      READ ONLY  NO
     3 PDB1          MOUNTED
     4 PDB2          MOUNTED
     5 PDB3          MOUNTED
     6 PDB4          MOUNTED
     7 PDB5          MOUNTED
     8 PDB6          MOUNTED

如果报错如下：

SQL> create pluggable database pdb6 from erp1@henry create_file_dest='/u01/app/oracle/oradata/CDB1/pdb6';
create pluggable database pdb6 from erp1@henry create_file_dest='/u01/app/oracle/oradata/CDB1/pdb6'
*
ERROR at line 1:
ORA-17628: Oracle error 1031 returned by remote Oracle server
ORA-01031: insufficient privileges

到源数据库里面针对ERP1数据库，授权SYSTEM用户 create pluggable database 权限即可。

SQL> alter session set container=erp1;

SQL> grant create pluggable database to system;

SQL> select * from dba_sys_privs where GRANTEE='SYSTEM';

GRANTE PRIVILEGE                　　ADM COM INH
------ --------------------------- --- --- ---
SYSTEM CREATE PLUGGABLE DATABASE   NO  NO  NO
SYSTEM GLOBAL QUERY REWRITE        NO  YES YES
SYSTEM CREATE TABLE                NO  YES YES
SYSTEM DEQUEUE ANY QUEUE           YES YES YES
SYSTEM ENQUEUE ANY QUEUE           YES YES YES
SYSTEM SELECT ANY TABLE            NO  YES YES
SYSTEM MANAGE ANY QUEUE            YES YES YES
SYSTEM UNLIMITED TABLESPACE        NO  YES YES
SYSTEM CREATE MATERIALIZED VIEW    NO  YES YES

 

6. 打开PDB6

SQL> alter pluggable database pdb6 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME  OPEN MODE  RESTRICTED
---------- --------- ---------- ----------
     2 PDB$SEED      READ ONLY  NO
     3 PDB1          MOUNTED
     4 PDB2          MOUNTED
     5 PDB3          MOUNTED
     6 PDB4          MOUNTED
     7 PDB5          MOUNTED
     8 PDB6          READ WRITE NO