Oracle 传输表空间(Transportable Tablespaces) 跨操作系统迁移表空

这篇演示endianness格式相同的跨操作系统的迁移，测试环境如下：
Source 端：
操作系统：Oracle Linux 6.1 32位
endianness格式: little
数据库版本：11.2.0.3
Target 端：
操作系统：OracleLinux 6.1 64位
endianness 格式: little
数据库版本：11.2.0.3
1. 查看endianness格式查看endianness 格式，可以使用如下命令：
SQL> col platform_name for a40
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
--------------------------------------------------- --------------
6 AIX-Based Systems (64-bit) Big
16 Apple Mac OS Big
21 Apple Mac OS (x86-64) Little
19 HP IA Open VMS Little
15 HP Open VMS Little
5 HP Tru64 UNIX Little
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
18 IBM Power Based Linux Big
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
--------------------------------------------------- --------------
11 Linux IA (64-bit) Little
13 Linux x86 64-bit Little
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
20 Solaris Operating System (x86-64) Little
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
20 rows selected.
从上面的查询结果来看，基本都是little 类型的endianness，
或者：
SQL> SELECT d.PLATFORM_NAME,ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORMtp, V$DATABASE d
3 WHERE tp.PLATFORM_NAME =d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------------------------------
Linux IA (32-bit) Little
SQL> SELECT d.PLATFORM_NAME,ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORMtp, V$DATABASE d
3 WHERE tp.PLATFORM_NAME =d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------------------------------
Linux x86 64-bit Little
2. Source上创建测试表空间这里创建表空间，并在该表空间上创建几个自包含的对象，即对象的内容都在这个表空间里，和其他表空间没有关联。

SQL> select tablespace_name,status fromdba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
SQL> select file_name fromdba_data_files;
FILE_NAME
---------------------------------------------------------------
/u01/app/oracle/oradata/anqing/users01.dbf
/u01/app/oracle/oradata/anqing/undotbs01.dbf
/u01/app/oracle/oradata/anqing/sysaux01.dbf
/u01/app/oracle/oradata/anqing/system01.dbf
--创建表空间
SQL> create tablespace anqing datafile '/u01/app/oracle/oradata/anqing/anqing01.dbf'size
50M;
Tablespace created.
--创建用户，并指定表空间
SQL> create user anqing identified byanqing default tablespace anqing temporary tablespace temp;
User created.
SQL> grant connect,resource to anqing;
Grant succeeded.
--创建表：
SQL> conn anqing/anqing;
Connected.
SQL> create table anqing as select * fromall_objects;
Table created.
3. 在两端创建backup 的目录使用数据泵之前，需要创建一个存放文件的目录。

这个目录要写入Oracle 的数据字典中才能识别。

SQL> create directory backup as'/u01/backup';
Directory created.
SQL> col owner format a5
SQL> col directory_name format a25
SQL> select * from dba_directories;
4. 检查表空间自包含SQL> execute dbms_tts.transport_set_check('anqing', TRUE);
PL/SQL procedure successfully completed.
--查看自包含验证结果：
SQL> select * fromtransport_set_violations;
no rows selected
--如果不符合规定的，这里会显示的相关的信息。

比如：
SQL> select * fromtransport_set_violations;
VIOLATIONS
-----------------------------------------------------------------------------
ORA-39917: 可插入集内不允许使用SYS 拥有的对象ANQING (在表空间ANQING 中)
5. 将表空间设置成read -only
SQL>alter tablespace anqing read only;
Tablespacealtered.
SQL>select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
---------------------------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
ANQING READ ONLY
6 rowsselected.
6. 生成：TransportableTablespace Set一个完成的Transportable tablespace set 由2部分组成，expdp 导出的表空间的metadata，还有就是表空间对应的数据文件。

[oracle@tianlesoftware u01]$ expdp dumpfile=anqing.dmp directory=backup transport_tablespaces=anqing transport_full_check=y logfile=anqing.log
Export: Release 11.2.0.3.0 - Production onMon Feb 27 17:44:32 2012
Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options Starting"SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA dumpfile=anqing.dmpdirectory=backup transport_tablespaces=anqing transport_full_check=ylogfile=anqing.log
Processing object typeTRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object typeTRANSPORTABLE_EXPORT/TABLE
Processing object typeTRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table"SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfullyloaded/unloaded
************************************************************************ ******
Dump file set forSYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/backup/anqing.dmp
************************************************************************ ******
Datafiles required for transportabletablespace ANQING:
/u01/app/oracle/oradata/anqing/anqing01.dbf
Job"SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completedat 17:45:41 [oracle@tianlesoftware u01]$
7. 将Transportable Tablespace set 传送到Target端
（1）将表空间anqing 对应的数据文件copy到Target 对应的ORADATA目录下。

