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

最新推荐文章于 2024-08-15 09:09:59 发布

wangwei

最新推荐文章于 2024-08-15 09:09:59 发布

阅读量1.7k

点赞数

分类专栏： oracle12c

本文链接：https://blog.csdn.net/weiwangsisoftstone/article/details/42143419

版权

oracle12c 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

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

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

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

下面分别利用上述三种方法克隆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 这个视图

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>