ogg异构oracle-mysql

/*
参数文件里，TABLE参数中有cols和COLSEXCEPT 项
前者用来选择要capture的字段，后者指定要排除的字段
但是官方文档上有一句
Do not exclude key columns, and do not use COLSEXCEPT to exclude columns that contain unsupported data types.
*/
scott.bonus表无主键无唯一索引，若是在gg源端发出，会导致gg目的端abend，除非目的端复制进程参数文件有APPLYNOOPUPDATES 或者ALLOWNOOPUPDATES 参数)

--当目标端为oracle时用rowid解决增量同步
--1.
--源端
--add trandata hr.ah9 cols(id) nokey
--目标端
alter table hr.ah9 add (row_id rowid);
alter table hr.ah9 add constraint ah9_pk unique (row_id) enable;
map hr.ah9, target hr.ah9 colmap (usedefaults, row_id = @token ("TKN-ROWID")) keycols (row_id);

--2.编辑ext1,排除不需要同步的列或用COLS捕获需要同步的列
table ggs.test_pri, COLSEXCEPT (NAME2,NAME3);

一、环境描述：
源端：
ip:192.168.92.31
os：redhat5.4
Oracle： 11.2.0.1.0
goldgate:12.1.2.1.0 for oracle
字符集：gbk
目标端：
os：redhat5.4
Mysql:5.7.18
goldgate:12.1.2.1.0 for mysql
ip:192.168.92.61
字符集：utf8

二、Oracle to Mysql 需要注意的地方：
Supported data types for mysql：
CHARDOUBLETINYTEXTVARCHARDATEMEDIUMTEXTINTTIMELONGTEXTTINYINTYEARBLOBSMALL INTDATETIMETINYBLOBMEDIUM INTTIMESTAMPMEDIUMBLOBBIG INTBINARYLONGBLOBDECIMALVARBINARYENUMFLOATTEXTBIT(M)
Oracle GoldenGate supports InnoDB storage engine for a source MySQL database
goldengate对mysql只支持innodb引擎
所以，在创建mysql端的表的时候，要指定表为innodb引擎。
create table mysql (name char(10)) engine=innodb;
当然5.5默认的存储引擎就是InnoDB。

三、Oracle端的基础配置
1.安装oracle11g略过
2.初始化ogg
将ogg压缩包（V34339-01.zip）解压到 /home/oracle/app/oracle/ogg 下
cd /home/oracle/app/oracle/ogg
./ggsci

--已存在不需要执行
create subdirs
Creating subdirectories under current directory /home/oracle/app/oracle/ogg
Parameter files /home/oracle/app/oracle/ogg/dirprm: already exists
Report files /home/oracle/app/oracle/ogg/dirrpt: created
Checkpoint files /home/oracle/app/oracle/ogg/dirchk: created
Process status files /home/oracle/app/oracle/ogg/dirpcs: created
SQL script files /home/oracle/app/oracle/ogg/dirsql: created
Database definitions files /home/oracle/app/oracle/ogg/dirdef: created
Extract data files /home/oracle/app/oracle/ogg/dirdat: created
Temporary files /home/oracle/app/oracle/ogg/dirtmp: created
Stdout files /home/oracle/app/oracle/ogg/dirout: created
3.修改oracle数据库的参数
3.1 修改数据库为归档模式
3.2 打开辅助日志
alter database add supplemental log data;
3.3 关闭回收站
alter system set recyclebin=off deferred;

--Oracle11.2.0.4版本需要更改 ENABLE_GOLDENGATE_REPLICATION
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;

3.4 创建复制用的用户，并授权
--创建相应表空间
create tablespace oggtbs datafile '/home/oracle/app/oracle/oradata/ORCL/datafile/oggtbs01.dbf' size 500M autoextend on;

--授权
create user ggs identified by ggs default tablespace oggtbs;
GRANT create table to ggs;
GRANT CONNECT TO ggs;
GRANT ALTER ANY TABLE TO ggs;
GRANT ALTER SESSION TO ggs;
GRANT CREATE SESSION TO ggs;
GRANT FLASHBACK ANY TABLE TO ggs;
GRANT SELECT ANY DICTIONARY TO ggs;
GRANT SELECT ANY TABLE TO ggs;
GRANT RESOURCE TO ggs;
GRANT DELETE ANY TABLE TO ggs;
GRANT INSERT ANY TABLE TO ggs;
GRANT UPDATE ANY TABLE TO ggs;
GRANT RESTRICTED SESSION TO ggs;

