OCP-07：CDB PDB 的创建与克隆

文章大纲

在 CDB 中，数据字典表和视图定义的元数据仅存储在根目录中。
每个 PDB 都有自己的一组数据字典表和视图，用于包含在 PDB 中的数据对象。
参考：https://docs.oracle.com/database/121/ADMIN/cdb_mon.htm#ADMIN13933

创建 PDB 和应用 PDB

可以使用多种技术创建 PDB、应用程序容器和应用程序种子。

从 Scratch 创建 PDB

例子 1：不使用子句创建 PDB

SYS@orcl(CDB$ROOT)> alter session set pdb_file_name_convert='pdbseed','salpdb1';

Session altered.

SYS@orcl(CDB$ROOT)> create pluggable database salpdb1 admin user imxcai identified by 123;

Pluggable database created.

SYS@orcl(CDB$ROOT)> 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 SALPDB1                        MOUNTED

例 2：创建 PDB 并将预定义的 Oracle 角色授予 PDB 管理员

SYS@orcl(CDB$ROOT)> alter session set pdb_file_name_convert='pdbseed','salpdb2';

Session altered.

SYS@orcl(CDB$ROOT)> create pluggable database salpdb2 admin user imxcai identified by 123 roles=(dba);

Pluggable database created.

SYS@orcl(CDB$ROOT)> 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 SALPDB1                        MOUNTED
         6 SALPDB2                        MOUNTED

例 3：使用 Storage Default TableSpace Path_Prefix 和 File_Name_Convert 子句：

create pluggable database pdb3 admin user imxcai identified by oracle storage (maxsize 3g) 
default tablespace imxcai 
datafile '/u01/app/oracle/oradata/ORCL/pdb3/imxcai01.dbf' size 200m autoextend ON
path_prefix = '/u01/app/oracle/oradata/ORCL/pdb3/'
file_name_convert = ('/u01/app/oracle/oradata/ORCL/pdbseed', '/u01/app/oracle/oradata/ORCL/pdb3');

克隆本地 PDB

方法一：

SYS@orcl(CDB$ROOT)> alter pluggable database pdb2 open read only;

Pluggable database altered.

SYS@orcl(CDB$ROOT)> alter session set pdb_file_name_convert='pdb2', 'pdb4';

Session altered.

SYS@orcl(CDB$ROOT)> create pluggable database pdb4 from pdb2;

Pluggable database created.

SYS@orcl(CDB$ROOT)> show pdbs;

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

方法二：

create pluggable database pdb4 from pdb2
path_prefix = '/u01/app/oracle/oradata/ORCL/pdb4/'
file_name_convert = ('/u01/app/oracle/oradata/ORCL/pdb2/',
'/u01/app/oracle/oradata/ORCL/pdb4/')
service_name_convert = ('pdb2','pdb4') nologging;

不克隆数据：

create pluggable database pdb4 from pdb2 nodata
path_prefix = '/u01/app/oracle/oradata/ORCL/pdb4/'
file_name_convert = ('/u01/app/oracle/oradata/ORCL/pdb2/',
'/u01/app/oracle/oradata/ORCL/pdb4/');

no data 仅对 PDB 中的普通用户数据有效。

克隆远程 PDB

验证字符集与字节序是否一致：

select * from nls_database_parameters where parameter='NLS_CHARACTERSET' or parameter='NLS_LANGUAGE' or parameter='NLS_NCHAR_CHARACTERSET';

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;

在目标测创建 dblink：

vim $ORACLE_HOME/network/admin/tnsnames.ora
ORCL10 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.131.142)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


create database link ora12 connect to system identified by oracle using 'ORCL10';

测试 tnsnames ：

[oracle@oel7-host2 ~]$ tnsping ORCL10

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 13-JAN-2025 14:23:21

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel7-host1.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

源端授权：

SYS@orcl(CDB$ROOT)> grant CREATE PLUGGABLE DATABASE,cdb_dba,sysoper to system container=all;

