ITPub博客

首页 > 数据库 > Oracle > 同字节序跨平台表空间传输的测试

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

原创 Oracle 作者:jolly10 时间:2008-10-07 09:46:34 0 删除 编辑
目的:将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.

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

下一篇: 大表exp/imp迁移
请登录后发表评论 登录
全部评论

注册时间:2008-02-20

  • 博文量
    263
  • 访问量
    769067