3.5 登陆到ogg，执行初始化
在源库上执行：
GGSCI (ora11g) 2> edit params ./globals
在统计模式下输入并保存：ggschema ggs

在SQLPLUS 下去运行：
quit
sqlplus / as sysdba
@sequence.sql 根据提示输入：ggs

--#必须，针对ddl复制
alter system set recyclebin=off deferred scope=both;
--ogg安装目录执行配置脚本 prompt输入 ggs
@marker_setup.sql
@ddl_setup.sql
@role_setup.sql

grant GGS_GGSUSER_ROLE to ggs;
@ddl_enable.sql

--10g需要安装dbms_share_pool包：
@?/rdbms/admin/dbmspool.sql ho
@ddl_pin ggs;

四、Mysql端的基础配置
1.安装mysql5.7略过
2.给root配置密码：
mysql> use mysql
mysql> UPDATE user SET authentication_string =PASSWORD('123456') where USER='root' and host='root' or host='localhost';
mysql> show grants for root;
mysql> FLUSH PRIVILEGES;
mysql> exit
3.因为复制需要二进制日志，所以启动mysql的时候需要启用二进制日志
mysqld_safe --user=mysql --log-bin=/tmp/binlog.001 --log-bin-index=/tmp/binlog.index --max-binlog-size=10M --binlog-do-db=mysql &

--解压安装ogg by zhuyj
unzip V47367-01_GoldenGate\ V12.1.2.1.0\ for\ MySQL\ on\ Linux\ x86-64.zip
mkdir /usr/local/ogg
tar -xvf ggs_Linux_x64_MySQL_64bit.tar /usr/local/ogg

4.创建ogg的初始化目录
[mysql@nosql2 11.2]$ ./ggsci

Oracle GoldenGate Command Interpreter for MySQL
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Linux, x64, 64bit (optimized), MySQL Enterprise on Apr 23 2012 04:34:25
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (nosql2) 1> create subdirs
Creating subdirectories under current directory /usr/local
Parameter files /usr/local/dirprm: already exists
Report files /usr/local/dirrpt: created
Checkpoint files /usr/local/dirchk: created
Process status files /usr/local/dirpcs: created
SQL script files /usr/local/dirsql: created
Database definitions files /usr/local/dirdef: created
Extract data files /usr/local/dirdat: created
Temporary files /usr/local/dirtmp: created
Stdout files /usr/local/dirout: created

源端配置大致分为如下三个步骤：配置mgr，配置抽取进程，配置投递进程
在源端先创建一张表，记得带主键：
create table ah4(id int ,name varchar(10),primary key(id));

1.登陆ogg，配置全局设置
./ggsci
dblogin userid ggs password ggs

--查看远端全局变量
view params ./globals
ggschema ggs

2.配置mgr
GGSCI (ora11g) 3> edit param mgr

PORT 7809
DYNAMICPORTLIST 7810-7909
--AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

PS：
MANAGER进程参数配置说明：
PORT：指定服务监听端口；这里以7809为例，默认端口为7809
DYNAMICPORTLIST：动态端口：可以制定最大256个可用端口的动态列表，当指定的端口不可用时，管理进程将会从列表中选择一个可用的端口，源端和目标段的Collector、Replicat、GGSCI进程通信也会使用这些端口；
COMMENT：注释行，也可以用--来代替;
AUTOSTART：指定在管理进程启动时自动启动哪些进程；
AUTORESTART：自动重启参数设置：本处设置表示每3分钟尝试重新启动所有EXTRACT进程，共尝试5次；
PURGEOLDEXTRACTS：定期清理trail文件设置：本处设置表示对于超过3天的trail文件进行删除。
LAGREPORT、LAGINFO、LAGCRITICAL：
定义数据延迟的预警机制：本处设置表示MGR进程每隔1小时检查EXTRACT的延迟情况，如果超过了30分钟就把延迟作为信息记录到错误日志中，如果延迟超过了45分钟，则把它作为警告写到错误日志中。

3.启动mgr，并查看状态
start mgr
info all
4.添加并查看需要复制的表：
add trandata hr.ah4
add trandata hr.ah5

info trandata hr.*

