Oracle GoldenGate Veridata – How it works?

My previous post was about the Automatic Repair Feature in Oracle GoldenGate Veridata. Hope it was informative. Below is the link,

https://www.oracle-scn.com/oracle-goldengate-veridata-automatic-repair-feature/

Oracle GoldenGate Veridata – Automatic Repair Feature

As you’re aware, Oracle GoldenGate Veridata is a tool used for real-time data comparison, equipped with an automatic repair feature. Yet, numerous users are eager to understand the mechanics behind this real-time comparison process. This post delves into the operational details of Oracle GoldenGate Veridata.

It is a two-step process for Oracle GoldenGate Veridata to compare the data in real-time. They are,

1. Initial step or Row hash step MOOS – Mostly Out-Of-Sync
2. COOS – Confirm Out-Of-Sync

Here’s a brief overview of what occurs.

Initial step or Row hash step MOOS – Mostly Out Of Sync

• Rows retrieved from source/target databases

• Data converted into standard format

• Hashes data and sent to Server

• Out-of-sync rows stored in MOOS queue

COOS – Confirm Out Of Sync

• Ensures accurate results by comparing each row

• It happens in parallel with Row hash with configured latency threshold

• Confirm out of Sync requests can be batched

• Good performance for remote databases

• Controlled by a profile setting

• Executing the look ups in parallel

Let us see in detail.

1. Initial step or Row hash step MOOS – Mostly Out-Of-Sync

This marks the initial stage of the comparison process. Once initiated, the Veridata agent retrieves rows from both source and target tables using a specified query. Oracle GoldenGate Veridata, being a heterogeneous data comparison and repair tool, ensures compatibility by standardizing data types if the source and target databases differ.

Data retrieval involves fetching primary key (PK) column values directly (actual values are fetched), while non-key columns are hashed to minimize network transfer for comparison. This unique hashing process streamlines the comparison process, providing a dependable and efficient means of determining similarities or differences between rows in the source and target databases.

Additionally, it’s possible to alter the default hashing option. Oracle GoldenGate Veridata allows you to set up fetching the actual values of Non-Key columns for comparison instead of relying solely on hashing. However, this adjustment may lead to decreased performance and increased network usage, particularly with a higher number of columns.

After completing the initial comparison, if Oracle GoldenGate Veridata detects discrepancies in some rows, it refrains from displaying the comparison results to the user. Instead, it stores these rows in the Maybe Out-Of-Sync (MOOS) queue in memory. This precaution is necessary due to concurrent real-time replication, which may have some replication latency, potentially leading to the Out-Of-Sync rows being in transit. Subsequently, replication will synchronize them again.

2. COOS – Confirm Out-Of-Sync

The verification process, known as confirmation or Confirm-Out-Of-Sync (COOS), guarantees precise outcomes by validating the status of rows in a dynamic setting. This process entails making conditional inquiries on either the source or target using the rows obtained from the MOOS queue, with the status being assessed as one of the subsequent possibilities:

in-flight:

The row was initially not synchronized during the comparison process, but it has now been corrected. In this scenario, it is presumed that replication or a similar mechanism implemented the modification; however, Oracle GoldenGate Veridata could not verify the synchronization of the rows.

in-sync:

The values from the source row were transferred to the target row either through replication or a similar process. Even if the status shows as “in-sync,” it doesn’t ensure that the rows are synchronized at any given moment, especially if the underlying tables are constantly changing. However, it does signify that replication is functioning properly.

persistently Out-Of-Sync:

Since the initial comparison step occurred, the row has remained unchanged, indicating it is likely out of sync.

By default, confirmation processing takes place concurrently with the initial comparison step, but the confirmation of each row is delayed until after a specified replication latency threshold has passed. For instance, if the latency is set to 60 seconds and an out-of-sync row is identified during the initial comparison step at 10:00, the confirmation step for that row is postponed until 10:01 to ensure replication can apply any pending changes. Once latency is considered, rows can be definitively marked as Out-Of-Sync and cataloged in one or more Out-Of-Sync reports.

Let’s delve into this scenario, where a user is comparing data of the Employee table in both the source and target databases. Upon initiating the comparison job, the process begins with the first step, known as the Initial Comparison step.

The Employee table contains a primary key column EMP_ID. As previously mentioned, Oracle GoldenGate Veridata retrieves the actual values of EMP_ID and hashes the values of other non-key columns. It’s important to note that there is an ongoing replication process in progress. If any data disparities occur, say some 10 rows being Out-Of-Sync, Oracle GoldenGate Veridata will not immediately confirm them as Out-Of-Sync.

Given the real-time replication setup and concurrent data replication, it’s possible that these 10 Out-Of-Sync rows have yet to be replicated to the target. This might be due to latency. So, Oracle GoldenGate Veridata waits for it. The Out-Of-Sync rows are stored in the MOOS queue in memory.

Reminder:

By default, confirmation processing takes place concurrently with the initial comparison step, but the confirmation of each row is delayed until after a specified replication latency threshold has passed. For instance, if the latency is set to 60 seconds and an Out-Of-Sync row is identified during the initial comparison step at 10:00, the confirmation step for that row is postponed until 10:01 to ensure replication can apply any pending changes. Once latency is considered, rows can be definitively marked as out-of-sync and cataloged in one or more out-of-sync reports.

After surpassing the latency threshold, the process moves to the second stage known as the Confirm-Out-Of-Sync step. Here, only the rows that are Out-Of-Sync are examined, without utilizing hashing. All values from both primary key and non-key columns are retrieved in their original form (actual values). The Out-Of-Sync rows are compared value by value. If the same data inconsistencies persist after this stage, these rows are confirmed as Out-Of-Sync. The user is then presented with details of the Out-Of-Sync rows for further action.

Now, the user can perform a repair of data.

Before concluding this article, there are a few things we need to understand.

The latency threshold is crucial in this context. In the previously mentioned scenario, it was established at 60 seconds, but it is adjustable manually. The Delay Confirm-Out-Of-Sync parameter within the Profile Configuration governs this threshold. Users are better acquainted with their replication environment and the daily latency they encounter. Hence, they can customize this parameter according to their requirements.

Delay Confirm-Out-Of-Sync By (seconds):

Delays the confirmation step by the specified number of seconds to account for replication lag. Delaying the confirmation step reduces the number of false Out-Of-Sync results that occur because an updated source value was not replicated fast enough.

Refer to the screenshot below for clarification.

We all know that, the Oracle GoldenGate Veridata comparison is a two-step process as follows:

1. Initial step or Row hash step MOOS – Mostly Out-Of-Sync
2. COOS – Confirm Out-Of-Sync

You see, it’s not always required to follow a two-step process. Users have the option to bypass it altogether, specifically skipping the second step known as “COOS – Confirm-Out-Of-Sync.”

The parameter Perform Confirm Out-Of-Sync Step in the Profile Configuration allows users to decide whether to execute or bypass the second step when comparing data in Oracle GoldenGate Veridata.

Perform Confirm-Out-Of-Sync Step:

Controls whether or not the confirmation step is performed. By default, it is performed. Clear the check box under Value to skip the confirmation step and only perform the initial (row hash) comparison. You might skip the confirmation step if, for example, activity on the source tables is quiesced or if replication is not continuously updating the target table(s).

Please refer to the screenshot below for visual clarification.

I hope this provides you with a comprehensive grasp of how Oracle GoldenGate Veridata operates.

Cheers 🙂