欢迎来到HugNew-拥抱变化,扫一扫右边二维码关注微信订阅号:Martin说 或 加QQ群:427697041互相交流,Stay hungry, Stay foolish.

将数据库表从ms sql server 2005迁移到oracle 11g

数据库 jim 3599℃ 2评论

0. 背景

需要将ms sql server 2005的数据转移到oracle 11g中,大约几百兆的数据,几十万个记录

使用了sql server自己的数据导入导出功能,发现只能导出数据表的sql文件,没法导出数据,或者导出数据的时候报告一些OLE DB的错误。

无奈之下,只能转投命令行工具,发现可以导出类是csv格式的数据。

下面主要描述使用中间数据文件,csv格式的数据文件来做导入导出,主要工作在于中间文件的处理

主要使用了sql server自带的bcp, sqlcmd 和oracle自带的sqlldr命令行工具来进行数据迁移

1. 准备

需要将sql server中的表结构重新在oracle中创建,本文中将sql server中的所有的表的字段都创建成了varchar2类型的字符串了

可以在sql server中导出表结构的sql,然后更改一下作为oracle的创建表的sql

2. sql server 2005的数据导出

F:\>bcp Information.dbo.TempTeShu out f:\sqlserver-oracle\TempTeShu.txt -c  -t','  -U sa -P mypassword

F:\sqlserver-oracle\tables_data\>bcp Information.dbo.Infoes out f:\sqlserver-oracle\Infoes.txt  -c -t',' -U sa -P mypassword

开始复制...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]警告: 具有格式文件的 BCP 导入
将把分隔列中的空字符串转换为 NULL。
已成功将 1000 行大容量复制到主文件。总共接收到: 1000
已成功将 1000 行大容量复制到主文件。总共接收到: 2000
已成功将 1000 行大容量复制到主文件。总共接收到: 3000
已成功将 1000 行大容量复制到主文件。总共接收到: 4000
已成功将 1000 行大容量复制到主文件。总共接收到: 5000
已成功将 1000 行大容量复制到主文件。总共接收到: 6000
已成功将 1000 行大容量复制到主文件。总共接收到: 7000
已成功将 1000 行大容量复制到主文件。总共接收到: 462000
已成功将 1000 行大容量复制到主文件。总共接收到: 463000
已成功将 1000 行大容量复制到主文件。总共接收到: 464000
已成功将 1000 行大容量复制到主文件。总共接收到: 465000

已复制 465090 行。
网络数据包大小(字节): 4096
总时钟时间(毫秒)     : 11108  平均值: (每秒 41869.82 行。)

注意上述命令中的-t选项是字段分割符,使用了’,’,这主要是因为在第一次导出数据的时候发现数据中没有使用'(单引号),而双引号在数据中有被使用。

3. 查看导出的数据行数是否与数据库中记录数一致

在数据库中查看记录数是45090,但是导出的数据行数是45732,说明了数据中带有回车换行符。
在vim中查找首行不是数字的(数据的第一个字段是ID,是个数字),查找出哪个字段中有回车换行,使用sql语句在数据库中就将回车换行替换掉。

譬如说找到Infoes表中的phone字段中含有回车换行,这样在sql server mgmt studio中启动查询窗口,执行如下update

update Information.dbo.Infoes
SET phone = REPLACE(phone, CHAR(13), ',')
update ResidentInformation.dbo.InhabitantInfoes
SET phone = REPLACE(phone, CHAR(10), ',')

4. 重新导出数据,并且查看是否行数与数据库中记录数一致,如不一致重新执行3

5. 将导出的数据行首与行尾加上‘(单引号)

在vim下面使用如下

:%s/^/'/g
:%s/$/'/g

在目前为止数据中会有一些乱码,我们的最终目标是utf8编码的ascii文件,如下所示:

>file Infoes
Infoes.txt: UTF-8 Unicode text, with very long lines, with CRLF line terminators

6. 将文件中的NUL删除

>sed -i 's/\x00//g' Infoes.txt