Logging of supplemental redo log data is enabled for table HR.AH1.
Columns supplementally logged for table HR.AH1: ID.
Logging of supplemental redo log data is enabled for table HR.AH2.
Columns supplementally logged for table HR.AH2: ID.
Logging of supplemental redo log data is enabled for table HR.AH3.
Columns supplementally logged for table HR.AH3: ID.
Logging of supplemental redo log data is enabled for table HR.AH4.
Columns supplementally logged for table HR.AH4: ID.
Logging of supplemental redo log data is disabled for table HR.COUNTRIES.
Logging of supplemental redo log data is disabled for table HR.DEPARTMENTS.
Logging of supplemental redo log data is disabled for table HR.EMPLOYEES.
Logging of supplemental redo log data is disabled for table HR.JOBS.
Logging of supplemental redo log data is disabled for table HR.JOB_HISTORY.
Logging of supplemental redo log data is disabled for table HR.LOCATIONS.
Logging of supplemental redo log data is disabled for table HR.REGIONS.
Logging of supplemental redo log data is disabled for table HR.SURE1.

5.配置抽取进程
edit params ext3

extract ext3
dynamicresolution
userid ggs,password ggs
exttrail /home/oracle/app/oracle/ogg/dirdat/xs
table hr.ah4;
table hr.ah5;

add extract ext3,tranlog,begin now
add exttrail /home/oracle/app/oracle/ogg/dirdat/xs,extract ext3

--跟踪文件配置关键字长度<=2
--添加多线程
edit params ext4
extract ext4
dynamicresolution
userid ggs,password ggs
exttrail /home/oracle/app/oracle/ogg/dirdat/xs4
table hr.ah4;
table hr.ah5;

add extract ext4,tranlog,begin now
add exttrail /home/oracle/app/oracle/ogg/dirdat/x4,extract ext4

edit params ext5
extract ext5
dynamicresolution
userid ggs,password ggs
exttrail /home/oracle/app/oracle/ogg/dirdat/xs4
table hr.ah4;
table hr.ah5;

add extract ext5,tranlog,begin now
add exttrail /home/oracle/app/oracle/ogg/dirdat/x5,extract ext5

PS：
ext的模板可以是：
EXTRACT extmb
setenv (NLS_LANG = "AMERICAN_AMERICA.UTF8")
SETENV (ORACLE_HOME = "/u01/oracle/product/11.2.0/db_1")
SETENV (ORACLE_SID = "orcl")
USERID ggs, PASSWORD ggs
--GETTRUNCATES
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE ./dirrpt/extmb.dsc,APPEND,MEGABYTES 1024
--THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENS 60000
DBOPTIONS ALLOWUNUSEDCOLUMN
WARNLONGTRANS 2h,CHECKINTERVAL 3m
EXTTRAIL ./dirdat/mb
--TRANLOGOPTIONS EXCLUDEUSER USERNAME
FETCHOPTIONS NOUSESNAPSHOT
TRANLOGOPTIONS CONVERTUCS2CLOBS
TABLE hr.emp;
SETENV：配置系统环境变量
USERID/ PASSWORD：指定OGG连接数据库的用户名和密码，这里使用3.4部分中创建的数据库用户OGG;
COMMENT：注释行，也可以用--来代替;
TABLE：定义需复制的表，后面需以；结尾
TABLEEXCLUDE：定义需要排除的表，如果在TABLE参数中使用了通配符，可以使用该参数指定排除掉得表。
GETUPDATEAFTERS|IGNOREUPDATEAFTERS：
是否在队列中写入后影像，缺省复制
GETUPDATEBEFORES| IGNOREUPDATEBEFORES：
是否在队列中写入前影像，缺省不复制
GETUPDATES|IGNOREUPDATES：
是否复制UPDATE操作，缺省复制
GETDELETES|IGNOREDELETES：
是否复制DELETE操作，缺省复制
GETINSERTS|IGNOREINSERTS：
是否复制INSERT操作，缺省复制
GETTRUNCATES|IGNORETRUNDATES：
是否复制TRUNCATE操作，缺省不复制；

6.配置投递进程

edit params push3

extract push3
passthru
dynamicresolution
userid ggs,password ggs
rmthost 192.168.92.61,mgrport 7809
rmttrail /usr/local/dirdat/xs
table hr.ah4;
table hr.ah5;

GGSCI (ora11g) 18> add extract push3,exttrailsource /home/oracle/app/oracle/ogg/dirdat/xs
GGSCI (ora11g) 19> add rmttrail /usr/local/dirdat/xs,extract push3

--配置多线程
edit params push4

extract push4
passthru
dynamicresolution
userid ggs,password ggs
rmthost 192.168.92.61,mgrport 7809
rmttrail /usr/local/dirdat/xs4
table hr.ah4;
table hr.ah5;