（2）将expdp 导出的表空间metadta 数据copy 到Target 端的backup 目录下
[oracle@tianlesoftware backup]$ scp anqing.dmp 192.168.3.201:/u01/backup
oracle@192.168.3.201's password:
anqing.dmp 100% 104KB 104.0KB/s 00:00
[oracle@tianlesoftware anqing]$ scp anqing01.dbf 192.168.3.201:/u02/app/oracle/oradata/anqing
oracle@192.168.3.201's password:
anqing01.dbf 100% 50MB 16.7MB/s 00:03
8.在Target 系统上Import 表空间的metadata
--这里创建一个用户，然后remap_schema：
SQL> create user dave identified bydave;
User created.
SQL> grant connect,resource to dave;
Grant succeeded.
rac1:/u02/app/oracle/oradata/anqing>impdp directory=backup dumpfile=anqing.dmp transport_datafiles=/u02/app/oracle/oradata/anqing/anqing01.dbf
remap_schema=anqing:dave logfile=anqing.log
Import: Release 11.2.0.3.0 - Production onMon Feb 27 17:51:49 2012
Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Miningand Real Application Testing options
Master table"SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfullyloaded/unloaded Starting"SYS"."SYS_IMPORT_TRANSPORTABLE_01": /******** AS SYSDBA directory=backupdumpfile=anqing.dmptransport_datafiles=/u02/app/oracle/oradata/anqin g/anqing01.dbfremap_schema=anqing:dave logfile=anqing.log
Processing object typeTRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object typeTRANSPORTABLE_EXPORT/TABLE
Processing object typeTRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job"SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completedat 17:52:18
注意：（1）这里transport_datafiles 写的是datafile的全路径，也可以用transport_datafiles来对数据文件进行重命名和移动位置。

（2）如果文件很多，也可以写入个配置文件里。

导入时通过PARFILE参数来指定。

9．查看并修改表空间状态
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
ANQING READ ONLY
6 rows selected.
至此，Source 和Target 端都是read only 状态，修改2个表空间的状态：
SQL> alter tablespace anqing read write;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
ANQING ONLINE
6 rows selected.
10. 验证TTS在Target 端查询我们之前创建的表：
SQL> conn dave/dave;
Connected.
SQL> select count(*) from anqing;
COUNT(*)
----------
56281
数据已经迁移过来，TTS 操作完成。

小结：1. 我们之前的表空间对应的是anqing 用户，在TTS 过程中，我们remap 到了dave 用户下。

2. DB 版本相同，time zone version 相同，如果time zone 不同，就会遇到ORA-39322的错误。

Oracle TTSORA-39322: Cannot use transportable tablespace with different timezone version 说明
/tianlesoftware/article/details/7298547
3. 虽然这里测试的是32位linux 到64位linux，但是他们对应的endianness值相同，所以这里不需要进行endianness的转换。

MOS 上的相关链接：
10g : Transportable Tablespaces AcrossDifferent Platforms [ID 243304.1]
How to Move Tablespaces Across PlatformsUsing Transportable Tablespaces With RMAN [ID 371556.1]
PS:原先设计的是windows 64到linux 64的TTS，结果2端time zoneversion 不一致，导致imp 报错，本本上暂时也没有64位的11.2.0.3的安装介质，下载又巨慢，4.7G。

就改成linux 32到linux 64了。

