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的初始化目录
[[email?protected] 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

（编辑：惠州站长网）

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