Product: Streamsets Data Collector
Question:
Here is the screenshot of the JDBC_84 error that is encountered in some occasions while running the Oracle CDC pipeline.
Answer:
Here are some simple steps to simulate this error
1. Open a sqlplus shell and create a test table:
SQL> CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(50));Table created.
2. Create an "Oracle CDC >> Trash" pipeline to read changes from TEST table:
- Set "Initial Change" = "From Latest Change"
- "Buffer Changes Locally" must be checked
- Set "Maximum Transaction Length" = "${10 * SECONDS}"
- (Extra: go to Log Config and set log level to DEBUG)
3. Start pipeline, open a sqlplus shell and execute an insertion (ensure autocommit is off):
SQL> set autocommit off;SQL> show autocommit;autocommit OFFSQL> insert into TEST values (1, 'Marcus');1 row created.
4. Wait longer than 10 seconds. If you configured a DEBUG log level, you should see something like the following (note the timestamps of the log traces):
2020-07-30 08:30:22,982 [...] DEBUG OracleCDCSource - Num Active Txns = 0 Total Cached Records = 0 Commit SCN = , SCN = 2588984, Operation = 1, Txn Id = 1.25.961, Timestamp = 2020-07-30T08:30:21, Row Id = AAASFyAAHAAAAH/AAD, Redo SQL = insert into "C##SDC"."TEST"("ID","DESCRIPTION") values ('1','Marcus')...2020-07-30 08:30:33,923 [...] INFO OracleCDCSource - Removing expired transactions.2020-07-30 08:30:33,923 [...] INFO OracleCDCSource - Removing transaction with id: 1.25.9612020-07-30 08:30:33,924 [...] INFO OracleCDCSource - Removed 1 transactions and a total of 1 records.5. Wait for a while, and you will see the error in the UI:
The bottom line is it is important to set the Transaction Max Length parameter in oracle CDC pipelines. When it is set to a very short value we can encounter these JDBC_84 and setting it to a very high value can affect the restart of the CDC pipelines since when we resuming from offset at time T, we start the pipeline with a mining window equals to (T - max_transaction_length, T - max_transaction_length + max_transaction_window). We do this to avoid losing data (i.e. those in-progress transactions which were pending to commit at time T and that we need to recreate after restart). So there is indeed a penalty in the sense that we have to re-read those "Maximum Transaction Length" seconds before processing new data.
So if we set the Transaction Max Length setting to very high during restart we will need to ensure that there is sufficient archive logs in the destination to go back and read.