PDB创建之Creating a PDB by Cloning an Existing PDB or Non-CDB

￡神魔★判官ぃ 2022-08-06 05:19 316阅读 0赞

通过克隆方式创建pdb分为  
1.克隆本地PDB  
![Center][]

2.克隆远程的PDB（从另一个cdb的pdb中克隆）

![Center 1][]

3.通过克隆一个no-cdb数据库创建pdb

![Center 2][]

下面分别利用上述三种方法克隆PDB

I.Cloning a Local PDB

1.In SQL\*Plus, ensure that the current container is the root.  
SQL> show pdbs  
  
    CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED  
\---------- ------------------------------ ---------- ----------  
         2 PDB$SEED                       READ ONLY  NO  
         3 PDB1                           READ WRITE NO  
         4 PDB2                           READ WRITE NO  
         5 PDB3                           READ WRITE NO  
SQL> show con\_name  
  
CON\_NAME  
\------------------------------  
CDB$ROOT  
SQL>  
  
2.Run the CREATE PLUGGABLE DATABASE statement, and specify the source PDB in the FROM clause. Specify other clauses when they are required.  
CREATE PLUGGABLE DATABASE pdb4 FROM pdb2   
  FILE\_NAME\_CONVERT = ('D:\\app\\oracle12c\\oradata\\ORACLE12C\\pdb2', 'D:\\app\\oracle12c\\oradata\\ORACLE12C\\pdb4');  
    
