Oracle Redo Logs Limited Duration - Help Required

[vinayakct]

Hi Community!

I have a use-case to work on where the source endpoint that we are using is Oracle & DB2, the issue that I am facing is that the Redo logs and Archive logs both are available only for 1 hour.
Considering that we cannot ask Oracle Database Administrator team to change the duration, Can someone suggest possible approaches on how to proceed with Full and CDC loads for it?
Please consider the possibility that we might lose on some data from the redo logs, so what should be the approach in that case?

This is urgent, so any help would be appreciated.

Thanks!

[john_wang]

Hello @vinayakct ,

Welcome to Qlik Community forum and thanks for reaching out here!

First of all, there is no way to move DB2 transaction log position as the retrieving changes API(s) does not support it. It's different in Oracle, there are several available options for Oracle to add/move the redo log files locations:

1- Adding a more redo log storage (DEST_ID), or

2- Make a duplicate copy to a folder in Oracle server (without more DEST_ID), or

3- Copy the redo log files to a folder in Qlik Replicate server and let Replicate to process the redo log file directly

this can be done adding an additional destination ID in Oracle database (which may keep the redo log file longer time eg 3 days), or by using 3rd party software or OS commands to copy the files, then make the corresponding config in Oracle source endpoint.

Please mind that the approach will be out of Qlik Replicate because if the latency longer than 1 hour then the redo log file will be lost. The 3rd party tools/shell scripts will guarantee the redo log files immediate movement regardless latency in Qlik Replicate.

I suggest you consider working with our Professional Services team (fee based) for the right solution for the appropriate configuration in your environment. Please reach out to your Account Manager to initiate this engagement if necessary.

Hope this helps.

John.

[vinayakct]

Hi @john_wang ,

Thanks for your prompt response, understanding from the solution which you mentioned, once we duplicate / move the redo log files to a different location in Oracle Server or in Qlik Replicate Server then we can continue with our data movement process.

Also as suggested in your approach, I believe following the above methods would eliminate the case where data might be lost. Would this understanding be correct?

[john_wang]

Hello @vinayakct ,

Yes, you are correct.

Thank you for your great support!

John.

[john_wang · Accepted solution]

Hello @vinayakct ,

I'm pasting more detailed description about the transaction log files position, I wrote it in another article thread.

While Qlik Replicate capturing changes from database transaction log files (TLOG in SQL Server, Redo LOG in Oracle, Journal in DB2i, BinLog in MySQL, OPLOG in MongoDB ...), in general there are 2 typical technologies:

1- Via calling the database itself APIs to get changes from transaction logs. For example in Oracle source endpoint, the Oracle LogMiner method; The default method in DB2 LUW , and SQL Server.

    IN this method, the transaction log files location are determined by the database itself, unless the folder can be changed within the database management.

    For DB2 LUW source endpoint, db2ReadLog API function is used to retrieve the transaction logs. I'm afraid there is no way to change the transaction log locations by manual, or 3rd party tools eg Commvault (backup tool), at least there is no input parameters in the API to specify the alternative transaction log files location.

2- Qlik Replicate unique technology rather than the database built-in methods

    In this method, Qlik Replicate capture changes by read the transaction log files directly, the transaction log files location can be different than the database transaction log files preconfigured path. Only a few of source endpoint support this method, eg Oracle source endpoint Replicate Log Reader, Alternate backup folder in SQL Server. 

Hope this helps.

John.

[SushilKumar]

Hello @vinayakct 

Or you can ask to another Destination id in oracle and exclude that from the backup and you can set the archive retention as per your choice as well. 

Regards,

Sushil Kumar