SQLSERVER2000实时同步数据到Oracle11G的的实现过程

jxdiscuz

Sqlserver2000连接Oracle11G数据库进行数据实时同步的实现过程
 
1，前提条件
已经有sqlserver2000环境，已经存在oracle11g环境，准备这两个数据库，建立各自的访问账号，两者之间需要ping通，telnet端口也要能通，目的是将sqlserver里面的数据自动同步到oracle数据库里面去。Sqlserver里面有链路服务器（linked server）可以实现。
 
2，在sqlserver上面建立odbc数据源
有2种方式：“Microsoft ODBC for Oracle”选项以及“Oracle inOraDb10g_home3”选项
2.1，“Microsoft ODBC for Oracle”驱动方式
  进入“控制面板”->“管理工具”->“数据源 (ODBC)”，双击打开。
  进入“系统 DSN”，点击“添加”按钮，在弹出的“选择你想为其安装数据源的驱动程序”选择框里面，选择在列表框中选择“Microsoft ODBC for Oracle”选项，单击“完成”按纽。
PS：这里不要选择安装oracle客户端时候生成的“Oracle in OraDB10g_home3”这个驱动。
如下图1.png所示：

 
 
之后在弹出的界面里面，设置
Data Source Name：这里就是数据源名称，是tnsnames.ora里面配置的数据源名称，可以用tnsping xxx能测试ok的数据源名称。
Description：描述，随意填写
User Name：用户，连接oracle数据库的用户名，比如scott
Server：软件服务器，默认为Oracle
这里，根据自己的实际情况，设置如下图2.png所示：


　　
之后点击“确定”按钮，就会生成新的ODBC数据源。
 
2.2，“Oracle in OraDb10g_home3”驱动选项
进入“控制面板”-> “管理工具”->“数据源 (ODBC)”，双击打开。
  进入“系统 DSN”，点击“添加”按钮，在弹出的选择框里面，选择在“选择你想为其安装数据源的驱动程序”列表框中选择“Oracle in OraDb10g_home3”选项，单击“完成”按纽。如下图3.png所示：


　　
 
之后再弹出的界面，设置
Data Source Nam：随便输入一个名字,这个名字就要后面sqlserver要用到的
Description：描述，随意填写
TNS Service Name：下拉列表中就是oracle中配置好的TNS信息,如果你开始没有配置的话这里就为空的.你选一个TNS名字后,可以点下test connection验证下看连接上了不.
User>
如下图5.png所示：

 
点击“Test Connection”按钮，输入连接oracle数据库的用户名密码，如果成功就会弹出“Connection successful”的提示框。之后点击“OK”按钮，完成创建，在“ODBC 数据源管理器”界面就会生成新的TEST数据源，如下图6.png所示：

 
 
3，在“企业管理器”创建连接oracle数据库的链路服务器
在前面创建好ODBC数据源之后，就可以开始建立连接oracle数据库的链路服务器了，进入sqlserver企业管理器“控制台目录”-> “SQL Server组”->“(LOCAL) (Windows NT)”-> “安全性”->“链路服务器”。在右边的空白窗口界面，右键点击选择“新建链路服务器”，开始设置：
在常规标签界面里面：
(0)，定义连接名称；
　　(1)，选其他数据源；指定程序名称为：Microsoft OLE DB Provider for Oracle; 
　　(2)，产品名称可不填； 
　　(3)，数据源指定刚才ODBC中定义好的数据源名称； 
　　(4)，提供程序字符串按 
　　以下格式填写：UserID=username;PassWord=userpasswd
　　(或者按如下格式：UID=username;PWD=userpasswd)，
　　这里的用户名和密码对应所要连接的oracle数据库中的用户名和密码。 

　　在安全性标签页里：
设置用此安全上下文进行，并录入oracle的数据库用户名和密码。

　　服务器选项标签页：可默认，
最后点击“确定”按钮完成链路服务起的建立工作，如下图7.png所示：

　　
 
4，验证链路服务器
进入sqlserver企业管理器“控制台目录”-> “SQL Server组”->“(LOCAL) (Windows NT)”-> “安全性”->“链路服务器”->“TEST”，点击表，即可在右边窗口看到该oracle数据库用户拥有的的所有表名，如下图8.png所示：

　　
 
　　
　　
　　
5，在sqlserver的查询分析器窗口验证链路服务器
-- 执行查询
SELECT * FROM OPENQUERY(TEST_ORCL, 'SELECT* FROM SCOTT.ZZZ_TEST') ;
 
