最近接到一個需求，需要從MySQL資料庫中將交易資料同步到Flat file(平面檔案），該需求的解決方案我們透過GoldenGate+OGG Application Adapter來實現。

具體配置步驟如下：
一.配置Oracle GoldenGate For MySQL
1.建立使用者
useradd oggusr
echo oracle | passwd --stdin oggusr

2.解壓軟體包
su - oggusr
unzip /opt/freeware/ggs_Linux_x64_MySQL_64bit.zip
mkdir ogg && cd ogg && tar xvf ../ggs_Linux_x64_MySQL_64bit.tar

3.執行ggsci
./ggsci

4.在ggsci中建立子目錄
GGSCI (hadoop1) 1> CREATE SUBDIRS

5.退出ggsci
GGSCI (hadoop1) 1> exit

6.配置ogg for mysql前的準備
6.1 配置extract引數指定二進位制索引檔案的路徑
TRANLOGOPTIONS ALTLOGDEST "/home/mysusr01/mysql/mylog/mysql-bin-hadoop1-39301.index"

7.配置源端
7.1 配置manager
GGSCI (hadoop1) 1> dblogin sourcedb etlanalydb1@CIT-EDC-ETLAPP1:39316, userid ggsuser,password ggsuser
GGSCI (hadoop1) 2> edit param mgr
port 7809

7.2 配置extract
GGSCI (hadoop1) 1> edit param ext1
extract ext1
setenv (MYSQL_HOME="/home/mysusr01/mysql")
TRANLOGOPTIONS ALTLOGDEST "/home/mysusr01/mysql/mylog/mysql-bin-hadoop1-39301.index"
sourcedb test@hadoop1:39301,userid root,password oracle
exttrail ./dirdat/e1
dynamicresolution
gettruncates
table test.*;

GGSCI (hadoop1) 17> add extract ext1,tranlog,begin now
GGSCI (hadoop1) 18> add exttrail ./dirdat/e1,extract ext1 ,megabytes 100

7.3 配置pump
GGSCI (hadoop1) 21> edit param pump1
extract pump1
rmthost localhost,mgrport 8809
rmttrail .dirdat/p1
passthru
gettruncates
table test.*;

GGSCI (hadoop1) 25> add extract pump1,exttrailsource ./dirdat/e1
GGSCI (hadoop1) 26> add rmttrail ./dirdat/p1,extract pump1,MEGABYTES 100

7.4 生成srcdef檔案
--編輯引數檔案
vi flatfile.prm
DEFSFILE ./dirdef/srcdef.def
sourcedb test@hadoop1:39301,userid root,password oracle
TABLE test.*;

--生成srcdef
./defgen paramfile dirprm/flatfile.prm

7.5啟動程式

GGSCI>start mgr

GGSCI>start ext1

GGSCI>start pump1

GGSCI>info all

二.配置OGG Application Adapter
1.配置mgr

GGSCI>edit param mgr

port 7809

2.配置ffwriter屬性

cp AdapterExamples/filewriter/ffwriter.properties dirprm/

vim dirprm/ffwriter.properties

#------------------------

#LOGGING OPTIONS

#------------------------

goldengate.log.logname=ffwriter

goldengate.log.level=INFO

goldengate.log.modules=LOGMALLOC

goldengate.log.level.LOGMALLOC=ERROR

goldengate.log.tostdout=false

goldengate.log.tofile=true

#------------------------

#FLAT FILE WRITER OPTIONS

#------------------------

goldengate.flatfilewriter.writers=dsvwriter

goldengate.userexit.chkptprefix=ffwriter_

#------------------------

# dsvwriter options

#------------------------

dsvwriter.mode=DSV

dsvwriter.rawchars=false

dsvwriter.includebefores=false

#dsvwriter.includecolnames=false

dsvwriter.includecolnames=true

dsvwriter.omitvalues=false

dsvwriter.diffsonly=false

dsvwriter.omitplaceholders=false

#dsvwriter.files.onepertable=false

dsvwriter.files.prefix=csv

dsvwriter.files.data.rootdir=./dirout

dsvwriter.files.data.ext=_data.dsv

dsvwriter.files.data.tmpext=_data.dsv.temp

dsvwriter.files.data.rollover.time=10

#dsvwriter.files.data.rollover.size=

dsvwriter.files.data.norecords.timeout=10

dsvwriter.files.control.use=true

dsvwriter.files.control.ext=_data.control

dsvwriter.files.control.rootdir=./dirout

dsvwriter.dsv.nullindicator.chars=

dsvwriter.dsv.fielddelim.chars=|

dsvwriter.dsv.linedelim.chars=\n

dsvwriter.dsv.quotes.chars="

dsvwriter.dsv.quotes.escaped.chars=""

#dsvwriter.metacols=position,txind,opcode,timestamp,schema,table

dsvwriter.metacols=txind,opcode,timestamp,schema,table

dsvwriter.metacols.txind.fixedlen=1

dsvwriter.metacols.txind.begin.chars=B

dsvwriter.metacols.txind.middle.chars=M

dsvwriter.metacols.txind.end.chars=E

dsvwriter.files.formatstring=pump_%s_%t_%d_%05n

#------------------------

# ldvwriter options

#------------------------

ldvwriter.mode=LDV

ldvwriter.rawchars=true

ldvwriter.includebefores=false

ldvwriter.includecolnames=false

ldvwriter.files.onepertable=false

ldvwriter.files.data.rootdir=./dirout

ldvwriter.files.data.ext=.data

ldvwriter.files.data.tmpext=.temp

ldvwriter.files.data.rollover.time=10

ldvwriter.files.data.norecords.timeout=10

ldvwriter.files.control.use=true

ldvwriter.files.control.ext=.ctrl

ldvwriter.files.control.rootdir=./dirout

ldvwriter.metacols=position,timestamp,@TOKEN-RBA,@TOKEN-POS,opcode,txind,schema,table

ldvwriter.metacols.TOKEN-RBA.fixedlen=10

ldvwriter.metacols.TOKEN-POS.fixedlen=10

ldvwriter.metacols.timestamp.fixedlen=26

ldvwriter.metacols.schema.fixedjustify=right

ldvwriter.metacols.schema.fixedpadchar.chars=Y

ldvwriter.metacols.opcode.fixedlen=1

ldvwriter.metacols.opcode.insert.chars=I

ldvwriter.metacols.opcode.update.chars=U

ldvwriter.metacols.opcode.delete.chars=D

ldvwriter.metacols.txind.fixedlen=1

ldvwriter.metacols.txind.begin.chars=B

ldvwriter.metacols.txind.middle.chars=M

ldvwriter.metacols.txind.end.chars=E

ldvwriter.metacols.txind.whole.chars=W

ldvwriter.ldv.vals.missing.chars=M

ldvwriter.ldv.vals.present.chars=P

ldvwriter.ldv.vals.null.chars=N

ldvwriter.ldv.lengths.record.mode=binary

ldvwriter.ldv.lengths.record.length=4

ldvwriter.ldv.lengths.field.mode=binary

ldvwriter.ldv.lengths.field.length=2

ldvwriter.files.rolloveronshutdown=false

ldvwriter.statistics.toreportfile=false

ldvwriter.statistics.period=onrollover

ldvwriter.statistics.tosummaryfile=true

ldvwriter.statistics.overall=true

ldvwriter.statistics.summary.fileformat=schema,table,schemaandtable,total,gctimestamp,ctimestamp

ldvwriter.statistics.summary.delimiter.chars=|

ldvwriter.statistics.summary.eol.chars=\n

ldvwriter.metacols.position.format=dec

ldvwriter.writebuffer.size=36863

3.配置ffwriter程式

GGSCI>edidt param ffwriter

extract ffwriter

sourcedefs ./dirdef/srcdef.def

CUSEREXIT ./flatfilewriter.so CUSEREXIT PASSTHRU INCLUDEUPDATEBEFORES,PARAMS "./dirprm/ffwriter.properties"

table test.*;

GGSCI>ADD EXTRACT ffwriter, EXTTRAILSOURCE dirdat/p1

4.啟動程式

GGSCI>start mgr

GGSCI>start ffwriter

注：以上配置都正常的話可以在dirout目錄下生成對應的平面檔案。

5.整個資料落地的架構圖：

補：搭建過程中踩過的深坑
坑1：Adapter端收到的remote trail檔案大小0位元組
解決方案：將ogg param中配置的檔案路徑從相對路徑改成絕對路徑問題解決，這個問題確實很奇怪，在Oracle+OGG+Adapter配置中相對路徑也是生效的，但是在MySQL+OGG+Adapter中卻遇到了這個問題。

坑2：OGG端的EXT程式無法正常讀取MySQL的二進位制檔案，錯誤資訊如下：
2016-05-27 15:15:48 ERROR OGG-00146 Oracle GoldenGate Capture for MySQL, ext1.prm: Call to VAMInitialize returned with error status 600: VAM Client Report <cause of="" failure="" :="" error="" no="" 13="" -="" confirm="" that="" the="" log="" file="" exists,="" path="" is="" correct,="" and="" correct="" permissions="" are="" set="" for="" oracle="" goldengate. Also try specifying the path to the log index file by using the TRANLOGOPTIONS parameter with the ALTLOGDEST option. - /aifs01/users/mysusr02/mysql/mylog/mysql-bin-CIT-EDC-ETLAPP1-39316.index
解決方法：二進位制索引檔案和正在讀取的二進位制檔案的other許可權必須要有rx許可權，這裡也很怪，即使我將MySQL的group條件到ogg使用者中也不行。
chmod 755 mysql-bin-CIT-EDC-ETLAPP1-39316.index
chmod 755 mysql-bin-CIT-EDC-ETLAPP1-39316.nnnnn

坑3：Adapter端ffwriter程式無法正常解析OGG端生成的source def檔案，錯誤資訊如下：
ERROR OGG-00425 Oracle GoldenGate Capture, ffwriter.prm: No DB login established to retrieve a definition for table etlanalydb1.test.
解決方案：該問題的真正原因是因為MySQL的初始化引數中lower_case_table_name設定成1（表名大小寫不敏感），在這種情況下生成的srcdef檔案目標端的ffwriter程式不識別。

以上為我在部署OGG+Adapter過程中遇到的一些問題，特記錄之。

MySQL + Oracle GoldenGate + OGG Application Adpater

相關文章