sql server定时同步oracle数据表

cheng029

公司现在需求oracle数据库中的某些表定期同步到sql server数据库中，折腾了好几天遇到了各种问题，但还是搞好了，现在把操作步骤记录下来供大家参考。
我一开始使用的是sql server 2005 sp4 中文版和 oracle 11g之间同步，在配置过程中发现sql server2005不能完美的支持oracle11g的数据同步，官方解释如下：
在 Microsoft SQL Server 2005年或 Microsoft SQL Server 2008年中，您将配置事务复制使用 Oracle 11g 发布服务器。快照代理程序运行时，您会收到以下错误消息:
Msg 21613，级别 16，状态 1，
过程 sp_IHsyncmetadata，行 223
约束列ColumnName表TableName中找不到。原文地址：https://support.microsoft.com/zh-cn/kb/960574

大致意思就是这是sql server的一个bug需要安装补丁，我去看了一下sql server 2005 的补丁好像只有英文版的，但我安装的是中文版的所以就放弃打补丁了，解决方案就是升级sql server数据库为sql server 2008 r2，注意sql server 2008也会有此问题，所以这里我选择的是sql server 2008 r2 ，当然如果你安装的是英文版的sql server2005也可以去下载安装补丁不用升级版本。
补丁下载地址：https://support.microsoft.com/zh-tw/kb/959195
下面开始配置数据同步
首先设置sql server agent服务为本地管理员帐户运行，然后重启服务。


在sql server服务器端安装oracle client，版本最好和oracle数据库版本一致。我这里安装的是oracle 11g client
安装完毕后分别在sql server服务器和oracle服务器上执行以下注册表修改项，1，2，3，4另存为reg文件依次执行。
1.
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\OraOLEDB.Oracle]
@="Oracle Provider for OLE DB"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\OraOLEDB.Oracle\CLSID]
@="{3F63C36E-51A3-11D2-BB7D-00C04FA30080}"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\OraOLEDB.Oracle\CurVer]
@="OraOLEDB.Oracle.1"
2.
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\OraOLEDB.Oracle.1]
@="Oracle Provider for OLE DB"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\OraOLEDB.Oracle.1\CLSID]
@="{3F63C36E-51A3-11D2-BB7D-00C04FA30080}"

3.注意标红的路径是你安装client的路径，改为你的路径就好了。
Windows Registry Editor Version 5.00
[HKEY_CLASSES_ROOT\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}]
@="OraOLEDB.Oracle"
"OLEDB_SERVICES"=dword:ffffffff
[HKEY_CLASSES_ROOT\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\ExtendedErrors]
@="Extended Error Lookup Service"
[HKEY_CLASSES_ROOT\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\ExtendedErrors\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}]
@="Oracle OLE DB Error Lookup"
[HKEY_CLASSES_ROOT\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\InprocServer32]
@="D:\\app\\wangjingfeng\\product\\11.2.0\\client_2\\BIN\\OraOLEDB11.DLL"
"ThreadingModel"="Both"
[HKEY_CLASSES_ROOT\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\OLE DB Provider]
@="Oracle Provider for OLE DB"
[HKEY_CLASSES_ROOT\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\ProgID]
@="OraOLEDB.Oracle.1"
[HKEY_CLASSES_ROOT\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\TypeLib]
@="{0BB9AFD1-51A1-11D2-BB7D-00C04FA30080}"
[HKEY_CLASSES_ROOT\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\VersionIndependentProgID]
@="OraOLEDB.Oracle"


4.
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Providers\OraOLEDB.Oracle]
"AllowInProcess"=dword:00000001
在oracle数据库端建立同步用的帐号：
可以使用以下脚本执行建立：

若要从 Oracle SQL*Plus 实用工具运行该脚本，则请

• 在 SQL Server 分发服务器中，打开一个命令提示符窗口。

• 若要使用 SQL*PLUS 连接到 Oracle 数据库并从其默认安装目录执行 oracleadmin.sql 脚本，请键入以下语法：

  sqlplus system/P@$$W0rd@orcl @"c:\Program Files\Microsoft SQL Server\<InstanceName>\MSSQL\Install\oracleadmin.sql"
  

  在此示例中，使用内置 Oracle 帐户 system 连接到网络名称为“orcl”的 Oracle 数据库。

• 在得到提示后，请指定用户名称、用户密码和默认的表空间。

  
  

