Oracle 传输表空间(TTS)

官方文档: Transporting Data (oracle.com)

使用场景

不同操作系统之间复制数据文件.
比如: solaris x86 32bit >> linux IA 64bit

-- 查看当前platform:
select PLATFORM_ID,platform_name from v$database;

-- 查看所有支持的platform:
select * from v$transportable_platform;

示例

概况

CDB名: kmg

PDB名: PM

-- 源库
SYS@DB19C >>> select PLATFORM_ID,platform_name from v$database;

PLATFORM_ID PLATFORM_NAME
----------- ----------------------------------
         20 Solaris Operating System (x86-64)

-- 目标库
SYS@kmg >>> select PLATFORM_ID,platform_name from v$database;

PLATFORM_ID PLATFORM_NAME
----------- ----------------------------------
         13 Linux x86 64-bit

-- -------------------------------------------------------------------------
-- 在源库创建一个表空间, 包含两个数据文件:
create tablespace TTS_TBS datafile '/u01/app/oracle/oradata/DB19C/tts_tbs_01.dbf' size 50M autoextend on;
alter tablespace TTS_TBS add datafile '/u01/app/oracle/oradata/DB19C/tts_tbs_02.dbf' size 50M autoextend on;

-- 查看:
select file_name,tablespace_name,bytes,online_status from dba_data_files where tablespace_name='TTS_TBS';
FILE_NAME                                          TABLESPACE_NAME BYTES      ONLINE_STATUS
-------------------------------------------------- --------------- ---------- -------------
/u01/app/oracle/oradata/DB19C/tts_tbs_01.dbf       TTS_TBS         52428800   ONLINE
/u01/app/oracle/oradata/DB19C/tts_tbs_02.dbf       TTS_TBS         52428800   ONLINE

源库

要传输的表空间一定要处于只读状态!!!

-- 检查表空间是否自包含:
-- 自包含:表空间中的对象不依赖于任何其它表空间对象
exec dbms_tts.transport_set_check('TTS_TBS');

-- 如果返回空说明对应表空间是自包含的, 非空的话根据提示手动处理.
select * from TRANSPORT_SET_VIOLATIONS;

-- 创建一个测试表:
create table t_tts_test(id number(3,0),name varchar2(32)) tablespace TTS_TBS;
insert into t_tts_test values(1,'TOM');
insert into t_tts_test values(2,'Jerry');
commit;

-- 表空间改为只读:
alter tablespace TTS_TBS read only;

-- (可选)创建目录:
create or replace directory dir_datapump as '/export/home/oracle/data_pump';
grant read,write on directory dir_datapump to public;

-- expdp导出(这里导出的是元数据):
expdp system/pwd transport_tablespaces=tts_tbs dumpfile=dir_datapump:tts_tbs.dmp

-- !在表空间还在只读状态下复制对应数据文件到目标库.
-- bash:
scp /u01/app/oracle/oradata/DB19C/tts_tbs_* oracle@10.0.0.10:/home/oracle/data_pump/

以上步骤做好后, 源库表空间可以改回读写状态了.

目标库

如果平台不一致需要做转换

-- rman:
convert datafile '/home/oracle/data_pump/tts_tbs_01.dbf','/home/oracle/data_pump/tts_tbs_02.dbf' 
to platform='Linux x86 64-bit' 
from platform='Solaris Operating System (x86-64)' 
db_file_name_convert='/home/oracle/data_pump/','/u01/app/oracle/oradata/KMG/PM/';
-- 提示:
    /*
    Starting conversion at target at 2024-02-26 17:44:13
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=269 device type=DISK
    channel ORA_DISK_1: starting datafile conversion
    input file name=/home/oracle/data_pump/tts_tbs_01.dbf
    converted datafile=/u01/app/oracle/oradata/KMG/PM/tts_tbs_01.dbf
    channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
    channel ORA_DISK_1: starting datafile conversion
    input file name=/home/oracle/data_pump/tts_tbs_02.dbf
    converted datafile=/u01/app/oracle/oradata/KMG/PM/tts_tbs_02.dbf
    channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
    Finished conversion at target at 2024-02-26 17:44:15
    */

导入数据

-- (可选)创建目录:
create or replace directory dir_datapump as '/home/oracle/data_pump';
grant read,write on directory dir_datapump to public;

-- impdp导入:
impdp system/pwd@PM directory=dir_datapump \
dumpfile=tts_tbs.dmp \
transport_datafiles='/u01/app/oracle/oradata/KMG/PM/tts_tbs_01.dbf','/u01/app/oracle/oradata/KMG/PM/tts_tbs_02.dbf' 

-- 验证:
SYS@PM >>> select * from t_tts_test;
	ID NAME
------ -------
	 1 TOM
	 2 Jerry
-- 查出来了.

-- (可选) 把表空间改为读写:
alter tablespace tts_tbs read write;
-- 查看:
select status,tablespace_name from dba_tablespaces;