Windows Platform. of The Transportable Tablespace
目录
TOC \o "1-3" \h \z \u 关于 transportable tablespaces. PAGEREF _Toc354157436 \h 1 08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003300350034003100350037003400330036000000
The transportable tablespace 用于以下功能：... PAGEREF _Toc354157437 \h 1 08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003300350034003100350037003400330037000000
About Transporting Tablespaces Across Platforms. PAGEREF _Toc354157438 \h 2 08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003300350034003100350037003400330038000000
Transportable Tablespace 限制条件... PAGEREF _Toc354157439 \h 3 08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003300350034003100350037003400330039000000
Compatibility Considerations for Transportable Tablespaces. PAGEREF _Toc354157440 \h 4 08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003300350034003100350037003400340030000000
实施步骤：... PAGEREF _Toc354157441 \h 4 08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003300350034003100350037003400340031000000
实例操作：... PAGEREF _Toc354157447 \h 5 08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003300350034003100350037003400340037000000
实施准备信息... PAGEREF _Toc354157448 \h 5 08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003300350034003100350037003400340038000000
此次操作之前面临的几个问题：... PAGEREF _Toc354157449 \h 6 08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003300350034003100350037003400340039000000
第一步， platform和endianness. PAGEREF _Toc354157450 \h 6 08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003300350034003100350037003400350030000000
第二步，确认表空间和表空间的自包含... PAGEREF _Toc354157454 \h 7 08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003300350034003100350037003400350034000000
第三步： expdp表空间... PAGEREF _Toc354157466 \h 9 08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003300350034003100350037003400360036000000
第四步：copy dmp 文件和表空间的DBF文件到traget库中... PAGEREF _Toc354157467 \h 10 08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003300350034003100350037003400360037000000
第五步：在目标库中执行impdp入库... PAGEREF _Toc354157468 \h 11 08D0C9EA79F9BACE118C8200AA004BA90B02000000080000000E0000005F0054006F0063003300350034003100350037003400360038000000
关于 transportable tablespaces
Tablesport tablespace 可以从一个库到另外一个库中实现表空间集的复制。但必须注意的是，在Transport tablespace 之前，需要将所表空间设置为read only 模式。用户需要EXP_FULL_DATABASE 权限。
The tablespaces being transported can be either dictionary managed or locally managed. Starting with Oracle9i, the transported tablespaces are not required to be of the same block size as the target database standard block size
Moving data using transportable tablespaces is much faster than performing either an export/import or unload/load of the same data. This is because the datafiles containing 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 the tablespace objects to the new database.
The transportable tablespace 用于以下功能：
· Exporting and importing partitions in data warehousing tables
· Publishing structured data on CDs
· Copying multiple read-only versions of a tablespace on multiple databases
· Archiving historical data
· Performing tablespace point-in-time-recovery (TSPITR)
About Transporting Tablespaces Across Platforms
Starting with Oracle Database 10g, you can transport tablespaces across platforms. This functionality can be used to:
· Allow a database to be migrated from one platform. to another
· Provide an easier and more efficient means for content providers to publish structured data and distribute it to customers running Oracle Database on different platforms
· Simplify the distribution of data from a data warehouse environment to data marts, which are often running on smaller platforms
· Enable the sharing of read-only tablespaces between Oracle Database installations on different operating systems or platforms, assuming that your storage system is accessible from those platforms and the platforms all have the same endianness, as described in the sections that follow
Many, but not all, platforms are supported for cross-platform. tablespace transport. You can query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported, and to determine each platform's endian format (byte ordering). The following query displays the platforms that support cross-platform. tablespace transport:
使用sql 语句查询支持的平台：
SQL> SELECT * FROM V$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 Based Linux Big
13 Linux x86 64-bit Little
16 Apple Mac OS Big
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
21 Apple Mac OS (x86-64) Little
20 rows selected
If the source platform. and the target platform. are of different endianness, then an additional step must be done on either the source or target platform. to convert the tablespace being transported to the target format. If they are of the same endianness, then no conversion is necessary and tablespaces can be transported as if they were on the same platform.
Before a tablespace can be transported to a different platform, the datafile header must identify the platform. to which it belongs. In an Oracle Database with compatibility set to 10.0.0 or later, you can accomplish this by making the datafile read/write at least once.
Transportable Tablespace 限制条件
Be aware of the following limitations as you plan to transport tablespaces:
· The source and target database must use the same character set and national character set.
· You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.
· Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.
· Beginning with Oracle Database 10g Release 2, you can transport tablespaces that contain XMLTypes, but you must use the IMP and EXP utilities, not Data Pump. When using EXP, ensure that the CONSTRAINTS and TRIGGERS parameters are set to Y (the default).
The following query returns a list of tablespaces that contain XMLTypes:
Compatibility Considerations for Transportable Tablespaces
When you create a transportable tablespace set, Oracle Database computes the lowest compatibility level at which the target database must run. This is referred to as the compatibility level of the transportable set. Beginning with Oracle Database 10g, a tablespace can always be transported to a database with the same or higher compatibility setting, whether the target database is on the same or a different platform. The database signals an error if the compatibility level of the transportable set is higher than the compatibility level of the target database.
The following table shows the minimum compatibility requirements of the source and target tablespace in various scenarios. The source and target database need not have the same compatibility setting.
Minimum Compatibility Requirements
| 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 |
实施步骤：
1、使用视图 v$transportable_platform. 查询两个库的endian format,如果两个库的是相同的平台就不用了这一步了
2、表空间的自包含（单向自包含和双向自包含）
3、使用expdp导出表空间（如果这里是不同的endianness，则需要执行convert the tablespace）
4、拷贝dmp文件和表空间的dbf文件到目标库中
5、执行impdp 导入目标库中
实例操作：
实施准备信息
平台信息：
============================================
硬件平台： IBM X3620 M3
系统版本： Windows enterprise 2008 server r2 X64
Oracle 版本：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
需要操作库的信息：
各海量项目对应的测试数据库实例
编号 项目名称 服务器 实例 用户名
1 XHL 192.168.10.196 DDBC KUYR4
2 HHL 192.168.10.198 DDBC KUYR2
3 ZSM 192.168.10.198 DDBC zjsm
4 SDX 192.168.10.197 DDBC KUYR5
5 DKO 192.168.10.197 MDCK KUYR1
备注： 此次工作目的，需要对现有的测试库进行整理。把目前的3个服务器中的5个schema数据整理到一台服务器中。【数据都会被整理到198上】
此次操作之前面临的几个问题：
1、 三个服务器上有多个实例，并多用户中有相同用户名，存在于目标数据库
2、 不同schema中有相同的表空间名称
3、 在同一库中多个用户共用同一个表空间
4、 表空间中都存在分区表（这里需要用到分区表交换）
5、 每个用户下的数据量大概在500G—1000G
综合考虑上面各个问题，决定使用表空间传输的方式来快速解决此次多库整理问题。具体的实例操作以196DDBC KUYR4数据传输到198 中为例子。
技术亮点：
1、 表空间的传输
2、 分区表交换
第一步， platform和endianness
使用sql语句来查询
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORM. tp, V$DATABASE d
3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------------------------- --------------
Microsoft Windows x86 64-bit Little
第二步，确认表空间和表空间的自包含
使用sql语句来查看用户下的表空间
SQL> select tablespace_name from dba_data_files where tablespace_name in (
2 select tablespace_name from user_tables group by tablespace_name union
3 select tablespace_name from user_tab_partitions group by tablespace_name) group by tablespace_name
4 ;
TABLESPACE_NAME
------------------------------
IRFT_TS_SEC_201209
IRFT_TS_SEC_201211
IRFT_TS_SEC_201208
IRFT_TS_SEC_201210
IRFT_TS_MAIN
IRFT_TS_STATISTICS
IRFT_TS_SEC_DEFAULT
IRFT_TS_LOG_201301
8 rows selected
查询出来 KUYR4用户中需要有8个表空间需要传输，接下来确认表空间的自包含。
Oracle transportable tablespace 时候回检查导出的表空间是否符合自包含性。比如表A表保存在user表空间中，而其索引ind保存在 ints表空间中。那么导入的时候只导出user表空间，那么A表的索引就会毫无意义。而自包含只包括性分为两种：单向自包含和双向自包含。单向自包含是指导出的表空间内的对象不依赖任何非导出的表空间内的任何对象，使用参数设置“transport_full_check=n”可以做这样的检查（impdp默认的设置）；而双向自包含是指导出的表空间内的对象不依赖非导出的表空间内的任何对象，并且非导出表空间内的而对象不依赖任何导出表空间内的任何对象，使用参数设置“ransport_full_check=y”可以做这样的检查。
而对于表空间的自包含这里会用到oracle的 DBMS_TTS包，执行检查表空间的自包含。使用DBMS_TTS包之前必须要用给用户授予execute_catalog_role权限，初始化默认权限是给SYS用户的。
如下：
grant execute_catalog_role to KUYR2;
The following statement can be used to determine whether tablespaces sales_1 and sales_2 are self-contained, with referential integrity constraints taken into consideration (indicated by TRUE).
SQL> EXECUTE sys.DBMS_TTS.TRANSPORT_SET_CHECK('IRFT_TS_SEC_201208',true);
PL/SQL procedure successfully completed
After invoking this PL/SQL package, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this view is empty. The following example illustrates a case where there are two violations: a foreign key constraint, dept_fk, across the tablespace set boundary, and a partitioned table, jim.sales, that is partially contained in the tablespace set.
SQL> select *from sys.transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
ORA-39921: 默认分区 (表) 表空间 USERS (对于 VAV_IRFT_SEC) 未包含在可传输集内。
ORA-39901: 分区表 KUYR4.VAV_IRFT_SEC 部分包含在可传输集内。
那么使用sql 来查询表VAV_IRFT_SEC分区表的分配情况：
SQL> select segment_name,partition_name,tablespace_name from user_segments where segment_name='VAV_IRFT_SEC';
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
----------------------- ------------------------------ ------------------------------
VAV_IRFT_SEC IRFT_TS_SEC_P_20120831 IRFT_TS_KUYR4_SEC_201208
VAV_IRFT_SEC IRFT_TS_SEC_P_20120901 IRFT_TS_KUYR4_SEC_201209
VAV_IRFT_SEC IRFT_TS_SEC_P_20120902 IRFT_TS_KUYR4_SEC_201209
VAV_IRFT_SEC IRFT_TS_SEC_P_20120903 IRFT_TS_KUYR4_SEC_201209
VAV_IRFT_SEC IRFT_TS_SEC_P_20120904 IRFT_TS_KUYR4_SEC_201209
VAV_IRFT_SEC IRFT_TS_SEC_P_20120905 IRFT_TS_KUYR4_SEC_201209
VAV_IRFT_SEC IRFT_TS_SEC_P_20120906 IRFT_TS_KUYR4_SEC_201209
…………
表分区是按照每天一个分区，一个月一个表空间。所以，在这里需要对分区表VAV_IRFT_SEC进行交换分区。如下写的一个过程
begin
for n in (select *
from user_segments
where segment_name = 'VAV_IRFT_SEC'
and (
partition_name like 'IRFT_TS_SEC_P_201208%' or
partition_name like 'IRFT_TS_SEC_P_201209%' or
partition_name like 'IRFT_TS_SEC_P_201210%' or
partition_name like 'IRFT_TS_SEC_P_201211%' or
partition_name like 'IRFT_TS_SEC_P_DEFAULT%'
)) loop
execute immediate 'create table ' || n.partition_name ||'_ex as select * from VAV_IRFT_SEC where 1=0';
execute immediate 'alter table VAV_IRFT_SEC exchange partition '||n.partition_name||' with table '||n.partition_name || '_ex ';
end loop;
end;
在执行这个过程之前，我们需要给KUYR2用户单独创建一个表空间（BACKUP），并设置为用户默认表空间。这个表空间容量不需要太大，30M就够了。因为在做交换分区的时候，oracle只是把分区表的记录对象存放在表空间中。执行脚本完成之后，再去查看分区表的表空间都是backup，可以看来交换分区已经把所有的表存放在BACKUP一个表空间里了。到这里我们可以把表的自包含问题解决了。
alter user KUYR2 default tablespace backup;
第三步： expdp表空间
在做expdp表空间之前，需要对表空间设置为read only 模式，
TABLESPACE_NAME
------------------------------
IRFT_TS_SEC_201209
IRFT_TS_SEC_201211
IRFT_TS_SEC_201208
IRFT_TS_SEC_201210
IRFT_TS_SEC_DEFAULT
Sql segment ：Alter tablespace XXX read only;
设置完成之后开始执行expdp
C:\Users\Administrator>expdp system/DDBC@192.168.10.196/DDBC dumpfile= KUYR2_new_1.dmp directory=DATA_PUMP_DIR transport_tablespaces=IRFT_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, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 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=KUYR2_new_1.dmp directory=DATA_PUMP_DIR transport_tablespaces=MDM
S_TS_SEC_201208 TRANSPORT_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\DDBC\DPDUMP\KUYR4_NEW_1.DMP
******************************************************************************
可传输表空间 IRFT_TS_SEC_201208 所需的数据文件:
E:\ORACLE_DATAFILE\XJHLDATA\SEC\IRFT_TS_SEC_201208.DBF
作业 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已于 09:35:06 成功完成
第四步：copy dmp 文件和表空间的DBF文件到traget库中
这里需要将DMP文件 KUYR2_new_1.dmp和E:\ORACLE_DATAFILE\XJHLDATA\SEC\IRFT_TS_SEC_201208.DBF 拷贝到198服务器指定目录中。
第五步：在目标库中执行impdp入库
需要把copy过来DMP和DBF两个文件impdp入库，
KUYR2/******** dumpfile=KUYR2_1.dmp directory=DATA_PUMP_DIR TRANSPORT_DATAFILES=G:\196\DDBC\KUYR2\IRFT_TS_SEC_201208.DBF
;;;
Import: Release 11.2.0.1.0 - Production on 星期二 4月 16 18:59:06 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "KUYR4"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "KUYR4"."SYS_IMPORT_TRANSPORTABLE_01": KUYR4/******** dumpfile=KUYR4_1.dmp directory=DATA_PUMP_DIR TRANSPORT_DATAFILES=G:\196\DDBC\KUYR4\IRFT_TS_SEC_201208.DBF
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作业 "KUYR4"."SYS_IMPORT_TRANSPORTABLE_01" 已于 18:59:14 成功完成
在目标库中查询，刚才传入的几个表空间。
SQL> select tablespace_name from dba_data_files where tablespace_name in (
2 select tablespace_name from user_tables group by tablespace_name union
3 select tablespace_name from user_tab_partitions group by tablespace_name) group by tablespace_name
4 ;
TABLESPACE_NAME
------------------------------
IRFT_TS_SEC_201209
IRFT_TS_SEC_201211
IRFT_TS_SEC_201208
IRFT_TS_SEC_201210
IRFT_TS_SEC_DEFAULT
已经导入成功导入库中，接下来需要做
1、 对这几个表空间修改模式，更改为read write 模式；
2、 再次交换分区，把分区交换回到原有的表空间中，
分区再次交换：impdp完成之后此时的分区表暂时不是分区表而是一张一张独立的表存储在相应时间的表空间中，需要还原分区表
执行程序：
begin
for n in (select * from user_segments
where
tablespace_name ='IRFT_TS_SEC_201209' or
tablespace_name ='IRFT_TS_SEC_201211' or
tablespace_name ='IRFT_TS_SEC_201208' or
tablespace_name ='IRFT_TS_SEC_201210' or
tablespace_name ='IRFT_TS_SEC_DEFAULT'
and segment_type = 'TABLE') loop
execute immediate 'alter table VAV_IRFT_SEC exchange partition IRFT_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 from user_segments where segment_name='VAV_IRFT_SEC';
Transportable tablespace 的工作已经完成。
还有其他的表空间，但是只需要重新执行1到5步就可以。如果没有分区表，就不需要做2步和5步中的分区交换工作。
注意：在此次过程中会遇到 target 库中的表空间名称和source 库的表空间名有相同的，需要对表空间rename。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24468357/viewspace-772417/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/24468357/viewspace-772417/
7180
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
