跨平台表空间传输（linux 10g表空间跨平台迁移到window 11g）-阿里云开发者社区

最近公司的一个项目里的linux 系统中的oracle 10g数据库，需要把某个表空间里的所有数据都迁移到window 2003的11g里，经过我与dba的交流、测试，决定使用跨平台的表空间传输技术，目前此项任务已经完成，经过测试，没有问题，下面是我为此项任务做的测试，此次是做linux（oracle 10g）对window （oracle 11g）做跨平台表空间传输（同字节顺序）需要注意：

对于导出文件dmp来说，Oracle实现了跨平台的处理，即在任何一个可运行的平台上，都能使用imp将其导入，但是对于表空间所包含的数据文件而言，各个平台上是不一样的，包含不能通过简单的复制来处理。数据文件不是独立于OS的。

传输表空间移动数据速度非常快。

使用前提：

1.源和目标数据库使用相同的字符集（也就是说little endian与big engian）；

2.目标数据库不能有和源表空间相同的表空间；

3.源和目标数据库拥有相同的块大小；

4.必须传输自包含的对象集；

5.源和目标数据库运行的硬件平台必须相同；

下图是我的环境

一、环境检查

1、检查linux平台的环境

[root@master ~]# su - oracle
[oracle@master ~]$ sqlplus sys/dl528888@10.1.88.188:1521/bgtp as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 16 09:33:01 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select instance_name,host_name,version from v$instance where rownum < 2;
INSTANCE_NAME HOST_NAME VERSION
---------------- ---------------------------------------------------------------- -----------------
BGTP master 10.2.0.1.0

2、检查windows平台的环境

[oracle@master dbs]$ sqlplus sys/dl528888@10.1.88.191:1521/bgtp as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 16 09:22:58 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select instance_name,host_name,version from v$instance where rownum < 2;
INSTANCE_NAME HOST_NAME VERSION
---------------- ---------------------------------------------------------------- -----------------
bgtp ADMIN-F99B9A60C 11.2.0.1.0

二、字节顺序检查

1、linux平台的

通过v$transportable_platfrom与v$database来查看本地平台的字节顺序

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 64-bit for AMD Little

2、windows平台的

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
----------------------------------------------------------------------------------------------------- --------------
Microsoft Windows IA (32-bit) Little

三、进行准备工作

1、在linux平台里先建立dl表空间

SQL> create tablespace dl logging datafile '/home/oracle/oradata/BGTP/dl_data.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;
Tablespace created.

2、创建dl用户默认dl表空间

SQL> create user dl identified by dl default tablespace dl;
User created.

3、查看dl用户所属的表空间

SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='DL';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
-------------------- ------------------------------ ------------------------------
DL DL TEMP

4、连接用户

SQL> conn dl/dl
Connected.
SQL> show user
USER is "DL"

5、创建测试表dl_test

SQL> create table dl_test as select * from dict;
Table created.
SQL> select count(*) from dl_test;
COUNT(*)
----------
1870

四、进行传输

1、查看数据文件的位置

SQL> select file_name from dba_data_files where tablespace_name='DL';
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/home/oracle/oradata/BGTP/dl_data.dbf

2、然后进行压缩数据文件

SQL> !
[oracle@master ~]$ cd /home/oracle/oradata/BGTP/
[oracle@master BGTP]$ ll
total 1056712
-rw-r----- 1 oracle oinstall 7061504 May 16 10:07 control01.ctl
-rw-r----- 1 oracle oinstall 7061504 May 16 10:07 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 May 16 10:07 control03.ctl
-rw-r----- 1 oracle oinstall 52436992 May 16 10:05 dl_data.dbf
-rw-r----- 1 oracle oinstall 52429312 May 15 22:00 redo01.log
-rw-r----- 1 oracle oinstall 52429312 May 16 07:01 redo02.log
-rw-r----- 1 oracle oinstall 52429312 May 16 10:07 redo03.log
-rw-r----- 1 oracle oinstall 314580992 May 16 10:07 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 May 16 10:07 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 May 15 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 26222592 May 16 10:07 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 May 16 07:06 users01.dbf
[oracle@master BGTP]$ tar zcf dl_data.tar.gz dl_data.dbf
[oracle@master BGTP]$ du -sh dl_data.dbf
51M dl_data.dbf
[oracle@master BGTP]$ du -sh dl_data.tar.gz
180K dl_data.tar.gz

3、使用exp导出dl表空间

导出之前，先查看表空间dl是否有self-contained（自包含），通过系统包dbms_tts来检查表空间是否自包含，如果有的话，就不能进行传输

先进行非严格方式（full_check=false）

SQL> exec sys.dbms_tts.transport_set_check('DL',TRUE);
PL/SQL procedure successfully completed.
SQL> select * from sys.transport_set_violations;
no rows selected

进行严格方式

SQL> exec sys.dbms_tts.transport_set_check('DL',TRUE,true);
PL/SQL procedure successfully completed.
SQL> select * from sys.transport_set_violations;
no rows selected

这表示该表空里的对象集是自包含的(对象及其索引都在此表空间中)

下面可以进行exp了

4、在进行exp导出之前，先把dl表空间设为只读

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> alter tablespace dl read only;
Tablespace altered.
[oracle@master tmp]$ exp userid=\'sys/dl528888 as sysdba\' tablespaces=DL transport_tablespace=y file=/tmp/dl.dmp
Export: Release 10.2.0.1.0 - Production on Wed May 16 10:29:47 2012
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
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
About to export transportable tablespace metadata...
For tablespace DL ...
. exporting cluster definitions
. exporting table definitions
. . exporting table DL_TEST
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
[oracle@master tmp]$ ll dl.dmp
-rw-r--r-- 1 oracle oinstall 16384 May 16 10:29 dl.dmp
[oracle@master tmp]$ du -sh dl.dmp
20K dl.dmp