ogg-kafka

 

环境搭建

Oracle 环境搭建

源端 OGG 依赖 Oracle 环境，可安装 Oracle instant client，如果源端和数据库在同一台服务器则不需要此小节的步骤。

下载地址：https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm

默认安装到 /usr/lib 的 Oracle 目录

vi ~/.bash_profile

export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_BASE=/usr/lib/oracle/11.2
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH
export PATH

source 使环境变量生效。

配置监听的数据库 tns 文件信息

mkdir -p /usr/lib/oracle/11.2/client64/network/admin

vi tnsnames.ora

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.51.213)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)

目标端 ogg 配置

kafka 解压配置

环境变量配置

export OGG_HOME=/opt/ogg4bigdata # OGGforBigdata解压目录
export LD_LIBRARY_PATH=$JAVA_HOME/jre/lib/amd64:$JAVA_HOME/jre/lib/amd64/server:$JAVA_HOME/jre/lib/amd64/libjsig.so:$JAVA_HOME/jre/lib/amd64/server/libjvm.so:$OGG_HOME/lib
export PATH=$OGG_HOME:$PATH

控制器,配置并启动

GGSCI (localhost.localdomain) 1> edit param mgr
PORT 7809
DYNAMICPORTLIST 7810-7909
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3

GGSCI (localhost.localdomain) 1> start mgr

复制进程

GGSCI (localhost.localdomain) 1> edit param rekafka
REPLICAT rekafka
TARGETDB LIBFILE libggjava.so SET property=dirprm/kafka.props
REPORTCOUNT EVERY 1 MINUTES, RATE
GROUPTRANSOPS 10000
MAP FLINKUSER.TABLE_CH, TARGET FLINKUSER.TABLE_CH;

kafka 及生产者配置

位置：${ogg_home}/dirprm

