Qlik Replicate: Best Practices and Recovery Steps for Redo Log Retention and Usage

[narendersarva]

This article outlines recommended retention policies for Oracle redo logs and provides detailed recovery steps in scenarios where redo logs are needed. It includes guidance on preserving logs, identifying stream positions, verifying log availability, and using SCN-based recovery with SQL_REDO extraction for issue resolution.

Resolution

Generally, Qlik recommends a 24-hour retention period for redo logs, but this can vary depending on the storage capacity and backup policies of each organization, as they have different requirements.

When customers back up their redo logs, they typically retain backups for at least 2 weeks to 1 month. This allows them to restore the logs if necessary. The specific policy regarding the retention of redo logs in backup media should be clarified with the customer.

Steps to Take When Redo Logs Are Required for Recovery

1. Preserve the Redo Logs: When an incident occurs, it’s critical to preserve the redo logs as quickly as possible. This ensures that they can be replayed if necessary.
   
   
2. Identify the Stream Position: First, identify the stream position of the record that is failing. You can use the task log to locate the problematic record and its corresponding stream position.
   
   
3. Verify Redo Log Existence: Once you have the stream position, determine the corresponding redo log sequence number and thread number. Verify whether the redo log still exists on disk.
   
   If it exists on disk, copy it to a different filesystem to ensure it is not deleted by RMAN backups.
   
   If the redo log has already been deleted, you will need to restore it from backup media. The DBA can assist in restoring the file from backup.

Replay the Redo Logs

1. Locate the Redo Log File: Once the redo log is available, query the v$archived_log view to find where the redo log file is located. You can use the following SQL:
   

   SELECT name, sequence#, thread#, first_change#
   FROM v$archived_log
   WHERE sequence# = [sequence#] AND thread# = [thread#];

2. Get the SCN (System Change Number): Use the first_change# value from the query above as the starting point for the redo log recovery.
   
   
3. Create a New Task in Replication: In your replication setup, create a new task and add the affected table. In the advanced options, provide the SCN value from the first_change# column, which marks the point where the task should start.
   
   
4. Verify Reproduction: After setting the SCN, verify whether you can reproduce the issue. If you can, proceed with the next step.
   
   
5. Log Mining and SQL_REDO: Use standard log mining to extract the SQL_REDO statements from the redo logs. These can be found in the V$logmnr_contents view. The SQL_REDO statements will provide the actual SQL that was executed, which will allow you to check if all column data was included in the redo log. 

By following these steps, you can ensure proper recovery from redo logs and address any issues related to missing or inconsistent data.

Environment

• Qlik Replicate