OGG for Big Data同步Cassandra遇两类问题求助

环境配置 #

• Oracle GoldenGate for BigData版本：21.9.0.0.3
• Cassandra Handler版本：4.17.0，通过OGG自带脚本/DependencyDownloader/cassandra.sh下载

进程启动参数文件 #

SPECIALRUN
END RUNTIME
TARGETDB LIBFILE libggjava.so SET property=dirprm/inievo.props
REPORTCOUNT EVERY 1 MINUTES, RATE
GROUPTRANSOPS 1000
EXTFILE ${EXTFILE}
MAP U_ALLIN.*, TARGET U_ALLIN.*;

inievo.props配置 #

#The handler properties
gg.handler.cassandra.type=cassandra
gg.handler.cassandra.mode=op
gg.handler.cassandra.contactPoints=10.152.13.7,10.152.13.8,10.152.13.9
gg.handler.cassandra.ddlHandling=CREATE,ADD,DROP
gg.handler.cassandra.compressedUpdates=true
gg.handler.cassandra.cassandraMode=async
gg.handler.cassandra.consistencyLevel=LOCAL_QUORUM
gg.handler.cassandra.dataCenter=cas

#Regex properties
gg.schemareplaceregex=[$]
gg.schemareplacestring=_

#Javawriter properties
javawriter.stats.display=TRUE
javawriter.stats.full=TRUE
javawriter.bootoptions=-Xmx1G -Xms1G -Duser.timezone=UTC -Djava.class.path=.:ggjava/ggjava.jar:./dirprm

#Logging properties
gg.log=log4j
gg.log.level=INFO
gg.report.time=3600sec

