快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

使用GoldenGate 实现Oracle for Oracle 单向数据同步

请联系QQ:1793040 索取软件

背景:

阅读新闻

[日期:2015-09-16] 来源:Linux社区
作者:lqding
[字体:大 中 小]

实验环境

数据源端:host1 ip 192.168.199.163

数据目标端:host2 ip 192.168.199.104

两台机器都安装 http://www.linuxidc.com/Linux/2015-09/123120.htm 文中描述安装配置好了GoldenGate 。

要实现数据的同步,Oracle源端必须满足如下设置

Oracle需要运行在归档模式下

SQL> startup mount
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 574621728 bytes
Database Buffers 255852544 bytes
Redo Buffers 2412544 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

2.开启日志附加属性

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

ogg安装目录下提供了一些demo的sql

[oracle@localhost ogg]$ ls demo_ora_*
demo_ora_create.sql demo_ora_insert.sql demo_ora_lob_create.sql demo_ora_misc.sql demo_ora_pk_befores_create.sql demo_ora_pk_befores_insert.sql demo_ora_pk_befores_updates.sql

我们使用demo_ora_create.sql在源和目的端都创建两张表,在源端使用demo_ora_insert.sql插入数据。

host1

SQL> alter user scott identified by tiger account unlock;

User altered.

SQL> grant resource to scott; #ggsci 登录数据库时需要该权限
Grant succeeded.

SQL> grant select any dictionary to scott; # add trandata时需要该权限

Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> @demo_ora_create.sql
SQL> @demo_ora_insert.sql

host2

SQL> alter user scott identified by tiger account unlock;

User altered.
SQL> grant resource to scott; #ggsci 登录数据库时需要该权限
Grant succeeded.

SQL> grant select any dictionary to scott; # add trandata时需要该权限
SQL> conn scott/tiger
Connected.
SQL> @demo_ora_create.sql

为需要同步的表,添加附加日志

GGSCI (localhost.localdomain) 1> dblogin userid scott, password tiger
Successfully logged into database.

GGSCI (localhost.localdomain) 2> add trandata scott.tcustmer

Logging of supplemental redo data enabled for table SCOTT.TCUSTMER.

GGSCI (localhost.localdomain) 3> add trandata scott.tcustord

Logging of supplemental redo data enabled for table SCOTT.TCUSTORD.

一、初始化加载数据

在源端配置一个初始化Extract,用来同步表中现有数据

1
2 GGSCI (localhost.localdomain) 7> ADD EXTRACT EINILOAD, SOURCEISTABLE
EXTRACT added.

ADD EXTRACT命令用来添加一个EXTRACT, EINILOAD为Extract的group name 。SOURCEISTABLE表示数据源为表。

查看Extract的信息

GGSCI (localhost.localdomain) 9> INFO EXTRACT *, TASKS

EXTRACT EINILOAD Initialized 2015-09-11 15:25 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Not Available
First Record Record 0
Task SOURCEISTABLE

配置初始化加载的捕获参数

GGSCI (localhost.localdomain) 10> edit params EINILOAD


–GoldenGate Initial Data Capture
–for TCUSTMER and TCUSTORD

EXTRACT EINILOAD
USERID system, PASSWORD “oracle”
RMTHOST 192.168.199.104, MGRPORT 7809
RMTTASK REPLICAT, GROUP RINILOAD
TABLE SCOTT.TCUSTMER;
TABLE SCOTT.TCUSTORD;

在目标端,配置一个REPLICAT

1
2 GGSCI (localhost.localdomain) 2> ADD REPLICAT RINILOAD, SPECIALRUN
REPLICAT added.

查看REPLICAT信息

GGSCI (localhost.localdomain) 4> info replicat *, tasks

REPLICAT RINILOAD Initialized 2015-08-22 14:18 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:02:50 ago)
Log Read Checkpoint Not Available
Task SPECIALRUN

配置Replicat参数

GGSCI (localhost.localdomain) 5> edit params riniload


–GoldenGate Initial Load Delivery

