Oracle Source - Replicating Synonym CDC data

[Al_gar]

We're setting up Oracle as a source for a Primavera database with CDC to get some synonyms objects, but I saw on another thread I saw that only views and tables are supported by Replicate. If this is the case, for the following scenario:

- Synonym_A that points to View_A
- View_A is a view that points to 2 more views View_AA, View_AB
       + View_AA is a view that points to Table_A, Table_B, and Table_C
       + View_AB is a view that points to Table_D

Tables in View_AA and View_AB are owned by a different schema owner

By importing View_A, are we going to be able to get CDC data loaded?

[john_wang · Accepted solution]

Hello @Al_gar ,

Thanks for posting here.

Qlik Replicate is log-based CDC product (with a few exceptions), so far if the object's changes cannot be recorded in transaction logs (TLOG in SQL Server, Redo LOG in Oracle, Journal in DB2i, BinLog in MySQL, OPLOG in MongoDB ...) then it's impossible to get the relevant changes from transactions logs.

In Oracle database both Synonym and VIEW do not contain real data, they are metadata definition only (the only exception is Oracle Materialized View which stores data rows), the data rows are queries results while accessing the Synonym and/or VIEW. Because Oracle does not record Synonym/VIEW's data into Redo Log, Replicate has no way to capture their change data. This is also the default behavior in most RDBMS.

When it comes to your specific questions:

1- Repeat Full Load ONLY tasks can be used to replicate the view data

2- Synonym is invisible in Replicate GUI in current versions

3- Every view is based on one or several base table(s), we may still use regular CDC tasks to replicate all the base tables changes to target side (adding filters if possible to discard the useless records), and then build up the similar VIEWs in target database to get the wish data (certainly the target side DB must support VIEW as well)

Hope this helps.

Regards,

John.