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
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
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.