前提条件:
1.源库的表空间为只读模式。
2.源库和目标库数据库字符集相同、国家字符集相同。
2.如果字节序不相同(大端、小端)，则需要转换。
3.是自包含的
4.system表空间不能传输，或者，用户SYS拥有的对象。
select * from v$transportable_platform;
select platform_name from v$database;

1.源库准备

创建数据目录

  1. mkdir /opt/oracle/mydump -p
  2. create directory   mydump  as '/opt/oracle/mydump'; 
  3. grant read on directory mydump to public;
  4. grant write  on directory mydump  to public;

创建被传输的表空间

  1. SQL> create   tablespace tt1   datafile;                                                                   
  3. Tablespace created.                                                                                        
  5. SQL> create   tablespace tt2   datafile;                                                                   
  7. Tablespace created.

创建管理用户

  1. SQL> create   user tts identified by "tts" default tablespace tt1;  
  2. SQL> grant  connect,resource,dba to tts;

创建数据

  2. SQL> conn tts                                                                                              
  3. Enter password:                                                                                            
  4. Connected.                                                                                                 
  5. SQL> create table tbs01 as select * from dba_objects;                                                      
  7. Table created.                                                                                             
  9. SQL> create index idx_obj on tbs01(object_id) tablespace tt2;                                              
  11. Index created.

执行关联检查

  1. exec sys.dbms_tts.transport_set_check('tt1',TRUE,TRUE); 
  2. select * from transport_set_violations;

表空间设置只读

  1. alter tablespace tt1 read only;
  2. alter tablespace tt2 read only;
  3. select tablespace_name,status from dba_tablespaces where status='READ ONLY';

expdp导出

  1. expdp system/19961216wpp  directory=mydump dumpfile=tts.dmp transport_tablespaces=tt1,tt2  transport_full_check=y  logfile=tts.log;
  2. ------------------------------------------------------------------------------------------------------------------------------
  3. [oracle@orcltest admin]$ expdp system/19961216wpp  directory=mydump dumpfile=tts.dmp transport_tablespaces=tt1,tt2  transport_full_check=y  logfile=tts.log;
  5. Export: Release 18.0.0.0.0 - Production on Thu Oct 3 20:17:17 2019
  6. Version 18.3.0.0.0
  8. Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
  10. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
  11. Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=mydump dumpfile=tts.dmp transport_tablespaces=tt1,tt2 transport_full_check=y logfile=tts.log 
  12. Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/INDEX_STATISTICS
  13. Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
  14. Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
  15. Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
  16. Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
  17. Processing object type TRANSPORTABLE_EXPORT/TABLE
  18. Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
  19. Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
  20. ******************************************************************************
  21. Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  22.   /opt/oracle/mydump/tts.dmp
  23. ******************************************************************************
  24. Datafiles required for transportable tablespace TT1:
  25.   /opt/oracle/oradata/ORCL/datafile/o1_mf_tt1_gscpj63c_.dbf
  26. Datafiles required for transportable tablespace TT2:
  27.   /opt/oracle/oradata/ORCL/datafile/o1_mf_tt2_gscpjbbq_.dbf
  28. Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu Oct 3 20:17:38 2019 elapsed 0 00:00:20

2.目标库准备

1.创建数据库目录

2.创建用户

  1. create   user tts identified by "tts" ;
  2. grant  connect.resource,dba to tts;

3.cp 源库表空间到目标库

  1. /opt/oracle/oradata/ORCL/datafile/o1_mf_tt2_gscpjbbq_.dbf
  2. /opt/oracle/oradata/ORCL/datafile/o1_mf_tt1_gscpj63c_.dbf

4.目标端数据文件转换

如果开启了OMF，就先修改数据文件名在转换 mv o1mf_tt1_h542ff8k.dbf tt1.dbf

  1.  convert  datafile '/opt/oracle/tts/tt1.dbf'  to platform='Linux x86 64-bit' DB_FILE_NAME_CONVERT '/opt/oracle/tts','/opt/oracle/oradata/orcl';

5.导入数据

  1. impdp system/19961216wpp directory=mydump dumpfile=tts.dmp transport_datafiles=/opt/oracle/oradata/ORCL/datafile/o1_mf_tt1_gscpj63c_.dbf,/opt/oracle/oradata/ORCL/datafile/o1_mf_tt2_gscpjbbq_.dbf logfile=ttsimp.log

6.修改表空间为读写

  1. alter  tablespace   tt1 read write;
  2. alter  tablespace   tt2 read write;

7.修改表空间用户

  1. alter  user tts default tablespace   tt1;

3.tts高级技巧（创建截至特定scn的可传输表空）

1.源库操作基本与上面相同
2.查看当前scn select current_scn from v$database;
3.除scn之外也可以使用日志序列号和还原点

  1.  rman target  /
  3.  transport tablespace tt1,tt2 
  4.  tablespace destination '/opt/oracle/tts'
  5.  auxiliary destination '/opt/oracle/auxiliary'
  6.  datapump directory mydump 
  7.  dump file 'tts_2.dmp'
  8.  import script 'tts_2_impsql'
  9.  export log 'tts_2.log'
  10.  until scn 1730989;

4.导出文件示例

  1. [oracle@apex mydump]$ ls
  2. o1_mf_fixf_hwyf5270_.dbf  tts_3.dmp  tts_3_imp.sql  tts_3.log

5.执行tts_3_imp.sql导入

拷贝导出文件到新库导入
如果导入报错，cat tts_3_imp.sql查看示例sql导入

  1. sqlplus /  as sysdba
  2. @tts_3_imp.sql
  3. impdp fixf/12345 directory=mydump dumpfile= 'tts_2.dmp' transport_datafiles= '/home/oracle/mydump/o1_mf_fixf_hwybyl49_.dbf'

如下错误需要先进行一次全备

  1. RMAN-00571: ===========================================================                                                                                          
  2. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============                                                                                          
  3. RMAN-00571: ===========================================================                                                                                          
  4. RMAN-03002: failure of transport tablespace command at 02/22/2020 23:00:07                                                                                       
  5. RMAN-03015: error occurred in stored script Memory Script                                                                                                        
  6. RMAN-06026: some targets not found - aborting restore                                                                                                            
  7. RMAN-06024: no backup or copy of the control file found to restore