最近公司的一个项目里的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平台的环境
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
[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平台的环境
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
[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来查看本地平台的字节顺序
1
2
3
4
5
|
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平台的
1
2
3
4
5
|
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表空间
1
2
3
|
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. |
跨平台表空间传输(linux 10g表空间跨平台迁移到window 11g)
2012-11-05 20:36:01 发表评论
1
2
|
SQL> create user dl identified by dl default tablespace dl; User created. |
3、查看dl用户所属的表空间
1
2
3
4
5
|
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username= 'DL' ; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE -------------------- ------------------------------ ------------------------------ DL DL TEMP |
4、连接用户
1
2
3
4
|
SQL> conn dl/dl Connected. SQL> show user USER is "DL" |
5、创建测试表dl_test
1
2
3
4
5
6
7
8
|
SQL> create table dl_test as select * from dict; Table created. SQL> select count(*) from dl_test; COUNT(*) ---------- 1870 |
四、进行传输
1、查看数据文件的位置
1
2
3
4
5
|
SQL> select file_name from dba_data_files where tablespace_name= 'DL' ; FILE_NAME ------------------------------------------------------------------------------------------------------------------------------------------------------ /home/oracle/oradata/BGTP/dl_data.dbf |
2、然后进行压缩数据文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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)
1
2
3
4
5
6
7
|
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 |
进行严格方式
1
2
3
4
5
6
7
|
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表空间设为只读
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
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 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 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 |
导出成功
5、然后把dl_data.tar.gz与dl.dmp都传输到window的平台上
1
2
3
|
[oracle @master tmp]$ ll dl* -rw-r--r-- 1 oracle oinstall 174677 May 16 10 : 08 dl_data.tar.gz -rw-r--r-- 1 oracle oinstall 16384 May 16 10 : 29 dl.dmp |
6、然后把这2个文件传输到window平台的e:soft目录下对dl_data.tar.gz进行解压
7、然后在window平台上建立用户dl
8、然后进行imp导入
9、然后查看dl_test表数据是否正确
10、在查看linux里的dl_test表里的数据
可以看得2者的数据都一致,所以此次跨平台表空间传输成功
11、最后再将dl表空间设置为读写模式,然后就可以进行正常的数据操作了
这样就完成了跨平台传输表空间,这种方法很方便、而且快速,以后将作为我公司oracle的数据迁移的方案之一!