1. Veridata 23c (23.1.0.0.0)
  2. Manage
  3. Jobs
  4. Repairing Out-Of-Sync Jobs

5.4.8 Repairing Out-Of-Sync Jobs

To repair Out-of-Sync Comparisons

Click Monitor Jobs in the navigation pane, and click the Completed Jobs Tab.

Click a job name in the list to view the Job Summary.
  • Click More Actions and click Repair.
  • To repair an individual compare pair, click the compare pair name in the list to open the Compare Pair Details page, and click Repair.
Oracle GoldenGate Veridata allows repair of duplicate records for the tables that do not have Primary Key's in the following databases: Oracle, MySQL and MSSQL. The Details for Out-Of-Sync page is displayed.

Note:

If the table contains duplicate records, then it is suggested to use System Generated Mapping in Compare-Pair. If you decide to use User-defined mapping then, ensure that the user-defined column mappings have unique dataset. If the row has Skipped status icon, then the row is ignored from Repair.
The Repair Jobs page displays a summary of all repair jobs. You can use the Filters on this page to display older repair jobs and to filter repair jobs by repair status and job name. Following are the possible repair statuses:
  • Pending
  • Running
  • Out-of-Sync
  • Successful
  • Failed
  • Warning
  • Cancelled
  • Downloading SQL Statements for Out-of-Sync Records
    Oracle GoldenGate Veridata provides the Download Repair SQL functionality to view the SQL Queries generated as part of Veridata Repair. The Download SQL Queries is enabled only when either Oracle or SQL Server is used as the target database.

Parent topic: Jobs

5.4.8.1 Downloading SQL Statements for Out-of-Sync Records

Oracle GoldenGate Veridata provides the Download Repair SQL functionality to view the SQL Queries generated as part of Veridata Repair. The Download SQL Queries is enabled only when either Oracle or SQL Server is used as the target database.

You can look at these SQL Statements before the Oracle GoldenGate Veridata executes them onto the target database, or execute these SQL statements by yourself on any of the other database tools. With the Download Repair SQL functionality, you can download the SQL statements for all your out-of-sync-records, and can also execute them at your convenience. To download SQL queries:
  1. From the Finished Jobs page, select the out-of-sync comparisons.
    You can select Jobs, groups, and compare pairs for downloading SQL statements.
  2. To download SQL queries for out-of-sync data, select the job from the finished jobs and click Download Repair SQL.
    The Download Repair SQL button is enabled only for users who have access to the repair access functionality.
    The SQL files are downloaded to the browser where the application is being used.

    When you click Download Repair SQL, the file gets downloaded to the browser.

If the record is already repaired, then the check box against the record is replaced with date and time of repair. Those records are not available for downloading SQL statements.

Parent topic: Repairing Out-Of-Sync Jobs

5.4.8.1.1 Executing the SQL File

To execute the SQL file:
  1. When you click Download Repair SQL, a SQL file is generated. Copy this zip to the target system. In order to enable access to target Oracle DB, run the following query on the target database. This is a one-time operation only:
    CREATE OR REPLACE DIRECTORY VDT_LOB_DIR AS '<path of lob files>';
    For example:
    CREATE OR REPLACE DIRECTORY VDT_LOB_DIR AS '/scratch/lobs';.
    Here:
    /scratch/lobs is the path on the target system where the generated SQL files and LOB files should be copied. When the SQL scripts are executed from the DB prompt, the LOB files are read from /scratch/lobs
  2. Unzip the zip file.
    LOB files are generated only when the table has BLOB datatypes and huge data for CLOB/NCLOB.
    The directories are created as follows:
    <JobName><Timestamp>
    • <GroupName>
      • SQL file (format – <ComparePairName>.sql)
      • LOB file (format - <TableName>_<ColumnName>_<Timestamp>.lob)
    • <Group2>
      • SQL file
      • LOB files

    o SQL file (format – <ComparePairName>.sql)

    o LOB file (format - <TableName>_<ColumnName>_<Timestamp>.lob)

  3. Copy the SQL and LOB files to VDT_LOB_DIR. However, it is not mandatory to copy the sql file to VDT_LOB_DIR. But, the LOB files are required in VDT_LOB_DIR.
  4. If the data has any multibyte characters, then ensure that the NLS_LANG environment variable is set before launching the DB Terminal.
    On Linux:
    1. Bourne/Bash Shell
      • export NLS_LANG=<NLS_LANGUAGE>_<NLS_TERRITORY>.<NLS_CHARACTERSET>.

        For example, export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

    2. C/TCSH Shell
      • setenv NLS_LANG <NLS_LANGUAGE>_<NLS_TERRITORY>.<NLS_CHARACTERSET>.

        For example, setenv NLS_LANG AMERICAN_AMERICA.AL32UTF8

    On Windows:
    • Set the NLS_LANG in System Variable. Value should be <NLS_LANGUAGE>_<NLS_TERRITORY>.<NLS_CHARACTERSET>.

      For example: AMERICAN_AMERICA.AL32UTF8

    Note: Launch the DB terminal after NLS_LANG is set.
  5. Login to DB terminal and execute the SQL file: SQL> @<sql file>. For example: SQL> @VeridataRepair.sql. If prompted, enter the value of NLS_CHARACTERSET.

    Note:

    If the SQL file is in a different location than DB terminal is open, then ensure to include the complete path of the SQL file while executing it. For example, SQL> @/Home/User/filename.sql.

Parent topic: Downloading SQL Statements for Out-of-Sync Records