Sql server至oracle历史数据迁移

目标：

新系统上线时需要老系统中的数据，把老数据库中数据按新数据库的要求转入新数据库中。且要求此过程尽量短，保证新老系统切换满足要求。

分析：

必须做的事情

整理出新系统中表对应老系统的sql,

辅助事情

1，为缩短迁移时间，把一些表迁移分二次进行，因此，找出有时间间隔不进行老数据更新的表。

2，源数据库表中有分区且数据量大的表进行折分迁移。

3，对抽取的数据进行一些验证，如条数、对应字段的总和…..

采用方式

由于不涉及采购一些抽取工具，且尽量采用简单的方式。

由于sql脚本是一个大的工作量，且可能经常调整，因此，决定采用一配制模板来定义。内容大概如下：

目标用户

表名

源脚本

参数

源验证字段

目标验证字段

增全量

 

 

目标用户是由于oralce中有多个用户，

源脚本为对应源的sql，为追求简单要求表字段顺序与sql相同。

参数是为了在sql中加入参数然后通过参数定义的内容进行替换，此内容以逗号分隔，最终产生多次导出。

源验证字段为源表sql中的字段，与目标验证字段对应。

增全量是为了比较灵活的配制成是否需要先初始化。

 

假设以上内容完成后，其实，我们可以采用很多种方式来达到迁移，比如直接自己写java，在此为了效率，确定sql server采用bcp命令导出数据，再ftp上传目标，再采用外部表方式进行。此处有说外部表方式比sql loader慢很多，但经测试外部表速度可以接受，且比较简单，因此采用外部表方式。

产生bcp脚本

产生两份BCP脚本，一初始化，一增量。

假设源sql是这样的。

SELECT

c.[SalesId]

.........其它字段

FROM

