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 23ai and higher, additional methods exist to manage and maintain ACDR configured tables. You can retain the underlying AUTO-CDR-related columns as UNUSED columns or drop them immediately after calling the REMOVE_AUTO_CDR procedure.

If you apply the ADD_AUTO_CDR procedure to a table, then by default, its internal columns are marked as unused if AUTO_CDR is removed. After calling REMOVE_AUTO_CDR, the unused columns could be manually deleted at a later stage or can be immediately removed using some additional parameters. For details, see Removing Conflict Detection and Resolution From a Table.

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.

Connect to the inbound server database as a Oracle GoldenGate administrator.
Run the ADD_AUTO_CDR procedure and specify the table to configure for latest timestamp conflict detection and resolution.
Run the ADD_AUTO_CDR_COLUMN_GROUP procedure and specify one or more column groups in the table.
Repeat the previous steps in each Oracle Database that replicates the table.

Example 9-22 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 9-23 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;
/