NUL是ascii字符0, 在sql server中没有存储值的字段可能会导出成这个NUL
而sql server中的NULL值,会导出空字符串

7. 将数据中的^M去掉,此字符在vim中是\r, carriage return

在vim中指向如下替换:

:%s/\r//g

8. 经过上述处理,file Infoes.txt会出现是 extended ascii, 还可以使用VIM对数据进行转码存储

在我的VIM文件中可以如下

:set fileencoding=utf8

然后保存即可
这时用file infoes.txt可以看到是utf8编码的文件带CRLF

9. 使用上面的CSV格式的数据,可以使用sqlldr装载到oracle数据库中

9.1 首先编写对应的ctl文件, infoes.ctl,如下:

LOAD DATA
CHARACTERSET 'UTF8'
INFILE 'inhabitantInfoes.txt'
REPLACE into TABLE inhabitantInfoes
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'"
TRAILING NULLCOLS
(
ID ,
RoomID ,
RoadNumID ,
RoadID ,
BuildID ,
CommunityID ,
StreetID ,
DistrictGovID ,
Name ,
IdentityCardCode ,
Sex ,
Nationality ,
Birth ,
Birthplace 

)

9.2 使用sqlldr装载数据

sqlldr myname/mypassword@localhost:1521/orcl control=infoes.ctl log=infoes.log bad=infoes.bad

然后查看对应的log和bad文件是否没有错误, 如果有错误可以更改数据文件的格式

F:\sqlserver-oracle\tables_data>set TAB=Room

F:\sqlserver-oracle\tables_data>sqlldr myresident/myresident@localhost:1521/orcl
control=%TAB%.ctl log=%TAB%.log bad=%TAB%.bad

SQL*Loader: Release 11.2.0.1.0 - Production on 星期四 4月 9 12:43:28 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

达到提交点 - 逻辑记录计数 64
达到提交点 - 逻辑记录计数 128
达到提交点 - 逻辑记录计数 192

达到提交点 - 逻辑记录计数 465059
达到提交点 - 逻辑记录计数 465076
达到提交点 - 逻辑记录计数 465090

这样如果没有错误数据就完全导入了。

附记:这个数据库文件如果再大的话,是不是导出数据文件就会非常大,譬如说几G,这样的话就很难离线处理了,可以分批次导入,导出的方式去做。 或者写程序来实现两个数据库格式的转换。

ref:
https://technet.microsoft.com/zh-cn/library/ms162802(v=sql.90).aspx
https://technet.microsoft.com/zh-cn/library/ms170207(v=sql.90).aspx
http://www.jb51.net/article/23664.htm
http://www.cnblogs.com/xiaogangqq123/archive/2011/09/30/2196727.html
http://chenjie.blog.51cto.com/24029/7195/
http://blog.itpub.net/18922393/viewspace-709034/
http://blog.chinaunix.net/uid-743704-id-2014695.html
http://coupling001.blog.163.com/blog/static/174925389201262093959635/
http://blog.csdn.net/lwei_998/article/details/19154371
http://blog.csdn.net/wojiaoxiaol/article/details/2343320

转载请注明:HugNew » 将数据库表从ms sql server 2005迁移到oracle 11g

喜欢 (1)or分享 (0)
发表我的评论
取消评论

表情
(2)个小伙伴在吐槽
  1. 经博主提醒,可以使用navicat for sql server来连接sql server数据库来导出csv数据 kettle可以用来进行ETL数据转换 经过测试,navicat导出的数据完全符合要求,而且已经经过转码到UTF8,就是导出过程稍慢 Kettle Spoon还不太会用 对于少量数据<200M,还是可以使用这些简单导入导出工具的。 对于大量数据是不是必须要用Kettle这种类似的工具或者写一个程序在线处理? 否则数据导入导出和处理就非常慢了
    匿名2015-04-09 15:05 回复
    • kettle一个工具就可以完成异构数据库的迁移
      匿名2015-04-10 07:16 回复