[#PARAM#].[dbo].SbSelections a,

[#PARAM#].dbo.SbSales c,

where a.PoolId=b.PoolId

and

a.SalesId=c.SalesId 

#{and a.BusinessDate &INIT} 

and   a.BusinessDate = c.BusinessDate

 

这个时候，可以把参数替换PARAM产生一sql,而对于配制了增量的内容，则#{and a.BusinessDate &INIT}此部分内容，初始化部分会替换为 BusinessDate ，增量会替换为BusinessDate >='''+@paramdate+'''，而当配制为全量时，则#{and a.BusinessDate &INIT}内容替换为空。

     在这个时候，产生bcp命令的同时，我们要记录一下这条sql产生的行及验证字段的和，因此，要产生以下两内容（以下内容是定义于存储过程内），

set @Sql ='bcp "SELECT convert( varchar,BusinessDate,112 ) ,ProvincialCentreId ,DistrictId ,SportsType ,case when PoolType in (''CHP'',''GPW'',''FNL'') then ''TRN'' ELSE SportsType + ''MA'' END POOL_GROUP ,PoolType  ,IsFixedOdds ,AupBetType ,BetAupBetType ,BetAupFormula ,GrossSales ,CancelAmount ,ConcludedSales ,PayableDividend ,PaidDividend ,ExpiredDividend FROM BisDS.dbo.SbAupDistrict WHERE BusinessDate >='''+@paramdate+''' "  queryout '+@outpath+'T_DISTRICT_BET_AUP_101.txt  -t "," -c -T'

exec master..xp_cmdshell @Sql

set  @exetime=dateDiff(ss,@exeDate,getdate())

insert into ##tab_bcp2582 select  'T_DISTRICT_BET_AUP',101,count(1),  @exetime,sum([GrossSales]),'GROSS_SALES_AMOUNT' from  BisDS.dbo.SbAupDistrict WHERE BusinessDate >=convert(datetime,@paramdate)

set @exeDate=getdate()

第二条插入语句是为了记录这个文件名，行数，验证和字段，目录验证字段，最后

set @Sql ='bcp "SELECT  * from tempdb..##tab_bcp2582"  queryout '+@outpath+'INIT_ALL_BCP.txt  -t "," -c -T'

exec master..xp_cmdshell @Sql

这样就可把这部分内容也记录于文件中。

注：以上内容生成时注意sql换成一行，且要去除一些注释之类，第二sql且采用去掉字段从from 开始再加上自己要的内容。

编写bat调用bcp

假设上面两存储过程文件通过解析自动生成后，这时编写bat调用

先设定一个参数文件parameter.cfg

#数据库服务器名的参数配置

ServerName=CSLJC-358DBD6CB=

#增量分隔时间

initdate=2013-01-01=

#bcp输出路径

outpath=E:\share\bisdata\=

ftp_id=192.168.109.133=

ftp_user=root=

ftp_pass=@WSX1qaz=

ftp_path=/app/g2bis/bisdata=

初始化bat，内容大致如下：

@echo off

rem 把文件中内容写入变量

set logfilename=errorlog.txt

echo off

for /f "tokens=1,2 delims=="  %%i  in (config/parameter.cfg) do (

set  %%i=%%j

)

 

set batpath=%~dp0

cd %batpath:~0,3%

cd %batpath%

del *.txt

 

sqlcmd -S %ServerName% -E -b -i "%batpath%Sp\ext_init_bcp.sql" >nul 2>&1

 

if %errorlevel% neq 0 (

  echo.

  set errMsg=创建存储过程失败!

  echo. >> %logfilename%

  echo 创建存储过程失败!>> %logfilename%

  echo. >> %logfilename%

  echo.

  goto :exit_here

)

echo 创建存储过程成功!

 

 

rem execute procedure

 

set sql=exec BisInterfaceDb.dbo.spData_init_bcp '%initdate%','%outpath%'

 

sqlcmd -S %ServerName% -E -b -Q "%sql%" >nul 2>&1

 

if %errorlevel% neq 0 (

  echo.

  set errMsg=执行存储过程失败!

  echo. >> %logfilename%

  echo 执行存储过程失败!>> %logfilename%

  echo. >> %logfilename%

  echo.

  goto :exit_here

)

 

echo 执行存储过程成功!

rem delete procedure

 

 

set sql=if exists ( select 1 from sysobjects where name = 'spData_init_bcp' and type = 'P' )

set sql=%sql% drop procedure spData_init_bcp

sqlcmd -S %ServerName% -E -b -Q "%sql%" >nul 2>&1

if %errorlevel% neq 0 (

  echo.

  set errMsg=删除存储过程失败!

  echo. >> %logfilename%

  echo 删除存储过程失败!>> %logfilename%

  echo. >> %logfilename%

  echo.

  goto :exit_here

)

echo 删除存储过程成功!

:exit_here

内容就是先把存储过程装入，再执行，再删除。

这样执行后会在指定的目录生成数据文件

ftp.bat内容大概如下

echo off

rem 把文件中内容写入变量

set logfilename=errorlog.txt

echo off

for /f "tokens=1,2 delims=="  %%i  in (config/parameter.cfg) do (

set  %%i=%%j

)

 

set batpath=%~dp0

echo open %ftp_id%>info.ftp

echo %ftp_user%>>info.ftp

echo %ftp_pass%>>info.ftp

echo cd %ftp_path%>>info.ftp

echo prompt>>info.ftp

echo mput %outpath%*.txt>>info.ftp

echo quit>>info.ftp

cd %batpath:~0,3%

ftp -s:%batpath%info.ftp >%batpath%ftp.log

echo 发送完成

pause

内容不解释，自己一看就清楚，增量的bat及初始化一样，无非就是执行不同的存储过程。

目标脚本

 目录要做的事情就是以外部表方式装载数据。

建立路径

create or replace directory DIR_TRANS_EXT_DATA

  as '/app/g2bis/bisdata';

建立一对应全表的外部表

create table BIS_TRANS.INIT_EXT_EXECUTE

(

  ext_name         VARCHAR2(128),

  ext_param        VARCHAR2(128),

  ext_source_row   VARCHAR2(128),

  ext_source_time  VARCHAR2(128),

  ext_source_total VARCHAR2(128),

  ext_targ_col     VARCHAR2(128),

  ext_targ_row     VARCHAR2(128),

  ext_targ_time    VARCHAR2(128)

)

organization external

(

  type ORACLE_LOADER

  default directory DIR_TRANS_EXT_DATA

  access parameters

  (

    records delimited by newline

    badfile 'INIT_ALL_BCP.bad'

    logfile 'INIT_ALL_BCP.log'

    fields terminated by ","

    missing field values are null

  )

  location ('ADD_ALL_BCP.txt','INIT_ALL_BCP.txt')

)

reject limit UNLIMITED;

 

对于创建外部表，可以根据oracle内容字典生成执行，也比较简单。

而对于插入，也采用根据oracle字典表自动生成语句，每次执行时，动态改表外部表对应的文件名。由于本次迁移字段比较简单，一般为字符、数据、时间，因此，只把为时间行的进行to_timestamp转换，由于ftp后，行后回车字符进行了改变，因此，最后一字段进行去除chr(13)。

最后对目录数据库与源信息进行验证。程序执行尽量考虑可重复执行，出错修改简单===

结束

以上内容虽然是一实例的描述，但希望只是一个思路，因为其中涉及的东西不可能相同，但，我们的信念是，让程序做复杂的事，让人做简单的事，做好事件的分工。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/134308/viewspace-761203/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/134308/viewspace-761203/