add extract push4,exttrailsource /home/oracle/app/oracle/ogg/dirdat/x4
add rmttrail /usr/local/dirdat/x4,extract push4

GGSCI (orcl) 17> add rmttrail /usr/local/dirdat/x4,extract push4
PS：
push的模板：
EXTRACT pushmb
SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
USERID ggs, PASSWORD ggs
PASSTHRU
RMTHOST 192.168.0.165, MGRPORT 7809, compress
RMTTRAIL /home/oracle/app/oracle/ogg/dirdat/xs
TABLE hr.ah4;

RMTHOST：指定目标系统及其Goldengate Manager进程的端口号，还用于定义是否使用压缩进行传输，本例中的compress为压缩传输；
RMTTRAIL：指定写入到目标断的哪个队列；
EXTTRAIL：指定写入到本地的哪个队列；
SQLEXEC：在extract进程运行时首先运行一个SQL语句；
PASSTHRU：禁止extract进程与数据库交互，适用于Data Pump传输进程；
REPORT：定义自动定时报告；
STATOPTIONS：定义每次使用stat时统计数字是否需要重置；
REPORTCOUNT：报告已经处理的记录条数统计数字；
TLTRACE：打开对于数据库日志的跟踪日志；
DISCARDFILE：定义discardfile文件位置，如果处理中油记录出错会写入到此文件中；
DBOPTIONS：指定对于某种特定数据库所需要的特殊参数；
TRANLOGOPTIONS：指定在解析数据库日志时所需要的特殊参数，例如：对于裸设备，可能需要加入以下参数 rawdeviceoggset 0
WARNLONGTRANS：指定对于超过一定时间的长交易可以在gsserr.log里面写入警告信息，本处配置为每隔3分钟检查一次场交易，对于超过2小时的进行警告；

7.配置define文件
因为是异构，所以define作为2个数据库之间表的关系映射，是必不可少的。
--删除defsfile参数文件
--rm -rf /home/oracle/app/oracle/ogg/dirdef/ah4.prm

GGSCI (ora11g) 21> edit params ah4

defsfile /home/oracle/app/oracle/ogg/dirdef/ah4.prm
userid ggs,password ggs
table hr.*;

去相应的目录下生产define文件：应先对每张表创建trandata，在生成异构的表定义映射
[oracle@ora11g 11.2]$ ./defgen paramfile dirprm/ah4.prm

***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 16:58:29

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

Starting at 2014-04-02 15:47:20
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Fri Jan 27 17:17:51 EST 2012, Release 2.6.18-308.el5
Node: ora11g
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited

Process id: 31345

***********************************************************************
** Running with the following parameters **
***********************************************************************
defsfile /home/oracle/app/oracle/ogg/dirdef/ah4.prm
userid ggs,password ***
table hr.ah4;
Retrieving definition for HR.AH4

Definitions generated for 1 table in /home/oracle/app/oracle/ogg/dirdef/ah4.prm

