topic Re: Pk handling in Oracle19c in Qlik Replicate

Pk handling in Oracle19c

Pranita123 — Thu, 25 Jan 2024 07:43:15 GMT

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

Re: Pk handling in Oracle19c

john_wang — Thu, 25 Jan 2024 07:50:11 GMT

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.

Re: Pk handling in Oracle19c

SushilKumar — Thu, 25 Jan 2024 09:38:10 GMT

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

Re: Pk handling in Oracle19c

Pranita123 — Wed, 14 Feb 2024 07:51:16 GMT

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

Re: Pk handling in Oracle19c

john_wang — Wed, 14 Feb 2024 15:20:19 GMT

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.

Re: Pk handling in Oracle19c

Pranita123 — Tue, 23 Apr 2024 07:48:50 GMT

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

Re: Pk handling in Oracle19c

john_wang — Tue, 23 Apr 2024 14:17:01 GMT

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.

Re: Pk handling in Oracle19c

Pranita123 — Wed, 24 Apr 2024 09:09:22 GMT

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

Re: Pk handling in Oracle19c

john_wang — Wed, 24 Apr 2024 13:12:45 GMT

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.

Re: Pk handling in Oracle19c

SushilKumar — Wed, 01 May 2024 08:06:03 GMT

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

Re: Pk handling in Oracle19c

Pranita123 — Fri, 03 May 2024 11:19:32 GMT

Hii @john_wang,

Thanks for response,

One more question If an update is required in the details logs, how can the replicate apply the delete without the details logs? Delete is also happening and is reflected at the target end as well.

Regards,

Pranita

Re: Pk handling in Oracle19c

john_wang — Fri, 03 May 2024 14:06:39 GMT

Hello @Pranita123 ,

I guess I did not get the question well however Qlik Replicate retrieves both REDO and UNDO entries from Oracle redo log files to make both BeforeImage and AfterImage are obtained to make sure the data integration.

Hope this helps.

John.

Re: Pk handling in Oracle19c

SushilKumar — Fri, 03 May 2024 14:16:31 GMT

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

Re: Pk handling in Oracle19c

SushilKumar — Fri, 03 May 2024 14:19:33 GMT

Hello @Pranita123

More elaborative information about having primary keys

Primary keys in Qlik Replication are critical for ensuring faster replication performance. By defining a primary key on the table being replicated, the replication process can ensure data integrity, enforce referential integrity, and maintain consistency between the source and the target databases. Additionally, primary key indexing can also help improve the performance of the replication process by speeding up the retrieval of data from the source and target databases. Therefore, it is important to carefully design primary keys when implementing Qlik Replication to optimize the replication performance.

Regards,

Sushil Kumar