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

布满荆棘的人生 2022-05-31 13:50 286阅读 0赞

作者   杨禹航

出品   沃趣技术

PDB数据库的创建可以从现存的数据库中复制数据文件，包括种子容器、可插拔数据库、non-CDB数据库，创建时可以使用CREATE PLUGGABLE、RMAN、DBCA以及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

*  CDB seed (PDB$SEED)
 *  克隆已经存在的PDB
    
     Local PDB
    
     Remote PDB
 *  non-CDB数据库
 *  拔下的PDB

使用DBCA可以使用以下资源创建PDB

*  CDB seed (PDB$SEED)
 *  RMAN备份
 *  拔下的PDB

> 示例：(这里只演示使用create pluggable database命令方式创建PDB)

使用CDB seed创建PDB

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1][]

# ![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 1][]    #

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');

<table style="margin:0px 0px 10px;padding:0px;width:670px;max-width:100%;color:rgb(62,62,62);font-family:'Helvetica Neue', Helvetica, 'Hiragino Sans GB', 'Microsoft YaHei', Arial, sans-serif;font-size:16px;background-color:rgb(255,255,255);"> 
 <tbody style="margin:0px;padding:0px;max-width:100%;"> 
 <tr style="margin:0px;padding:0px;max-width:100%;"> 
 <td style="margin:0px;padding:0px 7px;max-width:100%;">ADMIN USER</td> 
 <td style="margin:0px;padding:0px 7px;border-left:none;max-width:100%;">用于执行管理任务的本地用户</td> 
 </tr> 
 <tr style="margin:0px;padding:0px;max-width:100%;"> 
 <td style="margin:0px;padding:0px 7px;border-top:none;max-width:100%;">STORAGE (MAXSIZE 2G)</td> 
 <td style="margin:0px;padding:0px 7px;border-top:none;border-left:none;max-width:100%;">指定了PDB可用的最大空间</td> 
 </tr> 
 <tr style="margin:0px;padding:0px;max-width:100%;"> 
 <td style="margin:0px;padding:0px 7px;border-top:none;max-width:100%;">DEFAULT TABLESPACE</td> 
 <td style="margin:0px;padding:0px 7px;border-top:none;border-left:none;max-width:100%;">为PDB指定默认的Tablespace</td> 
 </tr> 
 <tr style="margin:0px;padding:0px;max-width:100%;"> 
 <td style="margin:0px;padding:0px 7px;border-top:none;max-width:100%;">PATH_PREFIX</td> 
 <td style="margin:0px;padding:0px 7px;border-top:none;border-left:none;max-width:100%;">用来限制directory objects/Oracle XML/Create pfile/Oracle wallets所在的目录</td> 
 </tr> 
 <tr style="margin:0px;padding:0px;max-width:100%;"> 
 <td style="margin:0px;padding:0px 7px;border-top:none;max-width:100%;">FILE_NAME_CONVERT</td> 
 <td style="margin:0px;padding:0px 7px;border-top:none;border-left:none;max-width:100%;">设置子容器和数据文件副本的位置</td> 
 </tr> 
 </tbody> 
</table>

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

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1][]

这种方式可以通过使用当前CDB中的其他PDB，以及复制远端CDB中的PDB，还可以复制non-CDB数据库创建成PDB。

##  ##

## 丨Clone a Local PDB ##

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

## ![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 2][]    ##

在Clone本地PDB时需要注意一下几点：

*  使用的用户必须拥有’CREATE PLUGGABLE DATABASE’的权限(测试里使用sys用户)
 *  源PDB不可以是关闭状态
 *  如果CDB为shared undo，PDB必须为READ-ONLY状态
 *  如果CDB不是归档模式，那么PDB必须为READ-ONLY状态

> 说明:如果是Oracle Database 版本为12.1，那么PDB只能为READ-ONLY状态，因在12.1中undo模式，还只能选择shared undo。

这里使用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 ##

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

## ![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 3][]    ##

Clone远端PDB时需要注意以下几点：

*  如果PDB被Clone到的CDB的字符集不是AL32UTF8，那么源与目标字符集必须兼容。
 *  源端与目标端的字节顺序必须相同。
 *  连接的用户在CDB中必须拥有'CREATE PLUGGABLE DATABASE'的权限。
 *  源PDB不可以是关闭状态。
 *  如果远端CDB为shared undo，源PDB必须为READ-ONLY状态。
 *  如果远端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 ##

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

## ![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 4][]    ##

Clone远端的non-CDB时需要注意以下几点：

*  连接的用户必须拥有'CREATE PLUGGABLE DATABASE'的权限。
 *  non-CDB不可以是关闭状态。
 *  如果远端non-CDB不是归档模式，那么non-CDB必须为READ-ONLY状态。
 *  源端与目标端的字节序必须相同。
 *  如果创建的PDB来自于non-CDB，那么non-CDB的版本必须在12.1.0.2之后。
 *  如果创建的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下的对象仅仅会克隆对象的定义结构）。

测试克隆本地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下的对象中的数据依然克隆成功，而自建用户下的对象中数据并没有跟着一起克隆。

测试克隆远端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>

[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1]: /images/20220531/7650d9cee05e4c16a3775a5f1694dd7c.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 1]: /images/20220531/521c7450bb024569a1e0a5f28ae3d9a4.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 2]: /images/20220531/4fb461d040ee47eaba209a1c5f07cd9e.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 3]: /images/20220531/2763bdcdbaa44d8e830621c52b7d2cfe.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 4]: /images/20220531/882e5804b180490b8016f64ee91dc7e3.png