先看一下endianness格式问题：
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
--------------------------------------------------- --------------
6 AIX-Based Systems (64-bi t) Big
16 Apple Mac OS Big
21 Apple Mac OS (x86-64) Little
19 HP IA Open VMS Little
15 HP Open VMS Little
5 HP Tru64 UNIX Little
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
18 IBM Power Based Linux Big
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
--------------------------------------------------- --------------
11 Linux IA (64-bit) Little
13 Linux x86 64-bit Little
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
20 Solaris Operating System (x86-64) Little
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
20 rows selected.
从以上的结果显示，endian 格式是big的系统是AIX,HP-UNIX,Solaris等UNIX系统，而这些系统与硬件相关，所以不能模拟，所以这里直接引用MOS 上的一个示例：
10g : Transportable Tablespaces AcrossDifferent Platforms [ID 243304.1]
二. 示例
2.1. Check for restrictions--检查TTS 使用限制
Review the"Limitations on Transportable Tablespace Use" section in Note371556.1. Among otherthings, objects that reside in the SYSTEM tablespace and objects owned by SYSwill not be transported. This includes but is not limited to users, privileges,PL/SQL stored procedures, and views.
If you usespatial indexes, apply the solution in Note579136.1 "IMPDP TRANSPORTABLE TABLESPACE FAILS for SPATIALINDEX)" before continuing.
2.2. Prepare the database--使用TTS 之前的准备工作
2.2.1 Check that thetablespace will be self-contained
SQL>execute sys.dbms_tts.transport_set_check('TBS1,TBS2', true);
SQL> select * from sys.transport_set_violations;
==>These violations must be resolved before the tablespaces can betransported。

--检查表空间的自包含，这一步必须验证通过才可以进行TTS 操作。

2.2.2 Set the tablespaceto READ ONLY
SQL> alter tablespace REPOSIT read only;
Tablespace altered.
--将要迁移的表空间设置为只读。

2.3. Export metadata--在source 端（HP-UNIX）上导数表空间的meatdata
<HP-UX>
--使用exp
exp userid=\'/ as sysdba\'transport_tablespace=y
tablespaces=reposit
file=tts.dmp log=exp_tts.log
statistics=none
Export: Release 10.2.0.4.0 - Mon Nov 26 11:49:49 2007
...
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace REPOSIT ...
. exporting cluster definitions
. exporting table definitions
. . exporting table MTG_COL_DEP_CHG
. . exporting table MTG_DATABASES
....
. . exporting table SYBASE11_SYSUSERS
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
Review the exportlog for warnings and errors and resolve issues before continuing. Failure to doso can result in data loss.
--也可以使用expdp：
Datapump can be used for that purpose too:
expdp \'/ as sysdba\' directory=tts_dumpdumpfile=tts1_dp.dmp logfile=tts_dump_log:tts.log
transport_tablespaces=tts_1,tts_2 transport_full_check=y
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02":system/******** directory=tts_datafile dumpfile=tts1.dmplogfile=tts_dump_log:tts.log transport
_tablespaces=tts_1,tts_2 transport_full_check=y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02" successfullyloaded/unloaded ***********************************************************************D umpfile set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_02 is:
+DATA/tts1.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02" successfullycompleted at 14:00:34 Movement of data and Enabling TTS
2.4. Check the endianness of the target database and convert,if necessary--检查endianness 格式，如果需要则进行转换
2.4.1 Case 1: SameEndianness (Big->Big or Little->Little)--endianness 格式相同
The source platform is Sun SPARC Solaris:endianness Big
The target platform is HP-UX (64-bit): endianness Big
SQL> select PLATFORM_ID , PLATFORM_NAMEfrom v$database;
PLATFORM_IDPLATFORM_NAME
-----------------------------------------
3 HP-UX (64-bit)
No conversion is needed for files that (1) do NOT contain UNDO/Rollback segmentsand (2) have a source and target OS with the same endianness.
Refer to Note.415884.1 "CrossPlatform Database Conversion with same Endian" to determine whichfiles contain UNDO/Rollback segments.
2.4.2 Case 2: DifferentEndianness (Big->Little or Little->Big)--endianness 格式不同，则需要进行转换
The source platform is Microsoft WIndowsNT: endianness Little
The target platform is HP-UX (64-bit): endianness Big
If we move the files and import the tablespace:
--如果我们不转换，直接imp，则会报如下错误信息
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 1565:
"BEGINsys.dbms_plugts.beginImpTablespace('TBS_TTS',37,'SYS',1,0,8192,2,57" "54175,1,2147483645,8,128,8,0,1,0,8,462754339,1,1,5754124,NULL,0,0,NULL,NULL" "); END;"
IMP-00003: ORACLE error 1565 encountered
ORA-01565: error in identifying file'/database/db101b2/V101B2/datafile/reposit01.dbf' ORA-27047: unable to read the header block of file
HP-UX Error: 2: No such file or directory
Additional information: 2
ORA-06512: at "SYS.DBMS_PLUGTS", line 1540
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully
You have to convertthe files; the files can be converted on source OR on target :
--所以在开始TTS之前，必须先进行转换，这个转换可以在Source 端进行，也可以在target 端进行：
（1）locally on the SOURCE before theimport step so that the files are endian compatible: --在Source 端进行转换：
<Solaris>
rman target=/
Recovery Manager: Release 10.2.0.4.0 - 64bit
connected to target database: V101B2 (DBID=3287908659)
RMAN> convert tablespace 'REPOSIT'
2> to platform="Linux IA (32-bit)"
3> db_file_name_convert='/database/db101b2/V101B2/datafile/reposit01.dbf',
4> '/tmp/reposit01.dbf';
Starting backup at 26-NOV-07
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=8 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=/database/db101b2/V101B2/datafile/reposit01.dbf converted datafile=/tmp/reposit01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 26-NOV-07
The converted datafile is staged in /tmp directory until it is copied to thetarget server.
--转换之后的datafile存放在/tmp 目录下，我们把/tmp 目录下的datafile 拷贝到Target的对应目录。

