Oracle PDB迁移必知：RELOCATE AVAILABILITY MAX与NORMAL的隐藏差异大揭秘！

在Oracle多租户环境中，PDB（Pluggable Database）的迁移是常见的运维操作。今天我们将通过一个实际案例，深入探讨RELOCATE AVAILABILITY MAX和RELOCATE AVAILABILITY NORMAL两种迁移方式的区别。

环境准备

源库环境

-- 查看PDB状态
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PROD                           READ WRITE NO

-- 字符集检查
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

-- 数据文件位置
SQL> col file_name for a60
SQL> select con_id,file_name from cdb_data_files order by 1;
    CON_ID FILE_NAME
---------- ------------------------------------------------------------
         1 /oradata/CDB/system01.dbf
         1 /oradata/CDB/sysaux01.dbf
         1 /oradata/CDB/undotbs01.dbf
         1 /oradata/CDB/users01.dbf
         3 /oradata/CDB/prod/system01.dbf
         3 /oradata/CDB/prod/users01.dbf
         3 /oradata/CDB/prod/undotbs01.dbf
         3 /oradata/CDB/prod/sysaux01.dbf

创建迁移用户

-- 创建专用用户进行PDB迁移
SQL> create user c##pdbadm identified by oracle;
User created.

SQL> grant SYSOPER,CREATE PLUGGABLE DATABASE,connect TO c##pdbadm container=all;
Grant succeeded.

测试数据准备

SQL> alter session set container=prod;
Session altered.

SQL> create table test(id int,name varchar2(30));
Table created.

SQL> insert into test values(1,'aaa');
1 row created.

SQL> commit;
Commit complete.

目标库环境

-- 目标库PDB状态
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

-- 字符集一致性检查
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

-- 目标库数据文件
SQL> col file_name for a60
SQL> select con_id,file_name from cdb_data_files order by 1;
    CON_ID FILE_NAME
---------- ------------------------------------------------------------
         1 /oradata/CDB19C/users01.dbf
         1 /oradata/CDB19C/sysaux01.dbf
         1 /oradata/CDB19C/system01.dbf
         1 /oradata/CDB19C/undotbs01.dbf
         3 /oradata/CDB19C/pdb1/system01.dbf
         3 /oradata/CDB19C/pdb1/undotbs01.dbf
         3 /oradata/CDB19C/pdb1/sysaux01.dbf

网络配置

配置tnsnames.ora：

CDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.150)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB)
    )
  )

创建数据库链接：

SQL> CREATE PUBLIC DATABASE LINK mydblink CONNECT TO c##pdbadm IDENTIFIED BY oracle USING 'CDB';
Database link created.

SQL> select * from dual@mydblink;
D
-
X

关键差异：AVAILABILITY MAX vs NORMAL

场景一：AVAILABILITY MAX的限制

-- 尝试使用不同PDB名称进行迁移（失败）
SQL> CREATE PLUGGABLE DATABASE pdb2 FROM prod@mydblink 
     RELOCATE AVAILABILITY MAX 
     file_name_convert=('/oradata/CDB/prod/','/oradata/CDB19C/pdb2');

ERROR at line 1:
ORA-65348: unable to create pluggable database

-- 使用相同PDB名称进行迁移（成功）
SQL> CREATE PLUGGABLE DATABASE prod FROM prod@mydblink 
     RELOCATE AVAILABILITY MAX 
     file_name_convert=('/oradata/CDB/prod/','/oradata/CDB19C/prod/');

关键发现：AVAILABILITY MAX要求目标端PDB名称必须与源端保持一致。

场景二：AVAILABILITY NORMAL的灵活性

-- 使用不同PDB名称进行迁移（成功）
SQL> CREATE PLUGGABLE DATABASE pdb2 FROM prod@mydblink 
     RELOCATE AVAILABILITY NORMAL 
     file_name_convert=('/oradata/CDB/prod/','/oradata/CDB19C/pdb2');

Pluggable database created.

-- 验证迁移状态
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           MOUNTED

SQL> col pdb_name for a20; 
SQL> select pdb_id, pdb_name, status, refresh_mode,refresh_interval from cdb_pdbs;
    PDB_ID PDB_NAME            STATUS     REFRES REFRESH_INTERVAL
---------- -------------------- ---------- ------ ----------------
         3 PDB1                NORMAL     NONE
         2 PDB$SEED            NORMAL     NONE
         4 PDB2                RELOCATING NONE

关键发现：AVAILABILITY NORMAL允许目标端使用不同的PDB名称。

数据一致性验证

迁移过程中新增数据

-- 源端继续操作
SQL> alter session set container=prod;
Session altered.

SQL> insert into test values(2,'bbb');
1 row created.

SQL> commit;
Commit complete.

目标端完成迁移

-- 打开迁移后的PDB
SQL> alter pluggable database pdb2 open;
Pluggable database altered.

-- 检查最终状态
SQL> select pdb_id, pdb_name, status, refresh_mode,refresh_interval from cdb_pdbs;
    PDB_ID PDB_NAME            STATUS     REFRES REFRESH_INTERVAL
---------- -------------------- ---------- ------ ----------------
         3 PDB1                NORMAL     NONE
         2 PDB$SEED            NORMAL     NONE
         4 PDB2                NORMAL     NONE

-- 验证数据完整性
SQL> alter session set container=pdb2;
Session altered.

SQL> select * from test;
        ID NAME
---------- ------------------------------
         1 aaa
         2 bbb

源库状态确认

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO

总结与建议

通过本次实战，我们明确了两种迁移选项的核心区别：

1. RELOCATE AVAILABILITY MAX

   • 要求目标端PDB名称必须与源端保持一致
   • 适用于需要严格保持命名一致的场景

2. RELOCATE AVAILABILITY NORMAL

   • 允许目标端使用不同的PDB名称
   • 提供更大的灵活性，适用于需要重命名的迁移场景

最佳实践建议：

• 在规划迁移时，提前确定PDB命名策略
• 如果不需要重命名，推荐使用AVAILABILITY MAX以确保一致性
• 如果需要重命名，选择AVAILABILITY NORMAL并确保文件路径正确映射

两种方式都能保证数据的完整性和一致性，选择取决于具体的业务需求和命名规范要求。