Qlik Replicate - CDC after a Full-Load

[lqthinguyen]

Good evening,

We are performing Replication with CDC option and we encountered the below errors.  Please enlighten me what is the sequence process with the attrep_changes* tables  eg 1) create table attrep_changes 2) populate it, and 3) truncate it; besides the impacts.   THANK YOU

 ]E:  ORA-00942: table or view does not exist  [1020416]  (oracle_endpoint_load.c:862)

E:  Failed executing truncate table statement: TRUNCATE TABLE <schema_name>."attrep_changesD88A64BE_2000010" [1020403]

[john_wang · Accepted solution]

Hello @lqthinguyen ,

Thanks for reaching out to Qlik Community!

I'm assuming you are running Oracle as target endpoint. The net change table (naming rule "attrep_changes<uniquenessID>") is special in Oracle target as every target table owns itself net change table. It's different than other RDBMS, in most of other RDBMS, all the tables (in the same task) share the same net change table.

Qlik Replicate collects/retrieves changes records from source endpoint, then push the records to the interim Net Change table in Oracle DB, and merge the changes records from Net Change table to final target tables, this is the default behavior under Batch Optimized Apply mode.

When a task startup (with CDC enabled), the Net Change table will be created in Oracle DB. It will be used as staging table during the Change data Processing: gets a batch changes from source endpoint, processing, then cleans up the records in it after all the existing batch records are applied to target successfully; repeats the processing for next batch, or drops & recreate the Net Change table if get DDL from source endpoint (eg adds a new column). Until the task stopping - the Net Change table will be dropped as one of the step of task stopping.

When it comes to your error messages, please set TARGET_APPLY to Verbose, recreate the error and we may find helpful information from the task log file. The task log file default location is under DATA folder, in Windows it's "C:\Program Files\Attunity\Replicate\data\logs\<reptask_TaskName>.log".

Hope this helps.
John.

[lqthinguyen · Accepted solution]

Good afternoon Mr. Wang,

Thank you for a thorough reply, sir.

It seems TRUNCATE TABLE attrep_change* should not be part of the steps you have shared when it comes to the attrep_change* table; meanwhile we will follow your instructions to capture more details to share with you.

Do you see any negative impact from the error message?

Theresa

[Heinvandenheuvel]

@lqthinguyen   >>> It seems TRUNCATE TABLE attrep_change* should not be part of the steps you have shared 

Incorrect. The truncate is part of every batch to implement the step "cleans up the records in it after all the existing batch records are applied to target successfully".

Only a careful study of the entire log can explain why the table was not there while expected. And you possibly need logging for TARGET_APPLY set to DEBUG. The TRUNCATE should be happening over and over. It is created as a TEMP table. Maybe there was a disconnect and re-attach not correctly handled?

Did this happens just once, or more often? 1 temp table, or many?

Was the error reported on the task first  startup? At the end? While otherwise running successfully?

Was there a re-attach message around (before) the truncate issue time?

Because a tasklog can be silent for 30 minutes while everything is working fine, I like to set PERFORMANCE to trace to get more regular messages, but with Oracle Sources that may become too verbose.

Hein.

[lqthinguyen]

Good morning

I want to add an excerpt of the old error message 

00002904: 2024-03-28T00:00:54 [SOURCE_CAPTURE ]E: Archived Redo log with the sequence 582745 does not exist, thread 1 [1022318] (oradcdc_thread.c:933)
00002904: 2024-03-28T00:00:54 [SOURCE_CAPTURE ]E: Endpoint is disconnected [1020414] (endpointshell.c:3988)
00002904: 2024-03-28T00:00:54 [SOURCE_CAPTURE ]E: Error executing source loop [1020414] (streamcomponent.c:1940)
00002904: 2024-03-28T00:00:54 [TASK_MANAGER ]E: Stream component failed at subtask 0, component st_0_RMC Prod O [1020414] (subtask.c:1396)
00002904: 2024-03-28T00:00:54 [SOURCE_CAPTURE ]E: Stream component 'st_0_RMC Prod O' terminated [1020414] (subtask.c:1565)
00004792: 2024-03-28T00:00:55 [TARGET_APPLY ]E: Failed executing truncate table statement: TRUNCATE TABLE "QLIK_RMC_APP_USER"."attrep_changes7DC22695_2000218" [1020403] (oracle_endpoint_load.c:856)
00004792: 2024-03-28T00:00:55 [TARGET_APPLY ]E: ORA-00942: table or view does not exist [1020416] (oracle_endpoint_load.c:862)
00004792: 2024-03-28T00:00:55 [TARGET_APPLY ]E: Failed executing truncate table statement: TRUNCATE TABLE "QLIK_RMC_APP_USER"."attrep_changes7DC22695_2000266" [1020403]

