Goldengate異構 mysql——>oracl

實驗環境：
源端：
192.168.9.146
CentOS6.4 64bit
mysql5.1.66

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

同步的表：test.test2
OGG_HOME /ggs

目標端：
192.168.9.235
Redhat6.4 64bit
oracle11.2.0.4.0

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

同步的表：ggs.test2
OGG_HOME /app/goldengate

*******************************************************************************
注意：oracle goldengate for mysql 與 mysql版本問題，mysql不要使用5.0以下，5.6以上的版本。
測試發現ggs_121210_Linux_x64_MySQL_64bit.zip不支援5.1.66版本，到時會有報錯。
ERROR OGG-00146 Oracle GoldenGate Capture for MySQL, ext_te2.prm: VAM function VAMRead returned unexpected result: error 600 – VAM Client Report <CAUSE OF FAILURE : Sanity Check Failed for events
WHEN FAILED : While reading log event from binary log
WHERE FAILED : MySQLBinLog Reader Module
CONTEXT OF FAILURE : No Information Available!>.
*******************************************************************************

本次實驗使用的是如上版本。

ogg下載地址：
/> 耐心註冊個賬號既可下載。

*******************************************************************************
源端配置：
安裝mysql 略實驗採用的是rpm裝

1. mysql配置檔案設定：主要是一個，開啟binlog，並且格式為row格式，ogg只支援row格式。往配置檔案加入兩行：
[root@nagios ~]# cat /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog-format=row

檢視是否修改成功 log_bin = ON binlog-format = ROW

mysql> show variables like "%log%";
+-----------------------------------------+---------------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------------+
| back_log | 50 |
| binlog_cache_size | 33554432 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | ROW |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | /var/run/mysqld/mysqld.log |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| log | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /var/log/mysqld.log |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| max_binlog_cache_size | 536870912 |
| max_binlog_size | 536870912 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sql_log_update | ON |
| sync_binlog | 0 |
+-----------------------------------------+---------------------------------+
38 rows in set (0.01 sec)

mysql>

