数据泵 TTS（传输表空间技术）
1、源库准备环境
--创建被传输的表空间
create tablespace tts logging datafile '/home/oracle/app/oradata/orcl/tts01.dbf'
size 40m autoextend on next 10m maxsize 100m extent management local;
create tablespace ttsind logging datafile '/home/oracle/app/oradata/orcl/ttsind01.dbf'
size 40m autoextend on next 10m maxsize 100m extent management local;
--创建用户，并将表空间作为默认表空间
SQL> create user tts identified by tts default tablespace tts;
SQL> grant connect,resource to tts;
SQL> grant select on dba_objects to tts;
[oracle@11g ~]$ sqlplus tts/tts
--在该表空间创建表，用于测试
create table datapump as select * from dba_objects;
delete from datapump where object_id is null;
create index ind_datapump on datapump(object_id) tablespace ttsind;
create table datapump02 as select * from dba_objects;
create bitmap index ind_datapump02 on datapump02(object_type) tablespace ttsind;
commit;
SQL> select count(*) from datapump;
COUNT(*)
----------
74886
SQL> select count(*) from datapump02;
COUNT(*)
----------
74888
2. 源库信息统计
--查询表空间有哪些用户 (sys用户)
SQL> select distinct owner from dba_segments where tablespace_name in ('TTS','TTSIND') ;
OWNER
------------------------------
TTS
--计算某个表空间所包含对象的大小
select 'SIZE_TABELSPACE' NAME,sum(user_bytes)/1024/1024 SIZE_M from dba_data_files where tablespace_name='TTS' UNION ALL select 'SIZE_OBJECT' NAME,sum(nvl(bytes,0))/1024/1024 SIZE_M from user_segments where tablespace_name='TTS';
NAME SIZE_M
--------------- ----------
SIZE_TABELSPACE 39
SIZE_OBJECT
-- 查看表空间内对象的个数
select segment_type,count(1) as"NUM" from dba_segments where tablespace_name in ('TTS','TTSIND') group by segment_type;
SEGMENT_TYPE NUM
------------------ ----------
TABLE 2
INDEX 2
--查看DBLINK 信息：
SQL> select owner,object_name,object_type from dba_objects where object_type='DATABASELINK';
--查看JOB 信息：
SQL> select count(*) from dba_jobs where schema_user in ('DATAPUMP'); ('','')
3. 检查源、目标平台Endianness
--在源数据库平台上：
SQL> col PLATFORM_NAME for a40
SQL>
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> col PLATFORM_NAME for a40
SQL>
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
由于源和目标平台的Endianness一致，可以省去convert这一步。
--查看 transportable_platform 支持总表
select * from v$transportable_platform;
4、检查源库表空间是否自包含
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TTS',true);
PL/SQL 过程已成功完成。
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
未选定行
说明表空间是自包含的。
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TTSIND',true);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
--------------------------------------------------------------------------------
ORA-39907: Index TTS.IND_DATAPUMP in tablespace TTSIND points to table TTS.DATAP
UMP in tablespace TTS.
ORA-39907: Index TTS.IND_DATAPUMP02 in tablespace TTSIND points to table TTS.DAT
APUMP02 in tablespace TTS.
5、产生传输表空间集 （源库）
SQL>CREATE DIRECTORY dump_dir as '/home/oracle/dump_dir';
[oracle@11g ~]$ mkdir -p /home/oracle/dump_dir
SQL>grant read,write on directory dump_dir to SYSTEM;
将表空间置为只读。
SQL> alter tablespace tts read only;
表空间已更改。
SQL> alter tablespace ttsind read only;
表空间已更改。
-- 检查表空间是否为read only
SQL> select tablespace_name,status from dba_tablespaces where status='READ ONLY';
TABLESPACE_NAME STATUS
------------------------------ ---------
TTS READ ONLY
TTSIND READ ONLY
使用数据泵导出传输表空间的元数据
[oracle@11g dump_dir]$ expdp \'sys/oracle as sysdba\' DIRECTORY=dump_dir DUMPFILE=tts_%U.dmp transport_tablespaces=tts,ttsind transport_full_check=y logfile=exp_tts.log parallel=1
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Wed Mar 11 14:20:02 2015 elapsed 0 00:00:59
注：如果Endianness不一致，还需要使用RMAN进行转换表空间的数据文件。
若两个平台字节序不相同，通过RMAN命令将源库中需要传输的表空间所对应的数据文件转换：
>rman target /
RMAN> convert tablespace ‘表空间名’
to platform=’目标平台‘
db_file_name_convert=’数据文件位置’,’转换后的位置’;
如：将HP-UX (64-bit) Big 转换成Linux IA (64-bit) Little
RMAN> convert tablespace ‘test1’
to platform=’Linux IA (64-bit)’ db_file_name_convert=’/u01/oradata/test01.dbf’,’/temp/test01.dbf’;
6、目标库创建相关环境
SQL>CREATE DIRECTORY dump_dir as '/home/oracle/dump_dir';
[oracle@11g ~]$ mkdir -p /home/oracle/dump_dir
SQL>grant read,write on directory dump_dir to SYSTEM;
7. 将Transportable Tablespace set 传送到Target端
--查看源库表空间数据文件路径
SQL> select name from v$datafile where ts# in (select ts# from v$tablespace where name in ('TTS','TTSIND'));
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oradata/orcl/tts01.dbf
/home/oracle/app/oradata/orcl/ttsind01.dbf
（1）将表空间tts , ttsind 对应的数据文件copy到Target 对应的ORADATA目录下。
（2）将expdp 导出的表空间metadta 数据copy 到Target 端的backup 目录下
[oracle@11g dump_dir]$ scp tts_01.dmp 10.100.25.13:/home/oracle/dump_dir/
oracle@10.100.25.14's password:
tts_01.dmp 100% 132KB 132.0KB/s 00:00
[oracle@11g orcl]$ scp tts01.dbf 10.100.25.13:/home/oracle/app/oradata/orcl/
oracle@10.100.25.14's password:
tts01.dbf 100% 40MB 40.0MB/s 00:01
[oracle@11g orcl]$ scp ttsind01.dbf 10.100.25.13:/home/oracle/app/oradata/orcl/
oracle@10.100.25.14's password:
ttsind01.dbf 100% 40MB 40.0MB/s 00:01
--将源库表空间改为 read writer
SQL> alter tablespace ttsind read write;
Tablespace altered.
SQL> alter tablespace tts read write;
Tablespace altered.
8、导入表空间 （目标库）
-- 目标库检查是否拥有TTS表空间内所有用户
SQL> select username from dba_users where username in ('TTS');
--在目标数据库中，如果没有，创建相应的用户。
SQL> create user tts identified by tts;
User created.
SQL> grant connect,resource to tts;
Grant succeeded.
--在Target 系统上Import 表空间的metadata。
[oracle@11g dump_dir]$ impdp \'sys/oracle as sysdba\' DIRECTORY=dump_dir DUMPFILE=tts_%U.dmp transport_datafiles=/home/oracle/app/oradata/orcl/tts01.dbf,/home/oracle/app/oradata/orcl/ttsind01.dbf parallel=1 logfile=exp_tts.log
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Wed Mar 11 18:18:33 2015 elapsed 0 00:00:14
注意：
（1）这里transport_datafiles 写的是datafile的全路径， 也可以用transport_datafiles来对数据文件进行重命名和移动位置。
（2）如果文件很多，也可以写入个配置文件里。 导入时通过PARFILE参数来指定。
--将被导入的表空间置为read write。
SQL> alter tablespace tts read write;
Tablespace altered.
SQL> alter tablespace ttsind read write;
Tablespace altered.
9、核查对象
SQL> select segment_type,count(1) as"NUM" from dba_segments where tablespace_name in ('TTS','TTSIND') group by segment_type;
SEGMENT_TYPE NUM
------------------ ----------
TABLE 2
INDEX 2
