19c 同版本下 relocate 迁移pdb

[TOC]

一、环境介绍

将CDB:GBKDB中的pdb: gbkpdb1 relocate 到新的CDB:ORCLCDB，并改名为newgbkpdb1

源  端19c：   GBKDB:gbkpdb1              ZHS16GBK

目标端19c：    ORCLCDB:relocatedpdb1     AL32UTF8
复制

官方文档介绍：

Relocating a PDB (oracle.com)

二、Prerequisites

This scenario assumes the following:

• The user in the local database has the CREATE PLUGGABLE DATABASE privilege in the root container.

• The remote CDB is in local undo mode.

• The remote and local CDBs are in ARCHIVELOG mode.

• The common user in the remote CDB to whom the database link connects has the CREATE PLUGGABLE DATABASE, SESSION, and SYSOPER privilege.

• The local and remote CDBs have the same options installed.

• If the character set of the destination CDB is not AL32UTF8, then the source CDB and destination CDB must have compatible character sets and national character sets.If the character set of the destination CDB is AL32UTF8, then this requirement does not apply.

三、实施步骤

方法1：使用命令行

1.源库\目标端为归档模式(必须)

-- 源端：
SYS@GBKDB> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/product/19c/dbhome_1/dbs/arch
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8

-- 目标端：
SYS@ORCLCDB> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     72
Next log sequence to archive   74
Current log sequence           74
复制

2.源库赋予dblink用户权限

SYS@GBKDB> grant connect, sysoper, create pluggable database to SYSTEM container=all;

Grant succeeded.
复制

3.目标端：在CDB创建dblink

-- 这里的DBLINK是连接到源库的CDB，而非PDB
SYS@ORCLCDB> create public database link link_gbkdb connect to system identified by oracle using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.0.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = GBKDB)))';

Database link created.


--测试dblink
SYS@ORCLCDB> select * from dual@link_GBKDB;

D
-
X
复制

4.目标端执行 relocate pdb

SYS@ORCLCDB> create pluggable database relocatedpdb1  from gbkpdb1@link_gbkdb relocate  FILE_NAME_CONVERT=('/opt/oracle/oradata/GBKDB/gbkpdb1/','/opt/oracle/oradata/ORCLCDB/relocatedpdb1/');

Pluggable database created.
复制

5.检查PDB状态

-- 源端：
SYS@GBKDB> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 GBKPDB1                        READ WRITE NO
         
-- 目标端：
SYS@ORCLCDB> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 RELOCATEDPDB1                  MOUNTED
复制

结论：此时源库和目标库都存在PDB，但是源库为READ WRITE，目标库为MOUNT状态。 

6.源端继续做DML等操作

SYS@GBKDB> alter session set container=GBKPDB1;

Session altered.

SYS@GBKDB> create table test1 as select * from dba_objects;

Table created.

SYS@GBKDB> create table test2 as select * from dba_objects;

Table created.

SYS@GBKDB> delete from test1;

72367 rows deleted.

SYS@GBKDB> commit;

Commit complete.

SYS@GBKDB> delete from test2;

72368 rows deleted.

-- 这个删除delete test2的事务不提交。。
复制

7.目标端打开PDB

SYS@ORCLCDB> alter pluggable database relocatedpdb1 open;

Pluggable database altered.
复制

 目标端open pdb期间观察源端情况：

-- 再开一个会话，观察目标端open pdb期间，源端pdb状态，可以看到
SYS@GBKDB> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 GBKPDB1                        MOUNTED
SYS@GBKDB> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
 
-- 观察源端alert 日志：
Pluggable database GBKPDB1 closed
GBKPDB1(3):JIT: pid 7313 requesting stop
GBKPDB1(3):Buffer Cache flush started: 3
2022-10-25T14:34:02.272746+08:00
GBKPDB1(3):Buffer Cache flush finished: 3
Pluggable database GBKPDB1 closed
2022-10-25T14:34:07.470170+08:00
Deleted file /opt/oracle/oradata/GBKDB/gbkpdb1/temp012022-10-25_13-58-45-658-PM.dbf
Deleted file /opt/oracle/oradata/GBKDB/gbkpdb1/undotbs01.dbf
Deleted file /opt/oracle/oradata/GBKDB/gbkpdb1/sysaux01.dbf
Deleted file /opt/oracle/oradata/GBKDB/gbkpdb1/system01.dbf
复制

目标端open pdb期间观察目标端情况：

-- 目标端：
SYS@ORCLCDB> show pdbs;

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


