Oracle GoldenGate 异构平台同步（Mysql到Oracle）

werew33

Oracle GoldenGate 异构平台同步（Mysql到Oracle）

一、OGG安装配置（源端）
1、OGG下载
http://www.oracle.com/technetwor ... ownloads/index.html

Mysql 选择适用于 Linux x86-64 上 MySQL 的 Oracle GoldenGate 12.1.2.1.0 版
Oracle 选择适用于 Linux x86-64 上 Oracle 的 Oracle GoldenGate 12.1.2.1.0 版


ggs_Linux_x64_MySQL_64bit.tar


mkdir -p /opt/local/ggs

tar xvf ggs_Linux_x64_MySQL_64bit.tar

cd /opt/local/ggs

./ggsci

GGSCI (mysql) 1>  help     查看gg的各种命令

GGSCI (mysql) 1>  create subdirs       在当前目录创建一些目录

-----------------------------------------------------------------------------------

Creating subdirectories under current directory /opt/local/ggs

Parameter files                /opt/local/ggs/dirprm: already exists
Report files                   /opt/local/ggs/dirrpt: created
Checkpoint files               /opt/local/ggs/dirchk: created
Process status files           /opt/local/ggs/dirpcs: created
SQL script files               /opt/local/ggs/dirsql: created
Database definitions files     /opt/local/ggs/dirdef: created
Extract data files             /opt/local/ggs/dirdat: created
Temporary files                /opt/local/ggs/dirtmp: created
Credential store files         /opt/local/ggs/dircrd: created
Masterkey wallet files         /opt/local/ggs/dirwlt: created
Dump files                     /opt/local/ggs/dirdmp: created

-----------------------------------------------------------------------------------




二、Mysql 数据库配置

my.cnf

打开

log-bin 日志

配置 binlog_format 格式为 row


登陆mysql

mysq -uroot -p

MariaDB [(none)]> create database moxian;

MariaDB [(none)]> use moxian;

MariaDB [(none)]> create table test (id int,name char(10)) engine=innodb;



目标端：Oracle 库配置：
--------------------------------------------------------------------------------
SQL> alter system set enable_goldengate_replication=true;

SQL> create user test identified by test ;   

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> conn test/test
Connected.

SQL> create table test (id int,name varchar2(10));

Table created.



--------------------------------------------------------------------------------


三、OGG同步配置

源端：Mysql库配置

cd /opt/local/ggs/

./ggsci

GGSCI (mysql) 3> dblogin sourcedb moxian@localhost:3306,userid root,password rldb123
Successfully logged into database.

----------------------------------------------------------------------------------------
使用root 帐号，登陆到刚才创建的moxian库中
----------------------------------------------------------------------------------------


配置mgr：

GGSCI (mysql DBLOGIN as root) 4> edit param mgr

port 7801
dynamicportlist 7800-7810
autorestart extract *,waitminutes 2,resetminutes 5


-------------------------------------------------------------------------------------------
GGSCI (mysql DBLOGIN as root) 5> start mgr
Manager started.

-------------------------------------------------------------------------------------------
GGSCI (mysql DBLOGIN as root) 6> info mgr

Manager is running (IP port mysql.7801, Process ID 1665).

-------------------------------------------------------------------------------------------
GGSCI (mysql DBLOGIN as root) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING   

-------------------------------------------------------------------------------------------

配置extract进程组：

GGSCI (mysql DBLOGIN as root) 8> edit param ext_1

extract ext_1
setenv (NLS_LANG=”AMERICAN_AMERICA.UTF8”)
tranlogoptions altlogdest /opt/data/mysql/binlog/mysql-bin.index
sourcedb moxian@localhost:3306,userid root,password rldb123
exttrail /opt/local/ggs/dirdat/e2
dynamicresolution
gettruncates
table moxian.test;


-------------------------------------------------------------------------------------------
GGSCI (mysql DBLOGIN as root) 9> add extract ext_1,tranlog,begin now
EXTRACT added.


-------------------------------------------------------------------------------------------
GGSCI (mysql DBLOGIN as root) 10> add exttrail /opt/local/ggs/dirdat/e2,extract ext_1
EXTTRAIL added.


配置pump进程组：
GGSCI (mysql DBLOGIN as root) 11> edit params pump_1

extract pump_1
setenv (MYSQL_HOME=”/opt/local/mysql”)
setenv (NLS_LANG=”AMERICAN_AMERICA.UTF8”)
rmthost 10.6.0.207,mgrport 7801
rmttrail /opt/local/ggs/dirdat/e2
passthru
gettruncates
table moxian.test;