-- 录入数据
insert into openquery(TEST_ORCL,'SELECT *FROM SCOTT.ZZZ_TEST')
values(2,'b');
 
SELECT * FROM OPENQUERY(TEST_ORCL, 'SELECT* FROM SCOTT.ZZZ_TEST') ;
 
--删除数据
DELETE FROM openquery(TEST_ORCL,'SELECT *FROM SCOTT.ZZZ_TEST')
WHERE>
SELECT * FROM OPENQUERY(TEST_ORCL, 'SELECT* FROM SCOTT.ZZZ_TEST') ;
 
-- 修改数据
UPDATE openquery(TEST_ORCL,'SELECT * FROM SCOTT.ZZZ_TEST')SET NAME='A2' WHERE>
SELECT * FROM OPENQUERY(TEST_ORCL, 'SELECT* FROM SCOTT.ZZZ_TEST') ;
　　
　　
6，查看后台的链路服务器的ddl语句
在sqlserver的企业管理器里面看不到，可以通过SQL Manager 2008 for SQL Server工具来查看，你可以不通过sqlserver的管理器来操作，可以直接使用ddl语句来创建，ddl语句如下所示：

　　
　　
EXEC master.dbo.sp_addlinkedserver @server= N'TEST_ORCL',　　@srvproduct = 'Oracle',
　　@provider = 'MSDAORA', @datasrc = 'TEST_ORCL',
　　@location = '',
　　@provstr = 'UID=username;PWD=paxxx',
　　@catalog = ''
　　GO
　　
　　EXEC master.dbo.sp_serveroption @server =N'TEST_ORCL',
　　@optname = 'collation compatible',
　　@optvalue = 'false'
　　GO
　　
　　EXECmaster.dbo.sp_serveroption @server = N'TEST_ORCL',
　　@optname = 'collation name',
　　@optvalue= 'null'
　　GO
　　
　　EXEC master.dbo.sp_serveroption @server =N'TEST_ORCL',
　　@optname = 'data access',
　　@optvalue = 'true'
　　GO
　　
　　EXEC master.dbo.sp_serveroption @server =N'TEST_ORCL',
　　@optname = 'connect timeout',
　　@optvalue = '0'
　　GO
　　
　　EXEC master.dbo.sp_serveroption @server =N'TEST_ORCL',
　　@optname = 'query timeout',
　　@optvalue = '0'
　　GO
　　
　　EXEC master.dbo.sp_serveroption @server =N'TEST_ORCL',
　　@optname = 'rpc',
　　@optvalue = 'false'
　　GO
　　
　　EXEC master.dbo.sp_serveroption @server =N'TEST_ORCL',
　　@optname = 'rpc out',
　　@optvalue = 'false'
　　GO
　　
　　EXEC master.dbo.sp_serveroption @server =N'TEST_ORCL',
　　@optname = 'use remote collation',
　　@optvalue = 'true'
　　GO
　　
　　EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname = N'TEST_ORCL',
　　@locallogin = NULL,
　　@useself = 'false',
　　@rmtuser = N'username',
　　@rmtpassword = 'password'
　　GO
　　



　　
 
7，一些报错信息汇总
错误 7399: OLE DB 提供程序 MSDAORA' 报错。
OLEDB 错误跟踪 [OLE/DB Provider 'MSDAORA'>。
如下图：20150310/1.png
 
第一种解决方案：
代理中的sQl server 连接的问题,不能用windows身份验证,得用sysadmin登陆,
具体设置方法如下:在企业管理器中->管理->SQL Server 代理->右键菜单"属性"->分页选项卡上选"连接"->"使用SQL
Server 身份验证",之后"应用"跟"确定"就可以了。
 
第二种解决方案：
在建立odbd数据源的时候，不要用默认oracle客户端的“Oracle in OraDb10g_home3”驱动选项，要用“MicrosoftODBC for Oracle”驱动选项。
　　
　　
　　
8，最好进行数据同步
    可以写存储过程也可以做定时任务，将sqlserver库中的数据通过链路服务器的访问方式实时同步到oracle数据库。　　
　　 ----------------------------------------------------------------------------------------------------------------
　　有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!>
　　原博客地址：       http://blog.itpub.net/26230597/viewspace-1447291/
　　原作者：黄杉 (mchdba)
　　----------------------------------------------------------------------------------------------------------------