ORACLE 19C PDB从一个CDB近零停机迁移到另一个CDB

最新推荐文章于 2025-03-15 23:33:50 发布

原创最新推荐文章于 2025-03-15 23:33:50 发布 · 626 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

Oracle 专栏收录该内容

52 篇文章

订阅专栏

本文详细描述了如何在Oracle环境中，从CDB1将PDB1热迁移到CDB2，包括设置DBLINK连接、创建用户权限、模拟操作、创建新PDB以及处理可能遇到的错误。

目标：

把 PDB1从CDB1 热迁移到CDB2

前提:
1.两个CDB 开启归档
2.两个CDB 本地UNDO
3.要求用于DBLINK 的连接用户（源端），必须有CREATE PLUGGABLE DATABASE ，sysoper 权限

环境：
源端：cdb1
目标端：cdb2

操作步骤：

1. 源端创建DBLINK连接需要的用户,并授予必须的权限

登陆到源端 cdb1

[oracle@dbserver ~]$ . oraenv
ORACLE_SID = [cdb1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@dbserver ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 22 16:37:55 2023
Version 19.20.0.0.0

Copyright (c) 1982, 2022, Oracle. All rights reserved.

@ >conn / as sysdba

SYS@cdb1 >show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SYS@cdb1 >

SYS@cdb1 >create user c##relocate identified by oracle_4U;
SYS@cdb1 >grant create session,create pluggable database to c##relocate container=all;
SYS@cdb1 >grant sysoper to c##relocate container=all;

2. 目标端创建连接到源端的DBLINK

登陆到源端 cdb2
[oracle@dbserver ~]$ . oraenv
ORACLE_SID = [cdb2] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@dbserver ~]$ sqlplus /nolog
@ >conn / as sysdba

SYS@cdb2 >

SYS@cdb2 >create database link link_cdb1
2 connect to c##relocate
3 identified by oracle_4U
4 using 'cdb1'
5 ;

Database link created.

3.模拟有用户在源端PDB1上操作

登陆源端 CDB1上PDB1

[oracle@dbserver admin]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 22 16:43:15 2023
Version 19.20.0.0.0

@ >CONN SYSTEM/oracle_4U@pdb1_cdb1

SYSTEM@pdb1_cdb1 >create table test_relocate(create_date date);

SYSTEM@pdb1_cdb1 >insert into test_relocate values(sysdate);

SYSTEM@pdb1_cdb1 >

4. 使用RELOCATE创建PDB

登陆到源端 cdb2

SYS@cdb2 >create pluggable database pdb1
2 from pdb1@link_cdb1
3 relocate
4 ;

提示：使用OMF

SYS@cdb2 >show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB2 READ WRITE NO
4 PDB1 MOUNTED
SYS@cdb2 >

5.打开目标端PDB1之前，查看一下源端PDB1状态

SYS@cdb1 >show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO

而且模拟客户端可以正常执行DML操作
SYSTEM@pdb1_cdb1 >alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

SYSTEM@pdb1_cdb1 >select * from test_relocate;

CREATE_DATE
-------------------
2023-10-22 16:43:59

SYSTEM@pdb1_cdb1 >insert into test_relocate values(sysdate);

SYSTEM@pdb1_cdb1 >select * from test_relocate;

CREATE_DATE
-------------------
2023-10-22 16:43:59
2023-10-22 16:48:45

SYSTEM@pdb1_cdb1 >

6.打开目标端PDB1

SYS@cdb2 >alter pluggable database pdb1 open;

SYS@cdb2 >SHOW pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB2 READ WRITE NO
4 PDB1 READ WRITE NO
SYS@cdb2 >

7.检查源端PDB及客户端链接

--源端PDB1已经没有
SYS@cdb1 > show pdbs

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

客户端报错
SYSTEM@pdb1_cdb1 >r
1* select * from test_relocate
ERROR:
ORA-03114: not connected to ORACLE

SYSTEM@pdb1_cdb1 >conn system/oracle_4U@pdb1_cdb1

SYSTEM@pdb1_cdb1 >select * from test_relocate;

no rows selected

--发现迁移前，没有提交的数据，都回滚了。

附：

1.报错信息1：

*
ERROR at line 1:
ORA-17628: Oracle error 1031 returned by remote Oracle server
ORA-01031: insufficient privileges

解决：
grant sysoper to system container=all;

2.报错信息2：

1 create pluggable database pdb1
2 from pdb1@link_cdb1
3* relocate
create pluggable database pdb1
*
ERROR at line 1:
ORA-65122: Pluggable database GUID conflicts with the GUID of an existing
container.