oracle 同字节序跨平台,同字节序跨平台表空间传输的测试

目的：将linux的测试表空间传到到solaris[@more@]

源系统情况：

OS：RHEL4 U4

oracle:10.2.0.1

IP：172.17.61.131

目标系统情况：

OS：solaris 10

oracle:10.2.0.2

IP：172.17.61.130

linux平台下

[oracle@rhel131 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 6 08:50:31 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> col platform_name for a40

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

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

Linux IA (32-bit) Little

创建一个独立的表空间

SQL> create tablespace trans

2 datafile '/u01/app/oradata/orcl/trans.dbf' size 10m;

Tablespace created.

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

User created.

SQL> conn trans/trans

Connected.

SQL> create table test as select * from dict;

Table created.

SQL> select count(*) from test;

COUNT(*)

----------

659

导出要传输的表空间之前要先置为只读

SQL> conn /as sysdba

Connected.

SQL> alter tablespace trans read only;

Tablespace altered.

[oracle@rhel131 ~]$ NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 [oracle@rhel131 ~]$ export NLS_LANG

[oracle@rhel131 ~]$ exp '/ as sysdba' tablespaces=trans transport_tablespace=y file=exp_trans.dmp

Export: Release 10.2.0.1.0 - Production on Mon Oct 6 09:01:17 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

导出表空间

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)

Note: table data (rows) will not be exported

About to export transportable tablespace metadata...

For tablespace TRANS ...

. exporting cluster definitions

. exporting table definitions

. . exporting table TEST

. exporting referential integrity constraints

. exporting triggers

. end transportable tablespace metadata export

Export terminated successfully without warnings.

由于我的oracle版本是10的，所以对trans.dbf文件不需要转换，可直接传输过去。

如是9i的版本则需要通过RMAN转换文件格式。转换方法是：

RMAN> convert tablespace trans

to platform 'Solaris Operating System (x86)'

format '/tmp/%N_%f';

将trans.dbf和exp_trans.dmp通过ssh传输过去。

solaris平台下

$ pwd

/export/home/oracle

$ ls exp_trans.dmp trans.dbf

exp_trans.dmp trans.dbf

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 6 13:57:22 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options

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

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

Solaris Operating System (x86) Little

导入之前需要先建立帐户.

SQL> create user trans identified by trans;

User created.

SQL> grant connect,resource to trans;

Grant succeeded.

$ imp '/ as sysdba' tablespaces=trans transport_tablespace=y file=exp_trans.dmp datafiles=/export/home/oracle/trans.dbf

Import: Release 10.2.0.2.0 - Production on Mon Oct 6 14:01:49 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

About to import transportable tablespace(s) metadata...

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

. importing SYS's objects into SYS

. importing SYS's objects into SYS

. importing TRANS's objects into TRANS

. . importing table "TEST"

. importing SYS's objects into SYS

Import terminated successfully without warnings.

检查一下

SQL> select name from v$tablespace;

NAME

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

SYSTEM

UNDOTBS1

SYSAUX

USERS

TEMP

TRANS

6 rows selected.

SQL> select count(*) from trans.test;

COUNT(*)

----------

659

SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

UNDOTBS1 ONLINE

SYSAUX ONLINE

TEMP ONLINE

USERS ONLINE

TRANS READ ONLY

6 rows selected.

传输过来的表空间还处于read only状态，需要改成read write.同样原系统的trans表空间也要改成read write.

SQL> alter tablespace trans read write;

Tablespace altered.