｜导 语
Relocating a PDB是Oracle在12C中推出的一种新的数据迁移方式，在采用Relocate时可以使用最短的停机时间在不同的CDB直接迁移PDB。
Oracle 12.1中Relocate迁移数据时，需要源库处于read only状态，但由于12.2中 local undo 的推出，可以实现完全在线迁移，源库的PDB在read-write模式下就可以Relocate到远端CDB中，源PDB中的DML事务不会受到任何影响，整个迁移过程中不需要导出导入元数据，其迁移方式比XTTS更加简单快捷。
迁移过程中，在目标PDB Relocate完成后，源CDB和目标CDB会同时存在2个Relocate PDB，此时目标CDB中该PDB处于MOUNT状态。
当在目标CDB中的PDB OPEN时，源PDB会停止且Oracle会自动KILL掉源PDB连接的所有会话，并同步且应用源PDB的日志到目标PDB，同时也会回滚未提交的事务，应用完成后源PDB库的所有数据文件将会自动删除，目标PDB可以对外提供服务。
如果在Relocate过程中使用AVAILABILITY模式进行Relocate，新的连接请求Oracle会将其发送新PDB上，则完全实现PDB迁移的零停机。
其实Relocate的机制就是HotClone DBlink的增量恢复。
Relocate a PDB
Relocate a PDB Intoan Application Container
在使用RelocatePDB进行数据迁移时需要注意如下事项：
1.如果PDB被Relocate到的CDB的字符集不是AL32UTF8，那么源与目标字符集必须兼容。
2.源端与目标短的字节顺序必须相同。
3.连接的用户在CDB中必须拥有'CREATEPLUGGABLE DATABASE'的权限。
5.源端PDB必须为归档模式。
6.源端PDB必须是localundo模式。
7.当指定AVAILABILITY MAX字句时，要求目标PDB与源PDB名字必须保持一致。
这里演示将源CDB ora12c中的woqupdb使用Relocating的方式迁移到CDB orcl12c中，且命名为QDatapdb。
(1)源库
代码语言：javascript复制sys. ora12c>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
5WOQUPDB READ WRITENO
sys. ora12c>select file_name from cdb_data_files where con_id=5;
FILE_NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_system_fb2pjxo4_.dbf
/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_sysaux_fb2pjxo5_.dbf
/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_undotbs1_fb2pjxo6_.dbf
/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_users_fb2pjxo6_.dbf
Elapsed: 00:00:00.05
sys. ora12c>(2)在源库为Relocate的用户system赋权相关权限(createpluggable database)
代码语言：javascript复制sys. ora12c>show pdbs
sys. ora12c>grant connect,sysoper,create pluggable database to systemcontainer=all;
Grant succeeded.
Elapsed: 00:00:00.53
sys. ora12c>(3)目标库端创建dblink:woqu
代码语言：javascript复制sys. ora12c>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
5 WOQUPDB READ WRITE NO
sys. ora12c>CREATE DATABASE LINK orcl12c CONNECT TO system IDENTIFIEDBY oracle USING 'orcl12c';
Database link created.
Elapsed: 00:00:00.15
sys. ora12c>(4)源库和目标库的兼容性检查
1>检查shared undo模式
源库：
代码语言：javascript复制COL PROPERTY_NAME FOR A30
COL PROPERTY_VALUE FOR A30
SELECT property_name, property_value
FROM database_properties
WHERE property_name='LOCAL_UNDO_ENABLED';
PROPERTY_NAME PROPERTY_VALUE
------------------------------------------------------------
LOCAL_UNDO_ENABLED TRUE 目标库：
代码语言：javascript复制COLUMNproperty_name FORMAT A30
COLUMNproperty_value FORMAT A30
SELECTproperty_name, property_value
FROM database_properties
WHERE property_name = 'LOCAL_UNDO_ENABLED';
PROPERTY_NAME PROPERTY_VALUE
------------------------------------------------------------
LOCAL_UNDO_ENABLED TRUE
Elapsed:00:00:00.02
sys.orcl12c>2>检查源库是否未archivelog模式
源端：
代码语言：javascript复制sys. ora12c>ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
sys. ora12c>目标端：
代码语言：javascript复制sys. orcl12c>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
sys. orcl12c>3>检查源与目标字节顺序
源库：
代码语言：javascript复制sys. ora12c>select a.platform_id, a.platform_name, b.endian_format fromv$database a, v$transportable_platform b where a.platform_id=b.platform_id;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------- --------------
13 Linux x86 64-bit Little
Elapsed: 00:00:00.01
sys. ora12c>目标库：
代码语言：javascript复制sys. orcl12c>select a.platform_id, a.platform_name, b.endian_formatfrom v$database a, v$transportable_platform b wherea.platform_id=b.platform_id;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ --------------
13 Linux x86 64-bit Little
Elapsed: 00:00:00.00
sys. orcl12c>4>检查字符集
源库：
代码语言：javascript复制sys. ora12c>SELECT a.value || '_' || b.value || '.' || c.value NLS_LANG
FROM nls_database_parameters a, nls_database_parameters b,nls_database_parameters c
WHERE a.parameter = 'NLS_LANGUAGE' AND b.parameter = 'NLS_TERRITORY' ANDc.parameter = 'NLS_CHARACTERSET';
NLS_LANG
----------------------------------------
AMERICAN_AMERICA.AL32UTF8
Elapsed: 00:00:00.00
sys. ora12c>目标库：
代码语言：javascript复制sys. orcl12c>SELECT a.value || '_' || b.value || '.' || c.valueNLS_LANG
FROM nls_database_parameters a, nls_database_parameters b,nls_database_parameters c
WHERE a.parameter = 'NLS_LANGUAGE' AND b.parameter = 'NLS_TERRITORY' ANDc.parameter = 'NLS_CHARACTERSET';
NLS_LANG
----------------------------------------
AMERICAN_AMERICA.AL32UTF8
Elapsed: 00:00:00.00
sys. orcl12c>(5)设置目标端db_create_file_dest
代码语言：javascript复制sys. orcl12c>alter system set
db_create_file_dest='/u01/app/oracle/oradata';
System altered.
Elapsed: 00:00:00.01
sys. orcl12c> (6)在目标端使用RELOCATE进行PDB的迁移
代码语言：javascript复制sys. orcl12c>CREATE PLUGGABLE DATABASE qdatapdb FROM woqupdb@ora12cRELOCATE PATH_PREFIX = '/u01/app/oracle/oradata';
Pluggable database created.
Elapsed: 00:00:19.08
sys. orcl12c>目标日志信息：
代码语言：javascript复制2018-03-08T12:29:09.340050-05:00
CREATE PLUGGABLE DATABASE qdatapdb FROM woqupdb@ora12c RELOCATEPATH_PREFIX = '/u01/app/oracle/oradata'
2018-03-08T12:29:09.700432-05:00
Opatch validation is skipped for PDB QDATAPDB (con_id=3)
2018-03-08T12:29:26.987023-05:00
QDATAPDB(3):Endian type of dictionary set to little
****************************************************************
Pluggable Database QDATAPDB with pdb id - 3 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x00000000000000e1
****************************************************************
Applying media recovery for pdb-4099 from SCN 2571683 to SCN 2571702
Remote log information: count-1
thr-1, seq-11,logfile-/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/foreign_archivelog/WOQUPDB/2018_03_08/o1_mf_1_11_2331664873_.arc,los-2550003, nxs-18446744073709551615
QDATAPDB(3):Media Recovery Start
2018-03-08T12:29:27.618887-05:00
QDATAPDB(3):Serial Media Recovery started
2018-03-08T12:29:27.696115-05:00
QDATAPDB(3):Media Recovery Log/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/foreign_archivelog/WOQUPDB/2018_03_08/o1_mf_1_11_2331664873_.arc
2018-03-08T12:29:28.212326-05:00
QDATAPDB(3):Incomplete Recovery applied until change 2571702 time03/08/2018 12:29:27
2018-03-08T12:29:28.218004-05:00
QDATAPDB(3):Media Recovery Complete (orcl12c)
Completed:CREATE PLUGGABLE DATABASE qdatapdb FROM woqupdb@ora12c RELOCATE PATH_PREFIX ='/u01/app/oracle/oradata '(7)目标端查看Relocating的PDB状态
代码语言：javascript复制sys. orcl12c>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 QDATAPDB MOUNTED
sys. orcl12c>select pdb_id, pdb_name, status, refresh_mode,refresh_interval from cdb_pdbs;
PDB_ID PDB_NAME STATUS REFRES REFRESH_INTERVAL
---------- ------------------------------ ---------- ----------------------
2 PDB$SEED NORMAL NONE
3 QDATAPDB RELOCATING NONE
Elapsed: 00:00:00.03
sys. orcl12c>(8)检查源库PDB的状态
代码语言：javascript复制sys. ora12c>select pdb_id, pdb_name, status, refresh_mode,refresh_interval from cdb_pdbs;
PDB_ID PDB_NAME STATUS REFRES REFRESH_INTERVAL
---------- ------------------------------ ---------- ----------------------
2 PDB$SEED NORMAL NONE
5 WOQUPDB NORMAL NONE
Elapsed: 00:00:00.03
sys. ora12c>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
5 WOQUPDB READ WRITE NO
sys. ora12c> (9)源库woqupdb中创建表空间
代码语言：javascript复制sys. woqupdb>create tablespace tbs1 datafile size 20M;
Tablespace created.
Elapsed: 00:00:00.30
sys. woqupdb>(10)源库woqupdb中创建测试表t
代码语言：javascript复制sys. woqupdb>create table t tablespace tbs1 as select * fromdba_objects;
Table created.
Elapsed: 00:00:00.87
sys. woqupdb>select count(*) from t;
COUNT(*)
----------
72668
Elapsed: 00:00:00.03
sys. woqupdb>(11)目标库将QDatapdb打开
代码语言：javascript复制sys. orcl12c>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 QDATAPDB MOUNTED
sys. orcl12c>alter pluggable database qdatapdb open;
Pluggable database altered.
Elapsed: 00:00:13.54
sys. orcl12c>select pdb_id, pdb_name, status, refresh_mode,refresh_interval from cdb_pdbs;
PDB_ID PDB_NAME STATUS REFRES REFRESH_INTERVAL
---------- ------------------------------ ---------- ----------------------
2 PDB$SEED NORMAL NONE
3 QDATAPDB NORMAL NONE
Elapsed: 00:00:00.02
sys. orcl12c>目标端日志:
代码语言：javascript复制2018-03-08T12:41:19.958204-05:00
alter pluggable database qdatapdb open
2018-03-08T12:41:23.173900-05:00
Applying media recovery for pdb-4099 from SCN 2571702 to SCN 2573455
Remote log information: count-1
thr-1, seq-11, logfile-/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/foreign_archivelog/WOQUPDB/2018_03_08/o1_mf_1_11_2331664873_.arc,los-2550003, nxs-18446744073709551615
QDATAPDB(3):Media Recovery Start
2018-03-08T12:41:23.190073-05:00
QDATAPDB(3):Serial Media Recovery started
2018-03-08T12:41:23.287360-05:00
QDATAPDB(3):Media Recovery Log/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/foreign_archivelog/WOQUPDB/2018_03_08/o1_mf_1_11_2331664873_.arc
QDATAPDB(3):Successfully added datafile 71 to media recovery
QDATAPDB(3):Datafile #71: '/u01/app/oracle/oradata/ORCL12C/64FE807590395CF2E0535138A8C01D7F/datafile/o1_mf_tbs1_fb2xg3nw_.dbf'
QDATAPDB(3):Resize operation completed for file# 68, old size 368640K, newsize 378880K
2018-03-08T12:41:24.402663-05:00
QDATAPDB(3):Incomplete Recovery applied until change 2573455 time03/08/2018 12:41:20
2018-03-08T12:41:24.409434-05:00
QDATAPDB(3):Media Recovery Complete (orcl12c)
QDATAPDB(3):Autotune of undo retention is turned on.
QDATAPDB(3):Undo initialization finished serial:0 start:38039070end:38039070 diff:0 ms (0.0 seconds)
QDATAPDB(3):Database Characterset for QDATAPDB is AL32UTF8
QDATAPDB(3):Opatch validation is skipped for PDB QDATAPDB (con_id=0)
2018-03-08T12:41:25.956271-05:00
QDATAPDB(3):Opening pdb with no Resource Manager plan active
QDATAPDB(3):JIT: pid 17154 requesting stop
2018-03-08T12:41:28.851631-05:00
Applying media recovery for pdb-4099 from SCN 2573455 to SCN 2573475
Remote log information: count-1
thr-1, seq-11, logfile-/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/foreign_archivelog/WOQUPDB/2018_03_08/o1_mf_1_11_2331664873_.arc,los-2550003, nxs-18446744073709551615
QDATAPDB(3):Media Recovery Start
2018-03-08T12:41:28.852244-05:00
QDATAPDB(3):Serial Media Recovery started
2018-03-08T12:41:28.904519-05:00
QDATAPDB(3):Media Recovery Log/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/foreign_archivelog/WOQUPDB/2018_03_08/o1_mf_1_11_2331664873_.arc
2018-03-08T12:41:29.547567-05:00
QDATAPDB(3):Incomplete Recovery applied until change 2573475 time03/08/2018 12:41:26
2018-03-08T12:41:29.553124-05:00
QDATAPDB(3):Media Recovery Complete (orcl12c)
QDATAPDB(3):[17154] Successfully onlined Undo Tablespace 2.
QDATAPDB(3):Undo initialization finished serial:0 start:38043849end:38043883 diff:34 ms (0.0 seconds)
QDATAPDB(3):Database Characterset for QDATAPDB is AL32UTF8
2018-03-08T12:41:30.747145-05:00
QDATAPDB(3):[17154] Successfully onlined Undo Tablespace 2.
QDATAPDB(3):Undo initialization finished serial:0 start:38044944end:38045008 diff:64 ms (0.1 seconds)
Opatch validation is skipped for PDB QDATAPDB (con_id=3)
QDATAPDB(3):Deleting old file#15 from file$
QDATAPDB(3):Deleting old file#16 from file$
QDATAPDB(3):Deleting old file#17 from file$
QDATAPDB(3):Deleting old file#18 from file$
QDATAPDB(3):Deleting old file#19 from file$
QDATAPDB(3):Deleting old file#20 from file$
QDATAPDB(3):Deleting old file#21 from file$
QDATAPDB(3):Deleting old file#22 from file$
QDATAPDB(3):Adding new file#67 to file$(old file#15)
QDATAPDB(3):Adding new file#68 to file$(old file#16)
QDATAPDB(3):Adding new file#69 to file$(old file#17)
QDATAPDB(3):Adding new file#70 to file$(old file#18)
QDATAPDB(3):Adding new file#71 to file$(old file#22)
QDATAPDB(3):Successfully created internal service qdatapdb.example.com atopen
****************************************************************
Post plug operations are now complete.
Pluggable database QDATAPDB with pdb id - 3 is now marked as NEW.
****************************************************************
QDATAPDB(3):Pluggable database QDATAPDB dictionary check beginning
QDATAPDB(3):Pluggable Database QDATAPDB Dictionary check complete
QDATAPDB(3):Database Characterset for QDATAPDB is AL32UTF8
QDATAPDB(3):Opatch validation is skipped for PDB QDATAPDB (con_id=0)
2018-03-08T12:41:33.183843-05:00
QDATAPDB(3):Opening pdb with no Resource Manager plan active
Pluggable database QDATAPDB opened read write
Completed: alter pluggable database qdatapdb open源端日志：
代码语言：javascript复制2018-03-08T12:41:26.144319-05:00
WOQUPDB(5):JIT: pid 3741 requesting stop
WOQUPDB(5):KILL SESSION for sid=(77, 18753):
WOQUPDB(5): Reason = PDB closeimmediate
WOQUPDB(5): Mode = KILL HARD FORCE-/-/-
WOQUPDB(5): Requestor = USER(orapid = 33, ospid = 3741, inst = 1)
WOQUPDB(5): Owner = Process: USER(orapid = 57, ospid = 2560)
WOQUPDB(5): Result = ORA-0
Pluggable database WOQUPDB closed
WOQUPDB(5):JIT: pid 3741 requesting stop
Pluggable database WOQUPDB closed
2018-03-08T12:41:30.236316-05:00
Deleted Oracle managed file /u01/app/oracle/oradata/ora12c/woqupdb/ORA12C/64FE807590395CF2E0535138A8C01D7F/datafile/o1_mf_tbs1_fb2x5o8w_.dbf
Deleted file/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_users_fb2pjxo6_.dbf
Deleted file/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_temp_fb2pjxo6_.dbf
Deleted file/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_undotbs1_fb2pjxo6_.dbf
Deleted file/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_sysaux_fb2pjxo5_.dbf
Deleted file/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_system_fb2pjxo4_.dbf通过日志中我们可以看到连接woqupdb的session(77, 18753)被Oracle在后台kill掉了，并且woqupdb中的数据文件被自动删除。
(12)检查源库状态
代码语言：javascript复制sys. ora12c>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
sys. ora12c> 源库已经被删除。
(13)检查目标QDatapdb中的测试数据
代码语言：javascript复制sys. qdatapdb>select count(*) from t;
COUNT(*)
----------
72668
Elapsed: 00:00:00.04
sys. qdatapdb>select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
--------------------------------------------------------------------------------
SYSTEM /u01/app/oracle/oradata/ORCL12C/64FE8075903
95CF2E0535138A8C01D7F/datafile/o1_mf_system_fb2wq5p4_.dbf
SYSAUX /u01/app/oracle/oradata/ORCL12C/64FE8075903
95CF2E0535138A8C01D7F/datafile/o1_mf_sysaux_fb2wq5pn_.dbf
UNDOTBS1 /u01/app/oracle/oradata/ORCL12C/64FE8075903
95CF2E0535138A8C01D7F/datafile/o1_mf_undotbs1_fb2wq5po_.dbf
USERS /u01/app/oracle/oradata/ORCL12C/64FE8075903
95CF2E0535138A8C01D7F/datafile/o1_mf_users_fb2wq5pp_.dbf
TBS1 /u01/app/oracle/oradata/ORCL12C/64FE8075903
95CF2E0535138A8C01D7F/datafile/o1_mf_tbs1_fb2xg3nw_.dbf
Elapsed: 00:00:00.07
sys. qdatapdb>目标端数据与源库一致，表空间同样同步到QDATAPDB。
(14)检查源库状态
代码语言：javascript复制sys. woqu>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- --------------- ---------- ----------
2 PDB$SEED READ ONLY NO
5 PDBTEST READ WRITE NO
sys. woqu>｜作者简介
杨禹航·沃趣科技高级数据库技术专家
熟悉Oracle数据库内部机制，丰富的数据库及RAC集群层故障诊断、性能调优、OWI、数据库备份恢复及迁移经验。