Grant succeeded.

目标端创建 PDB：

SQL> create database link ora12 connect to system identified by oracle using 'ORCL10';

Database link created.

SQL> create pluggable database pdb2 from pdb@ora12
path_prefix = '/u01/app/oracle/oradata/ORCL/pdb2'
file_name_convert = ('/u01/app/oracle/oradata/ORCL/PDB/',
'/u01/app/oracle/oradata/ORCL/pdb2');  2    3    4

Pluggable database created

克隆非 CDB

目标端创建 dblink：

vim $ORACLE_HOME/network/admin/tnsnames.ora
NCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel7-host1.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ncdb)
    )
  )

create database link ora13 connect to system identified by oracle using 'NCDB';

创建 PDB：

create pluggable database pdb3 from ncdb@ora13

file_name_convert = ('/u01/app/oracle/oradata/NCDB/',

'/u01/app/oracle/oradata/ORCL/pdb3/');

运行 noncdb_to_pdb.sql 脚本：

alter session set container=pdb3;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Relocating a PDB

将 PDB 移动到其它 CDB，当目标 PDB 由于 alter pluggable database open 而联机时，Oracle 数据库将终止会话并关闭源 PDB。
此技术是在最少停机时间或没有停机时间的情况下移动 PDB 的最快方法，而拔出源 PDB 会导致 PDB 中断，在 relocation 期间，源 PDB 可以以 read/write 模式打开并且功能齐全。
源 CDB 和目标 CDB 的平台必须满足以下要求：

必须既有相同的字节序
源和目标平台上安装的数据库选项必须相同
如果目标 CDB 的字符集不是 AL32UTF8，则源 CDB 和目标 CDB 必须具有兼容的字符集和国家字符集
源 CDB 必须处于本地 undo 模式
PDB 名称要一致

验证是否处于本地 undo 模式：

select property_name,property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';

兼容性检查，在源端生成 PDB 的 xml 文件：

beg indbms_pdb.describe(
pdb_descr_file=>'/home/oracle/db1.xml',
pdb_name=>'pdb');
end;
/

将 db1.xml 复制到目标主机，然后进行检查：

set serveroutput on
declare
compatible constant varchar2(3):=
case dbms_pdb.check_plug_compatibility(
pdb_descr_file=>'/home/oracle/db1.xml',
pdb_name=>'pdb')
WHEN TRUE THEN 'YES'
ELSE 'NO'
end;
begin dbms_output.put_line(compatible);
end;
/

源库打开：

alter pluggable database PDB open;

目标端需要创建 dblink :

vim $ORACLE_HOME/network/admin/tnsnames.ora
MYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel7-host1.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb)
    )
  )


create database link mydb1 connect to system identified by oracle using 'MYDB';

创建 PDB：

create pluggable database db1 from db1@mydb1 relocate availability max FILE_NAME_CONVERT=('/u01/app/oracle/oradata/MYDB/DB1/','/u01/app/oracle/oradata/ORCL/DB1/');

如果出现以下提示，则表明没有权限：

源端授权：

grant create pluggable database,cdb_dba,sysoper to system container=all;

如果出现 ORA-65348 代表源目标 PDB 与目标 PDB 名称不同：

[oracle@oel7-host2 dbhome_1]$ oerr ora 65348
65348, 00000, "unable to create pluggable database"
// *Cause:  An attempt was made to relocate a pluggable database with
//          availability max clause using a target pluggable database name
//          which is different from source pluggable database name.
// *Action: Use source pluggable database name as the target pluggable database
//          name.
//

创建成功之后的状态：
目标：

源：

可以看到源处于 OPEN，目标处于 MOUNTED。
接下来启动目标中的 DB1，同时验证源 PDB 的状态：

源 PDB 的状态，由 OPEN 转为 MOUNTED：

拔出和插入 PDB

