利用10g传输表空间实现AIX平台到LINUX平台数据迁移

浏览数：59 / 时间：2015年06月20日

在 oracle10g 之前，数据文件是不能跨平台传输使用的，从oracle 10g 开始，oracle支持跨平台表空间传输（transport tablespace）。

数据文件不能跨平台的原因是：不同平台操作系统的字、字节存储顺序不同

如果将 4567 这个数据存储到不同的系统中，那么就会出现如下的顺序：

little-endian：存储的顺序是7654 ，小数在前（windows平台，LINUX平台）

big-endian：存储的顺序是 4567 ，大数在前（solaris及aix平台）

本文主要描述采用oracle的 transport tablespace技术，实现不同字节序跨平台迁移。

注意：传输表空间技术不能应用于system表空间或sys用户拥有的对象

源平台（AIX6.1+ oracle 11.2.0.4.0）相关信息如下

SQL> select * from v$version where rownum=1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL> set lines 200

SQL> col platform_name for a30;

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

------------------------------ --------------

AIX-Based Systems (64-bit) Big

目的平台（linux5.8 + oracle 11.2.0.4.0）相关信息如下

SQL> select * from v$version where rownum=1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

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

从上面的输出可以看到字节顺序是不同的， Linux平台是低位（Little），aix 平台是高位（Big）

实施如下;
    前提条件:
        a,在源和目标库构建directory(以sysdba用户)
        b,以system用户在源和目标库进行expdp/impdp
        c,在源库进行cross os表空间导出时,先须offline(alter tablespace crosstbs read only;)
        d,在源库和目标库要构建cross os表空间的对应用户
        e,源库和目标库存储dmp文件的目录(directory),可一致也可不一样,这个

目标库和源库上都要构建：

SQL> create directory backup as ‘/home/oracle/backup‘;

1：在AIX上创建表空间 aix_trans ，然后在该表空间上创建 aix_trans 用户

SQL> create tablespace aix_trans datafile ‘/oradata/CWFOL/datafile/test/aix_trans.dbf‘ size 10m;

Tablespace created.

SQL> create user trans identified by trans default tablespace aix_trans;

User created.

SQL> conn trans/trans;

Connected.

SQL> create table t(id int);

Table created.

SQL> insert into t values(200);

1 row created.

SQL> insert into t values(2001);

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from t;

COUNT(*)

----------

2：将 aix_trans 表空间设置为只读，并导出

SQL> conn /as sysdba

Connected.

SQL> alter tablespace aix_trans read only;

Tablespace altered.

$ expdp \‘/ as sysdba\‘ directory=backup dumpfile=aix_trans.dmp transport_tablespaces=aix_trans

Export: Release 11.2.0.4.0 - Production on Wed Jan 7 15:32:38 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" directory=backup dumpfile=aix_trans.dmp transport_tablespaces=aix_trans

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:

/home/oracle/backup/aix_trans.dmp

******************************************************************************

Datafiles required for transportable tablespace AIX_TRANS:

/oradata/CWFOL/datafile/test/aix_trans.dbf

Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Wed Jan 7 15:33:14 2015 elapsed 0 00:00:35

3：使用 rman的 convert 命令进行转换

$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jan 7 15:42:48 2015

connected to target database: CWFOL (DBID=325303246)

RMAN> convert tablespace aix_trans to platform ‘Linux x86 64-bit‘ format=‘/tmp/aix_trans01.dbf‘;

Starting conversion at source at 07-JAN-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=293 device type=DISK

channel ORA_DISK_1: starting datafile conversion

input datafile file number=00009 name=/oradata/CWFOL/datafile/test/aix_trans.dbf

converted datafile=/tmp/axi_trans01.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01

Finished conversion at source at 07-JAN-15

Starting Control File and SPFILE Autobackup at 07-JAN-15

piece handle=/backup/oradata/cf_c-325303246-20150107-04 comment=NONE

Finished Control File and SPFILE Autobackup at 07-JAN-15

上传aix_trans.dmp和axi_trans01.dbf到目标库：

在linux库上转换数据文件，将数据文件 axi_trans01.dbf 转换为 aix_trans.dbf

ASMCMD [+DATA/MECBS/DATAFILE] > cp /home/oracle/backup/axi_trans01.dbf +DATA/MECBS/DATAFILE/

copying /home/oracle/backup/axi_trans01.dbf -> +DATA/MECBS/DATAFILE/axi_trans01.dbf

[oracle@node1 backup]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jan 7 15:49:33 2015

connected to target database: MECBS (DBID=1527329870)

RMAN> convert datafile ‘+DATA/MECBS/DATAFILE/axi_trans01.dbf‘ db_file_name_convert ‘+DATA/MECBS/DATAFILE/axi_trans01.dbf‘,‘+DATA/MECBS/DATAFILE/aix_trans.dbf‘;

Starting conversion at target at 07-JAN-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=79 instance=MECBS1 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=143 instance=MECBS1 device type=DISK

channel ORA_DISK_1: starting datafile conversion

input file name=+DATA/mecbs/datafile/axi_trans01.dbf

converted datafile=+DATA/mecbs/datafile/aix_trans.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:08

Finished conversion at target at 07-JAN-15

Starting Control File and SPFILE Autobackup at 07-JAN-15

piece handle=+BACK/backup/db210_incr_20150107/pid-c-1527329870-20150107-08 comment=NONE

Finished Control File and SPFILE Autobackup at 07-JAN-15

SQL> create user trans identified by trans；

User created.

SQL> grant connect,resource to trans;

Grant succeeded.

在linux 平台导入数据

[oracle@node1 backup]$ impdp \‘ / as sysdba\‘ directory=backup dumpfile=aix_trans.dmp transport_datafiles=+DATA/MECBS/DATAFILE/aix_trans.dbf

Import: Release 11.2.0.4.0 - Production on Wed Jan 7 16:05:52 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Source time zone version is 11 and target time zone version is 14.

Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" directory=backup dumpfile=aix_trans.dmp transport_datafiles=+DATA/MECBS/DATAFILE/aix_trans.dbf

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Wed Jan 7 16:06:19 2015 elapsed 0 00:00:21

验证：

SQL> set lines 200

SQL> col PLATFORM_NAME for a30;

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> SQL>