Transporting Oracle9i Tablespaces from Solaris to HP-UX

我们知道跨平台表空间传输是在Oracle10g以后实现的,用RMAN命令可以完成。

那么在Oracle9i数据库中到底跨平台表空间传输能不能够实现呢?Oracle官方文档中提到:

The source and target database must be on the same hardware platform. For example, you can transport tablespaces between Sun Solaris Oracle databases, or you can transport tablespaces between Windows NT Oracle databases. However, you cannot transport a tablespace from a Sun Solaris Oracle database to an Windows NT Oracle database.

必须要在同一硬件平台上,并且举例无法从Solaris平台传输表空间到Windows平台。

但是实际上,这样的约束仅仅是由于字符序的限制。

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

可以看到所有的Windows平台上Oracle数据库均是小字符序的,而Solaris SPARC平台是大字符序,因此Oracle官方文档笼统地说不支持跨平台传输表空间。

选择同样为大字符序的操作系统Solaris SPARC 64bit和HP-UX IA (64-bit),做Oracle9i数据库的跨平台表空间传输,实验证明是完全可行的。

Solaris平台配置:Solaris10,Oracle9.2.0.6
HP-UX平台配置:HP-UX11,Oracle9.2.0.8

无论是操作系统平台还是数据库小版本号都是不一样的。

在Solaris端,USERS表空间中有数据文件/dev/md/rdsk/d29(裸设备),并且存在两张表,KAMUS.T1和SYSTEM.T。

SQL> select segment_name,owner from dba_segments where tablespace_name='USERS';

SEGMENT_NAME                   OWNER
------------------------------ ------------------------------
T                              SYSTEM
T1                             KAMUS

将USERS表空间设置为READONLY之后,开始传输表空间。

$ exp TRANSPORT_TABLESPACE=y TABLESPACES=(users)
 TRIGGERS=n CONSTRAINTS=n GRANTS=n FILE=/install/expdat.dmp

Export: Release 9.2.0.6.0 - Production on Thu Nov 19 17:25:48 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Username: SYS/oracle AS SYSDBA

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
Note: table data (rows) will not be exported
Note: grants on tables/views/sequences/roles will not be exported
Note: constraints on tables will not be exported
About to export transportable tablespace metadata...
For tablespace USERS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                              T
. . exporting table                             T1
. end transportable tablespace metadata export
Export terminated successfully without warnings.

然后通过dd命令将数据文件以及dmp文件copy到HP-UX机器上,在我的实验环境中,最终是dd到/dev/testvg/rlvol4上。

然后在HP-UX上导入metadata:

imp  PARFILE='par.f'

其中par.f文件的内容如下:

TRANSPORT_TABLESPACE=y 
FILE=/oracle/oradata/expdat.dmp
DATAFILES=('/dev/testvg/rlvol4')
TABLESPACES=(users) 
TTS_OWNERS=(system,kamus)  
FROMUSER=(system,kamus) 
TOUSER=(system,system)

成功导入之后,查看USERS表空间中的数据。

SQL> select segment_name,owner from dba_segments where tablespace_name='USERS';

SEGMENT_NAME                   OWNER
------------------------------ ------------------------------
T                              SYSTEM
T1                             SYSTEM

继续在该表空间中做其他操作,比如create table等,也一切正常。

只做了使用裸设备作为数据文件的测试,不确认是不是如果数据文件存储在文件系统上也同样可以成功。如果是AIX平台,要注意一下,在AIX中裸设备头部有4K的OS Reserved Area,dd的时候需要skip掉。

没有做更多平台的测试,但是可以推测,即使是Oracle9i,Linux,Windows这两个平台或者AIX,HP-UX,SOLARIS SPARC这三个平台之间都应该是可以顺利地完成表空间传输的。

Update@2009-12-28
朋友测试的结果,从HP-UX IA (64-bit)平台传输表空间到 AIX-Based Systems (64-bit)平台无法成功。即使dd了文件头也仍然会报错。

One Comment Add yours

  1. robin says:

    大师,学习了

Leave a Reply

Your email address will not be published. Required fields are marked *