ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 表空间的传输 for window 平台

表空间的传输 for window 平台

原创 Linux操作系统 作者:wangluozhongzi 时间:2013-09-09 17:21:03 0 删除 编辑

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

第一步, platformendianness. 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个服务器中的5schema数据整理到一台服务器中。【数据都会被整理到198上】

 

 

 

 

 

 

此次操作之前面临的几个问题:

 

 

1、  三个服务器上有多个实例,并多用户中有相同用户名,存在于目标数据库

2、  不同schema中有相同的表空间名称

3、  在同一库中多个用户共用同一个表空间

4、  表空间中都存在分区表(这里需要用到分区表交换)

5、  每个用户下的数据量大概在500G—1000G

 

综合考虑上面各个问题,决定使用表空间传输的方式来快速解决此次多库整理问题。具体的实例操作以196DDBC  KUYR4数据传输到198 中为例子。

 

 

技术亮点:

1、  表空间的传输

2、  分区表交换

 

 

第一步, platformendianness

使用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语句来查看用户下的表空间

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

 

SQL>

查询出来 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 

SQL>

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>

那么使用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.dmpE:\ORACLE_DATAFILE\XJHLDATA\SEC\IRFT_TS_SEC_201208.DBF 拷贝到198服务器指定目录中。

 

第五步:在目标库中执行impdp入库

 

 

需要把copy过来DMPDBF两个文件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 的工作已经完成。

还有其他的表空间,但是只需要重新执行15步就可以。如果没有分区表,就不需要做2步和5步中的分区交换工作。

注意:在此次过程中会遇到 target 库中的表空间名称和source 库的表空间名有相同的,需要对表空间rename

 

 

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24468357/viewspace-772417/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2013-06-26

  • 博文量
    2
  • 访问量
    2757