--并将生成的/home/oracle/app/oracle/ogg/dirdef/ah4.prm 传到目的端的相应目录中去
scp /home/oracle/app/oracle/ogg/dirdef/* root@192.168.92.61:/usr/local/dirdef

目的端配置大致分为如下三个步骤：配置mgr，配置checkpoint table，配置应用进程
在目的端先创建一张表，记得带主键：
mysql> create database hr;
mysql> use hr
mysql> create table ah4(id int ,name varchar(10),primary key(id));
mysql> show tables;

1.配置mgr
cd /usr/local
./ggsci
GGSCI (nosql2) 2> edit params mgr

GGSCI (nosql2) 3> start mgr
GGSCI (nosql2) 4> info all
2.配置checkpoint table
GGSCI (nosql2) 7> edit params ./GLOBALS

CHECKPOINTTABLE hr.checkpoint
添加：

--mysql登陆ogg 登陆报错解决：WARNING OGG-00769
ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

dblogin sourcedb hr userid root,password mysql

GGSCI (nosql2) 8> add checkpointtable hr.checkpointtab
--删除检查点然后输入 y 可以删除检查点新增表的同步时不需要增加此步
--delete checkpointtable hr.checkpointtab
add checkpointtable hr.checkpointtab
info checkpointtable hr.checkpointtab

在相应的mysql数据库中，也可以看到相应的表被添加了：
mysql> show tables;

3.配置应用进程：
--若同步的表发生变化，则此进程需要重新配置，然后目标段表会重新初始化数据对应的报错为：
2017-04-21 15:19:03 ERROR OGG-00446
2017-04-21 15:19:03 ERROR OGG-01668 PROCESS ABENDING.

--SOURCEDB 已改为TARGETDB 解决无主键表同步：APPLYNOOPUPDATES或ALLOWNOOPUPDATES 同步成功后此参数可以删除******
GGSCI (nosql2) 10> edit params rep3

replicat rep3
sourcedefs /usr/local/dirdef/ah4.prm
SOURCEDB hr,userid root,password mysql
reperror default,discard
discardfile /usr/local/dirrpt/rep4.dsc,append,megabytes 50
map hr.ah4, target hr.ah4;
map hr.ah5,target hr.ah5;

add replicat rep3,exttrail /usr/local/dirdat/xs,checkpointtable hr.checkpointtab

--增加多线程
add replicat rep4,exttrail /usr/local/dirdat/xs,checkpointtable hr.checkpointtab

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 root@192.168.92.61:/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 sure@localhost:3306,userid root,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）。

-------------------------------------------------------------------------------------------------------------------------
配置ogg异构mysql-oracle 单向同步
从mysql到oracle和oracle到mysql差不多。大致步骤如下：
环境是:
192.168.0.165 (Mysql ) —> 192.168.0.164 ( Oracle )
想将mysql的sure库下的ah6 同步到 oracle的 hr.ah6下
版本：
操作系统：redhat5.8
Oracle： 11.2.0.3
Mysql: 5.5.37
goldgate:
11.2.0.1.3 for oracle
11.2.0.1.1 for mysql
大致的配置过程如下：
----------------------------
源端（mysql,以log-bin方式启动的，format为row）
1.配置抽取进程
edit params ext5
extract ext5
setenv (MYSQL_HOME="/u01/mysql")
sourcedb sure@localhost:3306,userid root,password 123456
tranlogoptions altlogdest /tmp/binlog.index
exttrail /home/oracle/app/oracle/ogg/dirdat/mb
--dynamicresolution
--gettruncates
table sure.ah6;
2.给ext5添加本地trail
add extract ext5,tranlog,begin now
add exttrail /home/oracle/app/oracle/ogg/dirdat/mb,extract ext5
3.配置pump进程
edit params pump5
extract pump5
rmthost 192.168.0.164,mgrport 7809
rmttrail /home/oracle/app/oracle/ogg/dirdat/mb
passthru
table sure.ah6;
4.给pump5添加本地和远程的trail
add extract pump5,exttrailsource /home/oracle/app/oracle/ogg/dirdat/mb
add rmttrail /home/oracle/app/oracle/ogg/dirdat/mb,extract pump5
5.配置define文件
edit params ah6
defsfile /home/oracle/app/oracle/ogg/dirdef/ah6.prm
sourcedb sure@localhost:3306,userid root,password 123456
table sure.ah6;
生成define，并将define传到oracle服务器上
./defgen paramfile dirprm/ah6.prm
------------------------
以下是配置目的端（oracle）
1.配置接收进程
edit params rep2
replicat rep2
sourcedefs /home/oracle/app/oracle/ogg/dirdef/ah6.prm
userid ggs,password ggs
reperror default,discard
discardfile /home/oracle/app/oracle/ogg/dirrpt/rep2.dsc,append,megabytes 50
dynamicresolution
map sure.ah6, target hr.ah6;
2.添加checkpoint表
add checkpointtable ggs.checkpointtab
3.给接收进行添加trail和checkpoint table
add replicat rep2,exttrail /home/oracle/app/oracle/ogg/dirdat/mb,checkpointtable ggs.checkpointtab
add replicat rep2,exttrail /home/oracle/app/oracle/ogg/dirdat/mb,checkpointtable repggs.checkpointtab
--alter replicat rep2,exttrail /home/oracle/app/oracle/ogg/dirdat/mb,checkpointtable repggs.checkpointtab

需要注意的是，mysql需要使用log的format为row模式。另外，要注意binlog-do-db这个参数，如果配置了的话，那么一定要包含需要复制的数据库在内，我就在这个地方犯了傻。
----------------------------------------------------------------------------------------------------------------------
配置ogg异构oracle-mysql 双向同步注意事项
双向同步需要考虑的是怎么解决循环复制，以及同时更新一张表以谁为基准。
配置过程就不写了，大致和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：

posted @ 2019-10-14 15:23 virtual_daemon 阅读(551) 评论(0) 编辑收藏举报

刷新页面返回顶部

daemon

ogg异构oracle-mysql

公告