-- 观察目标端alert 日志：
2022-10-26T13:26:36.850761+08:00
alter  pluggable database relocatedpdb1 open
2022-10-26T13:26:39.009281+08:00
Applying media recovery for pdb-4099 from SCN 9743426 to SCN 9743464
Remote log information: count-1
thr-1, seq-8, logfile-/opt/oracle/product/19c/dbhome_1/dbs/archparlog_1_8_47acd5bc_1119016334.arc, los-9688135, nxs-18446744073709551615
RELOCATEDPDB1(6):Media Recovery Start
2022-10-26T13:26:39.010532+08:00
RELOCATEDPDB1(6):Serial Media Recovery started
RELOCATEDPDB1(6):max_pdb is 7
2022-10-26T13:26:39.055190+08:00
RELOCATEDPDB1(6):Media Recovery Log /opt/oracle/product/19c/dbhome_1/dbs/archparlog_1_8_47acd5bc_1119016334.arc
2022-10-26T13:26:39.711910+08:00
RELOCATEDPDB1(6):Incomplete Recovery applied until change 9743464 time 10/26/2022 13:26:36
2022-10-26T13:26:39.713299+08:00
RELOCATEDPDB1(6):Media Recovery Complete (ORCLCDB)
2022-10-26T13:26:40.112649+08:00
RELOCATEDPDB1(6):Autotune of undo retention is turned on. 
RELOCATEDPDB1(6):Undo initialization finished serial:0 start:9815812 end:9815812 diff:0 ms (0.0 seconds)
2022-10-26T13:26:40.828956+08:00
Violations: Type: 2, Count: 1
RELOCATEDPDB1(6):***************************************************************
RELOCATEDPDB1(6):WARNING: Pluggable Database RELOCATEDPDB1 with pdb id - 6 is
RELOCATEDPDB1(6):         altered with errors or warnings. Please look into
RELOCATEDPDB1(6):         PDB_PLUG_IN_VIOLATIONS view for more details.
RELOCATEDPDB1(6):***************************************************************
2022-10-26T13:26:41.492170+08:00
RELOCATEDPDB1(6):Opening pdb with no Resource Manager plan active
2022-10-26T13:26:41.832723+08:00
RELOCATEDPDB1(6):JIT: pid 15734 requesting stop
RELOCATEDPDB1(6):Buffer Cache flush started: 6
RELOCATEDPDB1(6):Buffer Cache flush finished: 6
2022-10-26T13:26:45.059369+08:00
Applying media recovery for pdb-4099 from SCN 9743464 to SCN 9744152
Remote log information: count-1
thr-1, seq-8, logfile-/opt/oracle/product/19c/dbhome_1/dbs/archparlog_1_8_47acd5bc_1119016334.arc, los-9688135, nxs-18446744073709551615
RELOCATEDPDB1(6):Media Recovery Start
2022-10-26T13:26:45.065248+08:00
RELOCATEDPDB1(6):Serial Media Recovery started
RELOCATEDPDB1(6):max_pdb is 7
2022-10-26T13:26:45.099956+08:00
RELOCATEDPDB1(6):Media Recovery Log /opt/oracle/product/19c/dbhome_1/dbs/archparlog_1_8_47acd5bc_1119016334.arc
2022-10-26T13:26:45.827732+08:00
RELOCATEDPDB1(6):Incomplete Recovery applied until change 9744152 time 10/26/2022 13:26:42
2022-10-26T13:26:45.833477+08:00
RELOCATEDPDB1(6):Media Recovery Complete (ORCLCDB)
RELOCATEDPDB1(6):Undo initialization recovery: err:0 start: 9821215 end: 9821222 diff: 7 ms (0.0 seconds)
RELOCATEDPDB1(6):[15734] Successfully onlined Undo Tablespace 2.
RELOCATEDPDB1(6):Undo initialization online undo segments: err:0 start: 9821222 end: 9821245 diff: 23 ms (0.0 seconds)
RELOCATEDPDB1(6):Undo initialization finished serial:0 start:9821215 end:9821261 diff:46 ms (0.0 seconds)
RELOCATEDPDB1(6):Database Characterset for RELOCATEDPDB1 is ZHS16GBK
RELOCATEDPDB1(6):Buffer Cache flush started: 6
RELOCATEDPDB1(6):Buffer Cache flush finished: 6
2022-10-26T13:26:46.392854+08:00
RELOCATEDPDB1(6):Undo initialization recovery: err:0 start: 9821744 end: 9821746 diff: 2 ms (0.0 seconds)
RELOCATEDPDB1(6):[15734] Successfully onlined Undo Tablespace 2.
RELOCATEDPDB1(6):Undo initialization online undo segments: err:0 start: 9821746 end: 9821855 diff: 109 ms (0.1 seconds)
RELOCATEDPDB1(6):Undo initialization finished serial:0 start:9821744 end:9821857 diff:113 ms (0.1 seconds)
RELOCATEDPDB1(6):Deleting old file#34 from file$ 
RELOCATEDPDB1(6):Deleting old file#35 from file$ 
RELOCATEDPDB1(6):Deleting old file#36 from file$ 
RELOCATEDPDB1(6):Adding new file#74 to file$(old file#34).             fopr-1, newblks-34560, oldblks-19200
RELOCATEDPDB1(6):Adding new file#75 to file$(old file#35).             fopr-1, newblks-39680, oldblks-15360
RELOCATEDPDB1(6):Adding new file#76 to file$(old file#36).             fopr-1, newblks-12800, oldblks-12800
RELOCATEDPDB1(6):Successfully created internal service RELOCATEDPDB1 at open
****************************************************************
Post plug operations are now complete.
Pluggable database RELOCATEDPDB1 with pdb id - 6 is now marked as NEW.
****************************************************************
RELOCATEDPDB1(6):Pluggable database RELOCATEDPDB1 dictionary check beginning
RELOCATEDPDB1(6):Pluggable Database RELOCATEDPDB1 Dictionary check complete
RELOCATEDPDB1(6):Database Characterset for RELOCATEDPDB1 is ZHS16GBK
2022-10-26T13:26:47.129500+08:00
Violations: Type: 2, Count: 3
RELOCATEDPDB1(6):***************************************************************
RELOCATEDPDB1(6):WARNING: Pluggable Database RELOCATEDPDB1 with pdb id - 6 is
RELOCATEDPDB1(6):         altered with errors or warnings. Please look into
RELOCATEDPDB1(6):         PDB_PLUG_IN_VIOLATIONS view for more details.
RELOCATEDPDB1(6):***************************************************************
2022-10-26T13:26:48.488620+08:00
RELOCATEDPDB1(6):Opening pdb with no Resource Manager plan active
RELOCATEDPDB1(6):joxcsys_required_dirobj_exists: directory object exists with required path /opt/oracle/product/19c/dbhome_1/javavm/admin/, pid 15734 cid 6
Pluggable database RELOCATEDPDB1 opened read write
Completed: alter  pluggable database relocatedpdb1 open
复制

 结论：

 1.目标端open pdb期间，源库的PDB先变成mount状态，最后被自动删除了。

 2. 目标端PDB打开的过程中，会应用源库redo，并且应用 undo 数据回滚未提交的事务

