GoldenGate + OGG Application Adpater"/>
MySQL + Oracle GoldenGate + OGG Application Adpater
最近接到一个需求,需要从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 78092.配置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过程中遇到的一些问题,特记录之。
来自 “ ITPUB博客 ” ,链接:/,如需转载,请注明出处,否则将追究法律责任。
转载于:/
更多推荐
MySQL + Oracle GoldenGate + OGG Application Adpater
发布评论