加入收藏 | 设为首页 | 会员中心 | 我要投稿 惠州站长网 (https://www.0752zz.com.cn/)- 办公协同、云通信、物联设备、操作系统、高性能计算!
当前位置: 首页 > 站长百科 > 正文

ogg异构oracle-mysql

发布时间:2020-12-24 06:20:39 所属栏目:站长百科 来源:网络整理
导读:副标题#e# /* 参数文件里,TABLE参数中有cols和COLSEXCEPT 项 前者用来选择要capture的字段,后者指定要排除的字段 但是官方文档上有一句 Do not exclude key columns,and do not use COLSEXCEPT to exclude columns that contain unsupported data types.

PS:
REPLICAT进程参数配置说明:
ASSUMETARGETDEFS:假定两端数据结构一致使用此参数;
SOURCEDEFS:假定两端数据结构不一致,使用此参数指定源端的数据结构定义文件,该文件需要由GlodenGate工具产生。
MAP:用于指定源端与目标端表的映射关系;
MAPEXCLUDE:用于使用在MAP中使用*匹配时排除掉指定的表;
REPERROR:定义出错以后进程的响应,一般可以定义为两种:
ABEND,即一旦出现错误即停止复制,此为缺省配置;
DISCARD,出现错误后继续复制,只是把错误的数据放到discard文件中。
DISCARDFILE:定义discardfile文件位置,如果处理中油记录出错会写入到此文件中;
SQLEXEC:在进程运行时首先运行一个SQL语句;
GROUPTRANSOPS:将小交易合并成一个大的交易进行提交,减少提交次数,降低系统IO消耗。
MAXTRANSOPS:将大交易拆分,每XX条记录提交一次。

--view report rep3查询进程报错:OGG-00303 Could not open /usr/local/dirdef/ah4.prm: No such file or directory.
解决:
--复制生成的def文件到目标端
scp ah4.prm [email?protected]:/usr/local/dirdef
原因:
two ways to solve the issue:
Use a defgen paramfile with NOEXTATTR option
or
generate definition file with the same OGG version as the OGG target site version.
In OGG 11.2,there is a new parameter NOEXTATTR. This is used in DEFGEN.
When the OGG version in a target site is lower than the source site,defgen needs to use parameter NOEXTATTR to generate a sourcedef file which target site can read . If using a sourcedef file generated without NOEXTATTR,a replicat will abend with error 00303.

--查看错误日志
tail -100f /usr/local/ggserr.log

4.测试
在目的端启动rep3进程,在源端启动ext3和push3进程。
在源端的ah4表中插入一条数据,看是否在目的端的ah4表中能看到。
源端进程:

GGSCI (ora11g) 30> info all 多线程 ext1 ext2 ext3
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:03
EXTRACT RUNNING EXT2 00:00:00 00:00:07
EXTRACT RUNNING EXT3 00:44:09 00:00:06
EXTRACT RUNNING PUSH1 00:00:00 00:00:03
EXTRACT RUNNING PUSH2 00:00:00 00:00:03
EXTRACT RUNNING PUSH3 00:00:00 00:37:40

GGSCI (ora11g) 31>
源端插入数据:
insert into ah4 values(1,‘aaaccc‘);
insert into ah4 values(2,‘aaaccc‘);
insert into ah4 values(3,‘aaaccc‘);
commit;

源端的ogg日志:
2014-04-02 16:19:26 INFO OGG-00993 Oracle GoldenGate Capture for Oracle,ext3.prm: EXTRACT EXT3 started.
2014-04-02 16:19:26 INFO OGG-01052 Oracle GoldenGate Capture for Oracle,ext3.prm: No recovery is required for target file /home/oracle/app/oracle/ogg/dirdat/xs000000,at RBA 0 (file not opened).
2014-04-02 16:19:26 INFO OGG-01478 Oracle GoldenGate Capture for Oracle,ext3.prm: Output file /home/oracle/app/oracle/ogg/dirdat/xs is using format RELEASE 11.2.
2014-04-02 16:19:26 INFO OGG-01517 Oracle GoldenGate Capture for Oracle,ext3.prm: Position of first record processed Sequence 1122,RBA 14423056,SCN 0.20548956,Apr 2,2014 3:35:22 PM.
2014-04-02 16:19:29 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start push3.
2014-04-02 16:19:29 INFO OGG-00963 Oracle GoldenGate Manager for Oracle,mgr.prm: Command received from GGSCI on host ora11g:52177 (START EXTRACT PUSH3 ).
2014-04-02 16:19:29 INFO OGG-00975 Oracle GoldenGate Manager for Oracle,mgr.prm: EXTRACT PUSH3 starting.
2014-04-02 16:19:29 INFO OGG-00992 Oracle GoldenGate Capture for Oracle,push3.prm: EXTRACT PUSH3 starting.
2014-04-02 16:19:29 INFO OGG-03035 Oracle GoldenGate Capture for Oracle,push3.prm: Operating system character set identified as UTF-8. Locale: en_US,LC_ALL:.
2014-04-02 16:19:29 INFO OGG-01815 Oracle GoldenGate Capture for Oracle,push3.prm: Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/home/oracle/app/oracle/ogg/dirtmp.
2014-04-02 16:19:29 INFO OGG-00993 Oracle GoldenGate Capture for Oracle,push3.prm: EXTRACT PUSH3 started.
2014-04-02 16:19:34 INFO OGG-01226 Oracle GoldenGate Capture for Oracle,push3.prm: Socket buffer size set to 27985 (flush size 27985).
2014-04-02 16:19:34 INFO OGG-01052 Oracle GoldenGate Capture for Oracle,push3.prm: No recovery is required for target file /home/oracle/app/oracle/ogg/dirdat/xs000000,at RBA 0 (file not opened).
2014-04-02 16:19:34 INFO OGG-01478 Oracle GoldenGate Capture for Oracle,push3.prm: Output file /home/oracle/app/oracle/ogg/dirdat/xs is using format RELEASE 11.2.


目的端进程:

GGSCI (nosql2) 21> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP3 00:00:00 00:00:02

--目的端数据库:
mysql> select * from ah4;

目的端的ogg日志:
2014-04-02 16:21:55 INFO OGG-00975 Oracle GoldenGate Manager for MySQL,mgr.prm: REPLICAT REP3 starting.
2014-04-02 16:21:55 INFO OGG-00995 Oracle GoldenGate Delivery for MySQL,rep3.prm: REPLICAT REP3 starting.
2014-04-02 16:21:55 INFO OGG-03035 Oracle GoldenGate Delivery for MySQL,rep3.prm: Operating system character set identified as UTF-8. Locale: en_US,LC_ALL:.
2014-04-02 16:21:55 INFO OGG-01815 Oracle GoldenGate Delivery for MySQL,rep3.prm: Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/home/oracle/app/oracle/ogg/dirtmp.
2014-04-02 16:21:55 INFO OGG-00996 Oracle GoldenGate Delivery for MySQL,rep3.prm: REPLICAT REP3 started.

2014-04-02 16:22:17 INFO OGG-00963 Oracle GoldenGate Manager for MySQL,mgr.prm: Command received from EXTRACT on host ::ffff:192.168.0.164 (START SERVER CPU -1 PRI -1 TIMEOUT 300 PARAMS ).
2014-04-02 16:22:17 INFO OGG-01677 Oracle GoldenGate Collector for MySQL: Waiting for connection (started dynamically).
2014-04-02 16:22:17 INFO OGG-00963 Oracle GoldenGate Manager for MySQL,mgr.prm: Command received from SERVER on host localhost.localdomain (REPORT 30868 7810).
2014-04-02 16:22:17 INFO OGG-00974 Oracle GoldenGate Manager for MySQL,mgr.prm: Manager started collector process (Port 7810).
2014-04-02 16:22:17 INFO OGG-01228 Oracle GoldenGate Collector for MySQL: Timeout in 300 seconds.
2014-04-02 16:22:22 INFO OGG-01229 Oracle GoldenGate Collector for MySQL: Connected to ::ffff:192.168.0.164:61104.
2014-04-02 16:22:22 INFO OGG-01669 Oracle GoldenGate Collector for MySQL: Opening /home/oracle/app/oracle/ogg/dirdat/xs000000 (byte -1,current EOF 0).
2014-04-02 16:22:53 INFO OGG-03010 Oracle GoldenGate Delivery for MySQL,rep3.prm: Performing implicit conversion of column data from character set windows-936 to ISO-8859-1.

双向同步需要考虑的是怎么解决循环复制,以及同时更新一张表以谁为基准。
配置过程就不写了,大致和oracle到mysql的单向+mysql到oracle的单向差不多。
需要注意的有如下几点:
1.oracle和mysql的2端,抽取(extract)和应用(replication)应该使用不同的用户
2.为解决禁止循环复制,应该在ext进程配置3个参数,如下:
oracle的extract:

extract ext4
dynamicresolution
userid ggs,password ggs
RANLOGOPTIONS EXCLUDEUSER repggs
GETAPPLOPS
IGNOREREPLICATES
exttrail /home/oracle/app/oracle/ogg/dirdat/dd
table hr.ah6;

mysql的extract:

extract ext5
setenv (MYSQL_HOME="/u01/mysql")
sourcedb [email?protected]:3306,password 123456
tranlogoptions altlogdest /tmp/binlog.index
TRANLOGOPTIONS EXCLUDEUSER reproot
GETAPPLOPS
IGNOREREPLICATES
exttrail /home/oracle/app/oracle/ogg/dirdat/mb
--dynamicresolution
--gettruncates
table sure.ah6;

只复制应用产生的数据,忽略replication产生的数据,以及忽略replication进程的用户。
3. ./GLOBALS 是全局变量,只在replication的时候有用,所以配置的时候需要加上:
GGSCHEMA repggs --这个参数只在oracle里面有
CheckpointTable repggs.checkpointtab --这个参数oracle和mysql里面都有
4.在extract用ggs/root用户,在replication用repggs/reproot用户
5.因为是双向同步,所以同一个表,比如说ah6,在oracle生成的define要传递到mysql去,在mysql生成的define也要传递到oracle去。
所以,建议命名规则是oracle->mysql:ah6_o2m.prm,mysql->oracle:ah6_m2o.prm
6.从oracle复制到mysql的dml操作,需要手工commit,这个还没找到解决的方法(除了把auto_commit改为on)。

(编辑:惠州站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

推荐文章
    热点阅读