Sybase到Oracle数据迁移.docx
《Sybase到Oracle数据迁移.docx》由会员分享，可在线阅读，更多相关《Sybase到Oracle数据迁移.docx（11页珍藏版）》请在冰豆网上搜索。
Sybase到Oracle数据迁移
Sybase到Oracle数据迁移
一Sybase数据导出
1.1Sybase数据导出到TXT文件
在跨数据库从Sybase导入Oracle过程中首先会遇到的问题是,中间以什么样的数据格式做为Sybase与Oracle都认同的中间数据格式.Sybase数据库导出一般使用BCP,BCP出来的TXT文档数据也能被Oracle所接受.所以在整个过程导出Sybase的数据选择BCP导出例:
导出命令
bcppersonal.PA_ACCOUNToutPA_ACCOUNT.txt-Upersonal-Psuntek-SSYBASE-c-oresult.txt
1.2Sybase导出TXT文件异常处理
在前面的基础上讲一下BCP导出的时候可能会遇到的问题,Sybase中Bcp导出的时候,如果表中的字段里面含有换行符号的话,会在txt文件中做换行处理,那行就会出现一行变成两行,换行符号在实际数据中可能是用户拷贝粘贴或者Web提交的把换行提交到数据库中.如图所示
上行的数据中,因为其数据中含有回车,一条记录导出TXT里换成了两行,我们在数据导出过程要考虑这种情况,这种情况也是数据导出后导入Oracle失败的主要原因.
解决以上情况的办法:
一般的BCP导出的命令
bcppersonal.PA_ACCOUNToutPA_ACCOUNT.txt-Upersonal-Psuntek-SSYBASE-c-oresult.txt
在上面的导出命令中加入一个参数:
-r"&_&",该参数的作用,指定换行符号.
最后的BCP命令
bcppersonal.PA_ACCOUNToutPA_ACCOUNT.txt-Upersonal-Psuntek-SSYBASE-c-r"&_&"-oresult.txt
导出的文档然后要做一些变换,变换后的TXT文档就不会换行,除非在字段数据中有换行符号,导出的TXT文档用UltraEdit编辑工具进行处理
首先把换行符替换成空格,防止一行数据因为有换行符号导出TXT时变成两行数据无法导入Oracle,替换方法见图
然后再把BCP自定义换行符号”&_&”替换成换行符号,替换方法见图
[以上两步操作以UltraEdit为例子]
通过以上操作就能解决Sybase导出数据一条记录因为数据中有换行符号在TXT变成两行而无法正确导入Oracle临时库的问题.
二导出数据导入Oralce临时库
2.1Sybase建表脚本转为Oracle建脚本
把导出的TXT文件如何完整导入到Oracle中,是导数据过程中需要重点注意的事项,特别是一种比较特殊的数据类型的转换.
首先Sybase导出的TXT文件要导入Oracle临时库,那么Oracle临时库的表结构应该跟Sybase的库结构一致.可以先用powerdesign之类的工具先把Sybase的表结构导出来,然后再根据导出来的表结构修改成Oracle的临时库,一些Sybase专用的字段类型如tinyint要转成Oracle的int,这种类型的转换是要注意的
在Sybase建表脚本转换到Oracle建表脚本的时候重点要注意像:
date之类的数据,因为Sybase中日期型的数据跟Oracle中日期型的数据格式是不同的,最好Oralce中当成字符处理,然后再在处理数据的脚本中做转换,在后面会提到.
例:
sybase建表脚本/*==============================================================*/
/*Table:
PA_ACCOUNT*/
/*==============================================================*/
createtablePA_ACCOUNT（
CN_USERIDnumeric（12）identity,
CN_PHONENUMvarchar（12）notnull,
CN_PASSWDvarchar（32）notnull,
CN_REG_MOTHEDtinyintnotnull,
CN_REG_TIMEdatetimenotnull,
CN_USER_STATUStinyintnotnull,
CN_IDCARDvarchar（18）notnull,
CN_LOGOUT_TIMEdatetimenull,
CN_ACTIVE_CODEvarchar（12）null,
CN_AREACODEvarchar（4）notnull,
CN_REG_TYPEtinyintdefault1null
）
go
修改后的Oracle建表脚本
/*==============================================================*/
/*Table:
PA_ACCOUNT*/
/*==============================================================*/
createtablePA_ACCOUNT（
userseqint,
CN_USERIDnumeric（12）null,
CN_PHONENUMvarchar（12）null,
CN_PASSWDvarchar（32）null,
--CN_PASSWD1varchar（32）null,
CN_REG_MOTHEDintnull,
CN_REG_TIMEvarchar（30）null,
CN_USER_STATUSintnull,
CN_IDCARDvarchar（20）null,
CN_LOGOUT_TIMEvarchar（30）null,
CN_ACTIVE_CODEvarchar（12）null,
CN_AREACODEvarchar（4）null,
CN_REG_TYPEintdefault1null
）;
commit;
建议:
Sybase中对应的除数值型数据外,在Oracle中全部转换为字符型
2.2Sybase数据导入Oracle临时库
建议Sybase中非数值型的数据全部当字符型数据导入,然后在Oracle从临时库到正式库处理时做具体转换.
在大量txt类型的数据要导入Oracle中,Oracle有一个自带的工具,SQLLoader是Oracle提供的一个实用程序，用以将外部TXT文件按要求加载到Oracle数据库
SQLloader的用法很简单下面以导入一个pa_super_mail表为例简单介绍一下:
建立pa_super_mail.ctl,内容如下
loaddata
infile'..\pa_super_mail.txt'--指明TXT文件的路径
appendintotablepa_super_mai--指明要插入的表l
fieldsterminatedbyX'09'--字段终止于X'09'，是一个制表符（TAB）分开
（
CN_USERID,
CN_MAIL,
CN_PASSWD,
CN_MEMO--这几个为字段名,见下面的Oracle临时表建表脚本,注意只要字段名就可以了,不用指定数据类型
）
PA_SUPER_MAIL建表脚本
/*==============================================================*/
/*Table:
PA_SUPER_MAIL*/
/*==============================================================*/
createtablePA_SUPER_MAIL（
CN_USERIDnumeric（12）null,
CN_MAILvarchar（40）null,
CN_PASSWDvarchar（15）null,
CN_MEMOvarchar（100）null
）;
commit;
createindexi_PA_SUPER_MAILonPA_SUPER_MAIL（CN_USERID）;
表,Ctl文件及TXT数据文件准备好就可以用Sqlldr导入,具体命令如下:
sqlldrfj_ora/fj_ora@zxincontrol=pa_super_mail.ctlerrors=999999
fj_ora/fj_ora@zxin这个不用说了就是用户名密码及注册服务名
errors=999999表示如果导入数据失败等于该值便终止导入
在导入数据完毕,根据其产生的LOG文件可以看到有多少数据成功与失败及失败的原因,失败数据会写入一个bad文件中,从中可以分析出数据的问题之所在,不断修改而达到数据100%的导入率.
Log日志如下:
SQL*Loader:
Release9.2.0.6.0-Productionon星期一5月2120:
33:
212007
Copyright（c）1982,2002,OracleCorporation.Allrightsreserved.
控制文件:
pa_super_mail.ctl
数据文件:
..\pa_super_mail.txt
错误文件:
pa_super_mail.bad
废弃文件:
未作指定
:
（可废弃所有记录）
加载数:
ALL
跳过数:
0
允许的错误:
999999
绑定数组:
64行，最大256000字节
继续:
未作指定
所用路径:
常规
表PA_SUPER_MAIL
已加载从每个逻辑记录
插入选项对此表APPEND生效
列名位置长度中止包装数据类型
--------------------------------------------------------------------------
CN_USERIDFIRST*WHTCHARACTER
CN_MAILNEXT*WHTCHARACTER
CN_PASSWDNEXT*WHTCHARACTER
CN_MEMONEXT*WHTCHARACTER
表PA_SUPER_MAIL:
13189行加载成功
由于数据错误,0行没有加载。
由于所有WHEN子句失败,0行没有加载。
由于所有字段都为空的,0行没有加载。
为结合数组分配的空间:
66048字节（64行）
读取缓冲区字节数:
1048576
跳过的逻辑记录总数:
0
读取的逻辑记录总数:
13189
拒绝的逻辑记录总数:
0
废弃的逻辑记录总数:
0
从星期一5月2120:
33:
212007开始运行
在星期一5月2120:
33:
222007处运行结束
经过时间为:
00:
00:
00.89
CPU时间为:
00:
00:
00.12
从以上日志还可以看出导入数据的具体时间
建议:
可以把Sqlldr命令做成Bat批处理文件,方便现场人员操作与处理.
三Oracle临时库到Oracle正式库
本步骤是最为关键的一步,从Sybase到Oracle中,由于两边系统平台表结构及字段完全不同,所以要对照进行转换,在编写转换脚本的时候对原Sybase的平台表结构要足够的熟悉,最好有原始设计文档,不然是无法做数据转换的,在对Sybase与Oracle两边表结构足够熟悉后,开始进行脚本编写,下面介绍几个重点步骤.
3.1在写导入脚本中修改回滚时间提高效率
--修改回滚时间提高效率
ALTERSYSTEMSETundo_retention=30SCOPE=BOTH;
commit;
处理过程
……
处理完毕修改成默认时间
ALTERSYSTEMSETundo_retention=900SCOPE=BOTH;
commit;
3.2导入数据过程中注意记录两边用户ID对应关系表
在数据迁移过程中从Sybase平台到Oracle平台的用户新旧UserID情况应做详细记录,因为两边的Userid产生方式不一样也就是说用户到新平台其ID序列全部遵照Oracle平台规则产生,导入失败用户情况也应做详细记录,在导数据后进行数据查询与问题分析有很重要的作用,在后面的密码处理过程就会用到新旧对应关系表
例:
记录对应表
--新照ID对应表
createtabletrans_userid_relation
（
sybase_useridintnotnull,--现场用户id
oracle_useridintnotnull--新系统用户id
）;
commit;
createuniqueindexidx_userid_relation1ontrans_userid_relation（sybase_userid）;
createuniqueindexidx_userid_relation2ontrans_userid_relation（oracle_userid）;
commit;
--开户失败用户ID
createtabletrans_user_fail
（
sybase_useridintnotnull,--现场用户id
fail_codeintnotnull
）;
例:
过程处理中数据的写入
ifv_newuserid<1then
--开户失败写入数据中
v_errcount:
=v_errcount+1;
---1失败表示开户号码重复-2程序出错开户失败不进行下一步操作
--如果失败就写源ID
insertintotrans_user_fail（sybase_userid,fail_code）values（v_orguserid,v_newuserid）;
else
--开户成功写入对应表
insertintotrans_userid_relation（sybase_userid,oracle_userid）values（v_userid,v_newuserid）;
endif;
3.3导数据过程中游标的使用
在编写导脚本的过程中主要用到游标,因为一个用户的导入会涉及很多用户数据的导入,用户数据分布在不同的表中,所以用游标基本上是首选,在游标中还有嵌套游标
例:
--查询用户
declarev_userinfozxdbp_187.tb_userinfo%ROWTYPE;
cursorc_userinfoisselect*fromzxdbp_187.tb_userinfo
begin
openc_userinfo;
loop
fetchc_userinfointov_userinfo;
exitwhenc_userinfo%NOTFOUND;
DBMS_OUTPUT.PUT_LINE（'useridis:
'||v_userinfo.strUserid）;
--查询用户联系人群组
declarev_deptinfozxdbp_187.tb_deptinfo%ROWTYPE;
cursorc_deptinfoisselect*fromzxdbp_187.tb_deptinfowherestrUserid=v_userinfo.strUserid;
begin
openc_deptinfo;
loop
fetchc_deptinfointov_deptinfo;
exitwhenc_deptinfo%NOTFOUND;
DBMS_OUTPUT.PUT_LINE（'deptidis:
'||v_deptinfo.strDeptid）;
endloop;
closec_deptinfo;
end;
endloop;
closec_userinfo;
end;
在实际处理过程,如果用户数据百万级的,最外层最好不要用游标而改用循环,只在里面用游标.循环可以借用Oracle的序列,然后更新用户表中的一个新增字段进行循环操作达到游标的目的.
3.4导数据过程中数据写义的转换
用户数据从Sybase数据到Oracle新平台时可能还要做一些转换,这些转换要在对两边平台足够的了解与深入分析,并不断导入数据反复测试的情况下做出的转换,保证两边数据的高度一致性.
例:
对性别的定义
--取得用户的相关信息
selectcn_sex,cn_birthday,ch_pos,cn_job_place
intov_sex,v_birthday,v_job,v_company
fromfj_ora.pa_personal_settingwherecn_userid=v_userid;
--转换sex的定义现网是0男1女187定义是0女1男
ifv_sex=0then
v_sex:
=1;
else
v_sex:
=0;
endif;
例:
对状态定义的转换
--现网:
CN_STATUS0:
有效1：
无效CN_GROUP_TYPE1、企业组2、个人组CN_TYPE0:
统一留言组1：
其他组
--有效标记1_正常2_取消使用
ififlagvalid=0then
iflagvalid:
=1;
endif;
3.5关于Sybase日期型数据到Oracle的转换
在Sybase的日期在前面表脚本中转到Oracle的脚本中,我们是不能直接换成Oracle的date型的,在导入的时候会提示数据无效,所以在建表脚本的时候就把Sybase的date型对应成Oracle的varchar（50）类型,在转换脚本中做处理,这为把日期类型做重点说明,主要是因为在电信平台系统中,有很多日期的数据,日期型数据一般存储方式会有两种,一种直接用Varchar字符型保存,在整个系统中统一采用一种日期数据格式,如:
yyyy-mm-ddhh:
mm:
ss；另一种是直接采用Date型.这里对Date型做转换介绍.
SybaseDate型导入Oracle临时库中的示例数据如下:
Dec1420047:
26:
23:
390PM
Oracle中转换语句如下:
v_date:
=to_date（substr（v_datechar,1,length（v_datechar）-6）||substr（v_datechar,length（v_datechar）-1,2）,'MonDDYYYYHH:
MI:
SSPM','NLS_DATE_LANGUAGE=American'）;
v_datechar就是”Dec1420047:
26:
23:
390PM”这种类型的数据
3.6MD5密码的破解处理
Md5破解,你一定觉得很惊奇,因为MD5密码只是理论上有碰撞,现在还没有解密算法,如何解密?
这里要讲的是不是解密,而是用暴力破解的手段,因为在电信业务中密码一般比较特殊,因为在通话过程中能输入的密码只能是数字,所以就给了我们暴力密解的可能,并且一般在业务会限定密码位数长度,这更加提供了暴力破解密码的条件.
在现在智能网业务中,密码加密的算法是由自动业务的加密算生成的,所以要做Md5到智能网加密的转换.过程如下
1.用Md5函数对限定位数密码进行穷举,每个穷举的密码对源表中进行比对,然后更新成明码.
2.用智能网算法对明码进行加密,在需要的情况可以用保留字段保存明码
在附件里面提供了自己根据Java算法编写的智能网加解密Oracle版存储过程及MD5相关存储过程
在附件导数据过程,不是用的此方法,而是由另外的同事编写的软件进行该步骤,软件对Sybase导出的含密码的TXT文件,拷贝一份,然后进行解密处理,把Md5密码转换成智能网密码并在保留字段中保存明文密码,该TXT文件破解完成后导入数据库,最后用同步密码脚本进行密码同步,脚本如下:
updates133_user_baseinfoaset（reserver3,userpass）=（select_passwd,_passwd1
fromfj_ora.pa_account1b,trans_userid_relationc
wherea.userid=c.oracle_useridand_userid=c.sybase_userid）;
trans_userid_relation表为Sybase与Oracle对应用户ID表
pa_account1解密后的用户表,cn_passwd明码cn_passwd1智能网密码
以上两种处理MD5的方法,可以根据实际需要情况参考
四总结
在数据转换过程一种要多加测试,搭建Web,管理平台,自动业务相关等相关平台,像WEB这种验证可以打开原平台的URL与现平台的URL进行比较数据是否正确,自动业务验证相关流程是否能走通,统一管理平台验证订购关系是否正常,整个过程应该是一个极其繁琐又必须仔细小心的过程,在编写数据转换脚本时涉及变量长度定义的一定要根据表结构的字段长度进行比较,防止出现数据过长写入数据库过程中脚本被中断的情况,在导数据过程一定要注意对数据进行标识,标识在过程中那些数据已做导入处理,在意外情况下可以帮助你快速进行数据再次导入,或者在脚本就考虑脚本被中断脚本再执行的情况.
升级会员 