Configuring Latest Timestamp Conflict Detection and Resolution

The ADD_AUTO_CDR procedure in the DBMS_GOLDENGATE_ADM package configures latest timestamp conflict detection and resolution. The ADD_AUTO_CDR_COLUMN_GROUP procedure adds optional column groups.

For Oracle Database 23c and higher, the ADD_AUTO_CDR procedure also provides the option to remove hidden columns. The ADD_AUTO_CDR procedure includes the flag REMOVE_HIDDEN_COLUMNS to drop tables or columns that are unused. To know more, see ADD_AUTO_CDR Procedure in the Oracle Database PL/SQL Packages and Types Reference

With latest timestamp conflict detection and resolution, a conflict is detected when the timestamp column of the row LCR does not match the timestamp of the corresponding table row. The row LCR is applied if its timestamp is later. Otherwise, the row LCR is discarded, and the table row is not changed. When you run the ADD_AUTO_CDR procedure, it adds an invisible timestamp column for each row in the specified table and configures timestamp conflict detection and resolution. When you use the ADD_AUTO_CDR_COLUMN_GROUP procedure to add one or more column groups, it adds a timestamp for the column group and configures timestamp conflict detection and resolution for the column group.

You can configure an Oracle GoldenGate administrator using the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_GOLDENGATE_ADM package.
  1. Connect to the inbound server database as a Oracle GoldenGate administrator.
  2. Run the ADD_AUTO_CDR procedure and specify the table to configure for latest timestamp conflict detection and resolution.
  3. Run the ADD_AUTO_CDR_COLUMN_GROUP procedure and specify one or more column groups in the table.
  4. Repeat the previous steps in each Oracle Database that replicates the table.

Example 7-9 Configuring the Latest Timestamp Conflict Detection and Resolution for a Table

This example configures latest timestamp conflict detection and resolution for the hr.employees table. 

BEGIN
  DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR(
    SCHEMA_NAME => 'HR',
    TABLE_NAME  => 'EMPLOYEES');
END;
/

Example 7-10 Configuring Column Groups

This example configures the following column groups for timestamp conflict resolution on the HR.EMPLOYEES table:

  • The JOB_IDENTIFIER_CG column group includes the JOB_ID, DEPARTMENT_ID, and MANAGER_ID columns.

  • The COMPENSATION_CG column group includes the SALARY and COMMISSION_PCT columns. 

BEGIN
  DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR_COLUMN_GROUP(
    SCHEMA_NAME       => 'HR',
    TABLE_NAME        => 'EMPLOYEES',
    COLUMN_LIST       => 'JOB_ID, DEPARTMENT_ID, MANAGER_ID',
    COLUMN_GROUP_NAME => 'JOB_IDENTIFIER_CG');
END;
/

BEGIN
  DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR_COLUMN_GROUP(
    SCHEMA_NAME       => 'HR',
    TABLE_NAME        => 'EMPLOYEES',
    COLUMN_LIST       => 'SALARY, COMMISSION_PCT',
    COLUMN_GROUP_NAME => 'COMPENSATION_CG');
END;
/