skip to Main Content

What’s the easiest way to implement following solution:

There is an old legacy app which uses Oracle DB, the idea is to create a new service in SpringBoot which would observe for changes on a few specific tables in this Oracle DB and when they occur the service would then write them in a new Postgres DB.

What would be the best way to subscribe and listen for changes on these specific tables in Oracle DB?

Should I look into DB specific solutions or is there a more generic way provided by SpringBoot which is independent of DB provider?

I’ve seen this: https://docs.oracle.com/cd/E18283_01/java.112/e16548/dbchgnf.htm

but I would prefer if there is a way to do this without touching the legacy infrastructure in any way.

So to sum up, I need this:

We have databases A and B and we have a SpringBoot application, whenever change occurs in database A in a specific table I want to get details of that change and persist appropriate data to database B based on this change.

2

Answers


  1. A proper solution would require an awful lot of code to write.

    It is best to not re-invent that wheel.

    You can try something like debezium: https://debezium.io

    I do not know exactly how it works; their architecture is shown here:
    https://debezium.io/documentation/reference/stable/architecture.html
    but it is still unclear to me exactly how they capture changes.
    The top diagram involves kafka and might look like an overkill, but at the bottom of the page they state that you can embed debezium in your java app.

    They have a connector for Oracle:
    https://debezium.io/documentation/reference/stable/connectors/oracle.html

    Login or Signup to reply.
  2. You are following an often-used approach that has some drawbacks. Apparently the only fixed part is the usage of Spring Boot, but this “application” solution leads frequently to lost, duplication or a wrong order of the processed change events (with error accumulation).

    More pragmatic and save is the “database” solution that uses the transaction concept to avoid the above problems consisting of the three parts:

    Change Data Capture

    Use a DIY trigger solution or the Oracle provided to extract the change records from the legacy schema. Be sure that the change records have unique identification and unique order (no timestamps that can be duplicated)

    Transfer

    Simple transaction-based transfer of the change records from source to target database. SELECT and INSERT are your tools, do not use messaging systems that do not support transactions.

    Change Application

    Simple MERGE job maintaining the target snapshot by applying the changes – again transaction based. The status of the chnage record (open, applied,..) follows the same transactional scope.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search