gg.classpath=/shared/Middleware/GGPRE0T5/ggjava/lib/cassandra_4.17.0/*

问题与解决方案 #

问题1：OGG写入速率过高触发Cassandra全节点不可用 #

问题现象

OGG写入速率过快，导致Cassandra所有节点抛出AllNodesFailedException，错误日志如下：

=ERROR 2024-01-23 11:52:50.000560 [main] - Async statement execution on Cassandra failed for table [U_ALLIN.IVF_RISERVAPREMI] at position [-0000000000000000001].
com.datastax.oss.driver.api.core.AllNodesFailedException: All 3 node(s) tried for the query failed (showing first 3 nodes, use getAllErrors() for more): Node(endPoint=10.152.13.9:9042, hostId=9b44e6db-1334-41f8-b073-73506d94e879, hashCode=
2186c2ff): [com.datastax.oss.driver.api.core.NodeUnavailableException: No connection was available to Node(endPoint=10.152.13.9:9042, hostId=9b44e6db-1334-41f8-b073-73506d94e879, hashCode=2186c2ff)], Node(endPoint=bdpre35cas.gruppoitas.loc
al/10.152.13.8:9042, hostId=c993410a-4520-4b19-a84d-75bb857eff5a, hashCode=4d8e4253): [com.datastax.oss.driver.api.core.NodeUnavailableException: No connection was available to Node(endPoint=10.152.13.8:9042, hostId=c993410a-4520-4b19-a84d
-75bb857eff5a, hashCode=4d8e4253)], Node(endPoint=10.152.13.7:9042, hostId=083aba02-71b9-4bf3-a38f-c05045f89ef5, hashCode=21e99a7): [com.datastax.oss.driver.api.core.NodeUnavailableException: No connection was available to Node(endPoint=bd
pre34cas.gruppoitas.local/10.152.13.7:9042, hostId=083aba02-71b9-4bf3-a38f-c05045f89ef5, hashCode=21e99a7)]
        Suppressed: com.datastax.oss.driver.api.core.NodeUnavailableException: No connection was available to Node(endPoint=10.152.13.9:9042, hostId=9b44e6db-1334-41f8-b073-73506d94e879, hashCode=2186c2ff)
        Suppressed: com.datastax.oss.driver.api.core.NodeUnavailableException: No connection was available to Node(endPoint=10.152.13.8:9042, hostId=c993410a-4520-4b19-a84d-75bb857eff5a, hashCode=4d8e4253)
        Suppressed: com.datastax.oss.driver.api.core.NodeUnavailableException: No connection was available to Node(endPoint=10.152.13.7:9042, hostId=083aba02-71b9-4bf3-a38f-c05045f89ef5, hashCode=21e99a7)
Exception in thread "main" oracle.goldengate.util.GGException: Error detected handling transaction commit event.
        at oracle.goldengate.datasource.UserExitDataSource.commitTransaction(UserExitDataSource.java:2480)
        at oracle.goldengate.datasource.UserExitDataSource.commitTx(UserExitDataSource.java:2081)

Source Context :
  SourceModule            : [gglib.ggdal.adapter.java]
  SourceID                : [ggdal/Adapter/Java/JavaAdapter.cpp]
  SourceMethod            : [HandleJavaException]
  SourceLine              : [301]
  ThreadBacktrace         : [19] elements
                          : [/shared/Middleware/GGPRE0T5/libgglog.so(CMessageContext::AddThreadContext())]
                          : [/shared/Middleware/GGPRE0T5/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...))]
                          : [/shared/Middleware/GGPRE0T5/libgglog.so(_MSG_String(CSourceContext*, int, char const*, CMessageFactory::MessageDisposition))]
                          : [/shared/Middleware/GGPRE0T5/libggjava.so()]
                          : [/shared/Middleware/GGPRE0T5/libggjava.so(ggs::gglib::ggdal::CJavaRecordWriter::CommitTransaction(int, int, int, int, ggs::gglib::ggdal::CDALError&))]
                          : [/shared/Middleware/GGPRE0T5/replicat(ggs::gglib::ggdal::CDALRecordWriter::CommitTransaction(int, int, int, int, ggs::gglib::ggdal::CDALError&))]
                          : [/shared/Middleware/GGPRE0T5/replicat(GenericImpl::CommitTransaction(int, int, int, int))]
                          : [/shared/Middleware/GGPRE0T5/replicat(odbc_commit_transaction())]
                          : [/shared/Middleware/GGPRE0T5/replicat(ggs::er::ReplicatContext::commitAndCheckpoint(short, bool&, RepCsn&))]
                          : [/shared/Middleware/GGPRE0T5/replicat(ClassicWorker::CommitAndCheckpoint(short))]
                          : [/shared/Middleware/GGPRE0T5/replicat(ggs::er::ReplicatContext::processReplicatLoop())]
                          : [/shared/Middleware/GGPRE0T5/replicat(ggs::er::ReplicatContext::run())]
                          : [/shared/Middleware/GGPRE0T5/replicat()]
                          : [/shared/Middleware/GGPRE0T5/replicat(ggs::gglib::MultiThreading::MainThread::ExecMain())]
                          : [/shared/Middleware/GGPRE0T5/replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*))]
                          : [/shared/Middleware/GGPRE0T5/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**))]
                          : [/shared/Middleware/GGPRE0T5/replicat(main)]
                          : [/lib64/libc.so.6(__libc_start_main)]
                          : [/shared/Middleware/GGPRE0T5/replicat()]

2024-01-23 11:52:50  ERROR   OGG-15051  Java or JNI exception:
oracle.goldengate.util.GGException: Error detected handling transaction commit event.

***********************************************************************
*                   ** Run Time Statistics **                         *

解决方案

无需通过调高日志级别降速，可通过以下OGG侧参数精准控制速率：

1. 调整事务提交批次：将进程参数中的GROUPTRANSOPS从1000调小至200-500区间，减少单次提交的事务数量，降低Cassandra瞬时压力。
2. 添加速率限制参数：在进程参数文件中加入THROTTLE指令，限制每秒处理的记录数，例如：

   THROTTLE 1000
   

   表示每秒最多处理1000条记录，可根据Cassandra实际负载调整数值。
3. 优化Cassandra Handler异步参数：在inievo.props中添加以下参数，控制异步写入的并发和队列大小：

   # 限制异步并发请求数
   gg.handler.cassandra.maxConcurrentRequests=20
   # 限制请求队列大小，避免请求积压
   gg.handler.cassandra.queueSize=1000
   # 调整批量写入大小，避免过大导致节点压力过高
   gg.handler.cassandra.batchSize=500
   

4. 调整一致性级别（可选）：若业务允许，可将LOCAL_QUORUM调整为LOCAL_ONE，降低Cassandra写入时的节点同步压力，但需注意数据一致性风险。

问题2：Oracle TIMESTAMP(9)被映射为Cassandra TEXT类型 #

问题现象

Handler日志显示Oracle的TIMESTAMP(9)字段被转换为Cassandra的TEXT类型：

Cassandra column [dta_effetto] maps to GG column [DTA_EFFETTO] Cassandra data type [TEXT] GG index [17].
Cassandra column [dta_evento] maps to GG column [DTA_EVENTO] Cassandra data type [TEXT] GG index [18].

Oracle表字段类型：

SQL> desc table_name
Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DTA_EFFETTO                                        TIMESTAMP(9)
 DTA_EVENTO                                         TIMESTAMP(9)

解决方案

需通过配置类型映射，实现Oracle TIMESTAMP到Cassandra TIMESTAMP的正确转换：

1. 确保目标表字段类型正确：先检查Cassandra目标表的对应字段是否为TIMESTAMP类型，若为TEXT需手动修改，或通过DDL配置自动创建正确类型。
2. 全局类型映射配置：在inievo.props中添加全局类型映射规则：

   gg.handler.cassandra.typeMapping=TIMESTAMP(9)=TIMESTAMP
   

   若要覆盖所有TIMESTAMP类型，可简化为：

   gg.handler.cassandra.typeMapping=TIMESTAMP=TIMESTAMP
   

3. DDL自动创建表的类型映射：若使用OGG自动创建Cassandra表，需添加DDL类型映射：

   gg.handler.cassandra.ddlTypeMapping=TIMESTAMP(9)=TIMESTAMP
   

4. 列级精准映射（可选）：针对特定表的字段进行单独配置：

   gg.handler.cassandra.columnMapping=U_ALLIN.table_name.DTA_EFFETTO=TIMESTAMP
   gg.handler.cassandra.columnMapping=U_ALLIN.table_name.DTA_EVENTO=TIMESTAMP
   

配置完成后，重启OGG进程即可生效。

内容的提问来源于stack exchange，提问作者Gabrielenones