-
Bug
-
Resolution: Done
-
Major
-
1.3.0.Final, 1.4.0.Alpha1
-
None
-
False
-
False
-
Undefined
-
- Create new RDS Oracle database (v12)
- Create table with dummy data
- Submit connector to dbz
- Insert records into table
-
Trying to test the oracle connector with the AWS RDS version of Oracle.
Everything works fine when debezium is taking the initial snapshot of the table. But whenever new records get inserted, the changes are not streaming to the kafka topic. After a pretty long delay (~20mins or so) this shows up in the logs:
2020-11-12 03:30:18,713 WARN || All transactions with first SCN <= 577584 will be abandoned, offset: 576745 [io.debezium.connector.oracle.logminer.LogMinerHelper] 2020-11-12 03:30:18,713 WARN || Following long running transaction 09000F0095010000 will be abandoned and ignored: Transaction{firstScn=576785, lastScn=576785, redoSqls=[[insert into "ADMIN"."CUSTOMERS"("ID","FIRST_NAME","SALARY") values ('4','tammy','900');]]} [io.debezium.connector.oracle.logminer.LogMinerHelper] 2020-11-12 03:30:18,714 WARN || Following long running transaction 0800210083010000 will be abandoned and ignored: Transaction{firstScn=576748, lastScn=576748, redoSqls=[[insert into "ADMIN"."CUSTOMERS"("ID","FIRST_NAME","SALARY") values ('3','timmo','600');]]} [io.debezium.connector.oracle.logminer.LogMinerHelper] 2020-11-12 03:30:18,714 WARN || Following long running transaction 09001B0094010000 will be abandoned and ignored: Transaction{firstScn=576826, lastScn=576826, redoSqls=[[insert into "ADMIN"."CUSTOMERS"("ID","FIRST_NAME","SALARY") values ('5','cardi b','1000');]]} [io.debezium.connector.oracle.logminer.LogMinerHelper]
Not sure if this is linked to the fact that the oracle connector requires the oracle database to have 2 log files per group, because this is not possible in RDS Oracle.
- relates to
-
-
- Closed
-
[DBZ-2754] Oracle Connector(Using Logminer) with Oracle RDS (v12) does not capture changes
I took a deeper look at the warnings, these are actually normal.
When the connector detects a LOGSWITCH there is a certain amount of bookkeeping operations that need to occur within the connector's internal buffers. One of those operations is to check if there are any long running transactions and determine how to handle those. If there are any long running transactions, this prevents the connector from being able to flush the current starting SCN that is being used for mining operations to the offsets. This means should the connector be restarted or stop unexpectedly, it will restart at the SCN from the offsets, which means lots of events will be re-emitted because we need to reprocess those long running transactions where we never saw a commit yet.
The connector uses a fixed 4 hour window for maintaining long running transactions, which may be too small for certain environments and should be configurable. I'll look at adding this as a configurable option so that when a LOGSWITCH is detected and if you maintain redo & archive logs for a longer period of time, you can customize the setting to your needs.
That said, I'm curious if this might be related to a recent bug where not all COMMIT operations were being analyzed and this caused some transactions that used auto-commit mode to be abandoned and not processed correctly. I would strong suggest you upgrade to 1.4.1.Final as a baseline and see if this persists. In the meantime I'm going to proceed to making this fixed 4 hour window configurable either way since the nature of everyone's environment can be different.
mngeow can you share the steps you took to configure the RDS Oracle environment, the LogMiner user, and your connector's full configuration?
Yeah this is the same account the is used by the connector.
Not sure if this helps, but this is taken from the documentation:
When you create a DB instance, the master user account that you use to create the instance gets DBA privileges (with some limitations). Use the master user account for any administrative tasks such as creating additional user accounts in the database. You can't use the SYS user, SYSTEM user, and other Oracle-supplied administrative accounts.
I was doing everything with the master user account.
Marcus ngeow (Inactive)
added a comment - - edited Yeah this is the same account the is used by the connector.
Not sure if this helps, but this is taken from the documentation :
When you create a DB instance, the master user account that you use to create the instance gets DBA privileges (with some limitations). Use the master user account for any administrative tasks such as creating additional user accounts in the database. You can't use the SYS user, SYSTEM user, and other Oracle-supplied administrative accounts.
I was doing everything with the master user account. Marcus ngeow (Inactive)
added a comment - ccranfor@redhat.com, the transactions were performed using the default admin account that you get when creating an RDS instance on AWS.
Marcus ngeow (Inactive)
added a comment - ccranfor@redhat.com , the transactions were performed using the default admin account that you get when creating an RDS instance on AWS. mngeow, can you confirm if the transactions that are being abandoned are being performed by SYS, SYSTEM, or the LogMiner user account?
The transaction buffer size can now be adjusted, so the maximum duration of supported transactions is a function of how much memory you can afford for that buffer. Via
DBZ-3123we're planning to explore a more scalable solution, e.g. using off-heap memory or a distributed K/V store.ccranfor@redhat.com, in the mean time: is there a very clear ERROR message in the logs in case events from a long-running transaction are discarded due to this?