8.查看数据

-- 目标端：
SYS@ORCLCDB> alter session set container=RELOCATEDPDB1;

Session altered.

SYS@ORCLCDB> select count(*) from test1;

  COUNT(*)
----------
         0

SYS@ORCLCDB> select count(*) from test2;

  COUNT(*)
----------
     72359


-- 源端：
SYS@GBKDB> delete from test1 where rownum =1;
delete from test1 where rownum =1
*
ERROR at line 1:
ORA-01089: immediate shutdown or close in progress - no operations are permitted
Process ID: 11399
Session ID: 57 Serial number: 11702


SYS@GBKDB> select count(*) from test2;
select count(*) from test2
            *
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 7005
Session ID: 65 Serial number: 39837
复制

结论：目标端PDB打开后，可以查询到最新的数据，源端原会话session中断。 

方法2：使用dbca

  将CDB:GBKDB中的pdb: gbkpdb2 relocate 到新的CDB:ORCLCDB，并改名为newgbkpdb2 

源  端19c：   GBKDB:gbkpdb2              ZHS16GBK

目标端19c：    ORCLCDB:relocatedpdb2     AL32UTF8
复制

1.命令介绍

dbca方式官方文档描述：

./dbca -silent 
-relocatePDB
-sourceDB ORCLCDB                          --目标端cdb名称
-remotePDBName gbkpdb2                     --源端pdb名称
-remoteDBConnString 172.17.0.2:1521/GBKDB  --连接源端字符串
-remoteDBSYSDBAUserName sys                --源端拥有sysdba权限的用户
-remoteDBSYSDBAUserPassword oracle         --源端拥有sysdba权限的用户密码
-dbLinkUsername system                     --dblink用户名，需要拥有相应权限
-dbLinkUserPassword oracle                 --dblink用户名密码
-sysDBAUserName sys                        --目标端拥有sysdba权限的用户
-sysDBAPassword oracle                     --目标端拥有sysdba权限的用户密码
-pdbName relocatedpdb2                     --目标端relocate后dblink名称

-- 测试用户名密码、字符串是否正确
-- 连接源端CDB
[oracle@ora19c ~]$ sqlplus sys/oracle@172.17.0.2:1521/GBKDB as sysdba

