一、OGG安裝配置(源端)
1、OGG下載
http://www.oracle.com/technetwork/cn/middleware/goldengate/downloads/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