How to handle errors in batch processing during incremental data synchronization (Oracle → Kafka → DB)? [closed]

Question

Closed. This question needs to be more focused. It is not currently accepting answers.

---

Want to improve this question? Guide the asker to update the question so it focuses on a single, specific problem. Narrowing the question will help others answer the question concisely. You may edit the question if you feel you can improve it yourself. If edited, the question will be reviewed and might be reopened.

Closed 3 days ago.

Improve this question

I'm implementing an incremental data synchronization pipeline:

1. Oracle → Kafka (using OGG)

2. Kafka → Internal DB (using a custom consumer with Spring Cloud Stream)

The messages contain INSERT, UPDATE, and DELETE operations. My current implementation processes messages one by one, but performance is insufficient. I want to introduce batch processing to improve throughput.

​​Problem​​: When processing in batches, some SQL statements may fail due to:

• Unique constraint violations (some tables have two unique indexes)

• Inconsistent states between upstream and downstream when the program starts

​​Current approach​​:

• Single message processing with detailed error logging

• Manual intervention required for failures

​​What I've tried​​:

• Simple batch execution (all-or-nothing) - fails entire batch on any error

• Attempted to identify problematic records but complex with mixed operation types

​​Requirements​​:

1. Don't want to log entire batches for manual processing (high ops overhead)

2. Need to maintain data consistency

3. Prefer automatic recovery mechanisms where possible

​​Questions​​:

1. What are common patterns for handling partial failures in batch database synchronization?

2. How can I implement resilient batch processing that:

• Continues processing valid records

• zIsolates and handles errors appropriately

• Minimizes manual intervention

3. Are there Spring Cloud Stream patterns or Kafka features that could help here?

​​Environment​​:

• Spring Boot 2.7.x

• Spring Cloud Stream

• Kafka

• Oracle & Internal DB (proprietary)

• Community: Please provide enough code so others can better understand or reproduce the problem.
• Mark Rotteveel: This question seems to have been generated or "improved" with help of ChatGPT or another AI. Please be aware that using LLMs or other AIs to generate content is not allowed.

Answer 1

Since your potential "database synchronization" has nothing to do with Kafka itself - there's little incentive of using Spring Kafka's @RetriableTopic there: it's not Kafka's fault, it's your processing part.

It's failure of a downstream counterpart Kafka has no knowledge about and or connection to, and if it fails the ways you describe (e.g. constraint violation), there's little expectation it will improve before attempts.

I would suggest to catch an error, and then drop the message into some kind of makeshift DLQ/DLT (even republish to back to the same Kafka cluster but other topic). From that point on you can either automate processing of that DLQ, or do it manuyally, or both.

I assume you use @KafkaListener upon your processing logic POJO, hence you can either catch that right within the processing method, or use one of two layers of error handlers (make sense to start with KafkaListenerErrorHandler implementation, it works at listener method level).

If you, however, have reasonable expectations conditions that caused error at your "internal database" side can improve between takes - you can go ahead & configure yourself non-blocking retries with @RetriableTopic.

That would automate republishing of problematic messages into special retry topics & finally to DLT when all retry attempts are exhausted.

P.S. Also, you may consider to switch to manual acknowledgements in any of cases mentioned above to have fine control of when & how you submit offsets back.