SQL> CREATE PLUGGABLE DATABASE pdb4 FROM pdb2  
  2    FILE\_NAME\_CONVERT = ('D:\\app\\oracle12c\\oradata\\ORACLE12C\\pdb2', 'D:\\app\\o  
racle12c\\oradata\\ORACLE12C\\pdb4');  
  
插接式数据库已创建。  
  
SQL> show pdbs  
  
    CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED  
\---------- ------------------------------ ---------- ----------  
         2 PDB$SEED                       READ ONLY  NO  
         3 PDB1                           READ WRITE NO  
         4 PDB2                           READ WRITE NO  
         5 PDB3                           READ WRITE NO  
         6 PDB4                           MOUNTED  
SQL>  
  
3.Open the new PDB in read/write mode.  
SQL> alter pluggable database pdb4 open;  
  
插接式数据库已变更。  
  
SQL> show pdbs  
  
    CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED  
\---------- ------------------------------ ---------- ----------  
         2 PDB$SEED                       READ ONLY  NO  
         3 PDB1                           READ WRITE NO  
         4 PDB2                           READ WRITE NO  
         5 PDB3                           READ WRITE NO  
         6 PDB4                           READ WRITE NO  
SQL>

II.Creating a PDB by Cloning a Remote PDB

1.登录到另外的cdb1数据库如下现在把cdb1pdb1克隆到 oracle12c的cdb中   
cdb1  
SQL> show pdbs  
  
    CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED  
\---------- ------------------------------ ---------- ----------  
         2 PDB$SEED                       READ ONLY  NO  
         3 CDB1PDB1                       READ WRITE NO  
SQL>  
SQL>  
oracle12c  
SQL> show pdbs  
  
    CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED  
\---------- ------------------------------ ---------- ----------  
         2 PDB$SEED                       READ ONLY  NO  
         3 PDB1                           READ WRITE NO  
         4 PDB2                           READ WRITE NO  
         5 PDB3                           READ WRITE NO  
         6 PDB4                           READ WRITE NO  
SQL>  
  
2.建立oracle12c到cdb1的pdb cdb1pdb1的dblink连接  
create public database link cdb1pdb  
  connect to system  
  identified by Wangwei123  
  using 'cdb1pdbxx';  
    
SQL> create public database link cdb1pdb  
  2    connect to system  
  3    identified by Wangwei123  
  4    using 'cdb1pdbxx';  
数据库链接已创建。  
  
SQL> select con\_id,name,open\_mode from v$pdbs@cdb1pdb;  
  
    CON\_ID NAME                           OPEN\_MODE  
\---------- ------------------------------ ----------  
         3 CDB1PDB1                       READ WRITE  
  
CREATE PLUGGABLE DATABASE pdb5 FROM CDB1PDB1@pdb1\_link  
  FILE\_NAME\_CONVERT = ('D:\\app\\oracle12c\\oradata\\ORACLE12C\\pdb2', 'D:\\app\\oracle12c\\oradata\\ORACLE12C\\pdb4'); ;  
  
3.执行create pluggable database克隆语句  
  
CREATE PLUGGABLE DATABASE pdb5 FROM CDB1PDB1@cdb1pdb  
  FILE\_NAME\_CONVERT = ('D:\\app\\oracle12c\\oradata\\cdb1\\cdb1pdb1', 'D:\\app\\oracle12c\\oradata\\ORACLE12C\\pdb5');  
    
SQL> show con\_id con\_name  
  
CON\_ID  
\------------------------------  
1  
  
CON\_NAME  
\------------------------------  
CDB$ROOT  
SQL> CREATE PLUGGABLE DATABASE pdb5 FROM CDB1PDB1@cdb1pdb  
  2    FILE\_NAME\_CONVERT = ('D:\\app\\oracle12c\\oradata\\cdb1\\cdb1pdb1', 'D:\\app\\oracle12c\\oradata\\ORACLE12C\\pdb5');  
插接式数据库已创建。  
SQL>  
4.打开新克隆的pdb5  
SQL> alter pluggable database pdb5 open;  
插接式数据库已变更。  
SQL>  
SQL> show pdbs  
  
    CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED  
\---------- ------------------------------ ---------- ----------  
         2 PDB$SEED                       READ ONLY  NO  
         3 PDB1                           READ WRITE NO  
         4 PDB2                           READ WRITE NO  
         5 PDB3                           READ WRITE NO  
         6 PDB4                           READ WRITE NO  
         7 PDB5                           READ WRITE NO  
SQL>

III.Creating a PDB by Cloning a Remote Non-CDB  
1.用dbca创建一个non-cdb数据库nocdb

2.把新建的nocdb数据库克隆到oracle12c的cdb中  
C:\\Users\\Administrator>sqlplus "/as sysdba"  
SQL\*Plus: Release 12.1.0.2.0 Production on 星期五 12月 26 09:57:00 2  
Copyright (c) 1982, 2014, Oracle.  All rights reserved.  
连接到:  
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Pr  
With the Partitioning, OLAP, Advanced Analytics and Real Applicationions  
SQL> select name,cdb,con\_id from v$database;  
NAME               CDB        CON\_ID  
\------------------ ------ ----------  
NOCDB              NO              0  
SQL>  
oracle12c cdb:  
SQL> show pdbs  
  
    CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED  
\---------- ------------------------------ ---------- ----------  
         2 PDB$SEED                       READ ONLY  NO  
         3 PDB1                           READ WRITE NO  
         4 PDB2                           READ WRITE NO  
         5 PDB3                           READ WRITE NO  
         6 PDB4                           READ WRITE NO  
         7 PDB5                           READ WRITE NO  
  
3.创建oracle12c到noncdb的dblink连接  
SQL>  create public database link noncdblink  
  2    connect to system  
  3    identified by Wangwei123  
  4    using 'nocdb';  
数据库链接已创建。  
SQL> select name,cdb,con\_id from v$database@noncdblink;  
NAME               CDB        CON\_ID  
\------------------ ------ ----------  
NOCDB              NO              0  
SQL>  
     
4.执行克隆语句  
建立相应的目录  
  CREATE PLUGGABLE DATABASE pdb6 FROM nocdb@noncdblink  
  FILE\_NAME\_CONVERT = ('D:\\app\\oracle12c\\oradata\\nocdb', 'D:\\app\\oracle12c\\oradata\\ORACLE12C\\pdb6');  
    
  SQL>   CREATE PLUGGABLE DATABASE pdb6 FROM nocdb@noncdblink  
  2    FILE\_NAME\_CONVERT = ('D:\\app\\oracle12c\\oradata\\nocdb', 'D:\\app\\oracle12c\\  
oradata\\ORACLE12C\\pdb6');  
插接式数据库已创建。  
  
SQL> show pdbs  
  
    CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED  
\---------- ------------------------------ ---------- ----------  
         2 PDB$SEED                       READ ONLY  NO  
         3 PDB1                           READ WRITE NO  
         4 PDB2                           READ WRITE NO  
         5 PDB3                           READ WRITE NO  
         6 PDB4                           READ WRITE NO  
         7 PDB5                           READ WRITE NO  
         8 PDB6                           MOUNTED  
SQL>  
  
5.执行ORACLE\_HOME/rdbms/admin/noncdb\_to\_pdb.sql（在第一次打开新克隆pdb之前执行）  
SQL> alter session set container=pdb6;  
会话已更改。  
SQL> @D:\\app\\oracle12c\\product\\12.1.0\\dbhome\_1\\RDBMS\\ADMIN\\noncdb\_to\_pdb.sql

脚本执行完后，打开pdb6报错

SQL> alter pluggable database pdb6 open;  
警告: PDB 已变更, 但出现错误。  
SQL> show pdbs  
  
    CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED  
\---------- ------------------------------ ---------- ----------  
         2 PDB$SEED                       READ ONLY  NO  
         3 PDB1                           READ WRITE NO  
         4 PDB2                           READ WRITE NO  
         5 PDB3                           READ WRITE NO  
         6 PDB4                           READ WRITE NO  
         7 PDB5                           READ WRITE NO  
         8 PDB6                           READ WRITE YES                ---限制模式打开

查看alert日志发现

Fri Dec 26 13:21:51 2014  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
WARNING: Pluggable Database PDB6 with pdb id - 8 is  
         altered with errors or warnings. Please look into  
         PDB\_PLUG\_IN\_VIOLATIONS view for more details.  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

让查看PDB\_PLUG\_IN\_VIOLATIONS 这个视图

![Center 3][]Character set mismatch: PDB character set AL32UTF8. CDB character set ZHS16GBK，字符集不匹配导致的

修改pdb的字符为ZHS16GBK

SQL> show pdbs  
    CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED  
\---------- ------------------------------ ---------- ----------  
         8 PDB6                           READ WRITE YES  
SQL>  ALTER DATABASE CHARACTER SET internal\_use ZHS16GBK;  
数据库已更改。  
SQL> show pdbs  
  
  
    CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED  
\---------- ------------------------------ ---------- ----------  
         8 PDB6                           READ WRITE YES  
SQL> shutdown immediate  
插接式数据库已关闭。  
SQL> startup  
插接式数据库已打开。

问题解决：  
SQL> show pdbs  
    CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED  
\---------- ------------------------------ ---------- ----------  
         8 PDB6                           READ WRITE NO  
SQL>

[Center]: /images/20220806/2d21ae600ce44b0f8dec39035ec70f5b.png
[Center 1]: /images/20220806/fad0400339ec42ccbe3b7d54031159db.png
[Center 2]: /images/20220806/2254d241a8514107b26c6416d3e9ce53.png
[Center 3]: /images/20220806/fb243984f03c42569a776331f6e98d2a.png