REPLICAT RINILOAD
ASSUMETARGETDEFS
USERID system, PASSWORD “oracle”
DISCARDFILE https://www.linuxidc.com/Linux/2015-09/dirrpt/RINILOAD.dsc, PURGE
MAP scott.*, TARGET scott.*;

启动Extract

GGSCI (localhost.localdomain) 11> start extract einiload

Sending START request to MANAGER…
EXTRACT EINILOAD starting

查看日志

GGSCI (localhost.localdomain) 21> view report einiload

如果有报错,查找原因并解决

Processing table SCOTT.TCUSTMER

Processing table SCOTT.TCUSTORD

***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************

Report at 2015-09-11 16:23:40 (activity since 2015-09-11 16:23:33)

Output to RINILOAD:

From Table SCOTT.TCUSTMER:
# inserts: 2
# updates: 0
# deletes: 0
# discards: 0
From Table SCOTT.TCUSTORD:
# inserts: 2
# updates: 0
# deletes: 0
# discards: 0

REDO Log Statistics
Bytes parsed 0
Bytes output 574

日志显示,已成功同步数据。

到目的库上验证

SQL> select count(*) from tcustmer;

COUNT(*)
———-
2

SQL> select count(*) from tcustord;

COUNT(*)
———-
2

二、配置数据实时同步

源端,配置一个实时Extract

GGSCI (localhost.localdomain) 22> ADD EXTRACT EORAKK, TRANLOG, BEGIN NOW, THREADS 1
EXTRACT added.

编辑Extract的参数文件

GGSCI (localhost.localdomain) 23> EDIT PARAMS EORAKK


–Change Capture parameter file to capture
–TCUSTMER and TCUSTORD Changes

EXTRACT EORAKK
USERID system, PASSWORD oracle
RMTHOST 192.168.199.104, MGRPORT 7809
RMTTRAIL https://www.linuxidc.com/Linux/2015-09/dirdat/KK
TABLE SCOTT.TCUSTMER;
TABLE SCOTT.TCUSTORD;

为Extract添加远端tail file,也就是说这个tail file是在目的端生成的。

GGSCI (localhost.localdomain) 24> ADD RMTTRAIL https://www.linuxidc.com/Linux/2015-09/dirdat/KK, EXTRACT EORAKK, MEGABYTES 5
RMTTRAIL added.

验证结果

GGSCI (localhost.localdomain) 28> INFO RMTTRAIL *

Extract Trail: https://www.linuxidc.com/Linux/2015-09/dirdat/KK
Extract: EORAKK
Seqno: 0
RBA: 0
File Size: 5M

启动Extract 进程

GGSCI (localhost.localdomain) 29> start extract eorakk

Sending START request to MANAGER…
EXTRACT EORAKK starting

验证结果

GGSCI (localhost.localdomain) 30> INFO EXTRACT EORAKK, DETAIL

EXTRACT EORAKK Last Started 2015-09-11 17:07 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Log Read Checkpoint Oracle Redo Logs
2015-09-11 17:07:47 Thread 1, Seqno 25, RBA 36139008
SCN 0.1174781 (1174781)

Target Extract Trails:

Remote Trail Name Seqno RBA Max MB

https://www.linuxidc.com/Linux/2015-09/dirdat/KK 0 1050 5

Extract Source Begin End

/u01/app/oracle/oradata/orcl/redo01.log 2015-09-11 16:58 2015-09-11 17:07
Not Available * Initialized * 2015-09-11 16:58

Current directory/u01/app/ogg

Report file /u01/app/ogg/dirrpt/EORAKK.rpt
Parameter file /u01/app/ogg/dirprm/eorakk.prm
Checkpoint file /u01/app/ogg/dirchk/EORAKK.cpe
Process file /u01/app/ogg/dirpcs/EORAKK.pce
Stdout file /u01/app/ogg/dirout/EORAKK.out
Error log /u01/app/ogg/ggserr.log

GGSCI (localhost.localdomain) 31> VIEW REPORT EORAKK

GGSCI (localhost.localdomain) 32> INFO ALL

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING EORAKK 00:00:00 00:00:06

目标端,配置REPLICAT

安装checkpoint表

配置checkpoint表的名称