（2）remotely on the target server after having copiedthem on the server.
--先将datafile从source 端拷贝到Target ，然后在Target 端进行转换
Conversion ontarget platform is a way forward when the v$transportable_platform of thesource does Not list the target platform.
When conversionis done on the target platform then CONVERT DATAFILE is used instead of CONVERTTABLESPACE, ie:
RMAN> CONVERT DATAFILE
'/database/db101b2/V101B2/datafile/reposit01.dbf'
TO PLATFORM="Linux IA (32-bit)"
FROM PLATFORM="HP TRu64 UNIX"
DB_FILE_NAME_CONVERT="/database/db101b2/V101B2/datafile/","/tmp/";
--注意这里的命令与在source 端有出入
2.5. Move datafiles and export dump file$ftp tts.dmp
+
/database/db101b2/V101B2/datafile/reposit01.dbf (no conversion)
or
/tmp/reposit01.dbf (converted file ifconversion had been required)
2.6. Import metadata--使用imp
$ imp userid=\'/ as sysdba\'TRANSPORT_TABLESPACE=Y
datafiles=/database/db101b2/V101B2/datafile/reposit01.dbf
(or /tmp/reposit01.dbf )
file=tts.dmp log=imp_tts.log
Import: Release 10.2.0.4.0 - on Mon Nov 26 03:37:20 2007
Export file created by EXPORT:V10.02.00 via conventional path
About to import transportable tablespace(s) metadata...
...
. importing SYS's objects into SYS
. importing OMWB's objects into OMWB
. . importing table "MTG_COL_DEP_CHG"
...
. . importing table "SYBASE11_SYSUSERS"
Import terminated successfully without warnings.
Review the import log for warnings and errors and resolve issues
before continuing. Failure to do so can result in data loss.
--使用expdp：
If we exported with DataPump, import mustbe done with that same tool:
impdp \'/ as sysdba\' directory=tts_dumpdumpfile=tts1_dp.dmp logfile=tts_dump_log:tts.log
transport_datafiles='/database/oradata/tts1_db1.dbf','/database/oradata/tts2_db1.dbf'
It's not possible to import when tablespace already exists or when targetschema is not
created.
If users don't exist, DataPump provides an alternative by using remap_schema(for import utility we can create the schema), ie:
REMAP_SCHEMA=<source_user>:<target_user>
--如果impdp 的用户不存在，可以使用impdp 的remap_schema 参数来进行用户的转换。

If tablespace already exists in target, wecan use remap_tablespace parameter on impdp (there is no option in importbut rename tablespace at source or the existing one at target). REMAP_TABLESPACE=(<source_tbs1>:<target_tbs1>,<source_tbs2>:<target_tbs2>,...) --如果表空间在Target上已经存在，那么可以使用remap_tablespace 参数来进行表空间的转换。

2.7. Set the imported tablespace to READ WRITE--操作完毕后，将表空间设置为读写模式SQL> alter tablespace reposit readwrite;
Tablespace altered.。