--***********************************************************************
-- Copyright (c) 2003 Microsoft Corporation
--
-- File:
--  oracleadmin.sql
--
-- Purpose:
-- PL/SQL script to create a database user with the required
-- permissions to administer SQL Server publishing for an Oracle
-- database.
--
-- &&ReplLogin        == Replication user login
-- &&ReplPassword     == Replication user password
-- &&DefaultTablespace == Tablespace that will serve as the default
-- tablespace for the replication user.
-- The replication user will be authorized to allocate UNLIMITED space
-- on the default tablespace, which must already exist.
--
-- Notes:
--
-- This script must be run from an Oracle login having the
-- authorization to create a new user and grant unlimited tablespace on
-- any existing tablespace. The login must also be able to grant to the
-- newly created login the following authorizations:
--
-- create public synonym
-- drop public synonym
-- create sequence
--  create procedure
-- create session
-- create table
-- create view
--
-- Additionally, the following properties are also required for
-- transactional publications.
--
-- create any trigger
--
--  All of the privileges may be granted through a role, with the
-- exception of create table, create view, and create any trigger.
-- These must be granted explicitly to the replication user login.
-- In the script, all grants are granted explicitly to the replication
-- user.
--
-- In addition to these general grants, a table owner must explicitly
-- grant select authorization to the replication user on a table before
-- the table can be published.
--
***********************************************************************


ACCEPT ReplLogin CHAR PROMPT 'User to create for replication: ';
ACCEPT ReplPassword CHAR PROMPT 'Replication user passsword: ' HIDE;
ACCEPT DefaultTableSpace CHAR DEFAULT 'SYSTEM' PROMPT 'Default tablespace: ';


-- Create the replication user account
CREATE USER &&ReplLogin IDENTIFIED BY &&ReplPassword DEFAULT TABLESPACE &&DefaultTablespace QUOTA UNLIMITED ON &&DefaultTablespace;


-- It is recommended that only the required grants be granted to this
-- user.
--
-- The following 5 privileges are granted explicitly, but could be
-- granted through a role.
GRANT CREATE PUBLIC SYNONYM TO &&ReplLogin;
GRANT DROP PUBLIC SYNONYM TO &&ReplLogin;
GRANT CREATE SEQUENCE TO &&ReplLogin;
GRANT CREATE PROCEDURE TO &&ReplLogin;
GRANT CREATE SESSION TO &&ReplLogin;


-- The following privileges must be granted explicitly to the
-- replication user.
GRANT CREATE TABLE TO &&ReplLogin;
GRANT CREATE VIEW TO &&ReplLogin;


-- The replication user login needs to be able to create a tracking
-- trigger on any table that is to be published in a transactional
-- publication. The CREATE ANY privilege is used to obtain the
-- authorization to create these triggers.  To replicate a table, the
-- table owner must additionally explicitly grant select authorization
-- on the table to the replication user.
--
-- NOTE: CREATE ANY TRIGGER is not required for snapshot publications.
GRANT CREATE ANY TRIGGER TO &&ReplLogin;


不会使用脚本可以使用以下sql语句：
CREATE USER test IDENTIFIED BY test123 DEFAULT TABLESPACE cloudtable QUOTA UNLIMITED ON cloudtable;
GRANT CREATE PUBLIC SYNONYM TO test;
GRANT DROP PUBLIC SYNONYM TO test;
GRANT CREATE SEQUENCE TO test;
GRANT CREATE PROCEDURE TO test;
GRANT CREATE SESSION TO test;
GRANT CREATE TABLE TO test;
GRANT CREATE VIEW TO test;
GRANT CREATE ANY TRIGGER TO test;
grant select any table to test;
建立的用户是test密码是test123
注意一定不要给用于同步的用户任何数据库角色否则会有意想不到的报错。
下面开始在sql server数据库端配置分发：
右击复制-配置分发


一路下一步，配置数据库名可以自己随便定义，我这里默认：



数据库名自己可以定义





到此分发配置完毕。
下面新建oracle发布，右击本地发布-新建oracle发布








点击连接成功后点击确定。

发布类型选择快照发布

选择你需要发布的一个表


设置同步频率

这里使用sql server模拟代理



设置发布名称，到此发布服务器设置完毕。
下面开始设置本地订阅，右击本地订阅-新建订阅







注意这里我们都是用模拟进程账户。




点击完成，到此同步设置完毕，我们去看一下有没有同步到数据。

oracle数据库中的一个表已经同步过来。





可以参考微软文档：http://support2.microsoft.com/ho ... m=959195&kbln=zh-cn
来自群组: 北京运维圈