技术头条 - 一个快速在微博传播文章的方式     搜索本站
您现在的位置首页 --> Oracle --> 异构数据库复制解决方案-HVR

异构数据库复制解决方案-HVR

浏览:2245次  出处信息

     异构数据库之间复制的解决方案有很多,可以通过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可以用来做测试,帮助我们了解他们的产品。

建议继续学习:

  1. iOS 高性能异构滚动视图构建方案 —— LazyScrollView    (阅读:1503)
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
© 2009 - 2024 by blogread.cn 微博:@IT技术博客大学习

京ICP备15002552号-1