4

异构数据库复制解决方案-HVR -- Oracle -- IT技术博客大学习 -- 共学习 共进步！

 2 years ago
source link: https://blogread.cn/it/article/5193?f=hot1
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

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

浏览:1931次  出处信息

     异构数据库之间复制的解决方案有很多，可以通过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    <dir>          .
09/25/2011  10:49 PM    <dir>          ..
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</dir></dir>

    创建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    (阅读：898)
QQ技术交流群：445447336，欢迎加入！
扫一扫订阅我的微信号：IT技术博客大学习

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK