Creating Oracle GoldenGate Veridata Comparison Jobs for Oracle GoldenGate

Have you ever wanted to understand how Oracle GoldenGate and Veridata are integrated? I did and searched long and hard for a summary description of how these two great products work together to satisfy data synchronisation needs and providing assurances through data validation that the replication (synchronisation) process is actually working.

This blog will provide a high level overview of each product and how to leverage replication configurations in comparison jobs. The Oracle Data Integration Blogs site has many posts describing the features and capabilities of these two products.

Let’s start with some basic information about the two Oracle products we will be discussing, Oracle GoldenGate and Oracle GoldenGate Veridata 

*NOTE: Documentation references provided are from the following releases:

• Oracle GoldenGate 19.1
• Oracle GoldenGate Veridata 12.2.1.4

What is Oracle GoldenGate?

Oracle GoldenGate is a comprehensive software package for real-time data integration and replication. It enables high availability solutions, real-time data integration, transactional change data capture, data replication, transformations, and verification between operational and analytical enterprise systems.

Using Oracle GoldenGate, you can move committed transactions across multiple systems in your enterprise. Oracle GoldenGate enables you to replicate data between Oracle databases to other supported heterogeneous database, and between heterogeneous databases.

About Oracle GoldenGate Veridata

Oracle GoldenGate Veridata compares a source (original, or trusted) set of data with a target (secondary) set of data, identifies data that is out-of-sync, and enables you to repair any out-of-sync data. Oracle GoldenGate Veridata supports high-volume, 24×7 heterogeneous replication environments where downtime to compare data sets is not an option. 

Environment Background

I have a simple testing environment configured, two identical schemas, a source extract, a target replicat and an instance of Veridata with its Agents.

Source Schema	CAR_STORE_SRC
Target Schema	CAR_STORE_TGT
Extract	SOURCE
Replicat	TARGET
Source Agent	snabd01_src
Target Agent	snabd01_tgt
Objects	7 Tables, 1 View

 

My requirement is to repicate and compare all the tables in the source and target with the exception of the Car and Model detail tables and views should not be compared.

      

GoldenGate Replication

Replication is configured to capture the source schema objects and exclude the CAR_DETAILS and MODEL_DETAILS tables using a wild card expression, nothing complex.

Source Parameter File

extract SOURCE
useridalias OGG_ADMIN domain OGG
exttrail dirdat/aa 

TABLE CAR_STORE_SRC.*;
TABLEEXCLUDE CAR_STORE_SRC.*_DETAILS;

Replicat Paramter File

replicat TARGET
useridalias OGG_ADMIN domain OGG

MAP CAR_STORE_SRC.*, TARGET CAR_STORE_TGT.*;

 

Integrating Oracle GoldenGate with Oracle GoldenGate Veridata

Oracle GoldenGate and Oracle GoldenGate Veridata are in essence two independant solutions, one providing fast, robout, secure heterogeneous replication and the other data verification through comparisons. Each can be deployed separately with no dependency of having the other. Although Veridata can be configured to verify Oracle GoldenGate replication sources and targets, accounting for its typical lag times, there is no communication path established between the two products.

The integration is provided as a utility that allows the configuration of the Veridata Comparison jobs using the replication configuration used by Oracle GoldenGate. To get a better understanding of the Oracle Veridata Workflow, check out the Configuring Workflow Objects section which describes:

• Connections
• Groups
• Compare Pairs
• and Jobs

Other details are provided but these are the main parts required to configure and run a comparison job. 

In my environment I have configured the connections and will have the command line utilities create the rest for me.

 

Processing Oracle GoldenGate Parameter Files

To process and configure Oracle GoldenGate Veridata we will use two command line utilities:

1. Veridata GoldenGate Parameter Processing (VGPP)
2. Veridata Import Utiliy

The Veridata GoldenGate Parameter Processing (VGPP)

Veridata GoldenGate Parameter Processing (VGPP) is a command-line tool that uses the map and table parameter of extract and replicat parameter files to create the Oracle GoldenGate Veridata configurations. This allows use to reuse Replicat and Extract configurations in Oracle GoldenGate Veridata and supports many but not all of the GoldenGate Parameters:

The Veridata GoldenGate Parameter Processing command-line utility accepts one or two parameter files as input. One of the files must be a Replicat parameter file, and the other optional file must be an Extract parameter file. The tool will output a file containing the generated scripting file in XML format.

Using the tool is fairly simple to use but requires a properties file along with its few options. 

1. Create a properties file
2. Copy the replication parameter files to the Veridata Server host

 

The properties file text is provided in the VGPP Example documentation section. It contains the details of the Veridata Source and Target Agent and the Connections used on each of those to retrieve the database objects.

source.connection.host	Name of the Host running the Source Agent
source.connection.name	Name of the SourceConnection in the Veridata Server
source.connection.port	Port the Source Agent is running on
source.connection.user	Name of the Source Database User to connect with
source.connection.password	Password of the supplied SourceDatabase User
target.connection.host	Name of the Host running the Target Agent
target.connection.name	Name of the Target Connection in the Veridata Server
target.connection.port	Port the Target Agent is running on
target.connection.user	Name of the Target Database User to connect with
target.connection.password	Password of the supplied Target Database User

 

Sample properties file

source.connection.host=localhost
source.connection.name=SOURCE_CONN
source.connection.port=7850
source.connection.user=CAR_STORE_SRC
source.connection.password=aBcd34ç
target.connection.host=localhost
target.connection.name=TARGET_CONN
target.connection.port=7851
target.connection.user=CAR_STORE_TGT
target.connection.password=h1Jk|mn0

Running VGPP – see the VGPP Example

The utlity is located in the deployed Veridata domain, under the veridata/bin directory. 

command: ./veridata_param_process.sh -noscripting -wluser <Veridata_User> ./<EXTRACT_PARAM_FILE> ./<REPLICAT_PARAM_FILE> -p <PROPERTIES_FILE> -o <CONFIGURATIONS_OUTPUT_FILE>

example: ./veridata_param_process.sh -noscripting -wluser sydney ./srce.prm ./tgtr.prm -p my_veridata_conn_props.txt -o my_vgpp_compare_pair_config.xml

Reviewing the Generated Configuration File

*********
The generate configuration file in XML 
( my_vgpp_compare_pair_config.xml )
*********
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration SYSTEM "configuration.dtd">
<!--
Hostname : oggveridata01
OGGV-30003: Extract filename : ./srce.prm
OGGV-30002: Replicat filename : ./tgtr.prm
   August 3, 2022 3:07:51 PM GMT
-->
<configuration wildcard="ggs" operation="create" validation="required">
  <connection name="SOURCE_CONN" host="localhost" port="7850" user="CAR_STORE_SRC" password="aBcd34ç"></connection>
  <connection name="TARGET_CONN" host="localhost" port="7851" user="CAR_STORE_TGT" password="h1Jk|mn0"></connection>
  <group name="SOURCE_TARGET" source-conn="SOURCE_CONN" target-conn="TARGET_CONN">
    <filter type="include" schema="CAR_STORE_SRC" table="*"></filter>
    <filter type="exclude" schema="CAR_STORE_SRC" table="*_DETAILS"></filter>
    <compare-pair source-schema="CAR_STORE_SRC" source-table="*" target-schema="CAR_STORE_TGT" target-table="*"></compare-pair>
  </group>
  <job name="SOURCE_TARGET">
    <group name="SOURCE_TARGET"></group>
  </job>
</configuration>
*********

In the above text I’ve highlighted the values that I provided in the example and where there are now in the generated file. The parsed mapping section of the parameter files are used to create the filter include and exclude sections.

In the Web UI you also have the option to exclude Views, which as you recall in my requirements, I do not wich to compare the views but this is not in my configuration file. In order to exclude views, I would have two options:

1. EXCLUDE every View in my Parameter files
2. Use the exclude-views optional filter criteria

 

I will use the second to ensure I catch any and all views in the database schema. To use this option, two methods are available

1. Add it at the Group level, which would apply to the entire Group
2. Add it to the filter for the schema that will be included

 

At the Group level use <filter exclude-views=”true”/> and place this after the opening group element, like so:

  <group name=”SOURCE_TARGET” source-conn=”SOURCE_CONN” target-conn=”TARGET_CONN“>
    <filter exclude-views=”true”/>

I will add it to the filter include line and set it to true.

BEFORE	<filter type=”include” schema=”CAR_STORE_SRC” table=”*”></filter>
AFTER	<filter type=”include” schema=”CAR_STORE_SRC” table=”*” exclude-views=”true”></filter>

 

Now I can run the import of the configuration but before I do that I would like to make some recommendations to the generated file.

Firstly, you will notice that the naming convention for the compare group and job names are a concatenation of the extract and replicat. I have changed mine to include the suffixes of _GROUP and _JOB for the compare group and compare job respectively. I have used _COMPARE_GROUP and _COMPARE_JOB and the resuling configuration file now looks as follows:

*********
My Edited configuration file in XML 
( my_vgpp_compare_pair_config.xml )
*********
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration SYSTEM "configuration.dtd">
<!--
Hostname : oggveridata01
OGGV-30003: Extract filename : ./srce.prm
OGGV-30002: Replicat filename : ./tgtr.prm
   August 3, 2022 3:07:51 PM GMT
-->
<configuration wildcard="ggs" operation="create" validation="required">
  <connection name="SOURCE_CONN" host="localhost" port="7850" user="CAR_STORE_SRC" password="aBcd34ç"></connection>
  <connection name="TARGET_CONN" host="localhost" port="7851" user="CAR_STORE_TGT" password="h1Jk|mn0"></connection>
  <group name="SOURCE_CONN_TARGET_CONN_COMPARE_GROUP" source-conn="SOURCE_CONN" target-conn="TARGET_CONN">
    <filter type="include" schema="CAR_STORE_SRC" table="*" exclude-views="true"></filter>
    <filter type="exclude" schema="CAR_STORE_SRC" table="*_DETAILS"></filter>
    <compare-pair source-schema="CAR_STORE_SRC" source-table="*" target-schema="CAR_STORE_TGT" target-table="*"></compare-pair>
  </group>
  <job name="SOURCE_CONN_TARGET_CONN_COMPARE_JOB">
    <group name="SOURCE_CONN_TARGET_CONN_COMPARE_GROUP"></group>
  </job>
</configuration>
*********

Using the Import Utility

The import utility allows you to configure database connections, comparison groups including compare pairs, comparison jobs, and profiles. It takes an XML document as input then creates comparison objects in Veridata. Typically, the XML document matches the inputs on the configuration pages in the user interface.

Running Import Utility

The import utility is also fairly simply to use and only has a few options. Unless you will be deleting items from the Veridata Server configuration, I recommend to use the update option as it will update any existing items and add all new items that are not found in the repository.

command: ./veridata_import.sh -wluser <Veridata_User> -update <CONFIGURATIONS_OUTPUT_FILE>

example: ./veridata_import.sh -wluser sydney -update my_vgpp_compare_pair_config.xml

Since we will be connecting to the Veridata Server, you will be prompted to supply the user password. The user must have the appropriate Veridata roles. * By default the weblogic user, does not have Veridata Roles assigned.

You can now verify the configurations that have been imported into the Veridata Server:

• Connections – Exist or have been update

  

• Connections – Test Connection should be successful
• Group – Exist and configured with Connections and has expected Compare Pairs

• Job – Exist and linked to imported Group

Conclusion & General Recommendations

The integration of Oracle GoldenGate and Oracle GoldenGate Veridata is a simple one and uses two Veridata command line utilties to parse the GoldenGate replication setup in its parameter files, generating a configuration XML document that is then imported into Veridata.

Remember that the naming convetion used by VGPP concatenates the name of the Extract and Replicat as both the Group and Compare Job. Edit the generated file before using the import tool will provide better readability inside of Veridata.

Some other recommendations are to consider the amount of schemas and data that would need to be compared. If multiple schemas are being captured by Oracle GoldenGate, I would suggest to create a separate set of Groups and Jobs, to enable each to be excuted independantly and provide flexible run options. 

The Sample Configuration File documentation provides a good example of multiple items in a single configuration file, groups, compare pairs, and jobs can be included in a single file. To keep it simple, I have recommended to keep configuration file unique to the group/compar pair and job that will be managed in Veridata as these documents can get very big and difficult to read.

Lastly, after import, review and test all of the imported items prior to using them.

 

Hope this information is useful to you and if you do find it useful or have additonal comments or suggestions, please let me know.

Regards

Sydney