Oracle Debezium misses SCN in Offset when the DB logs are archived
463 views
Skip to first unread message
Anantha Bolar
Oct 7, 2022, 3:59:20 PM10/7/22
to debezium
Hi Team,
We are using the debezium embedded engine in a java service to capture changes from oracle database. and storing the offset file on AWS s3. via custom implementation. The Oracle DB has 100+ tables but we are interested in capturing changes in 5 tables only, so we defined these 5 tables in table.include.list. The handleBatch event has recordComitter.MarkBatchFinished() and recordCommitter.markProcessed(record) calls which I THINK triggers the customeOffsetStorage implementation to save the Oracle SCN on S3. The 5 tables are less frequently updated than other tables. Our logs are stored only for 6 hrs on oracle server and then moved to archive location on a different standby oracle server. Since these tables are less frequently updated, the handleBatch event also does not fire that frequent and hence recordComitter can not send a signal to store the offset to S3. so the offset in file is older most of the time. And if the java service needs a restart for some reason - it loads ab old SCN from the offset file which is no more in the logs because of 6 hours log retention policy and engines stops suggesting that scn not found and need a resnapshot.
Is there a way I can invoke the customoffsetStorage module more frequently to keep a more recent scn number on file? I do not want to allow all tables and filter them in handleBatch event.
Also - is there a way we can run oracle engine in CDC mode only and bypass the snapshot mode like postgres?
We are using the debezium embedded engine in a java service to capture changes from oracle database. and storing the offset file on AWS s3. via custom implementation. The Oracle DB has 100+ tables but we are interested in capturing changes in 5 tables only, so we defined these 5 tables in table.include.list. The handleBatch event has recordComitter.MarkBatchFinished() and recordCommitter.markProcessed(record) calls which I THINK triggers the customeOffsetStorage implementation to save the Oracle SCN on S3. The 5 tables are less frequently updated than other tables. Our logs are stored only for 6 hrs on oracle server and then moved to archive location on a different standby oracle server. Since these tables are less frequently updated, the handleBatch event also does not fire that frequent and hence recordComitter can not send a signal to store the offset to S3. so the offset in file is older most of the time. And if the java service needs a restart for some reason - it loads ab old SCN from the offset file which is no more in the logs because of 6 hours log retention policy and engines stops suggesting that scn not found and need a resnapshot.
Is there a way I can invoke the customoffsetStorage module more frequently to keep a more recent scn number on file? I do not want to allow all tables and filter them in handleBatch event.
Also - is there a way we can run oracle engine in CDC mode only and bypass the snapshot mode like postgres?
jiri.p...@gmail.com
Oct 10, 2022, 1:19:23 AM10/10/22
to debezium
Hi,
and set https://debezium.io/documentation/reference/1.9/connectors/oracle.html#oracle-property-snapshot-mode to schema_only for the second question.
J.
George H
Oct 11, 2022, 10:33:55 AM10/11/22
to debezium
Hi there,
My name is George form a recruitment consultancy in the UK.
We are recruiting for a Debezium Consultant for a remote working contract.
If you are interested or know anyone who has excellent Debezium experience please mail geo...@digisourced.com and rebecca...@recann.co.uk
Reply all
Reply to author
Forward
0 new messages