将拔出的 PDB 插入 CDB 来创建 PDB，使用描述 PDB 的 XML 元数据文件与 PDB 相关联的文件将其插入 CDB 。

示例一：使用 NOCPY 子句插入拔出的 PDB

alter pluggable database pdb1 close immediate;
alter pluggable database pdb1 unplug into '/home/oracle/pdb1.xml';
drop pluggable database pdb1 keep datafiles;

此时 PDB1 已经移除了：

重新插入 PDB1：

create pluggable database pdb1 using '/home/oracle/pdb1.xml' nocopy tempfile reuse;

示例二：使用 AS CLONE 和 NOCOPY 子句插入拔出的 PDB

alter pluggable database pdb2 close immediate;
alter pluggable database pdb2 unplug into '/home/oracle/pdb2.xml';
drop pluggable database pdb2 keep datafiles;
create pluggable database salespdb as clone using '/home/oracle/pdb2.xml' nocopy tempfile reuse;

示例三：使用 source_file_name_convert nocopy 和 storage 子句拔出和插入 PDB

首先依旧是导出 xml 文件然后删除 PDB：

alter pluggable database pdb3 close immediate;
alter pluggable database pdb3 unplug into '/home/oracle/pdb3.xml';
drop pluggable database pdb3 keep datafiles;

接下来创建数据存放目录，并将数据拷贝或移动到新位置：

mkdir /u01/app/oracle/oradata/ORCL/sales01
mv /u01/app/oracle/oradata/ORCL/pdb3/* /u01/app/oracle/oradata/ORCL/sales01

插入 PDB：

create pluggable database sales01 using '/home/oracle/pdb3.xml' source_file_name_convert=('/u01/app/oracle/oradata/ORCL/pdb3/', '/u01/app/oracle/oradata/ORCL/sales01/') nocopy storage (maxsize 5g) tempfile reuse;

示例四：使用 COPY PATH_PREFIX 和 FILE_NAME_CONVERT 子句拔出和插入 PDB

与示例三的区别在于不用手动复制数据到新的目录中。

alter pluggable database pdb1 close immediate;
alter pluggable database pdb1 unplug into '/home/oracle/orcl_pdb1.xml';
drop pluggable database pdb1 keep datafiles;

创建 PDB 目录：

mkdir /u01/app/oracle/oradata/ORCL/sales02

插入 PDB：

create pluggable database sales02 using '/home/oracle/orcl_pdb1.xml' copy path_prefix = '/u01/app/oracle/oradata/ORCL/PDB1/' file_name_convert = ('/u01/app/oracle/oradata/ORCL/PDB1','/u01/app/oracle/oradata/ORCL/sales02/');

将非 CDB 当作 PDB 插入到 CDB 中

CDB 与非 CDB 在同一台主机。

非 CDB 处于事务一致状态，并将其置于只读模式：

startup open read only

非 CDB 的 XML 文件：

begin
dbms_pdb.describe(
pdb_descr_file => '/home/oracle/ncdb.xml');end;
/

在 CDB 数据库中确认是否兼容：

SET SERVEROUTPUT ON
declare
compatible constant varchar2(3) :=
case dbms_pdb.check_plug_compatibility(pdb_descr_file =>
'/home/oracle/ncdb.xml',pdb_name => 'NCDB')
when true then 'yes'
else 'no'
end;
begin
dbms_output.put_line(compatible);
end;
/

返回 yes 说明兼容。

关闭非 CDB：

shutdown immediate

创建目录：

[oracle@oel7-host1 ~]$ mkdir /u01/app/oracle/oradata/MYDB/ncdb

插入非 CDB，将文件复制到新位置：

create pluggable database ncdb using '/home/oracle/ncdb.xml'
copy
file_name_convert = ('/u01/app/oracle/oradata/NCDB/',
'/u01/app/oracle/oradata/MYDB/ncdb/');

必须先运行脚本，才能首次打开 PDB：

alter session set container=ncdb;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

转换完成就可以启动了：