Oracle GoldenGate 異構平臺同步(Mysql到Oracle)

丶小炒肉發表於2016-06-01

一、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

 

相關文章