Windows Platform of The TransportableTablespace
目录
关于 transportable tablespaces. 1
The transportable tablespace 用于以下功能：... 1
About Transporting Tablespaces Across Platforms. 2
Transportable Tablespace 限制条件... 3
Compatibility Considerations for Transportable Tablespaces. 4
实施步骤：... 4
实例操作：... 5
实施准备信息... 5
此次操作之前面临的几个问题：... 6
第一步， platform和endianness. 6
第二步，确认表空间和表空间的自包含... 7
第三步： expdp表空间... 9
第四步：copy dmp 文件和表空间的DBF文件到traget库中... 10
第五步：在目标库中执行impdp入库... 11
Tablesport tablespace 可以从一个库到另外一个库中实现表空间集的复制。但必须注意的是，在Transport tablespace 之前，需要将所表空间设置为read only 模式。用户需要EXP_FULL_DATABASE 权限。
The tablespaces being transported can be either dictionary managed orlocally managed. Starting with Oracle9i, thetransported tablespaces are not required to be of the same block size as thetarget database standard block size
Movingdata using transportable tablespaces is much faster than performing either anexport/import or unload/load of the same data. This is because the datafilescontaining all of the actual data are just copied to the destination location,and you use an export/import utility to transfer only the metadata of thetablespace objects to the new database.
用于以下功能：
·Exporting and importing partitions in datawarehousing tables
·Publishing structured data on CDs
·Copying multiple read-only versions of atablespace on multiple databases
·Archiving historical data
·Performing tablespace point-in-time-recovery(TSPITR)
Starting with OracleDatabase 10g, you can transport tablespacesacross platforms. This functionality can be used to:
·Allow adatabase to be migrated from one platform to another
·Provide aneasier and more efficient means for content providers to publish structureddata and distribute it to customers running Oracle Database on differentplatforms
·Simplify thedistribution of data from a data warehouse environment to data marts, which areoften running on smaller platforms
·Enable thesharing of read-only tablespaces between Oracle Database installations on differentoperating systems or platforms, assuming that your storage system is accessiblefrom those platforms and the platforms all have the same endianness, asdescribed in the sections that follow
Many, but not all,platforms are supported for cross-platform tablespace transport. You can querytheV$TRANSPORTABLE_PLATFORMview to see the platforms that are supported, and to determineeach platform's endian format (byte ordering). The following query displays theplatforms that support cross-platform tablespace transport:
使用sql 语句查询支持的平台：
SQL> SELECT * FROMV$TRANSPORTABLE_PLATFORM;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------
1 Solaris[tm] OE(32-bit) Big
2 Solaris[tm] OE(64-bit) Big
7 Microsoft Windows IA(32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems(64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA(64-bit) Little
9 IBM zSeries BasedLinux Big
13 Linux x86 64-bit Little
16 Apple Mac OS Big
12 Microsoft Windows x8664-bit Little
17 Solaris Operating System(x86) Little
18 IBM Power BasedLinux Big
19 HP IA Open VMS Little
20 Solaris Operating System(x86-64) Little
21 Apple Mac OS(x86-64) Little
20 rowsselected
If the sourceplatform and the target platform are of different endianness, then anadditional step must be done on either the source or target platform to convertthe tablespace being transported to the target format. If they are of the sameendianness, then no conversion is necessary and tablespaces can be transportedas if they were on the same platform.
Before atablespace can be transported to a different platform, the datafile header mustidentify the platform to which it belongs. In an Oracle Database withcompatibility set to 10.0.0 or later, you can accomplish this by making thedatafile read/write at least once.
限制条件
Be aware of thefollowing limitations as you plan totransport tablespaces:
·The source and target database must use the same character setand national character set.
·You cannot transport a tablespace to a target database in whicha tablespace with the same name already exists. However, you can rename eitherthe tablespace to be transported or the destination tablespace before thetransport operation.
·Objects with underlying objects (such as materialized views) orcontained objects (such as partitioned tables) are not transportable unless allof the underlying or contained objects are in the tablespace set.
·Beginning with Oracle Database 10g Release 2, you can transporttablespaces that contain XMLTypes, but you must use the IMP and EXP utilities,not Data Pump. When using EXP, ensure that theCONSTRAINTSandTRIGGERSparameters are set toY(the default).
The followingquery returns a list of tablespaces that contain XMLTypes:
When youcreate a transportable tablespace set, Oracle Database computes the lowestcompatibility level at which the target database must run. This is referred toas the compatibility level of the transportable set. Beginning with OracleDatabase 10g, a tablespace can always betransported to a database with the same or higher compatibility setting,whether the target database is on the same or a different platform. Thedatabase signals an error if the compatibility level of the transportable setis higher than the compatibility level of the target database.
The following tableshows the minimum compatibility requirements of the source and targettablespace in various scenarios. The source and target database need not havethe same compatibility setting.
Minimum CompatibilityRequirements
Transport Scenario | Minimum Compatibility Setting | |
Source Database | Target Database | |
Databases on the same platform | 8.0 | 8.0 |
Tablespace with different database block size than the target database | 9.0 | 9.0 |
Databases on different platforms | 10.0 | 10.0 |
、使用视图v$transportable_platform查询两个库的endian format,如果两个库的是相同的平台就不用了这一步了
、表空间的自包含（单向自包含和双向自包含）
、使用expdp导出表空间（如果这里是不同的endianness，则需要执行convert the tablespace）
、拷贝dmp文件和表空间的dbf文件到目标库中
、执行impdp 导入目标库中
平台信息：
============================================
硬件平台： IBMX3620 M3
系统版本： Windowsenterprise 2008 server r2 X64
Oracle 版本：Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 - 64bit Production
需要操作库的信息：
各海量项目对应的测试数据库实例
编号项目名称服务器实例用户名
1 XO 192.168.10.196 cs vi4
2 HN 192.168.10.198 cs vi2
3 ZM 192.168.10.198 cs zm
4 SX 192.168.10.197 cs vi5
5 DO 192.168.10.197 mb vi1
备注：此次工作目的，需要对现有的测试库进行整理。把目前的3个服务器中的5个schema数据整理到一台服务器中。【数据都会被整理到198上】
1、三个服务器上有多个实例，并多用户中有相同用户名，存在于目标数据库
2、不同schema中有相同的表空间名称
3、在同一库中多个用户共用同一个表空间
4、表空间中都存在分区表（这里需要用到分区表交换）
5、每个用户下的数据量大概在500G—1000G
综合考虑上面各个问题，决定使用表空间传输的方式来快速解决此次多库整理问题。具体的实例操作以196combosys viva4数据传输到198 中为例子。
技术亮点：
1、表空间的传输
2、分区表交换
和endianness
sql语句来查询
SQL> SELECT d.PLATFORM_NAME,ENDIAN_FORMAT
2 FROMV$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
3 WHERE tp.PLATFORM_NAME =d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------------------------------------------------
Microsoft Windows x8664-bit Little
sql语句来查看用户下的表空间
2 select tablespace_name from user_tables group by tablespace_name union
3 select tablespace_name from user_tab_partitions group bytablespace_name) group bytablespace_name
4 ;
TABLESPACE_NAME
------------------------------
MDMS_TS_SEC_201209
MDMS_TS_SEC_201211
MDMS_TS_SEC_201208
MDMS_TS_SEC_201210
MDMS_TS_MAIN
MDMS_TS_STATISTICS
MDMS_TS_SEC_DEFAULT
MDMS_TS_LOG_201301
8 rows selected
viva4用户中需要有8个表空间需要传输，接下来确认表空间的自包含。
时候回检查导出的表空间是否符合自包含性。比如表A表保存在user表空间中，而其索引ind保存在 ints表空间中。那么导入的时候只导出user表空间，那么A表的索引就会毫无意义。而自包含只包括性分为两种：单向自包含和双向自包含。单向自包含是指导出的表空间内的对象不依赖任何非导出的表空间内的任何对象，使用参数设置“transport_full_check=n”可以做这样的检查（impdp默认的设置）；而双向自包含是指导出的表空间内的对象不依赖非导出的表空间内的任何对象，并且非导出表空间内的而对象不依赖任何导出表空间内的任何对象，使用参数设置“ransport_full_check=y”可以做这样的检查。
oracle的 DBMS_TTS包，执行检查表空间的自包含。使用DBMS_TTS包之前必须要用给用户授予execute_catalog_role权限，初始化默认权限是给SYS用户的。
SQL> EXECUTEsys.DBMS_TTS.TRANSPORT_SET_CHECK('MDMS_TS_SEC_201208',true);
PL/SQL procedure successfullycompleted
SQL> select *fromsys.transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
ORA-39921: 默认分区 (表) 表空间 USERS (对于) 未包含在可传输集内。
ORA-39901: 分区表 VIVA4.VAV_MDMS_SEC 部分包含在可传输集内。
sql 来查询表VAV_MDMS_SEC分区表的分配情况：
SQL> selectsegment_name,partition_name,tablespace_name from user_segments wheresegment_name='VAV_MDMS_SEC';
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
----------------------------------------------------- ------------------------------
VAV_MDMS_SEC MDMS_TS_SEC_P_20120831
VAV_MDMS_SEC MDMS_TS_SEC_P_20120901 MDMS_TS_VIVA4_SEC_201209
VAV_MDMS_SEC MDMS_TS_SEC_P_20120902 MDMS_TS_VIVA4_SEC_201209
VAV_MDMS_SEC MDMS_TS_SEC_P_20120903 MDMS_TS_VIVA4_SEC_201209
VAV_MDMS_SEC MDMS_TS_SEC_P_20120904 MDMS_TS_VIVA4_SEC_201209
VAV_MDMS_SEC MDMS_TS_SEC_P_20120905 MDMS_TS_VIVA4_SEC_201209
VAV_MDMS_SEC MDMS_TS_SEC_P_20120906 MDMS_TS_VIVA4_SEC_201209
…………
表分区是按照每天一个分区，一个月一个表空间。所以，在这里需要对分区表VAV_MDMS_SEC进行交换分区。如下写的一个过程
begin
for n in (select *
from user_segments
where segment_name = ''
and (
partition_name like 'MDMS_TS_SEC_P_201208%'or
partition_name like 'MDMS_TS_SEC_P_201209%'or
partition_name like'MDMS_TS_SEC_P_201210%' or
partition_name like'MDMS_TS_SEC_P_201211%' or
partition_name like'MDMS_TS_SEC_P_DEFAULT%'
)) loop
execute immediate 'create table ' ||n.partition_name ||'_ex as select * from VAV_MDMS_SEC where 1=0';
execute immediate 'alter tableVAV_MDMS_SEC exchange partition '||n.partition_name||' with table'||n.partition_name || '_ex ';
end loop;
end;
在执行这个过程之前，我们需要给viva4用户单独创建一个表空间（BACKUP），并设置为用户默认表空间。这个表空间容量不需要太大，30M就够了。因为在做交换分区的时候，oracle只是把分区表的记录对象存放在表空间中。执行脚本完成之后，再去查看分区表的表空间都是backup，可以看来交换分区已经把所有的表存放在BACKUP一个表空间里了。到这里我们可以把表的自包含问题解决了。
表空间
在做expdp表空间之前，需要对表空间设置为read only 模式，
TABLESPACE_NAME
------------------------------
MDMS_TS_SEC_201209
MDMS_TS_SEC_201211
MDMS_TS_SEC_201208
MDMS_TS_SEC_201210
MDMS_TS_SEC_DEFAULT
Sql segment ：Alter tablespace XXX read only;
设置完成之后开始执行expdp
C:\Users\Administrator>expdpsystem/combosys@192.168.10.196/combosys dumpfile=vi
va4_new_1.dmp directory=DATA_PUMP_DIRtransport_tablespaces=MDMS_TS_SEC_201208 T
RANSPORT_FULL_CHECK=Y
Export: Release 11.2.0.1.0 -Production on 星期三 4月 17 09:34:41 2013
Copyright (c) 1982, 2009, Oracleand/or its affiliates. All rightsreserved.
连接到: OracleDatabase 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Produc
tion
With the Partitioning, OLAP,Data Mining and Real Application Testing options
启动"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/********@192.168.10.196/com
bosys dumpfile=viva4_new_1.dmpdirectory=DATA_PUMP_DIR transport_tablespaces=MDM
S_TS_SEC_201208TRANSPORT_FULL_CHECK=Y
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功加载/卸载了主表"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYSTEM.SYS_EXPORT_TRANSPORTABLE_01的转储文件集为:
D:\APP\ADMINISTRATOR\ADMIN\COMBOSYS\DPDUMP\VIVA4_NEW_1.DMP
******************************************************************************
可传输表空间 MDMS_TS_SEC_201208 所需的数据文件:
作业"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已于 09:35:06 成功完成
文件和表空间的DBF文件到traget库中
这里需要将DMP文件viva4_new_1.dmp和E:\ORACLE_DATAFILE\XJHLDATA\SEC\MDMS_TS_SEC_201208.DBF拷贝到198服务器指定目录中。
入库
需要把copy过来DMP和DBF两个文件impdp入库，
viva4/********dumpfile=viva4_1.dmp directory=DATA_PUMP_DIRTRANSPORT_DATAFILES=G:\196\combosys\viva4\MDMS_TS_SEC_201208.DBF
;;;
Import: Release 11.2.0.1.0 -Production on 星期二 4月 16 18:59:06 2013
Copyright (c) 1982, 2009, Oracleand/or its affiliates. All rightsreserved.
;;;
连接到: OracleDatabase 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP,Data Mining and Real Application Testing options
已成功加载/卸载了主表"VIVA4"."SYS_IMPORT_TRANSPORTABLE_01"
启动"VIVA4"."SYS_IMPORT_TRANSPORTABLE_01": viva4/******** dumpfile=viva4_1.dmpdirectory=DATA_PUMP_DIR TRANSPORT_DATAFILES=G:\196\combosys\viva4\MDMS_TS_SEC_201208.DBF
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作业"VIVA4"."SYS_IMPORT_TRANSPORTABLE_01" 已于 18:59:14 成功完成
在目标库中查询，刚才传入的几个表空间。
SQL> select tablespace_name from dba_data_files where tablespace_namein (
2 select tablespace_name from user_tables group by tablespace_name union
3 select tablespace_name from user_tab_partitions group bytablespace_name) group bytablespace_name
4 ;
TABLESPACE_NAME
------------------------------
MDMS_TS_SEC_201209
MDMS_TS_SEC_201211
MDMS_TS_SEC_201208
MDMS_TS_SEC_201210
MDMS_TS_SEC_DEFAULT
已经导入成功导入库中，接下来需要做
1、对这几个表空间修改模式，更改为read write 模式；
2、再次交换分区，把分区交换回到原有的表空间中，
分区再次交换：impdp完成之后此时的分区表暂时不是分区表而是一张一张独立的表存储在相应时间的表空间中，需要还原分区表
执行程序：
begin
for n in (
where
tablespace_name ='MDMS_TS_SEC_201209' or
tablespace_name ='MDMS_TS_SEC_201211' or
tablespace_name ='MDMS_TS_SEC_201208' or
tablespace_name ='MDMS_TS_SEC_201210' or
tablespace_name ='MDMS_TS_SEC_DEFAULT'
andsegment_type = 'TABLE') loop
execute immediate 'alter table VAV_MDMS_SECexchange partition MDMS_TS_SEC_P_' ||
replace(substr(n.segment_name, 15,20),'_EX','')|| ' with table '|| n.segment_name ;
end loop;
end;
执行完成后，已经还原了分区表。
执行sql 查看分区表：
SQL> select segment_name,partition_name,tablespace_name fromuser_segments where segment_name='VAV_MDMS_SEC';
Transportable tablespace 的工作已经完成。
还有其他的表空间，但是只需要重新执行1到5步就可以。如果没有分区表，就不需要做2步和5步中的分区交换工作。
注意：在此次过程中会遇到 target 库中的表空间名称和source 库的表空间名有相同的，需要对表空间rename。