一、概述
任务 1：选择一个自包含的表空间集
可传输集中的数据库对象与可传输集之外的数据库对象之间可能存在逻辑或物理依赖关系。您只能传输自包含的表空间集，也就是说，
表空间集中的任何数据库对象都不依赖于该表空间集之外的任何数据库对象。
任务 2：生成可传输表空间集
在确保您拥有一组要传输的自包含表空间后，生成可传输表空间集。
任务 3：传输导出转储文件 将转储文件
传输到目录对象指向的DATA_PUMP_DIR目录，或您选择的任何其他目录。目标数据库必须可以访问新位置。
任务 4：传输表空间集 将表空间
的数据文件传输到目标数据库可访问的目录。
任务 5：（可选）将表空间恢复为读/写模式
使传输的表空间在源数据库中再次读/写。
任务 6：导入表空间集
要完成可传输表空间操作，请导入表空间集
二、确定表空间
1、确定传输表空间t1、t2
1.1、验证包含关系
SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('t1,t2', TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
无包含关系
测试一下存在包换关系
SQL> create index idx_objectid on t2(object_id) tablespace users;
Index created.
SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('t1,t2', TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
如果表的相应索引在表空间集之外，则不构成违规。
SQL> create index idx_empno on emp(empno,ename) tablespace t2;
Index created.
SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('t1,t2', TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
--------------------------------------------------------------------------------
ORA-39907: Index SCOTT.IDX_EMPNO in tablespace T2 points to table SCOTT.EMP in t
ablespace USERS.
SQL> drop index SCOTT.IDX_EMPNO;
Index dropped.
SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('t1,t2', TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
三、生成表空间集
设置只读
SQL> alter tablespace t1 read only;
Tablespace altered.
SQL> alter tablespace t2 read only;
Tablespace altered.
2.2、导出
SQL> create directory dump as '/home/oracle/backup';
Directory created
[oracle@19c01 ~]$ expdp \'sys\/oracle@pdbprod1 as sysdba\' dumpfile=trans%T.dmp logfile=trans%T.dmp transport_tablespaces=t1,t2
Export: Release 19.0.0.0.0 - Production on Sat Sep 17 10:23:06 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "sys/********@pdbprod1 AS SYSDBA" dumpfile=trans%T.dmp logfile=trans%T.dmp transport_tablespaces=t1,t2
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/admin/PRODCDB/dpdump/E871FE0F61161749E055020C29E19279/trans20220917.dmp
******************************************************************************
Datafiles required for transportable tablespace T1:
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/t1.dbf
Datafiles required for transportable tablespace T2:
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/t2.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/t3.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sat Sep 17 10:23:43 2022 elapsed 0 00:00:32
数据泵期间也可以使用TRANSPORT_FULL_CHECK做包含检查验证
[oracle@19c01 ~]$ expdp \'sys\/oracle@pdbprod1 as sysdba\' dumpfile=trans%U.dmp logfile=trans%U.dmp transport_tablespaces=t1,t2 TRANSPORT_FULL_CHECK=y
Export: Release 19.0.0.0.0 - Production on Sat Sep 17 10:25:48 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "sys/********@pdbprod1 AS SYSDBA" dumpfile=trans%U.dmp logfile=trans%U.dmp transport_tablespaces=t1,t2 TRANSPORT_FULL_CHECK=y
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39187: The transportable set is not self-contained, violation list is
ORA-39907: Index SYS.IDX_OBJECTID in tablespace USERS points to table SYS.T2 in tablespace T2.
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at Sat Sep 17 10:26:02 2022 elapsed 0 00:00:13
SQL> alter session set container=pdbprod1;
Session altered.
SQL> drop index IDX_OBJECTID;
Index dropped.
[oracle@19c01 ~]$ expdp \'sys\/oracle@pdbprod1 as sysdba\' dumpfile=trans%U.dmp logfile=trans%U.dmp transport_tablespaces=t1,t2 TRANSPORT_FULL_CHECK=y
Export: Release 19.0.0.0.0 - Production on Sat Sep 17 10:27:07 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "sys/********@pdbprod1 AS SYSDBA" dumpfile=trans%U.dmp logfile=trans%U.dmp transport_tablespaces=t1,t2 TRANSPORT_FULL_CHECK=y
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/admin/PRODCDB/dpdump/E871FE0F61161749E055020C29E19279/trans01.dmp
******************************************************************************
Datafiles required for transportable tablespace T1:
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/t1.dbf
Datafiles required for transportable tablespace T2:
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/t2.dbf
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/t3.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sat Sep 17 10:27:29 2022 elapsed 0 00:00:21
数据泵实用程序用于仅导出表空间的数据字典结构信息（元数据）。没有实际数据被卸载，因此即使对于大型表空间集，此操作也相对较快。
四、传输导出转储文件
[oracle@19c02 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Sep 17 10:30:07 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
SQL> show pdbs
SQL> create directory dump as '/home/oracle/backup';
Directory created.
[oracle@19c01 backup]$ scp /u01/app/oracle/admin/PRODCDB/dpdump/E871FE0F61161749E055020C29E19279/trans01.dmp oracle@19c02:/home/oracle/backup/
oracle@19c02's password:
trans01.dmp 100% 212KB 36.2MB/s 00:00
SQL> select file_name,tablespace_name from dba_data_files where tablespace_name in ('T1','T2');
FILE_NAME TABLESPACE_NAME
------------------------------------------------------------ --------------------
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/t1.dbf T1
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/t2.dbf T2
/u01/app/oracle/oradata/PRODCDB/PDBPROD1/t3.dbf T2
五、传输数据文件
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------ --------------
Linux x86 64-bit Little
转换数据文件，此时目标端为小端，假设源端也为小端，则不需要转换
方法一、
DBMS_FILE_TRANSFER包，
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object IN VARCHAR2,
source_file_name IN VARCHAR2,
destination_directory_object IN VARCHAR2,
destination_file_name IN VARCHAR2);
DBMS_FILE_TRANSFER.GET_FILE
source_directory_object IN VARCHAR2,
source_file_name IN VARCHAR2,
source_database IN VARCHAR2,
destination_directory_object IN VARCHAR2,
destination_file_name IN VARCHAR2);
source_database ：The name of a database link to the remote database where the file is located.
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object IN VARCHAR2,
source_file_name IN VARCHAR2,
destination_directory_object IN VARCHAR2,
destination_file_name IN VARCHAR2,
destination_database IN VARCHAR2);
通过这三个存储过程可以看到，该包可以用来做一下事情
及从一个磁盘组到另一个磁盘组，从asm至文件系统，文件系统至asm，从一个数据库至另一个数据库,并且自动转换大小端,并且直接通过dblink复制数据文件，省去传输数据文件的麻烦
SQL> create public database link pdbprod1 connect to system identified by oracle using 'pdbprod1';
Database link created.
SQL> select status from v$instance@pdbprod1;
STATUS
------------------------
OPEN
dbms_file_transfer.copy_file('sd','KEL.272.852423821','dd','kel.dbf')
SQL> create directory trans as '/u01/app/oracle/oradata/PRODCDB/PDBPROD1/'; --源端
Directory created.
SQL> create directory trans as '/u01/app/oracle/oradata/PRODCDB2/'; --目标端
Directory created.
--目标端
SQL> exec DBMS_FILE_TRANSFER.GET_FILE('trans','t1.dbf','pdbprod1','trans','t1.dbf');
PL/SQL procedure successfully completed.
SQL> exec DBMS_FILE_TRANSFER.GET_FILE('trans','t2.dbf','pdbprod1','trans','t2.dbf');
PL/SQL procedure successfully completed.
SQL> exec DBMS_FILE_TRANSFER.GET_FILE('trans','t3.dbf','pdbprod1','trans','t3.dbf');
PL/SQL procedure successfully completed.
方法二、使用rman的convert命令，该转换不支持undo段,
手工复制数据文件至目标端
[oracle@19c01 backup]$ scp /u01/app/oracle/oradata/PRODCDB/PDBPROD1/t*.dbf oracle@19c02:/home/oracle/backup/
oracle@19c02's password:
t1.dbf 100% 100MB 88.4MB/s 00:01
t2.dbf 100% 50MB 80.9MB/s 00:00
t3.dbf 100% 50MB 41.9MB/s 00:01
RMAN> convert datafile '/home/oracle/backup/t1.dbf' to platform 'Linux x86 64-bit' from platform 'Linux x86 64-bit' db_file_name_convert '/home/oracle/backup','/u01/app/oracle/oradata/PRODCDB2/';
Starting conversion at target at 17-SEP-22
convert datafile '/home/oracle/backup/t2.dbf' to platform 'Linux x86 64-bit' from platform 'Linux x86 64-bit' db_file_name_convert '/home/oracle/backup','/u01/app/oracle/oradata/PRODCDB2/';
convert datafile '/home/oracle/backup/t3.dbf' to platform 'Linux x86 64-bit' from platform 'Linux x86 64-bit' db_file_name_convert '/home/oracle/backup','/u01/app/oracle/oradata/PRODCDB2/';
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=237 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/backup/t1.dbf
converted datafile=/u01/app/oracle/oradata/PRODCDB2/t1.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 17-SEP-22
RMAN>
Starting conversion at target at 17-SEP-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/backup/t2.dbf
converted datafile=/u01/app/oracle/oradata/PRODCDB2/t2.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 17-SEP-22
RMAN>
Starting conversion at target at 17-SEP-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/backup/t3.dbf
converted datafile=/u01/app/oracle/oradata/PRODCDB2/t3.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 17-SEP-22
方法三、源端
[oracle@19c01 admin]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sat Sep 17 11:35:36 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRODCDB (DBID=3113239316)
RMAN> CONVERT TABLESPACE pdbprod1:t1,pdbprod1:t2 TO PLATFORM 'Linux x86 64-bit' db_file_name_convert='/u01/app/oracle/oradata/PRODCDB/PDBPROD1/','/tmp/';
Starting conversion at source at 17-SEP-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=272 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=46 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=281 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=43 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00024 name=/u01/app/oracle/oradata/PRODCDB/PDBPROD1/t1.dbf
channel ORA_DISK_2: starting datafile conversion
input datafile file number=00027 name=/u01/app/oracle/oradata/PRODCDB/PDBPROD1/t2.dbf
channel ORA_DISK_3: starting datafile conversion
input datafile file number=00028 name=/u01/app/oracle/oradata/PRODCDB/PDBPROD1/t3.dbf
converted datafile=/tmp/data_D-PRODCDB_I-3113239316_TS-T1_FNO-24_1m17uioo
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
converted datafile=/tmp/data_D-PRODCDB_I-3113239316_TS-T2_FNO-27_1n17uioo
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:02
converted datafile=/tmp/data_D-PRODCDB_I-3113239316_TS-T2_FNO-28_1o17uioo
channel ORA_DISK_3: datafile conversion complete, elapsed time: 00:00:02
Finished conversion at source at 17-SEP-22
expdp \'sys\/oracle@pdbprod1 as sysdba\' dumpfile=trans%U.dmp logfile=trans%U.dmp transport_tablespaces=t1,t2 TRANSPORT_FULL_CHECK=y directory=dump
impdp \'sys\/oracle as sysdba\' dumpfile=trans01.dmp directory=dump transport_datafiles='/u01/app/oracle/oradata/PRODCDB2/t1.dbf','/u01/app/oracle/oradata/PRODCDB2/t2.dbf','/u01/app/oracle/oradata/PRODCDB2/t3.dbf' logfile=tts02_import.log
六、源端表空间读写
SQL> alter tablespace t1 read write;
Tablespace altered.
SQL> alter tablespace t2 read write;
Tablespace altered.
七、目标端导入传输表空间集
expdp \'sys\/oracle as sysdba\' dumpfile=trans%U.dmp logfile=trans%U.dmp transport_tablespaces=t1,t2 TRANSPORT_FULL_CHECK=y
impdp \'sys\/oracle as sysdba\' dumpfile=trans%U.dmp directory=dump transport_datafiles='/u01/app/oracle/oradata/PRODCDB2/t1.dbf','/u01/app/oracle/oradata/PRODCDB2/t2.dbf','/u01/app/oracle/oradata/PRODCDB2/t3.dbf' logfile=tts_import.log
Import: Release 19.0.0.0.0 - Production on Sat Sep 17 11:18:46 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "sys/******** AS SYSDBA" dumpfile=trans%U.dmp directory=dump transport_datafiles=/u01/app/oracle/oradata/PRODCDB2/t1.dbf,/u01/app/oracle/oradata/PRODCDB2/t2.dbf,/u01/app/oracle/oradata/PRODCDB2/t3.dbf logfile=tts_import.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user SCOTT does not exist in the database
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at Sat Sep 17 11:18:50 2022 elapsed 0 00:00:03
SQL> create user scott identified by tiger account unlock;
User created.
SQL> grant dba to scott;
Grant succeeded.
[oracle@19c02 backup]$ impdp \'sys\/oracle as sysdba\' dumpfile=trans%U.dmp directory=dump transport_datafiles='/u01/app/oracle/oradata/PRODCDB2/t1.dbf','/u01/app/oracle/oradata/PRODCDB2/t2.dbf','/u01/app/oracle/oradata/PRODCDB2/t3.dbf' logfile=tts_import.log
Import: Release 19.0.0.0.0 - Production on Sat Sep 17 11:19:57 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "sys/******** AS SYSDBA" dumpfile=trans%U.dmp directory=dump transport_datafiles=/u01/app/oracle/oradata/PRODCDB2/t1.dbf,/u01/app/oracle/oradata/PRODCDB2/t2.dbf,/u01/app/oracle/oradata/PRODCDB2/t3.dbf logfile=tts_import.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sat Sep 17 11:20:19 2022 elapsed 0 00:00:22
八、小坑一枚
ORA-39123: Data Pump transportable tablespace job aborted
ORA-19722: datafile /u01/app/oracle/oradata/PRODCDB2/t1.dbf is an incorrect version
设置表空间 read only
执行传输表空间 expdp 操作
设置表空间 read write
修改表空间的对象，这个操作会修改 datafile header
将数据文件的备份副本传输到目标库
执行传输表空间 impdp 操作
在这种情况下，datafile header 和 dumpfile 内容存在差异，就会导致 ORA-19722 错误发生。
解决方法
唯一的解决方法就是再次执行expdp 操作，保证 dumpfile 和数据文件内容一致。
当执行传输表空间的expdp 操作后，对源库的数据文件进行进一步操作时，就会发生这种情况。不管是 XTTS 迁移还是传输表空间操作（两者操作是近似的），那么要注意 expdp 一定要在源库的物理备份后操作，注意操作顺序。
最后修改时间：2022-09-27 10:57:35
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