GGSCI (localhost.localdomain) 33> EDIT PARAMS https://www.linuxidc.com/Linux/2015-09/GLOBALS

HECKPOINTTABLE system.ggschkpt

生成checkpoint表

GGSCI (localhost.localdomain) 1> DBLOGIN USERID system, PASSWORD oracle
Successfully logged into database.

GGSCI (localhost.localdomain) 2> ADD CHECKPOINTTABLE

No checkpoint table specified, using GLOBALS specification (system.ggschkpt)…

Successfully created checkpoint table system.ggschkpt.

GGSCI (localhost.localdomain) 3>

添加Replicat

GGSCI (localhost.localdomain) 3> ADD REPLICAT RORAKK, EXTTRAIL https://www.linuxidc.com/Linux/2015-09/dirdat/KK
REPLICAT added.

创建Replicat的参数文件

GGSCI (localhost.localdomain) 4> EDIT PARAMS RORAKK


–Change Delivery parameter file to apply
–TCUSTMER and TCUSTORD Changes

REPLICAT RORAKK
USERID system, PASSWORD oracle
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE https://www.linuxidc.com/Linux/2015-09/dirrpt/RORAKK.DSC, PURGE
MAP scott.tcustmer, TARGET scott.tcustmer;
MAP scott.tcustord, TARGET scott.tcustord;

启动Replicat

GGSCI (localhost.localdomain) 5> start replicat rorakk

Sending START request to MANAGER…
REPLICAT RORAKK starting

验证结果

GGSCI (localhost.localdomain) 6> info replicat rorakk

REPLICAT RORAKK Last Started 2015-08-22 15:49 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Log Read Checkpoint File https://www.linuxidc.com/Linux/2015-09/dirdat/KK000000
First Record RBA 1050

验证数据同步

在源数据库,执行如下脚本,对两个表进行insert、update、delete操作

[oracle@localhost ogg]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 11 17:58:17 2015

Copyright (c) 1982, 2009, Oracle.All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/u01/app/ogg/demo_ora_misc.sql

查看源表中的数据

SQL> select count(*) from tcustmer;

COUNT(*)
———-
5

SQL> select count(*) from tcustord;

COUNT(*)
———-
3

查看目的表的数据

SQL> select count(*) from tcustmer;

COUNT(*)
———-
5

SQL> select count(*) from tcustord;

COUNT(*)
———-
3

数据已同步

Oracle GoldenGate 学习教程一:介绍和安装 http://www.linuxidc.com/Linux/2015-08/122146.htm

Oracle GoldenGate 学习教程二、配置和使用 http://www.linuxidc.com/Linux/2015-08/122325.htm

Oracle GoldenGate 学习教程三、加密 http://www.linuxidc.com/Linux/2015-09/122567.htm

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspxtid=12

本文永久更新链接地址:http://www.linuxidc.com/Linux/2015-09/123121.htm

linux

Oracle GoldenGate 介绍及安装配置教程
CentOS 5.9编译安装配置MySQL 5.6.10

相关资讯
GoldenGate Oracle数据同步
  • Oracle GoldenGate常用参数详解 (今 20:32)
  • GoldenGate对unused字段故障总结 (08月22日)
  • GoldenGate复制单表开并行 (05月06日)
  • GoldenGate单独一个表数据不同步解 (08月22日)
  • GoldenGate全部删除Trail 文件恢复 (08月22日)
  • 利用XAG在RAC环境下实现GoldenGate (12/18/2016 14:20:10)

本文评论
  查看全部评论 (0)

表情:表情
姓名:

字数



   
   

评论声明

  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款

最新资讯
  • Oracle GoldenGate常用参数详解
  • Linux 4.13 释出
  • Linux 桌面市场份额突破 3%
  • ReactOS 0.4.6 发布
  • Reddit 主代码将不再开源
  • C语言获取Linux系统精确时间
  • 华为宣布 AI 芯片
  • 一个网站 16% 的 JS 代码属于 Facebook
  • LinkedIn 开源 Kafka Cruise Control
  • 新勒索软件”Defray”可通过Microsoft Word

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1793040