同位元組序跨平臺表空間傳輸的測試

目的：將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.