Handle oracle SCN gap

[Willie Zhu]

Hi team,

We deployed debezium Oracle connector in our testing env, normally the SCN increase several thousands in one query loop, debezium works well. But sometimes the SCN increased much bigger in one query loop, in the following example, the SCN increased by 2219052488. In fact the row count during that time are similar with the other normal query loop, it's just a SCN gap.

Although we set the  "log.mining.batch.size.max":"200000", it took a long time to catch up when the SCN gap happen. 
 prevEndScn 17800237736902, time=2021-09-23T05:27:03Z current SCN 17802456789390, time=2021-09-23T05:27:09Z

Can we improve to detect such a case, and use the current SCN as end SCN when the SCN gap happen?  thoughts?

[Chris Cranford]

Hi Willie -

Unfortunately the log.mining.batch.size.max setting is the only alternative to try and work around this problem.  SCN gaps are typically a product of a hot-backup or some other process that causes Oracle to create these gaps.  Unfortunately the connector currently works off using SCN [start, end] ranges and detecting such a gap is quite difficult.  Do you have any idea what causes this gap in your system?  Could you also raise a Jira issue on this so we don't loose visibility of it?

Thanks
Chris

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/55efa739-977d-4fc7-8679-210537948706n%40googlegroups.com.

[Willie Zhu]

Hi Chris, 

     Yes, created JIRA issue https://issues.redhat.com/browse/DBZ-4036.
      We asked our DBA, they are not sure why the SCN gap happened, they are asking Oracle support. 

      I tried to some cod to detect SCN gap, see the PR https://github.com/debezium/debezium/pull/2728
      It can detect SCN gap. But if we stop debezium, and SCN gap happen, and then we start debezium, it can't detect SCN gap in such a case.

[Chris Cranford]

Hi Willie -

Andrey sent me some code they are using at Navis to address this change and its a little bit different than yours and should address some other pain points:

    1. It doesn't manipulate the endScn until the connector has "caught-up" in case of downtime.
    2. It can detect gaps in SCN even after restart

I'll open a PR with these changes later today and I'll ping you on the PR.  If you could test the changes in the PR locally with your environment that would be great to get feedback if it works as expected.

Thanks,
Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/1a26b482-e8a4-410b-ad55-ca251b62df5bn%40googlegroups.com.

[Chris Cranford]

Willie -

Just an update, I've got to reach out to Andrey to get some clarification on this, hopefully I'll know more tomorrow.

Chris