ogg改变表结构和新增表操作

修改表结构

修改表结构定义文件

1、[oracle端]切换oracle系统用户下

[oracle@vpc-oracle-01 ogg]$ su – oracle

2、[oracle端]修改表结构定义文件（由于配置table QHTX_DW.*;table QHTX.* 不需要操作）

[oracle@vpc-oracle-01 ogg]$ cd /u01/app/ogg/dirprm/
[oracle@vpc-oracle-01 ogg]$ vim htdefine.prm
defsfile /u01/app/ogg/dirdef/htdefine
userid ogg,password ogg
table QHTX_DW.*;

3、[oracle端]生成表结构文件并传到[kafka端]

[oracle@vpc-oracle-01 ogg]$ cd /u01/app/ogg/
[oracle@vpc-oracle-01 ogg]$ rm -rf /u01/app/ogg/dirdef/htdefine
[oracle@vpc-oracle-01 ogg]$ ./defgen paramfile ./dirprm/htdefine.prm
[oracle@vpc-oracle-01 ogg]$ scp /u01/app/ogg/dirdef/htdefine root@10.4.101.236:/usr/local/ogg/dirdef

4、[oracle端]重启oracle进程

GGSCI (qhtxdb) 4> stop *

Sending STOP request to EXTRACT EXTKAFKA ...
Request processed.

Sending STOP request to EXTRACT PUKAFKA ...
Request processed.

GGSCI (qhtxdb) 6> start *

Sending START request to MANAGER ...
EXTRACT EXTKAFKA starting

Sending START request to MANAGER ...
EXTRACT PUKAFKA starting


GGSCI (qhtxdb) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXTKAFKA    00:00:00      00:00:02    
EXTRACT     RUNNING     PUKAFKA     00:00:00      00:00:23

 5、[kafka端]重启kafka端，并标记监听起始点 

stop REKAFKA2
ALTER REPLICAT REKAFKA2 BEGIN 2022-12-22 09:00 
或者
alter replicat REKAFKA2,begin now
start REKAFKA2

新增表

切换用户，进入到ogg命令行

[oracle@vpc-oracle-01 ogg]$ su – oracle
[oracle@vpc-oracle-01 ogg]$ ./ggsci

查看进程状态是否正常

GGSCI (localhost.localdomain) 1> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXTKAFKA    00:00:00      00:00:00
EXTRACT     RUNNING     PUKAFKA     00:00:00      00:00:04

数据库添加表附加日志

GGSCI (localhost.localdomain) 3> dblogin userid ogg, password ggsadmin123
GGSCI (localhost.localdomain as ogg@DCDBTEST) 4> add trandata QHTX_DW.TP_SF_HOLD

修改EXT配置

GGSCI (localhost.localdomain as ogg@DCDBTEST) 6> edit param EXTKAFKA

  添加：TABLE QHTX_DW.TP_SF_HOLD;

 修改PUMP配置

GGSCI (localhost.localdomain as ogg@DCDBTEST) 6> edit param PUKAFKA

  添加：TABLE QHTX_DW.TP_SF_HOLD; 

目标端进行配置

1、查看ogg状态

[root@vpc-kafka-01 ogg]# cd /usr/local/ogg/
[root@vpc-kafka-01 ogg]# ./ggsci
GGSCI (localhost.localdomain) 1> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     REKAFKA     00:00:00      00:00:00

2、新增topic，由于新增topic需要添加新的REPLICAT进程为 REKAFKA2

GGSCI (localhost.localdomain) 4> edit param REKAFKA2
REPLICAT rekafka2
TARGETDB LIBFILE libggjava.so SET property=/usr/local/ogg/dirprm/htf_damp_dimensionTopic.props
sourcedefs /usr/local/ogg/dirdef/htdefine OVERRIDE
REPORTCOUNT EVERY 1 MINUTES, RATE
HANDLECOLLISIONS
GROUPTRANSOPS 10000
MAP QHTX_DW.TP_SF_HOLD,target QHTX_DW.TP_SF_HOLD;

参数解释：

REPLICATE：rekafka定义rep进程名称，现在新增的为REKAFKA2

TARGETDB LIBFILE：修改为对应的topic文件名，后面会创建该文件

最后一行改为：topic对应监听表名

GGSCI (localhost.localdomain) 6> add replicat rekafka2 exttrail /usr/local/ogg/dirdat/hr,checkpointtable ogg.checkpointtab

3、退出ogg命令行，创建topic文件

[root@localhost ogg]# cd /usr/local/ogg/dirprm/
[root@localhost dirprm]# cp htf_damp_rsync_topic.props htf_damp_dimensionTopic.props

修改新topic文件内容htf_damp_dimensionTopic.props

[root@qhtx_kafka_001 dirprm]# cat /usr/local/ogg/dirprm/OGG_T_DAMP_DW_LINK_DATA.props
gg.handlerlist = kafkahandler
gg.handler.kafkahandler.type=kafka
gg.handler.kafkahandler.KafkaProducerConfigFile=custom_kafka_producer.properties
#The following resolves the topic name using the short table name
gg.handler.kafkahandler.topicMappingTemplate=htf_damp_dimensionTopic
#The following selects the message key using the concatenated primary keys
#gg.handler.kafkahandler.keyMappingTemplate=${primaryKeys}
gg.handler.kafkahandler.keyMappingTemplate=${currentTimestamp}
#gg.handler.kafkahandler.format=avro_op
gg.handler.kafkahandler.format=json

gg.handler.kafkahandler.format.insertOpKey=I
gg.handler.kafkahandler.format.updateOpKey=U
gg.handler.kafkahandler.format.deleteOpKey=D
gg.handler.kafkahandler.format.truncateOpKey=T
gg.handler.kafkahandler.format.prettyPrint=false
gg.handler.kafkahandler.format.jsonDelimiter=CDATA[]
gg.handler.kafkahandler.format.includePrimaryKeys=true
gg.handler.kafkahandler.SchemaTopicName=htf_damp_dimensionTopic
gg.handler.kafkahandler.BlockingSend =false
gg.handler.kafkahandler.includeTokens=false
gg.handler.kafkahandler.mode=op


goldengate.userexit.writers=javawriter
javawriter.stats.display=TRUE
javawriter.stats.full=TRUE

gg.log=log4j
gg.log.level=INFO

gg.report.time=30sec

#Sample gg.classpath for Apache Kafka
gg.classpath=dirprm/:/usr/local/kafka/kafka_2.13-2.7.0/libs/*
#Sample gg.classpath for HDP
#gg.classpath=/etc/kafka/conf:/usr/hdp/current/kafka-broker/libs/*

javawriter.bootoptions=-Xmx512m -Xms32m -Djava.class.path=ggjava/ggjava.jar

4、配置完成，进行重启

oracle源端的ogg进行重启

GGSCI (localhost.localdomain as ogg@DCDBTEST) 11> stop *
GGSCI (localhost.localdomain as ogg@DCDBTEST) 12> start *

查看启动是否正常

GGSCI (localhost.localdomain as ogg@DCDBTEST) 30> info all

5、目标端标记监听起始点

stop REKAFKA2
ALTER REPLICAT REKAFKA2 BEGIN 2022-12-22 09:00 
或者
alter replicat REKAFKA2,begin now
start REKAFKA2