-- 经过测试，在目标端服务器上执行时候，因为是本地认证方式，所以下面这2个参数可以不要，或者说sysDBAPassword写错了也没关系。
-sysDBAUserName sys
-sysDBAPassword oracle


-- 获取更多帮助信息
[oracle@ora19c ~]$ dbca -relocatePDB -help
复制

2.dbca命令relocate PDB

-- 设置错误的的ORACLE_SID，并不影响
[oracle@ora19c ~]$ export ORACLE_SID=test
[oracle@ora19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 26 11:27:05 2022
Version 19.3.0.0.0

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

Connected to an idle instance.

SYS@test> 

[oracle@ora19c ~]$ dbca -silent \
> -relocatePDB \
> -sourceDB ORCLCDB \
> -remotePDBName gbkpdb2 \
> -remoteDBConnString 172.17.0.2:1521/GBKDB \
> -remoteDBSYSDBAUserName sys \
> -remoteDBSYSDBAUserPassword oracle \
> -dbLinkUsername system \
> -dbLinkUserPassword oracle \
> -sysDBAUserName sys \
> -sysDBAPassword oracle \
> -pdbName relocatedpdb2
Prepare for db operation
50% complete
Create pluggable database using relocate PDB operation
100% complete
Pluggable database "relocatedpdb2" plugged successfully.
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/relocatedpdb2/ORCLCDB4.log" for further details.

[oracle@ora19c ~]$ export ORACLE_SID=ORCLCDB
[oracle@ora19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 26 11:15:33 2022
Version 19.3.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SYS@ORCLCDB> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 RELOCATEDPDB2                  READ WRITE NO
         5 RELOCATEDPDB1                  READ WRITE NO
复制

3. 常见错误

3.1 dblink已存在

-- dbca执行过程中，会自动创建dblink，如果第一次执行报错后，重新执行需要先删除dblink
[FATAL] [DBT-19405] Database link (RELOCATEDPDB2_CLONE_LINK) is already exists.

--解决办法：
SYS@ORCLCDB> drop database link RELOCATEDPDB2_CLONE_LINK;
Database link dropped.
复制

3.2 sourceDB指定参数错误

-- sourceDB参数指定错误，数据库不存在
[FATAL] [DBT-05511] Specified database (testdb) does not exist.
   ACTION: Provide an existing database.
   
-- 解决办法：
设置正确的参数，注意sourceDB设置的是目标端的CDB名称，而不是源端。
复制

3.3 密码错误

-- 连接源端CDB remoteDBSYSDBAUserPassword密码参数指定错误
[FATAL] [DBT-05514] Failed to connect to the database (172.17.0.2:1521/GBKDB).
   CAUSE: Specified connection string (172.17.0.2:1521/GBKDB) or SYSDBA credential is not valid to connect to the database.
   ACTION: Specify a valid connection string or SYSDBA credential to connect to the database.
   
--解决办法：
设置正确的密码，连接源端CDB，通过sqlplus手动连接验证。
复制

3.4 执行sql 报错

-- file_name_convert=NONE导致执行create pluggable database 报错
[FATAL] [DBT-05505] Unable to execute sql script: (CREATE PLUGGABLE DATABASE relocatedpdb2 FROM gbkpdb2@relocatedpdb2_CLONE_LINK RELOCATE file_name_convert=NONE ).
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/relocatedpdb2/ORCLCDB1.log" for further details.

--解决办法：
-- 手动设置pdb_file_name_convert
alter system set PDB_FILE_NAME_CONVERT='/oradata/GBKDB/gbkpdb2/','/oradata/ORCLCDB/relocatedpdb2/';
复制

总结

目标库使用 create pluggable database relocate 选项：

1. 源库pdb会一直是read-write open 状态，直到目标端open pdb，源PDB上的用户DML事务都不会有任何影响。
2. 当目标库create pdb relocate完成时，会在源CDB和目标CDB同时存在 2 个relocate的PDB，只不过目标CDB中该PDB是 mount 状态，源CDB中pdb仍然是read-write open 状态。
3. 当目标库PDB执行open read-write 时，源库的DML等操作生成的redo日志会用作后期的 PDB 切换，并且此时源 PDB 会暂停，并且 KILL 掉源 PDB 库连接的会话，同时，目标库同步并应用源库redo ，并且应用 undo 数据回滚未提交的事务，当应用完成后源 pdb 库将自动删除，包含所有数据文件。
4. 12.2后，如果是低版本pdb relocate到高版本中，也支持跑$ORACLE_HOME/bin/dbupgrade 升级脚本。