异构数据库复制解决方案-HVR
异构数据库之间复制的解决方案有很多,可以通过JDBC方式连接数据库用程序读取数据,然后批量插入目标端,但是这种实时复制的话,需要通过触发器或者加入时间判断来执行;也可以通过触发器、Oracle Stream等来执行.最近国内最火的就是Oracle Goldengate,我过去2年也是一直在Oracle公司做Goldengate咨询、实施,Goldengate能够解析DB2、Oracle、MySQL、SQL Server、Sybase等数据库,而且跨平台、跨版本的特性使它迅速占领国内的市场,打破了以往DSG、Shareplex的统治领域,主要就是它支持异构数据库,实际上在欧洲还有一个产品叫HVR (High Volume Replication)它的原理和Goldengate一样,可以挖掘Oracle、SQL Server、DB2的日志,实时或者异步的方式同步到目标数据库,而且也是跨平台的方式,它的最大优势就是低成本、易操作的界面,同时它支持DDL(数据字典)的初始同步、双向复制,而不像Goldengate做个双向复制,还要用SQLEXEC去写,HVR在国内的案例目前只有广东公安局,这个可能和他们在中国没有分公司的关系,但是这个产品,我最近测试了一下,还是不错的。
1.创建环境变量:
HVR_HOME 软件安装目录,解压缩就可以
HVR_CONFIG= %HVR_HOME%/CONFIG
2. 创建HVR Channel用户:
$ sqlplus system/manager SQL> create user hvrhub identified by hvr 2 default tablespace users 3 temporary tablespace temp 4 quota unlimited on users; SQL> grant create session to hvrhub; SQL> grant create table to hvrhub; SQL> grant create sequence to hvrhub; SQL> grant create procedure to hvrhub; SQL> grant create trigger to hvrhub; SQL> grant create view to hvrhub; SQL> grant execute any procedure to hvrhub; $ sqlplus Enter user-name: / as sysdba SQL> grant execute on dbms_alert to hvrhub; SQL> exit;
在运行中,执行hvrgui,进入以后输入ORACLE_HOME、ORACLE_SID,然后指定用户,这个用户用于HVR的Catalog存储信息,包括复制的表、源数据库和目标数据库以及提取数据文件的路径。
创建测试用户,通过HVR软件自带的DEMO脚本:
C:\\HVR\\demo\\hvr_demo01\\base\\oracle>dir Volume in drive C has no label. Volume Serial Number is 70C5-C1F8 Directory of C:\\HVR\\demo\\hvr_demo01\\base\\oracle 09/25/2011 10:49 PM. 09/25/2011 10:49 PM .. 07/12/2011 01:44 PM 345 hvr_demo01.cre 07/12/2011 01:44 PM 54 hvr_demo01.drp 07/12/2011 01:44 PM 141 hvr_demo01.mod 3 File(s) 540 bytes 2 Dir(s) 42,558,943,232 bytes free
创建3个用户,并赋予相应的权限:
SQL> create user testdb1 identified by hvr 2 default tablespace users 3 temporary tablespace temp 4 quota unlimited on users; User created. SQL> create user testdb2 identified by hvr 2 default tablespace users 3 temporary tablespace temp 4 quota unlimited on users; User created. SQL> create user testdb3 identified by hvr 2 temporary tablespace temp 3 default tablespace users 4 quota unlimited on users; User created. SQL> grant create session to testdb1, testdb2, testdb3; Grant succeeded. SQL> grant create table to testdb1, testdb2, testdb3; Grant succeeded. SQL> grant create sequence to testdb1, testdb2, testdb3; Grant succeeded. SQL> grant create procedure to testdb1, testdb2, testdb3; Grant succeeded. SQL> grant create trigger to testdb1, testdb2, testdb3; Grant succeeded. SQL> grant create view to testdb1, testdb2, testdb3; Grant succeeded. SQL> grant execute any procedure to testdb1, testdb2, testdb3; Grant succeeded. 通过DEMO脚本像这3个数据库用户创建表,并插入数据: C:\\HVR\\demo\\hvr_demo01\\base\\oracle>more hvr_demo01.cre create table dm01_order ( prod_id number(11) not null, ord_id number(11) not null, cust_name varchar2(100) not null, cust_addr varchar2(100) ) / create table dm01_product ( prod_id number(11) not null, prod_price number(10,2) not null, prod_descrip varchar2(100) not null ) / C:\\HVR\\demo\\hvr_demo01\\base\\oracle>sqlplus testdb1/hvr @ hvr_demo01.cre SQL*Plus: Release 10.1.0.2.0 - Production on Mon Sep 26 00:04:28 2011 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options Table created. Table created. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Product ion With the Partitioning, OLAP and Data Mining options C:\\HVR\\demo\\hvr_demo01\\base\\oracle>sqlplus testdb1/hvr < hvr_demo01.mod SQL*Plus: Release 10.1.0.2.0 - Production on Mon Sep 26 00:04:38 2011 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> 2 3 4 Index created. SQL> 2 3 4 Index created. SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr oduction With the Partitioning, OLAP and Data Mining options C:\\HVR\\demo\\hvr_demo01\\base\\oracle>sqlplus testdb2/hvr < hvr_demo01.cre SQL*Plus: Release 10.1.0.2.0 - Production on Mon Sep 26 00:04:45 2011 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> 2 3 4 5 6 7 Table created. SQL> 2 3 4 5 6 Table created. SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr oduction With the Partitioning, OLAP and Data Mining options C:\\HVR\\demo\\hvr_demo01\\base\\oracle>sqlplus testdb2/hvr < hvr_demo01.mod SQL*Plus: Release 10.1.0.2.0 - Production on Mon Sep 26 00:04:50 2011 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> 2 3 4 Index created. SQL> 2 3 4 Index created. SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr oduction With the Partitioning, OLAP and Data Mining options C:\\HVR\\demo\\hvr_demo01\\base\\oracle>sqlplus testdb3/hvr < hvr_demo01.cre SQL*Plus: Release 10.1.0.2.0 - Production on Mon Sep 26 00:04:56 2011 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> 2 3 4 5 6 7 Table created. SQL> 2 3 4 5 6 Table created. SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr oduction With the Partitioning, OLAP and Data Mining options C:\\HVR\\demo\\hvr_demo01\\base\\oracle>sqlplus testdb3/hvr < hvr_demo01.mod SQL*Plus: Release 10.1.0.2.0 - Production on Mon Sep 26 00:05:01 2011 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> 2 3 4 Index created. SQL> 2 3 4 Index created. SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr oduction With the Partitioning, OLAP and Data Mining options C:\\HVR\\demo\\hvr_demo01\\base\\oracle>
在HVR界面里,配置数据源和目标的位置,参考HVR手册
在HVR选择要复制的表,在Tables右键点击Tables Select
选择两个表以后,点击ADD:
然后准备捕获数据,点击LOCATION然后选择CENTRAL右键点击DBCAPTURE
选择基于日志方式的数据捕获,而不用触发器:
选择数据复制的目标端,选择复制位置DECENTRAL->New Action->DbIntrgrate
点击选择OnErrorSaveFailedRow,这个控制复制的错误行如何被处理。
配置完源端和目标端以后,我们可以点击Channel查看我们的定义,Channel就是存储我们的配置信息:
我们配置以后,也可以随时通过点击Group右键properties修改我们的配置,从我们这个配置可以看到,我们源端是我们之前定义的3个数据库的2个表,testdb1,testdb2,testdb3,目前端是同样的3个库的2个表。
开始复制数据:
选择Hvr_demo01,我们之前定义的Channel,然后右键点击HVR LOAD
选择Create or Replcace Objects,选择要复制的位置,同时点击高级选项,选择一些高级参数
我们选择要复制的表,并行度等
点击右下角的HVR Load:
完成时会提示,启动了2个调度任务.但是这两个任务并没有启动,需要我们配置一个调度。
点击Scheduler,然后右键点击Create,然后选择Run in local instead
调度启动以后,会在HVR_CONFIG目录下面创建一些文件和脚本,C:\\HVR\\HVR_CONFIG>dir
Volume in drive C has no label.
Volume Serial Number is 70C5-C1F8
Directory of C:\\HVR\\HVR_CONFIG
09/26/2011 01:30 AM .
09/26/2011 01:30 AM ..
09/25/2011 11:28 PM files
09/26/2011 12:35 AM job
09/26/2011 01:29 AM log
09/26/2011 12:35 AM router
09/26/2011 12:35 AM sqlgen
09/26/2011 01:29 AM work
09/26/2011 01:30 AM wwwgen
0 File(s) 0 bytes
9 Dir(s) 42,559,631,360 bytes free
这样就基本完成了HVR的配置,HVR手册里面有几十个DEMO可以用来做测试,帮助我们了解他们的产品。
建议继续学习:
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:zhaoyu 来源: ZhaoYu Oracle Consultanting Life
- 标签: HVR 异构
- 发布时间:2012-04-09 13:41:11
- [54] IOS安全–浅谈关于IOS加固的几种方法
- [52] android 开发入门
- [52] 如何拿下简短的域名
- [51] 图书馆的世界纪录
- [49] Oracle MTS模式下 进程地址与会话信
- [49] Go Reflect 性能
- [47] 【社会化设计】自我(self)部分――欢迎区
- [46] 读书笔记-壹百度:百度十年千倍的29条法则
- [36] 程序员技术练级攻略
- [29] 视觉调整-设计师 vs. 逻辑