环境情况
源库:
os:Centos 7
db版本:12.2.0.1
endianness格式: little
当前运行模式:单机

目的库:
os:Centos 7
db版本:18.3.0.0
endianness格式: little
当前运行模式:单机(带dataguard,单机->2节点的rac)

1.查看操作系统endianness格式
源端:
SELECT d.PLATFORM_NAME,
    ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME =d.PLATFORM_NAME;
PLATFORM_NAME                                                                                         ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Linux x86 64-bit                                                                                      Little

目标端:
SQL> set linesize 1000;
SQL> SELECT d.PLATFORM_NAME,
  2     ENDIAN_FORMAT
  3  FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
  4  WHERE tp.PLATFORM_NAME =d.PLATFORM_NAME;

PLATFORM_NAME                                                                                         ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Linux x86 64-bit                                                                                      Little

2.在源端和目标端创建backup的目录
源端:
[oracle@localhost ~]$ mkdir -p /home/oracle/backup
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 15 15:50:34 2021

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

SQL> connect / as sysdba
Connected.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORA12CPDB1                     READ WRITE NO
         4 ORA12CPDB2                     READ WRITE NO
         6 ORA12CPDB4                     READ WRITE NO
         7 ORA12CPDB5                     READ WRITE YES
SQL> alter session set container=ORA12CPDB4;
Session altered.

SQL> create directory backup as ‘/home/oracle/backup‘;
Directory created.

SQL> grant read, write on directory backup to hxl;
Grant succeeded.

目标端:
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB3                           READ WRITE NO
         6 PDB4                           READ WRITE NO
         7 PDB5                           READ WRITE NO
         8 PDB6                           READ WRITE NO
         9 PDB7                           READ WRITE NO
        10 PDB8                           READ WRITE NO
SQL> alter session set container=pdb4;
Session altered.

SQL> create directory backup as ‘/home/oracle/backup‘;
Directory created.

SQL> grant read, write on directory backup to hxl;

Grant succeeded.

3.检查表空间自包含(就是改表空间里的数据没有和其他表空间数据有关联,如果有关联会报错)
源端操作

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         6 ORA12CPDB4                     READ WRITE NO
         
SQL> execute dbms_tts.transport_set_check(‘TPS_HXL‘, TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

VIOLATIONS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORA-39907: Index HXL.IDX_N2 in tablespace TPS_HXL points to table HXL.TB_TEST_CP in tablespace SYSTEM.
ORA-39907: Index HXL.UNIQ_IDX_N1 in tablespace TPS_HXL points to table HXL.TB_TEST_CP in tablespace SYSTEM.
这里有输出,说明该表空间的里的对象用到了另外的表空间

检查TB_TEST_CP对象所在的表空间
SQL> Select tablespace_name From dba_segments Where segment_name=‘TB_TEST_CP‘;

TABLESPACE_NAME
------------------------------
SYSTEM

将该对象迁移到TPS_HXL表空间
SQL> alter table hxl.tb_test_cp move tablespace TPS_HXL;

Table altered.

再次检查

SQL> execute dbms_tts.transport_set_check(‘TPS_HXL‘, TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected
没有输出,说明自检查已经通过了.

4.将表空间TPS_HXL设置成read only --no done
在源端将表空间TPS_HXL设置为只读模式
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         6 ORA12CPDB4                     READ WRITE NO
SQL> alter tablespace tps_hxl read only;

Tablespace altered.


5.expdp导出的表空间源数据
源库导出语句:
expdp hxl/oracle@ORA12CPDB4 dumpfile=TPS_HXL.dmp directory=backup transport_tablespaces=TPS_HXL transport_full_check=y logfile=TPS_HXL.log
ORA12CPDB4是连接到ORA12CPDB4的tns名称,如下所示:

ORA12CPDB4 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.134)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora12cpdb4)
    )
  )

[oracle@localhost admin]$ expdp hxl/oracle@ORA12CPDB4 dumpfile=TPS_HXL.dmp directory=backup transport_tablespaces=TPS_HXL transport_full_check=y logfile=TPS_HXL.log

Export: Release 12.2.0.1.0 - Production on Tue Jun 15 16:37:43 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
ORA-31626: job does not exist
ORA-31633: unable to create master table "HXL.SYS_EXPORT_TRANSPORTABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1161
ORA-01647: tablespace ‘TPS_HXL‘ is read-only, cannot allocate space in it
ORA-06512: at "SYS.KUPV$FT", line 1054
ORA-06512: at "SYS.KUPV$FT", line 1042
这里错误,因为hxl用户使用的表空间是tps_hxl,换成system用户执行
expdp system/oracle@ORA12CPDB4 dumpfile=TPS_HXL.dmp directory=backup transport_tablespaces=TPS_HXL transport_full_check=y logfile=TPS_HXL.log



6.将源端导出的元数据文件和表空间TPS_HXL对应的数据文件拷贝到目标端
scp /home/oracle/backup/TPS_HXL.dmp 192.168.56.113:/home/oracle/backup


查看表空间对应的数据文件
SQL> Select file_name From Dba_Data_Files Where tablespace_name=‘TPS_HXL‘;

FILE_NAME
---------------------------------------------------
/u01/app/oracle/oradata/ora12c/ora12cpdb4/hxl01.dbf

这里该表空间只有一个数据文件,若是有多个数据文件,需要全部拷贝到目的端
scp /u01/app/oracle/oradata/ora12c/ora12cpdb4/hxl01.dbf 192.168.56.113:/u01/app/oracle/oradata/ora18c/slnngk/c346a9f364ec42c8e0536f38a8c026fa/datafile/


7.在目标端系统上import表空间的metadata(使用hxl用户,若用户不相同需要用到remap_schema)
impdp hxl/oracle@tnspdb4 directory=backup dumpfile=TPS_HXL.dmp transport_datafiles=/u01/app/oracle/oradata/ora18c/slnngk/c346a9f364ec42c8e0536f38a8c026fa/datafile/hxl01.dbf logfile=TPS_HXL.log


8.查看并修改表空间状态
alter tablespace TPS_HXL read write;

pdb表空间传输(nodone)