2. 建立測試表test.test2 使用innodb儲存引擎
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table test2 (id int,name char(10)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test2 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from test2;
Empty set (0.00 sec)

3. 安裝ogg
建立ggs目錄，並安裝ogg。
[root@nagios ~]# mkdir /ggs
[root@nagios ~]# cd /ggs/
[root@nagios ggs]# ll | grep V32399-01.zip
-rw-r--r-- 1 root root 113288880 Jul 28 14:28 V32399-01.zip
[root@nagios ggs]# unzip V32399-01.zip
[root@nagios ggs]# tar -xvf ggs_Linux_x64_MySQL_64bit.tar
[root@nagios ggs]# ./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 (nagios) 1>
GGSCI (nagios) 1> create subdirs
Creating subdirectories under current directory /u01/ogg_ms
Parameter files /u01/ogg_ms/dirprm: already exists
Report files /u01/ogg_ms/dirrpt: created
Checkpoint files /u01/ogg_ms/dirchk: created
Process status files /u01/ogg_ms/dirpcs: created
SQL script files /u01/ogg_ms/dirsql: created
Database definitions files /u01/ogg_ms/dirdef: created
Extract data files /u01/ogg_ms/dirdat: created
Temporary files /u01/ogg_ms/dirtmp: created
Stdout files /u01/ogg_ms/dirout: created

4. 編輯.bash_profile, 加入如下三行
[root@nagios ~]# vi .bash_profile
export GGHOME=/ggs
export PATH=$PATH:$GGHOME
export MYSQL_UNIX_PORT=/var/lib/mysql/mysql.sock

生效檔案
source .bash_profile

5. 建立mgr程式。
GGSCI (nagios) 1> view params mgr

port 7809
dynamicportlist 7800-8000
autorestart extract *,waitminutes 2,resetminutes 5

啟動mgr程式
GGSCI (nagios) 2> start mgr

6. 建立抽取extract程式 ext_1
GGSCI (nagios) 2> view params ext_1

extract ext_1
setenv (MYSQL_HOME="/var/lib/mysql")
tranlogoptions altlogdest /var/lib/mysql/mysql-bin.index
sourcedb test@localhost:3306,userid root,password 123456
exttrail ./dirdat/e1
dynamicresolution
gettruncates
table test.test2;

新增：
GGSCI (nagios) 3> add extract ext_1,tranlog,begin now
EXTRACT added.
GGSCI (nagios) 3> add exttrail ./dirdat/e1,extract ext_1
EXTTRAIL added.

7. 建立傳輸程式pump_1
GGSCI (nagios) 3> view params pump_1

extract pump_1
rmthost 192.168.9.235,mgrport 7809
rmttrail /app/goldengate/dirdat/e1
passthru
gettruncates
table test.test2;

新增：
GGSCI (nagios) 4> add extract pump_1,exttrailsource ./dirdat/e1
EXTRACT added.
GGSCI (nagios) 4> add rmttrail /app/goldengate/dirdat/e1,extract pump_1
RMTTRAIL added.

/app/goldengate/ 目標端OGG-HOME

8. 異構平臺配置defgen：
GGSCI (nagios) 4>edit params defgen
defsfile /ggs/dirdef/defgen.prm
sourcedb test@localhost:3306, userid root,password 123456
table test.test2;

生成：
[root@nagios ggs]#./defgen paramfile dirprm/defgen.prm

傳送defgen檔案到目標端：
scp /ggs/dirdef/defgen.pm oracle@192.168.9.235:/app/goldengate/dirdef

9. 啟動抽取程式與傳輸程式
GGSCI (nagios) 2> start ext_1
GGSCI (nagios) 2> start pump_1

GGSCI (nagios) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING EXT_1 00:00:00 00:00:09
EXTRACT RUNNING PUMP_1 00:00:00 00:00:10

啟動的過程可能會報錯，檢視日誌報錯資訊，逐步分析處理！！

*******************************************************************************
目標端配置：
1. 配置oracle的環境變數
[oracle@testdb1 ~]:testdb1> cat prof_testdb1
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/ora11g
export GRID_BASE=/app/grid
export GRID_HOME=$GRID_BASE/grid11g
export ORACLE_SID=testdb1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GRID_HOME/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/OPatch
export EDITOR=vi
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export OGG_HOME=/app/goldengate
export LD_LIBRARY_PATH=$OGG_HOME:$LD_LIBRARY_PATH
export PATH=$OGG_HOME:$PATH
export PS1="[\u@\h \W]:$ORACLE_SID> "
umask 022

2. 建立ggs使用者，以及表test2
SQL> create user ggs identified by 123456 ;
SQL> grant dba to ggs;
SQL> conn ggs/123456
SQL> create table test2 (id int,name varchar2(10));

3. 安裝ogg，過程略，解壓一下即可。
ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

4. 配置mgr程式

GGSCI (testdb1) 1> view params mgr

port 7809
dynamicportlist 7800-8000
autorestart extract *,waitminutes 2,resetminutes 5

GGSCI (testdb1) 2> start mgr

5. 配置恢復程式
GGSCI (testdb1) 5> view params rep_1

replicat rep_1
sourcedefs /app/goldengate/dirdef/defgen.prm
userid ggs,password 123456
reperror default,discard
discardfile /app/goldengate/dirrpt/rep_1.dsc,append,megabytes 50
dynamicresolution
map test.test2, target ggs.test2;

新增：
GGSCI (testdb1) 6> add replicat rep_1,exttrail /app/goldengate/dirdat/e1

啟動程式:
GGSCI (testdb1) 6> start rep_1

6. 檢視程式，
GGSCI (testdb1) 6> info all

啟動也可能失敗，檢視日誌，分析錯誤！

錯誤集合：
1 ERROR OGG-00146 Oracle GoldenGate Capture for MySQL, ext_1.prm: VAM function VAMInitialize returned unexpected result: error 600 - VAM Client Report <CAUSE OF FAILURE : Position time is prior then earliest time available in the log : Earliest time available in the log is 2015-07-28 14:54:34#012WHEN FAILED : SetInitialPosition by time stamp#012WHERE FAILED : MySQLBinLog Reader Module#012CONTEXT OF FAILURE : No Information Available!>.

解決辦法：字面上理解，建立的時間提前了，所以先刪除。再新增

GGSCI (nagios) 29> delete extract ext_1

GGSCI (nagios) 32> add extract ext_1,tranlog,begin now
EXTRACT added.
GGSCI (nagios) 33> add exttrail ./dirdat/e2,extract ext_1
EXTTRAIL added.

會有另外報錯：
ERROR OGG-01044 Oracle GoldenGate Capture for MySQL, ext_1.prm: The trail './dirdat/e1' is not assigned to extract 'EXT_1'. Assign the trail to the extract with the command "ADD EXTTRAIL/RMTTRAIL ./dirdat/e1, EXTRACT EXT_1"

解決辦法已提示：

GGSCI (nagios) 36> ADD EXTTRAIL ./dirdat/e1, EXTRACT EXT_1
EXTTRAIL added.

啟動抽取程式成功！！！

GGSCI (nagios) 37> start ext_1

Sending START request to MANAGER ...
EXTRACT EXT_1 starting

GGSCI (nagios) 38> info all

Program Status Group Lag at Chkpt Time Since Chkpt

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

GGSCI (nagios) 39>

2 extract[32284]: segfault at 30 ip 00000000009341d7 sp 00007fcac7b0b900 error 4 in extract[400000+70b000]
執行一段時間後，抽取程式出現abended，檢視日誌報錯如上

解決辦法：

3. ERROR OGG-00146 Oracle GoldenGate Capture for MySQL, ext_te2.prm: VAM function VAMRead returned unexpected result: error 600 – VAM Client Report <CAUSE OF FAILURE : Sanity Check Failed for events
WHEN FAILED : While reading log event from binary log
WHERE FAILED : MySQLBinLog Reader Module
CONTEXT OF FAILURE : No Information Available!>.

解決辦法：
該提示，一般都是版本不對，檢查一下版本問題

4. 執行成功一段時間後，ext_1程式出現abended情況，日誌暫無報錯內容
INFO OGG-01053 Oracle GoldenGate Capture for MySQL, ext_1.prm: Recovery completed for target file ./dirdat/e1000245, at RBA 964.

感覺是一直在做recover，後面會自動恢復，但是資料已同步不過去了！

Goldengate異構 mysql——>oracl

相關文章