Sybase到Oracle数据迁移.doc

Sybase到Oracle数据迁移一 Sybase数据导出1.1 Sybase数据导出到TXT文件在跨数据库从Sybase导入Oracle过程中首先会遇到的问题是,中间以什么样的数据格式做为Sybase与Oracle都认同的中间数据格式.Sybase数据库导出一般使用BCP,BCP出来的TXT文档数据也能被Oracle所接受.所以在整个过程导出Sybase的数据选择BCP导出例:导出命令bcp personal.PA_ACCOUNT out PA_ACCOUNT.txt -Upersonal -Psuntek -SSYBASE -c -oresult.txt 1.2 Sybase导出TXT文件异常处理在前面的基础上讲一下BCP导出的时候可能会遇到的问题,Sybase中Bcp导出的时候,如果表中的字段里面含有换行符号的话,会在txt文件中做换行处理,那行就会出现一行变成两行,换行符号在实际数据中可能是用户拷贝粘贴或者Web提交的把换行提交到数据库中.如图所示上行的数据中,因为其数据中含有回车,一条记录导出TXT里换成了两行,我们在数据导出过程要考虑这种情况,这种情况也是数据导出后导入Oracle失败的主要原因. 解决以上情况的办法:一般的BCP导出的命令bcp personal.PA_ACCOUNT out PA_ACCOUNT.txt -Upersonal -Psuntek -SSYBASE -c -oresult.txt 在上面的导出命令中加入一个参数: -r&_&,该参数的作用,指定换行符号.最后的BCP命令 bcp personal.PA_ACCOUNT out PA_ACCOUNT.txt -Upersonal -Psuntek -SSYBASE -c -r&_& -oresult.txt 导出的文档然后要做一些变换, 变换后的TXT文档就不会换行,除非在字段数据中有换行符号,导出的TXT文档用UltraEdit编辑工具进行处理首先把换行符替换成空格,防止一行数据因为有换行符号导出TXT时变成两行数据无法导入Oracle,替换方法见图然后再把BCP自定义换行符号”&_&”替换成换行符号, 替换方法见图以上两步操作以UltraEdit为例子 通过以上操作就能解决Sybase导出数据一条记录因为数据中有换行符号在TXT变成两行而无法正确导入Oracle临时库的问题.二 导出数据导入Oralce临时库2.1 Sybase建表脚本转为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 */*=*/create table PA_ACCOUNT ( CN_USERID numeric(12) identity, CN_PHONENUM varchar(12) not null, CN_PASSWD varchar(32) not null, CN_REG_MOTHED tinyint not null, CN_REG_TIME datetime not null, CN_USER_STATUS tinyint not null, CN_IDCARD varchar(18) not null, CN_LOGOUT_TIME datetime null, CN_ACTIVE_CODE varchar(12) null, CN_AREACODE varchar(4) not null, CN_REG_TYPE tinyint default 1 null)go 修改后的Oracle建表脚本/*=*/* Table: PA_ACCOUNT */*=*/create table PA_ACCOUNT ( userseq int, CN_USERID numeric(12) null, CN_PHONENUM varchar(12) null, CN_PASSWD varchar(32) null, -CN_PASSWD1 varchar(32) null, CN_REG_MOTHED int null, CN_REG_TIME varchar(30) null, CN_USER_STATUS int null, CN_IDCARD varchar(20) null, CN_LOGOUT_TIME varchar(30) null, CN_ACTIVE_CODE varchar(12) null, CN_AREACODE varchar(4) null, CN_REG_TYPE int default 1 null);commit; 建议: Sybase中对应的除数值型数据外,在Oracle中全部转换为字符型 2.2 Sybase数据导入Oracle临时库 建议Sybase中非数值型的数据全部当字符型数据导入,然后在Oracle从临时库到正式库处理时做具体转换.在大量txt类型的数据要导入Oracle中,Oracle有一个自带的工具,SQL Loader 是 Oracle 提供的一个实用程序，用以将外部TXT文件按要求加载到 Oracle 数据库 SQL loader的用法很简单下面以导入一个pa_super_mail表为例简单介绍一下:建立pa_super_mail.ctl,内容如下load datainfile .pa_super_mail.txt -指明TXT文件的路径append into table pa_super_mai -指明要插入的表lfields terminated by X09 -字段终止于X09，是一个制表符（TAB）分开( CN_USERID, CN_MAIL, CN_PASSWD, CN_MEMO -这几个为字段名,见下面的Oracle临时表建表脚本,注意只要字段名就可以了,不用指定数据类型) PA_SUPER_MAIL建表脚本/*=*/* Table: PA_SUPER_MAIL */*=*/create table PA_SUPER_MAIL ( CN_USERID numeric(12) null, CN_MAIL varchar(40) null, CN_PASSWD varchar(15) null, CN_MEMO varchar(100) null);commit;create index i_PA_SUPER_MAIL on PA_SUPER_MAIL (CN_USERID); 表,Ctl文件及TXT数据文件准备好就可以用Sqlldr导入,具体命令如下:sqlldr fj_ora/fj_orazxin control=pa_super_mail.ctl errors=999999fj_ora/fj_orazxin 这个不用说了就是用户名密码及注册服务名errors=999999 表示如果导入数据失败等于该值便终止导入 在导入数据完毕,根据其产生的LOG文件可以看到有多少数据成功与失败及失败的原因,失败数据会写入一个bad文件中,从中可以分析出数据的问题之所在,不断修改而达到数据100%的导入率. Log日志如下: SQL*Loader: Release 9.2.0.6.0 - Production on 星期一 5月 21 20:33:21 2007 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 控制文件: pa_super_mail.ctl数据文件: .pa_super_mail.txt错误文件: pa_super_mail.bad废弃文件: 未作指定:(可废弃所有记录) 加载数: ALL跳过数: 0允许的错误: 999999绑定数组: 64 行，最大 256000 字节继续: 未作指定所用路径: 常规 表PA_SUPER_MAIL已加载从每个逻辑记录插入选项对此表APPEND生效 列名 位置 长度 中止 包装数据类型- - - - - -CN_USERID FIRST * WHT CHARACTER CN_MAIL NEXT * WHT CHARACTER CN_PASSWD NEXT * WHT CHARACTER CN_MEMO NEXT * WHT CHARACTER 表PA_SUPER_MAIL:13189 行 加载成功由于数据错误, 0 行 没有加载。由于所有 WHEN 子句失败, 0 行 没有加载。由于所有字段都为空的, 0 行 没有加载。 为结合数组分配的空间: 66048字节（64行）读取 缓冲区字节数: 1048576 跳过的逻辑记录总数: 0读取的逻辑记录总数: 13189拒绝的逻辑记录总数: 0废弃的逻辑记录总数: 0 从星期一 5月 21 20:33:21 2007开始运行在星期一 5月 21 20:33:22 2007处运行结束 经过时间为: 00: 00: 00.89CPU 时间为: 00: 00: 00.12 从以上日志还可以看出导入数据的具体时间 建议:可以把Sqlldr命令做成Bat批处理文件,方便现场人员操作与处理. 三 Oracle临时库到Oracle正式库本步骤是最为关键的一步,从Sybase到Oracle中,由于两边系统平台表结构及字段完全不同,所以要对照进行转换,在编写转换脚本的时候对原Sybase的平台表结构要足够的熟悉,最好有原始设计文档,不然是无法做数据转换的,在对Sybase与Oracle两边表结构足够熟悉后,开始进行脚本编写,下面介绍几个重点步骤. 3.1 在写导入脚本中修改回滚时间提高效率-修改回滚时间 提高效率ALTER SYSTEM SET undo_retention=30 SCOPE=BOTH;commit; 处理过程处理完毕修改成默认时间ALTER SYSTEM SET undo_retention=900 SCOPE=BOTH;commit; 3.2 导入数据过程中注意记录两边用户ID对应关系表 在数据迁移过程中从Sybase平台到Oracle平台的用户新旧UserID情况应做详细记录,因为两边的Userid产生方式不一样也就是说用户到新平台其ID序列全部遵照Oracle平台规则产生,导入失败用户情况也应做详细记录,在导数据后进行数据查询与问题分析有很重要的作用,在后面的密码处理过程就会用到新旧对应关系表例:记录对应表-新照ID对应表create table trans_userid_relation( sybase_userid int not null, -现场用户id oracle_userid int not null -新系统用户id);commit;create unique index idx_userid_relation1 on trans_userid_relation(sybase_userid);create unique index idx_userid_relation2 on trans_userid_relation(oracle_userid);commit;-开户失败用户IDcreate table trans_user_fail( sybase_userid int not null, -现场用户id fail_code int not null); 例:过程处理中数据的写入if v_newuserid1 then -开户失败写入数据中 v_errcount:=v_errcount+1; - -1失败表示开户号码重复 -2程序出错 开户失败不进行下一步操作 -如果失败就写源ID insert into trans_user_fail (sybase_userid,fail_code) values (v_orguserid,v_newuserid);else -开户成功写入对应表 insert into trans_userid_relation (sybase_userid,oracle_userid) values (v_userid,v_newuserid);end if; 3.3 导数据过程中游标的使用 在编写导脚本的过程中主要用到游标,因为一个用户的导入会涉及很多用户数据的导入,用户数据分布在不同的表中,所以用游标基本上是首选,在游标中还有嵌套游标 例: -查询用户 declare v_userinfo zxdbp_187.tb_userinfo%ROWTYPE; cursor c_userinfo is select * from zxdbp_187.tb_userinfo begin open c_userinfo; loop fetch c_userinfo into v_userinfo; exit when c_userinfo%NOTFOUND; DBMS_OUTPUT.PUT_LINE(userid is :| v_userinfo.strUserid); -查询用户联系人群组 declare v_deptinfo zxdbp_187.tb_deptinfo%ROWTYPE; cursor c_deptinfo is select * from zxdbp_187.tb_deptinfo where strUserid=v_userinfo.strUserid; begin open c_deptinfo; loop fetch c_deptinfo into v_deptinfo; exit when c_deptinfo%NOTFOUND; DBMS_OUTPUT.PUT_LINE(deptid is :| v_deptinfo.strDeptid); end loop; close c_deptinfo; end; end loop; close c_userinfo; end; 在实际处理过程,如果用户数据百万级的,最外层最好不要用游标而改用循环,只在里面用游标.循环可以借用Oracle的序列,然后更新用户表中的一个新增字段进行循环操作达到游标的目的.3.4 导数据过程中数据写义的转换用户数据从Sybase数据到Oracle新平台时可能还要做一些转换,这些转换要在对两边平台足够的了解与深入分析,并不断导入数据反复测试的情况下做出的转换,保证两边数据的高度一致性.例: 对性别的定义 -取得用户的相关信息 select cn_sex,cn_birthday,ch_pos,cn_job_place into v_sex,v_birthday,v_job,v_company from fj_ora.pa_personal_setting where cn_userid=v_userid; -转换sex的定义 现网是0男1女 187定义是0女1男 if v_sex=0 then v_sex:=1; else v_sex:=0; end if;例:对状态定义的转换-现网:CN_STATUS 0:有效 1：无效 CN_GROUP_TYPE 1、企业组 2、个人组 CN_TYPE 0:统一留言组1：其他组-有效标记 1_正常 2_取消使用 if iflagvalid=0 then iflagvalid:=1; end if; 3.5 关于Sybase日期型数据到Oracle的转换 在Sybase的日期在前面表脚本中转到Oracle的脚本中,我们是不能直接换成Oracle的date型的,在导入的时候会提示数据无效,所以在建表脚本的时候就把Sybase的date型对应成Oracle的varchar(50)类型,在转换脚本中做处理,这为把日期类型做重点说明,主要是因为在电信平台系统中,有很多日期的数据,日期型数据一般存储方式会有两种,一种直接用Varchar字符型保存,在整个系统中统一采用一种日期数据格式,如: yyyy-mm-dd hh:mm:ss；另一种是直接采用Date型.这里对Date型做转换介绍. Sybase Date型导入Oracle临时库中的示例数据如下:Dec 14 2004 7:26:23:390PM Oracle中转换语句如下:v_date:=to_date(substr(v_datechar,1,length(v_datechar)-6)|substr(v_datechar,length(v_datechar)-1,2),Mon DD YYYY HH:MI:SSPM,NLS_DATE_LANGUAGE = American); v_datechar就是” Dec 14 2004 7:26:23:390PM”这种类型的数据 3.6 MD5密码的破解处理 Md5破解,你一定觉得很惊奇,因为MD5密码只是理论上有碰撞,现在还没有解密算法,如何解密?这里要讲的是不是解密,而是用暴力破解的手段,因为在电信业务中密码一般比较特殊,因为在通话过程中能输入的密码只能是数字,所以就给了我们暴力密解的可能,并且一般在业务会限定密码位数长度,这更加提供了暴力破解密码的条件. 在现在智能网业务中,密码加密的算法是由自动业务的加密算生成的,所以要做Md5到智能网加密的转换.过程如下 1. 用Md5函数对限定位数密码进行穷举,每个穷举的密码对源表中进行比