gg.handlerlist=kafkahandler
gg.handler.kafkahandler.type=kafka
gg.handler.kafkahandler.KafkaProducerConfigFile=custom_kafka_producer.properties
gg.handler.kafkahandler.topicMappingTemplate=mytopic # 或使用 ${table_name}
gg.handler.kafkahandler.format=json
gg.handler.kafkahandler.mode=op
gg.classpath=dirprm/:/opt/kafka_2.12-2.8.0/libs/*:/opt/ogg4bigdata/:/opt/ogg4bigdata/lib/* # 修改Kafka及OGGforBigdata目录

bootstrap.servers=10.1.51.215: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
batch.size=102400
linger.ms=10000

源端 ogg 环境搭建

下载地址：

http://edelivery.oracle.com/osdc/faces/Home.jspx

解压

安装

配置响应文件

vi /opt/oracle/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

INSTALL_OPTION=ORA11g # 修改版本

SOFTWARE_LOCATION=/opt/oracle # ogg安装目录

START_MANAGER=false # 是否配置完毕后启动mgr

必须以 root 外 的其他用户来执行安装，并将 ogg 解压后的文件夹变更所有者 chown

[oracle@50srv153 Disk1]$ ./runInstaller -silent -responseFile /opt/oracle/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
正在启动 Oracle Universal Installer...

检查临时空间: 必须大于 120 MB。 实际为 300118 MB 通过
检查交换空间: 必须大于 150 MB。 实际为 3459 MB 通过
准备从以下地址启动 Oracle Universal Installer /tmp/OraInstall2024-01-11_11-14-44AM. 请稍候...[oracle@50srv153 Disk1]$ [FATAL] [INS-32037] 为主产品清单 (oraInventory) 所有权指定的操作系统组无效。
原因: 没有为主产品清单 (oraInventory) 所有权组指定值。
操作: 请指定一个操作系统组, 其成员应具有主产品清单目录 (oraInventory) 的写权限。
此会话的日志当前已保存为: /tmp/OraInstall2024-01-11_11-14-44AM/installActions2024-01-11_11-14-44AM.log。如果要保留此日志, Oracle 建议将它从临时位置中转移。

# 在/opt/oracle下面创建文件oraInst.loc文件
inventory_loc=/opt/oracle/oraInventory
inst_group=dba

# 安装脚本添加参数
[oracle@50srv153 opt]$ cd /opt/oracle/fbo_ggs_Linux_x64_shiphome/Disk1/
[oracle@50srv153 Disk1]$ ./runInstaller -silent -force -ignorePrereq -invPtrLoc /opt/oracle/oraInst.loc -responseFile /opt/oracle/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
正在启动 Oracle Universal Installer...

检查临时空间: 必须大于 120 MB。 实际为 300145 MB 通过
检查交换空间: 必须大于 150 MB。 实际为 3459 MB 通过
准备从以下地址启动 Oracle Universal Installer /tmp/OraInstall2024-01-11_11-29-51AM. 请稍候...[oracle@50srv153 Disk1]$ 可以在以下位置找到本次安装会话的日志:
/opt/ogg/oraInventory/logs/installActions2024-01-11_11-29-51AM.log
[oracle@50srv153 ogg]$ ggOracle GoldenGate Core 的 安装 已成功。
请查看 '/opt/ogg/oraInventory/logs/silentInstall2024-01-11_11-29-51AM.log' 以获取详细资料。

配置

控制器

PORT 7809
DYNAMICPORTLIST 7810-7909
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3

登录 Oracle 令牌,后续抽取使用，要有所需的权限

GGSCI (50srv153) 3> add credentialstore

Credential store created.

GGSCI (50srv153) 4> alter credentialstore add user flinkuser@orcl, password flinkpw alias orcl

Credential store altered.

GGSCI (50srv153) 5> dblogin useridalias orcl
Successfully logged into database.

抽取进程

EXTRACT ext_ora
SETENV (NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK)
USERIDALIAS orcl
GETTRUNCATES
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE ./dirrpt/ext_ora.dsc,APPEND,MEGABYTES 1000
WARNLONGTRANS 2h,CHECKINTERVAL 10m
EXTTRAIL ./dirdat/sk
TRANLOGOPTIONS MINEFROMACTIVEDG
DBOPTIONS ALLOWUNUSEDCOLUMN
DYNAMICRESOLUTION
FETCHOPTIONS FETCHPKUPDATECOLS,INCONSISTENTROW ALLOW
getupdatebefores
nocompressdeletes
nocompressupdates
table flinkuser.table_ch;

绑定抽取进程与文件

add extract ext_ora,TRANLOG, begin now
add ExtTrail ./dirdat/sk, Extract ext_ora, Megabytes 50

启动抽取进程

start ext_ora

报错

2024-01-11 16:06:09 ERROR OGG-02091 Operation not supported because enable_goldengate_replication is not set to true.

2024-01-11 16:06:09 ERROR OGG-01668 PROCESS ABENDING.

需开启 Oracle 系统参数

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE; # 无需重启

再次启动报错

MINEFROMACTIVEDG参数只能 DG 上用，删掉

2024-01-11 16:11:02 ERROR OGG-02801 Parameter MINEFROMACTIVEDG can only be used when the database is in READ ONLY mode.

2024-01-11 16:11:02 ERROR OGG-01668 PROCESS ABENDING.

再次启动报错

2024-01-11 16:15:41 ERROR OGG-00665 OCI Error describe for query (status = 942-ORA-00942: table or view does not exist), SQL<SELECT o.obj# FROM sys.obj$ o, sys.user$ u, sys.tab$ t WHERE o.name = :1 AND o.owner# = u.user# AND u.name = :2 AND o.obj# = t.obj#>.

2024-01-11 16:15:41 ERROR OGG-01668 PROCESS ABENDING.

百度为权限问题，为不影响其他人使用，新建账号赋予 dba 权限，新建 ogg 登录令牌

create user ogg identified by ogg;
grant dba to ogg;
########################################
GGSCI (50srv153) 13> add credentialstore

A credential store already exists.

ERROR: Unable to create a new credential store.

GGSCI (50srv153) 14> alter credentialstore add user ogg@orcl, password ogg alias ogg
Credential store altered.

GGSCI (50srv153) 15> dblogin useridalias ogg
Successfully logged into database.

修改参数令牌和字符集编码

EXTRACT ext_ora
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERIDALIAS ogg
GETTRUNCATES
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE ./dirrpt/ext_ora.dsc,APPEND,MEGABYTES 1000
WARNLONGTRANS 2h,CHECKINTERVAL 10m
EXTTRAIL ./dirdat/sk
DBOPTIONS ALLOWUNUSEDCOLUMN
DYNAMICRESOLUTION
FETCHOPTIONS FETCHPKUPDATECOLS,INCONSISTENTROW ALLOW
getupdatebefores
nocompressdeletes
nocompressupdates
table flinkuser.table_ch;

再次启动，运行几秒后失败

## 报错
2024-01-11 16:22:39 ERROR OGG-00446 Could not find archived log for sequence 1467 thread 1 under default destinations SQL <SELECT name FROM v$archived_log WHERE sequence# = :ora_seq_no AND thread# = :ora_thread AND resetlogs_id = :ora_resetlog_id AND archived = 'YES' AND deleted = 'NO' AND name not like '+%' AND standby_dest = 'NO' >, error retrieving redo file name for sequence 1467, archived = 1, use_alternate = 0Not able to establish initial position for begin time 2024-01-11 16:02:12.000000.

alter extract ext_ora,begin now

## 报错
2024-01-11 16:29:52 ERROR OGG-00446 error 2 (No such file or directory) opening redo log D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG for sequence 1468Not able to establish initial position for begin time 2024-01-11 16:29:14.000000.

抽取进程添加参数,生成trail文件

TRANLOGOPTIONS DBLOGREADER

投递进程

edit param pump_ora

EXTRACT pump_ora
RMTHOST 10.1.51.215, MGRPORT 7809, compress
PASSTHRU
RMTTRAIL /opt/ogg4bigdata/dirdat/sk
DYNAMICRESOLUTION
table flinkuser.table_ch;

绑定投递进程与本地文件、目标端文件

add extract pump_ora, exttrailsource ./dirdat/sk
add rmttrail /opt/ogg4bigdata/dirdat/sk, extract pump_ora, megabytes 100

启动投递进程报错

2024-01-11 17:14:37 ERROR OGG-01224 TCP/IP error 113 (No route to host), endpoint: 10.1.51.215:7809.

2024-01-11 17:14:37 ERROR OGG-01668 PROCESS ABENDING.

目标端关闭防火墙

重新启动，查看目标端已接收到 trail 文件

测试

启动目标端复制进程

查看 Kafka 生成 topic

打印 topic 内消息

新增字段 ADD_COL 后，插入数据，除新增字段外，可以正常监听发送