Oracle GoldenGate 之--异构平台同步（Mysql到Oracle）
Oracle GoldenGate 異構(gòu)平臺(tái)同步（Mysql到Oracle）
如圖所示：源端采用Mysql庫(kù)，目標(biāo)端采用Oracle庫(kù)
一、OGG安裝配置（源端）
1、OGG下載
https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=14841438
https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=14841440
[oracle@ogg ogg_ms]$ uname -a
Linux ogg 2.6.32-71.el6.i686 #1 SMP Wed Sep 1 01:26:34 EDT 2010 i686 i686 i386 GNU/Linux
[oracle@ogg?~]$?cd?/u01/ogg_ms/ [oracle@ogg?ogg_ms]$?ls ggs_Linux_x86_MySQL_32bit.tar [oracle@ogg?ogg_ms]$?tar?xvf?ggs_Linux_x86_MySQL_32bit.tar[oracle@ogg?ogg_ms]$?./ggsci Oracle?GoldenGate?Command?Interpreter?for?MySQL Version?11.2.1.0.1?OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux,?x86,?32bit?(optimized),?MySQL?Enterprise?on?Apr?23?2012?04:29:30 Copyright?(C)?1995,?2012,?Oracle?and/or?its?affiliates.?All?rights?reserved. GGSCI?(ogg)?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
二、數(shù)據(jù)庫(kù)配置
源端：mysql庫(kù)配置
數(shù)據(jù)庫(kù)配置文件： [root@ogg?~]#?cat?/etc/my.cnf #?Replication?Master?Server?(default) #?binary?logging?is?required?for?replication log-bin=mysql-bin#?binary?logging?format?-?mixed?recommended #binlog_format=mixedbinlog_format=row啟動(dòng)數(shù)據(jù)庫(kù)服務(wù) [root@ogg?~]#?service?mysql?start Starting?MySQL??連接數(shù)據(jù)庫(kù)???????????????????????????????????????????[??OK??] [root@ogg?~]#?mysql?-h?localhost?-u?mysql?-p Enter?password: Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?\g. Your?MySQL?connection?id?is?1 Server?version:?5.6.4-m7-log?Source?distribution Copyright?(c)?2000,?2010,?Oracle?and/or?its?affiliates.?All?rights?reserved. This?software?comes?with?ABSOLUTELY?NO?WARRANTY.?This?is?free?software, and?you?are?welcome?to?modify?and?redistribute?it?under?the?GPL?v2?license Type?'help;'?or?'\h'?for?help.?Type?'\c'?to?clear?the?current?input?statement. mysql>?show?databases; +--------------------+ |?Database???????????| +--------------------+ |?information_schema?| |?mysql??????????????| |?performance_schema?| |?test???????????????| +--------------------+ 4?rows?in?set?(0.00?sec) mysql>?use?test; Database?changed mysql>?show?tables; Empty?set?(0.00?sec)創(chuàng)建測(cè)試表（存儲(chǔ)引擎采用innodb） 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)目標(biāo)端：Oracle 庫(kù)配置：
[java]?view plaincopyprint?三、OGG同步配置
源端：Mysql庫(kù)配置
[oracle@ogg ogg_ms]$ ./ggsci
Oracle?GoldenGate?Command?Interpreter?for?MySQL Version?11.2.1.0.1?OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Linux,?x86,?32bit?(optimized),?MySQL?Enterprise?on?Apr?23?2012?04:29:30 Copyright?(C)?1995,?2012,?Oracle?and/or?its?affiliates.?All?rights?reserved. 用戶登錄（root）： GGSCI?(ogg)?2>?dblogin?sourcedb?test@localhost:3306,userid?root,password?oracle Successfully?logged?into?database.配置mgr： GGSCI?(ogg)?3>?edit?param?mgr port?7809 dynamicportlist?7800-8000 autorestart?extract?*,waitminutes?2,resetminutes?5GGSCI?(ogg)?4>?start?mgr Manager?started. GGSCI?(ogg)?5>?info?mgr Manager?is?running?(IP?port?ogg.7809).GGSCI?(ogg)?6>?info?all Program?????Status??????Group???????Lag?at?Chkpt??Time?Since?Chkpt MANAGER?????RUNNING配置extract進(jìn)程組： GGSCI?(ogg)?7>?edit?param?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?oracle exttrail?./dirdat/e2 dynamicresolution gettruncates table?test.test2;GGSCI?(ogg)?11>?add?extract?ext_1,tranlog,begin?now EXTRACT?added. GGSCI?(ogg)?12>?add?exttrail?./dirdat/e2,extract?ext_1 EXTTRAIL?added.配置pump進(jìn)程組： GGSCI?(ogg)?13>?edit?params?pump_1 extract?pump_1 rmthost?192.168.8.249,mgrport?7809 rmttrail?/u01/ogg/dirdat/e2 passthru gettruncates table?test.test2;GGSCI?(ogg)?14>?add?extract?pump_1,exttrailsource?./dirdat/e2 EXTRACT?added.GGSCI?(ogg)?15>?add?rmttrail??/u01/ogg/dirdat/e2,extract?pump_1 RMTTRAIL?added.異構(gòu)平臺(tái)配置defgen：GGSCI?(ogg)?16>?edit?params?defgen defsfile?/u01/ogg_ms/dirdef/defgen.prm sourcedb?test@localhost:3306,?userid?root,password?oracle table?test.test2;[oracle@ogg?ogg_ms]$?./defgen?paramfile?dirprm/defgen.prm ***********************************************************************Oracle?GoldenGate?Table?Definition?Generator?for?MySQLVersion?11.2.1.0.1?OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230Linux,?x86,?32bit?(optimized),?MySQL?Enterprise?on?Apr?23?2012?04:59:19 Copyright?(C)?1995,?2012,?Oracle?and/or?its?affiliates.?All?rights?reserved.Starting?at?2014-09-26?16:01:05 *********************************************************************** Operating?System?Version: Linux Version?#1?SMP?Wed?Sep?1?01:26:34?EDT?2010,?Release?2.6.32-71.el6.i686 Node:?ogg Machine:?i686soft?limit???hard?limit Address?Space?Size???:????unlimited????unlimited Heap?Size????????????:????unlimited????unlimited File?Size????????????:????unlimited????unlimited CPU?Time?????????????:????unlimited????unlimited Process?id:?2606 *********************************************************************** **????????????Running?with?the?following?parameters??????????????????** *********************************************************************** defsfile?/u01/ogg_ms/dirdef/defgen.prm sourcedb?test@localhost:3306,?userid?root,password?****** table?test.test2; Retrieving?definition?for?test.test2 Definitions?generated?for?1?table?in?/u01/ogg_ms/dirdef/defgen.prm傳送defgen文件到目標(biāo)端： [oracle@ogg?ogg_ms]$?scp?dirdef/defgen.prm?rh6:/u01/ogg/dirdef GGSCI?(ogg)?5>?info?all Program?????Status??????Group???????Lag?at?Chkpt??Time?Since?Chkpt MANAGER?????RUNNING EXTRACT?????ABENDED?????EXT_1???????00:00:00??????00:21:03 EXTRACT?????RUNNING?????PUMP_1??????00:00:00??????00:00:06啟動(dòng)extract和pump進(jìn)程： GGSCI?(ogg)?6>?start?extract?ext_1 Sending?START?request?to?MANAGER?... EXTRACT?EXT_1?starting GGSCI?(ogg)?7>?info?all Program?????Status??????Group???????Lag?at?Chkpt??Time?Since?Chkpt MANAGER?????RUNNING EXTRACT?????ABENDED?????EXT_1???????00:00:00??????00:21:17 EXTRACT?????RUNNING?????PUMP_1??????00:00:00??????00:00:09 extract進(jìn)程啟動(dòng)異常（abended）！
查看日志：
GGSCI (ogg) 36> view ggsevt
2014-09-26 17:24:56 ?INFO ? ?OGG-01053 ?Oracle GoldenGate Capture for MySQL, ext_1.prm: ?Recovery completed for target file ./dirdat/e20
00026, at RBA 961.
2014-09-26 17:24:56 ?INFO ? ?OGG-01057 ?Oracle GoldenGate Capture for MySQL, ext_1.prm: ?Recovery completed for all targets.
2014-09-26 17:24:56 ?INFO ? ?OGG-00182 ?Oracle GoldenGate Capture for MySQL, ext_1.prm: ?VAM API running in single-threaded mode.
2014-09-26 17:24:56 ?INFO ? ?OGG-01515 ?Oracle GoldenGate Capture for MySQL, ext_1.prm: ?Positioning to begin time Sep 26, 2014 3:52:01
PM.
2014-09-26 17:24:56 ?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 : ERROR NO 13 - Failed to access index file : Check File PATH/EXISTENCE/PERMISSI
ONS - /var/lib/mysql/mysql-bin.index
WHEN FAILED : While initializing binary log configuration
WHERE FAILED : MySQLBinLog Reader Module
CONTEXT OF FAILURE : No Information Available!>.
2014-09-26 17:24:56 ?ERROR ? OGG-01668 ?Oracle GoldenGate Capture for MySQL, ext_1.prm: ?PROCESS ABENDING.
附錄：解決方法（參考網(wǎng)絡(luò)文檔）
GoldenGate的官方文檔明確表示，GoldenGate需要將MySQL的日志格式（binlog_format）設(shè)置為ROW，其他兩種格式（MIXED or STATEMENT）是不支持。
########################################################################官方描述如下：
binlog_format: This parameter sets the format of the logs. It must be set to the value of ROW, which directs the database to log DML statements in binary format. Any other log format (MIXED or STATEMENT) causes Extract to abend.
########################################################################
但是MySQL在版本5.1.5之前是不支持ROW和MIXED格式（MySQL 5.1.5引入ROW，5.1.8引入MIXED）
#### ?官方描述如下：http://dev.mysql.com/doc/refman/5.1/en/binary-log-formats.html
Support for row-based logging was added in MySQL 5.1.5. Mixed logging is available beginning with MySQL 5.1.8. In MySQL 5.1.12,MIXED?become the default logging mode; in 5.1.29, the default was changed back to?STATEMENT?for compatibility with MySQL 5.0.
########################################################################
目前客戶的MySQL版本是5.0.6-beta版：
D:\MySQL Server 5.0\bin>mysql -u root -p
Enter password: *****
Welcome to the MySQL monitor.? Commands end with ; or \g.
Your MySQL connection id is 111 to server version: 5.0.6-beta-nt-log
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the buffer.
mysql> select version();
+-------------------+
| version()???????? |
+-------------------+
|?5.0.6-beta-nt-log|
+-------------------+
1 row in set (0.02 sec)
這個(gè)版本是不支持binlog_format=ROW，所以安裝好GolenGate后，啟動(dòng)抽取進(jìn)程時(shí)，進(jìn)程abend
########################################################################下面是出錯(cuò)信息：
2014-09-16 16:55:16? ERROR?? OGG-00146? VAM function VAMRead returned unexpected result: error 600 - VAM Client Report <CAUSE OF FAILURE : STATEMENT or MIXED level logging found so abending
WHEN FAILED : While reading log event from binary log
WHERE FAILED : MySQLBinLog Reader Module
CONTEXT OF FAILURE : No Information Available!>
因此如果想要采用GoldenGate來(lái)實(shí)現(xiàn)MySQL的數(shù)據(jù)實(shí)時(shí)同步，需要將MySQL版本升級(jí)到5.1.5以上.
四、目標(biāo)端ogg配置
配置MGR GGSCI?(rh6.cuug.net)?4>?edit?params?mgr port?7809 dynamicportlist?7800-8000 autorestart?extract?*,waitminutes?2,resetminutes?5 ~ GGSCI?(rh6.cuug.net)?5>?start?mgr Manager?started. GGSCI?(rh6.cuug.net)?6>?info?mgr Manager?is?running?(IP?port?rh6.cuug.net.7809).配置replicat進(jìn)程組： [oracle@rh6?ogg]$?./ggsci Oracle?GoldenGate?Command?Interpreter?for?Oracle Version?11.2.1.0.1?OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Linux,?x86,?32bit?(optimized),?Oracle?11g?on?Apr?23?2012?08:09:25 Copyright?(C)?1995,?2012,?Oracle?and/or?its?affiliates.?All?rights?reserved. GGSCI?(rh6.cuug.net)?1> GGSCI?(rh6.cuug.net)?1>?edit?param?rep_1 replicat?rep_1 sourcedefs?/u01/ogg/dirdef/defgen.prm userid?ogg,password?ogg reperror?default,discard discardfile?/u01/ogg/dirrpt/rep_1.dsc,append,megabytes?50 dynamicresolution map?test.test2,?target?test.test2;GGSCI?(rh6.cuug.net)?1>??add?replicat?rep_1,exttrail?/u01/ogg/dirdat/e2 REPLICAT?added.啟動(dòng)mgr和replicat進(jìn)程： GGSCI?(rh6.cuug.net)?3>?start?mgr Manager?started. GGSCI?(rh6.cuug.net)?4>?info?all Program?????Status??????Group???????Lag?at?Chkpt??Time?Since?Chkpt MANAGER?????RUNNING REPLICAT????STOPPED?????REP_1???????00:00:00??????00:00:34 REPLICAT????ABENDED?????RORA_1??????00:00:00??????383:08:47GGSCI?(rh6.cuug.net)?5>?start?replicat?rep_1 Sending?START?request?to?MANAGER?... REPLICAT?REP_1?starting GGSCI?(rh6.cuug.net)?6>?info?all Program?????Status??????Group???????Lag?at?Chkpt??Time?Since?Chkpt MANAGER?????RUNNING REPLICAT????RUNNING?????REP_1???????00:00:00??????00:00:00?
? ?由于源端extract進(jìn)程異常，數(shù)據(jù)不能同步，問(wèn)題未解決，歡迎指導(dǎo)，待續(xù)...
問(wèn)題解決：
更換mysql-5.5.12的版本，目前Golden Gate 版本不支持mysql-5.6
GGSCI (ogg) 1> edit param ext_1
配置/etc/my.cnf文件：
[java]?view plaincopyprint?并編輯extract進(jìn)程組配置文件
GGSCI (ogg) 1> edit param 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 oracle
exttrail ./dirdat/e2
dynamicresolution
gettruncates
table test.test2;
啟動(dòng)extract 進(jìn)程
[java]?view plaincopyprint?仍然失敗，以下為ogg日志：
[root@ogg ogg_ms]# tail ggserr.log
[java]?view plaincopyprint?
重新刪除extract進(jìn)程組，重新添加
[java]?view plaincopyprint?extract進(jìn)程，啟動(dòng)成功！
查看日志：
-bash-4.1$ tail? ggserr.log
[java]?view plaincopyprint?總結(jié)
以上是生活随笔為你收集整理的Oracle GoldenGate 之--异构平台同步（Mysql到Oracle）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： NFS技术实现图片共享存储
- 下一篇： Nginx和lvs在负载均衡方面的对比