00004792: 2024-03-28T00:00:55 [TARGET_APPLY ]E: Failed executing truncate table statement: TRUNCATE TABLE "QLIK_RMC_APP_USER"."attrep_changes7DC22695_2000218" [1020403] (oracle_endpoint_load.c:856)
00004792: 2024-03-28T00:00:55 [TARGET_APPLY ]E: ORA-00942: table or view does not exist [1020416] (oracle_endpoint_load.c:862)
00004792: 2024-03-28T00:00:55 [TARGET_APPLY ]E: Failed executing truncate table statement: TRUNCATE TABLE "QLIK_RMC_APP_USER"."attrep_changes7DC22695_2000266" [1020403] (oracle_endpoint_load.c:856)
00004792: 2024-03-28T00:00:55 [TARGET_APPLY ]E: ORA-00942: table or view does not exist [1020416] (oracle_endpoint_load.c:862)
00004792: 2024-03-28T00:00:55 [TARGET_APPLY ]E: Failed executing truncate table statement: TRUNCATE TABLE "QLIK_RMC_APP_USER"."attrep_changes7DC22695_2000089" [1020403] (oracle_endpoint_load.c:856)

[lqthinguyen]

Good morning

I want to add an excerpt of the old error message 

00002904: 2024-03-28T00:00:54 [SOURCE_CAPTURE ]E: Archived Redo log with the sequence 582745 does not exist, thread 1 [1022318] (oradcdc_thread.c:933)
00002904: 2024-03-28T00:00:54 [SOURCE_CAPTURE ]E: Endpoint is disconnected [1020414] (endpointshell.c:3988)
00002904: 2024-03-28T00:00:54 [SOURCE_CAPTURE ]E: Error executing source loop [1020414] (streamcomponent.c:1940)
00002904: 2024-03-28T00:00:54 [TASK_MANAGER ]E: Stream component failed at subtask 0, component st_0_RMC Prod O [1020414] (subtask.c:1396)
00002904: 2024-03-28T00:00:54 [SOURCE_CAPTURE ]E: Stream component 'st_0_RMC Prod O' terminated [1020414] (subtask.c:1565)
00004792: 2024-03-28T00:00:55 [TARGET_APPLY ]E: Failed executing truncate table statement: TRUNCATE TABLE "QLIK_RMC_APP_USER"."attrep_changes7DC22695_2000218" [1020403] (oracle_endpoint_load.c:856)
00004792: 2024-03-28T00:00:55 [TARGET_APPLY ]E: ORA-00942: table or view does not exist [1020416] (oracle_endpoint_load.c:862)
00004792: 2024-03-28T00:00:55 [TARGET_APPLY ]E: Failed executing truncate table statement: TRUNCATE TABLE "QLIK_RMC_APP_USER"."attrep_changes7DC22695_2000266" [1020403]

00004792: 2024-03-28T00:00:55 [TARGET_APPLY ]E: Failed executing truncate table statement: TRUNCATE TABLE "QLIK_RMC_APP_USER"."attrep_changes7DC22695_2000218" [1020403] (oracle_endpoint_load.c:856)
00004792: 2024-03-28T00:00:55 [TARGET_APPLY ]E: ORA-00942: table or view does not exist [1020416] (oracle_endpoint_load.c:862)
00004792: 2024-03-28T00:00:55 [TARGET_APPLY ]E: Failed executing truncate table statement: TRUNCATE TABLE "QLIK_RMC_APP_USER"."attrep_changes7DC22695_2000266" [1020403] (oracle_endpoint_load.c:856)
00004792: 2024-03-28T00:00:55 [TARGET_APPLY ]E: ORA-00942: table or view does not exist [1020416] (oracle_endpoint_load.c:862)
00004792: 2024-03-28T00:00:55 [TARGET_APPLY ]E: Failed executing truncate table statement: TRUNCATE TABLE "QLIK_RMC_APP_USER"."attrep_changes7DC22695_2000089" [1020403] (oracle_endpoint_load.c:856)

[Heinvandenheuvel]

Ah, so there is a lot going on at midnight. This could just be sub-optimal error handling. With that I mean that perhaps Replicate could/should have known there was no table to truncate, but skipping the truncate might not have made the replication recovery any better.

The big error condition really is the first one shown: " Archived Redo log with the sequence 582745 does not exist"

That's deadly for a CDC task. What happened? Why was it not there? Daily purge/move/backup at midnight?  How was the follow up to this condition? The shared log segment (thank you) only covers 1 second elapsed time. Did the task shut down or go in a wait state for that Archive to show up. Did it show up and recover? Did the task stop? - that would have been because of the missing archive, not due to that failing truncate.

I urge you to focus just on the missing archive issue and the root cause for that, which is 99.99% sure NOT an issue with Replicate but an issue with the source environment. Once you establish a full picture of what happened surrounding the errors and you still think Replicate could have done better, then you may want to raise a support ticket with the 'history' of event and/or full logs attached. Support would want to know: Start time; first error; recovery window; succesful resume?; end time and state.

Hein

[john_wang]

Thank you for the detailed explanation @Heinvandenheuvel , totally agree with you.

Hello @lqthinguyen ,Besides that, I'd like to suggest run the query:

select * From v$archived_log where name is not null order by sequence#;

please pay special attention to the column "STATUS", the "A" means available still, it's fine. if it's "D" (Deleted) the Replicate cannot read it and reported error.

Hope this helps.

John.

[lqthinguyen]

Good morning

Knowledge is powerful to make the best decision; therefore thank you very much for your replies!

We do know why the archived logs were not available to us.