OGG
一、常用命令(参考文档 https://www.cnblogs.com/lhrbest/p/7112896.html)
(安装rlwrap 使ggsci支持上下键查看历史语句)
登录:dblogin userid ogg_admin,password ogg_admin
查看进程 1、info all //显示所有进程整体情况(lag:进程延时,status.ABENDED进程非正常关闭) 2、info <进程名> //查看进程运行情况 3、info <进程名> showch //查看进程checkpoint详细信息 4、info <进程名> detail //查看trail文件,参数文件、报告文件、警告日志的位置等 5、stats <进程名>,<时间>,table tablename//查看进程处理的记录数 stats extractName,total //查看进程启动之后一共处理的记录数 stats extractName,daily,table tableName //查看当天这个表处理的记录数 6、view report <进程名> //获取错误信息
启停进程 1、start <进程名> //启动进程 2、start er * //启动所有extract 、replicate进程 3、start extract *t* //启动含t的extract进程 4、start replicate re* //启动re开头的replicate进程 5、stop er * //停止所有extract、replicate进
edit param mgr 1、purgeoldextracts /<goldengate安装目录>/dirdat/*, usecheckpoint, minkeepdays 7 //自动删除队列 保留7天
2、AUTOSTART ER *//启动manager时自动重启extract、replicate 3、AUTORESTART ER *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60//自动重新启动er,五分钟重试一次重试3次,每六十分钟清零
edit param ext1
1、BR BRINTERVAL 2H//每隔两小时把长事务保存磁盘(事务开启时extract捕捉事务,并扫描redolog存到内存中,停止extract时若长事务未提交且未开启BR,
重启进程时需保证源端仍存在此事务的redolog、archive log)
2、send extract ext1 , showtrans thread 1 count 10//查看节点1上最长的10个长事务
二、OGG->Kafka 源端配置(https://blog.csdn.net/dkl12/article/details/80447154)
1.edit param mgr
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
PORT 7839 DYNAMICPORTLIST 7840-7914 userid ogg_admin,password ogg_admin AUTOSTART ER * AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3 PURGEOLDEXTRACTS /OraData/ogg_tst/dirdat/*, usecheckpoints, minkeepdays 3 PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10 PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10 LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45
PORT 7839 #mgr进程使用的port口
DYNAMICPORTLIST 7840-7914 #若port口不可用,尝试port口列表
userid ogg_admin,password ogg_admin #用户名和密码
AUTOSTART ER * #启动mgr时自动启动所有replicate、extract进程
AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3 #自动重启
PURGEOLDEXTRACTS /OraData/ogg_tst/dirdat/*, usecheckpoints, minkeepdays 3 #trail文件保存三天
2.add trandata mdwadm.dwr_pnl
update、delete时,设置redo log记录主键,否则replicate进程update、delete报找不到主键错。源表应该设置主键,否则add trandata失败。
3.添加extract进程
edit param extTest
add extract extTest,tranlog,begin now
add exttrail /OraData/ogg_tst/dirdat/e1,extract extTes
//修改extract开始时间
alter extract exttest begin now
alter extract pmptest begin 2019-04-26 16:50
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
edit param extTest extract extTest SETENV (ORACLE_SID = "MDWYMSDB") SETENV (NLS_LANG = "american_america.AL32UTF8") userid ogg_admin, password ogg_admin exttrail /OraData/ogg_tst/dirdat/e1 TranlogOptions DBLOGREADER GETTRUNCATES TRANLOGOPTIONS DBLOGREADER GETUPDATEBEFORES NOCOMPRESSDELETES table mdwadm.dwr_pnl;
SETENV (NLS_LANG = "american_america.AL32UTF8") #设置字符集
exttrail /OraData/ogg_tst/dirdat/e1 #trail文件位置及文件名,文件名只能两位
table mdwadm.dwr_pnl; #表名,支持*匹配所有表
4.edit param pmpTest
add extract pmpTest,exttrailsource /OraData/ogg_tst/dirdat/e1
add rmttrail /data05/ogg/dirdat/e1,extract pmpTest
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
extract pmpTest passthru userid ogg_admin,password ogg_admin rmthost 10.79.2.62, mgrport 7839 rmttrail /data05/ogg/dirdat/e1 table mdwadm.dwr_pnl;
passthru #禁止ogg与oracle交互
rmthost 10.79.2.62, mgrport 7839 #目标端mgr进程ip 端口
rmttrail /data05/ogg/dirdat/e1 #目标端trail文件位置
三、ogg to kafka目标端
1.edit param mgr
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
PORT 7839 DYNAMICPORTLIST 7840-7914 AUTOSTART ER * AUTORESTART ER *, RETRIES 5, WAITMINUTES 3 PURGEOLDEXTRACTS /data05/ogg/dirdat/*, usecheckpoints, minkeepdays 3
2.edit param tstrep
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
REPLICAT tstrep sourcedefs /data05/ogg/dirdef/mdwadm.dwr_pnl TARGETDB LIBFILE libggjava.so SET property=dirprm/dwr_pnl.props REPORTCOUNT EVERY 1 MINUTES, RATE GROUPTRANSOPS 10000 GETUPDATEBEFORES MAP mdwadm.dwr_pnl TARGET mdwadm.dwr_pnl;
//已弃用 文件格式没有schema信息 gg.handler.kafkahandler.format=avro_op gg.handler.kafkahandler.format=delimitedtext
3.编辑kafka配置文件vim dwr_pnl.props
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
gg.handlerlist = kafkahandler gg.handler.kafkahandler.type=kafka gg.handler.kafkahandler.KafkaProducerConfigFile=custom_kafka_producer.properties gg.handler.kafkahandler.TopicName =dwr_pnl gg.handler.kafkahandler.format=json gg.handler.kafkahandler.BlockingSend=false gg.handler.kafkahandler.includeTokens=false gg.handler.kafkahandler.mode=op gg.handler.kafkahandler.maxGroupSize =100,1Mb gg.handler.kafkahandler.minGroupSize =50,500Kb goldengate.userexit.timestamp=utc goldengate.userexit.writers=javawriter javawriter.stats.display=TRUE javawriter.stats.full=TRUE gg.log=log4j gg.log.level=INFO gg.report.time=30sec gg.contentreplaceregex=(?<=[0-9]{4}([-/][0-9]{2}){2}): gg.contentreplacestring=CDATA[ ] gg.classpath=dirprm/:/opt/cloudera/parcels/KAFKA/lib/kafka/libs/*:/data05/ogg/:/data05/ogg/lib/* javawriter.bootoptions=-Xmx512m -Xms32m -Djava.class.path=ggjava/ggjava.jar
gg.handler.kafkahandler.SchemaTopicName # 当format为avro时,需配置SchemaTopicName
gg.handler.kafkahandler.BlockingSend #当值为true时,表示同步更新,下一个消息发送需要等到写入到目标topic中,且确认已经收到才发下一条消息。为false为异步更新,将一次性发给目标topic
gg.handler.kafkahandler.topicPartitioning#有两种参数值none | table,控制是否已发布到kafka的数据应按表分区。设置为表,不同表的数据被写入到不同的kafka主题。设置为None,来自不同表的数据交织在同一话题
gg.handler.kafkahandler.mode #当值为tx时,表示源端一次事务内的操作在kafka上作为一个record;值为op表示一次SQL传输一次
4.编辑生产者配置文件 vim custom_kafka_producer.properties
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
bootstrap.servers=10.79.2.62:9092,10.79.2.63:9092,10.79.2.64:9092
acks=1
#compression.type=gzip
reconnect.backoff.ms=1000
value.serializer=org.apache.kafka.common.serialization.ByteArraySerializer
key.serializer=org.apache.kafka.common.serialization.ByteArraySerializer
#100KB per partition
batch.size=102400
linger.ms=10000
5.添加replicat
add replicat tstrep exttrail /data05/ogg/dirdat/e1
四、问题小结
1.源表表结构修改,需重启extract进程,因extract会把表结构缓存在内存中。
2.可使用OGG自带工具 logdump工具查看trail文件。
//ogg进程出错,跳过错误记录 GGSCI (informatica2) 3> info rep mdlrep1 //查看replicate进程当前RBA
REPLICAT MDLREP1 Last Started 2019-04-18 14:35 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:02 ago) Process ID 40861 Log Read Checkpoint File /cdcdata/ogg_target/dirdat/md000000236 2019-06-17 02:38:40.004892 RBA 72050044
Logdump 1 >open /cdcdata/ogg_target/dirdat/md000000236//logdump打开trail文件 Logdump 2 >pos 72050044//position定位到该RBA Logdump 3 >n //next 跳转到下个RBA 得到下个RBA GGSCI (informatica2) 4> alter replicate mdlrep1,extrba 72050044 GGSCI (informatica2) 4> start mdlrep1
3.新增表同步
//1.在源端extract、pump进程,目标端replicate 进程param文件新增同步表 //2.停止extract、pump、replicate //3.查看extract进程scn GGSCI (fabdb2) 2> info ext1 //4.根据scn导出新增表数据到目标端 expdp ogg_admin/ogg_admin FLASHBACK_SCN=13329097888889 tables=fabadm.BSMATERIALEMPTYREPORT DIRECTORY=ogg_exp \ dumpfile=BSMATERIALEMPTYREPORT.dmp logfile=BSMATERIALEMPTYREPORT.log \ QUERY=\"WHERE timekey between \'20190201\' and \'20190213\' \" impdp \'/ as sysdba \' directory=OGG_EXP dumpfile=BSMATERIALEMPTYREPORT.dmp logfile=BSMATERIALEMPTYREPORT.log \ cluster=no remap_schema=fabadm:ogg_fab_receiver TABLE_EXISTS_ACTION='REPLACE' //5.重启extract、pump、replicate
仅作为笔记使用,记录信息过于简洁。