Cannot restore connector from SCN number.
Artem Beltiukov
Hello everyone.
We are using Debezium along with Oracle. We have a periodic Oracle patching that sometimes causes an exception: “Caused by: java.lang.IllegalStateException: None of log files contains offset SCN: 12345, re-snapshot is required".
Here's what I've found about it, and from what I've learned, there's not much I can do to avoid it:
https://debezium.io/blog/2023/06/29/debezium-oracle-series-part-3/#:~:text=None%20of%20the%20log
I'm not exactly sure what happens during the patching procedure, but as mentioned in the link above, our problem could be due to a constraint violation or an explicit user or system rollback. When this happens, the SCN it was trying to find is definitely NOT in the REDO log table.
There are two options to solve this problem:
- Restarting the connectors.
- Resetting the connector to a different SCN.
I can restart the connectors and they will work, but resetting the SCN number doesn't work for some reason, although it is mentioned as a solution to this problem here:
https://debezium.io/documentation/reference/stable/connectors/oracle.html
It just gets stuck and won't load data, no errors as well, connectors run but won't load. Restarting is still possible, but it takes so long. Could you please provide any ideas I can do to avoid the initial error or get the SCN reset to work? If you need any additional info please let me know.
Thanks in advance
Chris Cranford
What version of Debezium are you presently using and are connecting to Oracle RAC or to an Oracle Standalone instance?
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/8ce92544-2728-4075-aa07-99ce6dcfe004n%40googlegroups.com.
Artem Beltiukov
Chris Cranford
So with Oracle RAC, there are some known issues, particularly if your patching procedure impacts the availability of nodes. I would suggest you track/watch DBZ-7389 [1], I'm actively working on a development fix for this that I am actively testing already and hope to have in 2.6.0.CR1 and potentially back-ported to Debezium 2.5.x.
Thanks,
Chris
[1]: https://issues.redhat.com/browse/DBZ-7389
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/609f6058-8fbb-4acd-9d84-f5fda4f6cb04n%40googlegroups.com.
Artem Beltiukov
Yes, this bug can be the root cause of our problem and exception after patching, thanks for sharing!
But we have no errors after restarting from SCN. Actually, there is nothing in the logs either, and only when we restarted connector, logs about data loading started appearing. Could you suggest something here as well please?
Chris Cranford
Is there anyway you can enable DEBUG or TRACE logging just before your next patching cycle and share those logs so we can see what could be happening? Generally you should see some error in this case.
Thanks,
Chris
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/9d2ed5fc-ceea-49a9-bb54-931a07cb620cn%40googlegroups.com.
Artem Beltiukov
Hi
Thanks, I will try to get them!
But it is not related to loading from a specific SCN?
We couldn't give the connector a specific SCN number, I don't think it has anything to do with patches.
After “Caused by: java.lang.IllegalStateException: None of log files contains offset SCN: 12345, re-snapshot is required” exception we tried to set one of the previous to "12345" SCNs that we found in the REDO logs to load from, but it just didn't work, we had no errors, exceptions or messages. Seems like, it cant find updates or something, I have no idea. Can you please give me some advice on where I can find something?
Chris Cranford
Changing the offset's "scn" value manually may not fix the problem if the "scn" and "commit_scn" values are incorrectly set based and don't align to the database's current state. For example, even if you pick a valid SCN and that SCN is prior to when a DBA brings a new RAC node online, then you will continue to experience the failure because the connector presently does not resolve the inclusion of new RAC nodes correctly with Debezium 2.5.2.Final. But I'd prefer to avoid speculating and asking you to make unnecessary changes if your problem is unrelated to this and caused by something else. This is why the DEBUG/TRACE logs are important to know what the connector sees when it attempts to start.
While you obtain the logs, you could have your DBA check that for the specified SCN you've chosen, do you have archive logs for all RAC nodes that contain that SCN and all logs for those nodes that come after that SCN? If the DBA has purged any of those logs or if they determine that a new RAC node was added after the chosen SCN, then you're going to need to pick an SCN that is closer to the present or simply re-take a snapshot.
Thanks,
Chris
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/c20c612d-2560-4df4-9f0a-a5e611188c55n%40googlegroups.com.
Artem Beltiukov
Thanks for your help!!! I'm afraid I won't be able to provide logs, so I guess I'll wait for this bug to be fixed.
One last question. I saw that the PR has been sent. Could you please tell me when approximately it could be merged?
Thank you so much for your time and help!
Chris Cranford
Our goal is to have it as part of Debezium 2.6.0.CR1, so that should mean it will be merged in the next few days.
Thanks,
Chris
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/f96e7222-42ff-4038-a767-42b556a6ccccn%40googlegroups.com.
Chris Cranford
Looks like Jiri just merged this work; it should be available in our nightly builds [1] in about 6 hours.
Just grab the Oracle connector plugin from the link above and replace the contents of the local Oracle plug-in with the downloaded archive.
Thanks,
Chris
[1]: https://debezium.io/documentation/reference/nightly/install.html#_installing_a_debezium_connector