SQL server与Oracle数据同步以及数据库结构同步的问题

数据同步

一
1. 将oracle系统作为SQL server的链接服务器加入到SQL server中
2. 使用SQL语句通过链接服务器将SQLServer数据写入Oracle中。
比如我们建立了链接服务器MIS，而Oracle中在MIS用户下面建立了表contract_project，那么我们的 SQL语句就是：
DELETE FROM MIS..MIS.CONTRACT_PROJECT
–清空Oracle表中的数据
INSERT into MIS..MIS.CONTRACT_PROJECT
–将SQLServer中的数据写到Oracle中
SELECT contract_id,project_code,actual_money
FROM contract_project
如果报告成功，那么我们的数据就已经写入到Oracle中了。
SELECT * FROM MIS..MIS.CONTRACT_PROJECT
查看Oracle数据库中是否已经有数据了
二
通过行集函数OPENDATASOURCE
<ccid_nobr></ccid_nobr>
<ccid_code></ccid_code>　　
　　　　　 OPENDATASOURCE ( provider_name, init_string )
　　　　SELECT　*
　　FROM OPENDATASOURCE(
　　　　　　 ''MSDAORA'',
　　　　　　 ''Data Source=xzh.oracle;User ID=POS;Password=POS'')..POS.A0325
MSDAORA是OLEDB FOR ORACLE的驱动，初始化字符串指定本地服务名、用户名、口令。然后引用表中数据时要以服务器.用户名.表名。注意一定是四部分组成，用户名与表名一定要大写。
<ccid_nobr></ccid_nobr>
<ccid_code></ccid_code>　　
　　　　　 SELECT * INTO PUBS.DBO.A0325 FROM
　　OPENDATASOURCE(
　　　　　　 ''MSDAORA'',
　　　　　　 ''Data Source=xzh.oracle;User ID=POS;Password=POS'' )..POS.A0325　
将ORACLE中POS模式的A0325导入SQL的PUBS数据库。
三
Oracle提取SQL中的数据，大都通过透明网关来实现的。
在异构数据抽取过程中，最好采用SQL92标准的语法编写SQL代码，同时要注意不同数据库之间数据类型的转换关系，如ORACLE的日期类型用DATE、SQL用Datetime等。
四
用工具转移
1. kettle：kettle可以实现异构数据库的数据转换，例如mysql 到 Oracle、SQL server到Oracle等等。
2. 付费软件：ESB工具，金蝶中间件、IBM都有这种，可以进行数据同步的操作。

---

数据库结构同步

从Sql Server已有数据库中生成出Oracle的建库脚本，然后在Oracle数据库上执行。用到的生成工具是Power Designer。

1. 打开PD，新建一个PDM（物理数据模型），DBMS选择Microsoft SQL Server2012,Model name起名叫做mssdb。
2. 通过逆向工程将现有Sql Server数据库生成PDM
   （1）选择Database->Reverse Engineer Database，配置数据源，选择要迁移的数据库，输入用户名、密码。
   （2）选择数据库，选择用户dbo下的所有表、视图、存储过程、方法、触发器、序列等（由于Sql Server与oracle，数据库概念上的区别，这里不需选择用户、角色）。点击OK，生成PDM。已经建立过PDM的情况，这一步可以省略。
3. 生成Oracle物理数据模型
   选择Tools->Generate Physical Data Model
   （1）DBMS选择Oracle 10g，输入名称：oradb。
   （2）在Configure Model Options配置中， Model Settings的Table&View界面中，勾选Ignore identifying owner。Oracle中用户的概念与Sql Server不同，这里忽略owner。
   （3）Selection标签，选择需要生成的所有表、视图、外键、存储过程、方法、触发器、序列等。点击确定，即生成了oracle 的PDM。
   4、生成Oracle脚本
   与Sql Server不同的是，在Oracle里表名、字段名全部为大写，若要单独处理为小写，需加上双引号。而PD生成的脚本默认是有双引号的，这里需要修改默认配置，去掉双引号。
   然后选择oradb，然后选择Database->Generate Database，进入数据库生成界面
   在Format标签下，去掉勾选Owner prefix，它将省掉建表语句前“dbo.”所带来的麻烦；
   在Selection标签下，选择要生成脚本的各对象；
   在Preview标签下，可以预览预生成的脚本（表较多时，切换会比较慢）。
   点击确定，即得到生成的脚本。
   5、检查与调整
   脚本是PD自动生成的，因为表比较多，在oracle上直接去执行难免会有错误。所以在执行之前需要检查下脚本的正确性。这里提几点需要注意的地方。
   （1）Oracle要求表名、字段名等长度最多是30位，而Sql Server没有这个限制，所以可能会有在Sql Server创建正常的表而在Oracle下会创建失败。
   （2）检查一下主键、外键的名称，它们有可能是随机生成的名称。可根据相应规范进行修改。
   （3）检查字段名是否用到了Oracle的关键字。比如Sql Server命名“备注”字段可能会用“comment”、标题用“title”，但comment、title在Oracle中是关键字，不可以做为字段名称。
   （4）存储过程、方法是否符合Oracle语法。
   （5）Sql Server有自增字段，而Oracle没有。要实现此功能，需要相应创建序列、触发器。
   （6）Sql Server中字段类型为text的情况，如果是存二进制数据需要在Oracle中选用Blob字段类型。
   （7）Sql Server有外键的情况，主表记录删除，从表记录也会跟着删除；而Oracle默认情况是当从表有记录时，所对应的主表记录不允许删除。所以这种情况下外键需要添加外键级联删除。
   （8）生成的表、视图等个数是否正确，缺失的情况可单独生成脚本。
   系统不一样，出现问题的点可能也不一样，具体问题具体分析。
   6、建库
   在Oracle数据库服务（当然要先安装好Oracle服务端、并建立数据库）orcl实例下，使用用户sys登陆并创建用户orauser，并将resource、connect角色赋给orauser（这里，orauser使用默认表空间、默认临时表空间）。用刚创建的用户orauser登陆orcl数据库服务，执行前面已经生成的脚本，若干分钟后脚本执行完毕，观察一下执行过程中有无错误产生，也可以记录执行的日志以便日后查看；检查看表、视图等个数与Sql Server数据库中是否一致。如果都正确，那么，数据库结构从Sql Server到Oracle的迁移到此结束。当然，迁移的是否正确还需要在之后的使用中检查，发现错误及时修改即可。