Pk handling in Oracle19c

[Pranita123]

Hi Community,

I'm currently working with Oracle 19c as a source, and I've some tables without primary keys. Need to configure a task for a full load with Change Data Capture (CDC). How should I proceed in managing the absence of primary keys in this scenario? Your guidance on handling this situation would be greatly appreciated.

Regards,

Pranita 

[john_wang]

Hello @Pranita123 ,

Thanks for reaching out to Qlik Community!

Oracle source table CDC does not demand PK unless you have LOB columns (BLOB, CLOB etc). If the table has NO PK then Replicate will add all columns to supplemental logging (by default), unless the option Automatically add supplemental logging is turn off . 

In short, the table(s) should be replicated without any issue.

Hops this helps.

John.

[SushilKumar]

Hello team,

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.

Regards,

Sushil Kumar

[Pranita123]

Hi @john_wang  Thank you for your response,

Automatically adding supplemental logging is enabled, but when there is an update at the source, the target_ct table is showing as an insert.

Could you please guide me on the same?

Regards,

Pranita

[john_wang]

Hello @Pranita123 ,

Please check the source table DDL especially if any triggers defined on it. If you need help still, please open a support ticket with below information:

1- The source table creation DDL include PK/Unique Index and triggers (if have any)

2- Task Diag Packages with SOURCE_CAPTURE/TARGET_APPLY set to Verbose

3- The source table sample UPDATE SQL

thanks,

John.

[Pranita123]

Hello @john_wang ,

I made a mistake earlier; the issue isn't that updates are being treated as inserts, but rather that although we see three update counts on our console, no updates are reflected at the target.

We spoke with the DBA, who informed us that only database-level supplemental logging is enabled.

Given that the task involves tables without primary keys, do you believe enabling table-level supplemental logging could resolve this problem?

Thanks, 

Pranita Patole

[john_wang]

Hello Pranita Patole, @Pranita123 

Please make sure that the required supplemental logging is added for all columns on each table. For your scenario, If there is no Primary Key and no Unique Index, supplemental logging must be added on all columns.

Hope this helps.
John.

[Pranita123]

Thank you for your response, @john_wang 

However, they are not agreeing to supplemental logging due to storage concerns. Do we have any alternative ways?

Thanks,
Pranita

[john_wang]

Hello @Pranita123 ,

Qlik Replicate operates as a log-based CDC solution, extracting data changes from transaction logs. However, ensuring seamless data integration can be challenging if any crucial information is missing from the transaction logs.

Maybe a Full Load ONLY task could potentially address the issue. This approach helps in mitigating the risk of incomplete data integration resulting from any gaps in the transaction log

Regards,

John.

[SushilKumar]

Hello @Pranita123 

It is always advised to have PK for the tables which are going to take part in Replication by a tool which extract SQL from Transactions logs. 

As any replication tools require additional information to locate rows and columns on the target Side.

in case of oracle Supplemental logging do this. however, having PK is charry on cake. it mitigates the risk of data mismatch and make DML faster on target endpoint.

Regards,

Sushil Kumar