-------------------------------------------------------------------------------------------
GGSCI (mysql DBLOGIN as root) 12> add extract pump_1,exttrailsource /opt/local/ggs/dirdat/e2
EXTRACT added.


-------------------------------------------------------------------------------------------
GGSCI (mysql DBLOGIN as root) 13> add rmttrail  /opt/local/ggs/dirdat/e2,extract pump_1
RMTTRAIL added.



删除  PUMP
delete extract PUMP_1

启动 PUMP

start extract PUMP_1



异构平台配置defgen：
GGSCI (mysql DBLOGIN as root) 14> edit params defgen

defsfile /opt/local/ggs/dirdef/defgen.prm
sourcedb moxian@localhost:3306, userid root,password rldb123
table moxian.test;


cd /opt/local/ggs

./defgen paramfile dirprm/defgen.prm



-------------------------------------------------------------------------------------------
***********************************************************************
        Oracle GoldenGate Table Definition Generator for MySQL
      Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203
Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 20 2014 04:32:20

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


                    Starting at 2015-11-02 17:28:05
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Mon Jun 30 12:09:22 UTC 2014, Release 3.10.0-123.el7.x86_64
Node: mysql
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: 1790

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile /opt/local/ggs/dirdef/defgen.prm
sourcedb moxian@localhost:3306, userid root,password *******
table moxian.test;
Retrieving definition for moxian.test.


Definitions generated for 1 table in /opt/local/ggs/dirdef/defgen.prm.
-------------------------------------------------------------------------------------------




传送defgen文件到目标端oracle服务器上：


将/opt/local/ggs/dirdef/defgen.prm 文件放在 /opt/local/ggs/dirprm   目录下


-------------------------------------------------------------------------------------------
GGSCI (mysql) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT_1       00:00:00      00:00:02   
EXTRACT     RUNNING     PUMP_1      00:00:00      00:00:00  


-------------------------------------------------------------------------------------------
启动extract和pump进程：

GGSCI (mysql) 2> start extract ext_1

Sending START request to MANAGER ...
EXTRACT EXT_1 starting



-------------------------------------------------------------------------------------------
GGSCI (mysql) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT_1       00:00:00      00:00:02   
EXTRACT     RUNNING     PUMP_1      00:00:00      00:00:00





四、oracle目标端ogg配置:

安装 oracle 版 goldengate

cd fbo_ggs_Linux_x64_shiphome/Disk1

使用 oracle 运行用户进行安装

su oracle

运行 ./runInstaller               需要在图形化界面下安装


第一步， 选择oracle 版本

第二步， 1.安装路径， 2. oracle 数据库目录   3. 运行端口

第三步， 查看设置是否正确，确认以后点击 install 进行安装

第四步， 提示 Successfull 既为安装成功


进入 /opt/local/ggs  里面


配置MGR:

GGSCI (test) 1> edit params mgr

port 7801
dynamicportlist 7800-7810
autorestart extract *,waitminutes 2,resetminutes 5


-------------------------------------------------------------------------------------------
GGSCI (test) 2> start mgr
Manager started.


-------------------------------------------------------------------------------------------
GGSCI (test) 3> info mgr

Manager is running (IP port test.7801, Process ID 25272).


配置replicat进程组：

GGSCI (test) 4> edit param rep_1


replicat rep_1
setenv (NLS_LANG=”AMERICAN_AMERICA.UTF8”)
sourcedefs /opt/local/ggs/dirdef/defgen.prm
userid test,password test
reperror default,discard
discardfile /opt/local/ggs/dirrpt/rep_1.dsc,append,megabytes 50
dynamicresolution
map moxian.test, target test.test;

-------------------------------------------------------------------------------------------

GGSCI (test) 5> dblogin userid test,password test

GGSCI (test) 6> add checkpointtable test.chkptab

GGSCI (test) 7> add replicat rep_1,exttrail /opt/local/ggs/dirdat/e2,CHECKPOINTTABLE test.chkptab

GGSCI (test as test@orcl) 6> start mgr
Manager started.


GGSCI (test as test@orcl) 7> info mgr

Manager is running (IP port test.7801, Process ID 26106).


GGSCI (test as test@orcl) 8> start replicat rep_1

Sending START request to MANAGER ...
REPLICAT REP_1 starting






五、测试抽取: