You can configure automatic conflict detection and resolution in an Oracle GoldenGate configuration that replicates tables between Oracle Databases. To configure conflict detection and resolution for a table, call the ADD_AUTO_CDR procedure in the DBMS_GOLDENGATE_ADM package. These are administration APIs, which are expected to be called by an Oracle GoldenGate administrator who is granted privileges through the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE. This user needs to have privileges to modify the affected table. These APIs result in the version number being bumped for the object and also lock the object due to DDL execution.

When Oracle GoldenGate captures changes that originated at an Oracle Database, each change is encapsulated in a row logical change record (LCR). A row LCR is a structured representation of a DML row change. Each row LCR includes the operation type, old column values, and new column values. Multiple row LCRs can be part of a single database transaction.

When more than one replica of a table allows changes to the table, a conflict can occur when a change is made to the same row in two different databases at nearly the same time. Oracle GoldenGate replicates changes using the row LCRs. It detects a conflict by comparing the old values in the row LCR for the initial change from the origin database with the current values of the corresponding table row at the destination database identified by the key columns. If any column value does not match, then there is a conflict.

After a conflict is detected, Oracle GoldenGate can resolve the conflict by overwriting values in the row with some values from the row LCR, ignoring the values in the row LCR, or computing a delta to update the row values.

Automatic conflict detection and resolution does not require application changes for the following reasons:

• Oracle Database automatically creates and maintains invisible timestamp columns.

• Inserts, updates, and deletes use the delete tombstone log table to determine if a row was deleted.

• LOB column conflicts can be detected.

• Oracle Database automatically configures supplemental logging on required columns.

Supplemental logging is required to ensure that each row LCR has the information required to detect and resolve a conflict. Supplemental logging places additional information in the redo log for the columns of a table when a DML operation is performed on the table. When you configure a table for Oracle GoldenGate conflict detection and resolution, supplemental logging is configured automatically for all of the columns in the table. The additional information in the redo log is placed in an LCR when a table change is replicated.

Extract must be used for capturing. Integrated Replicat or parallel Replicat in integrated mode must be used on the apply side. LOGALLSUPCOLS should remain the default.

There is a hidden field KEYVER$$ of type timestamp that is optionally added to the DELETE TOMBSTONE table. This field is required for EARLIEST TIMESTAMP, DELETE ALWAYS WINS, and SITE PRIORITY resolution and it also exists in the base table. The existence of the field in the base table needs to be provided in the trail file metadata as a flag or token.

Primary Key updates is also supported in the DELETE TOMBSTONE table. An entry is inserted into the DELETE TOMBSTONE table for the row of the original key value (before image). The logic in the Extract which matches inserts in the DELETE TOMBSTONE table to deletes also needs to be matched to PK updates, or unique key (UK) with at least one non-nullable field, if there is no PK.

Site priority needs support from the Replicat, both the parameters are implemented and the setting is passed to the apply.

A column group is a logical grouping of one or more columns in a replicated table. When you add a column group, conflict detection and resolution is performed on the columns in the column group separately from the other columns in the table.

When you configure a table for Oracle GoldenGate conflict detection and resolution with the ADD_AUTO_CDR procedure, all of the scalar columns in the table are added to a default column group. To define other column groups for the table, run the ADD_AUTO_CDR_COLUMN_GROUP procedure. Any columns in the table that are not part of a user-defined column group remain in the default column group for the table.

Column groups enable different databases to update different columns in the same row at nearly the same time without causing a conflict. When column groups are configured for a table, conflicts can be avoided even if different databases update the same row in the table. A conflict is not detected if the updates change the values of columns in different column groups.

Description of the illustration xstrm_3v_086a.png

This example shows a row being replicated at database A and database B. The following two column groups are configured for the replicated table at each database:

• One column group includes the Office column. The invisible timestamp column for this column group is TS1.

• Another column group includes the Title and Salary columns. The invisible timestamp column for this column group is TS2.

These column groups enable database A and database B to update the same row at nearly the same time without causing a conflict. Specifically, the following changes are made:

• At database A, the value of Office was changed from 1080 to 1030.

• At database B, the value of Title was changed from MTS1 to MTS2.

Because the Office column and the Title column are in different column groups, the changes are replicated without a conflict being detected. The result is that values in the row are same at both databases after each change has been replicated.

DELETE TOMBSTONE table is a marker for a deleted record to distinguish it from a record, which never existed. A DELETE TOMBSTONE table contains at minimum the key columns and operation timestamp. This information is required for delete convergence becasue some incoming updates and inserts may be delayed from another site and the incoming LCR needs to be filtered against the tombstone operation timestamp to determine whether it should be applied.

Columns with names of the form CDRTS$ column group and CDRTS$ROW are used to contain timestamps that reflect modification times for column groups and the row. The DBMS_GOLDENGATE_ADM procedures ADD_AUTO_CDR(), ADD_AUTO_CDR_COLUMN_GROUP(), REMOVE_AUTO_CDR(), REMOVE_AUTO_CDR_COLUMN_GROUP(), ALTER_AUTO_CDR(), and ALTER_AUTO_CDR_COLUMN_GROUP() are presently used to configure ACDR with latest timestamp resolution. They are also used in configuration of ACDR with earliest timestamp resolution, The field ADDITIONAL_OPTIONS in both ADD_AUTO_CDR() and ALTER_AUTO_CDR() turn on the use of earliest timestamp. Turning on earliest timestamp automatically turn on versioning, which adds a new hidden column KEYVER$$ (version number) of type timestamp. A new flag value is added to acdrflags_kqldtvc to indicate earliest timestamp usage. This field is also added to the DELETE TOMBSTONE table. Delete conflicts are the reason that version number is needed. With an earliest timestamp resolution, delete conflicts, which can be transparent, might not only incorrectly succeed, they might prevent new inserts of the row (new versions). With a version timestamp, the delete can be correctly resolved against a row DML for the same row version.

The original insert of the row receives the current timestamp from its default value. The delete of this row then inserts the version number and the time when this row was inserted, into the tombstone table when there is a delete. On a new insert, by default, the version number receives the current timestamp again, thereby avoiding a false conflict with the present delete entries in the tombstone table.

When you run the ADD_AUTO_CDR procedure in the DBMS_GOLDENGATE_ADM package to configure a table for automatic Oracle GoldenGate conflict detection and resolution, a hidden timestamp column is added to the table. This hidden timestamp column records the time of a row change, and this information is used to detect and resolve conflicts.

When a row LCR is applied, a conflict can occur for an INSERT, UPDATE, or DELETE operation. The following table describes each type of conflict and how it is resolved.

DELETE ALWAYS WINS is enabled through the field ADDITIONAL_OPTIONS in both DBMS_GOLDENGATE_ADM procedures ADD_AUTO_CDR() and ALTER_AUTO_CDR(). This is again a delete conflict resolution method, which is not using latest timestamp resolution, therefore, versioning is needed. Turning on DELETE ALWAYS WINS automatically turns on versioning, which adds a new hidden column KEYVER$$ (version number) of type timestamp. A new flag value is also added to acdrflags_kqldtvc to indicate DELETE ALWAYS WINS usage. This field is also added to the DELETE TOMBSTONE table. The same versioning issues exist as the EARLIEST TIMESTAMP resolution.

With delta conflict detection, a conflict occurs when a value in the old column list of the row LCR differs from the value for the corresponding row in the table.

To configure delta conflict detection and resolution for a table, run the ADD_AUTO_CDR_DELTA_RES procedure in the DBMS_GOLDENGATE_ADM package. The delta resolution method does not depend on a timestamp or an extra resolution column. With delta conflict resolution, the conflict is resolved by adding the difference between the new and old values in the row LCR to the value in the table. This resolution method is generally used for financial data such as an account balance. For example, if a bank balance is updated at two sites concurrently, then the converged value accounts for all debits and credits.

Description of the illustration xstrm_3v_086a.png

This example shows a row being replicated at database A and database B. The Balance column is designated as the column on which delta conflict resolution is performed, and the TS1 column is the invisible timestamp column to track the time of each change to the Balance column. A change is made to the Balance value in the row in both databases at nearly the same time (@T20 in database A and @T22 in database B). These changes result in a conflict, and delta conflict resolution is used to resolve the conflict in the following way:

• At database A, the value of Balance was changed from 100 to 110. Therefore, the value was increased by 10.

• At database B, the value of Balance was changed from 100 to 120. Therefore, the value was increased by 20.

• To resolve the conflict at database A, the value of the difference between the new and old values in the row LCR to the value in the table. The difference between the new and old values in the LCR is 20 (120–100=20). Therefore, the current value in the table (110) is increased by 20 so that the value after conflict resolution is 130.

• To resolve the conflict at database B, the value of the difference between the new and old values in the row LCR to the value in the table. The difference between the new and old values in the LCR is 10 (110–100=10). Therefore, the current value in the table (120) is increased by 10 so that the value after conflict resolution is 130.

After delta conflict resolution, the value of the Balance column is the same for the row at database A and database B.

Priority resolution specified in Replicat parameter file between source and target in conflict resolution.

SITE PRIORITY is turned on for a database or PDB in the Replicat parameter file with the parameter ACDR SITE_PRIORITY {source_db_name}{OVERWRITE | IGNORE } specified to turn on SITE PRIORITY resolution for a table. If OVERWRITE is specified, then the source table has priority and conflicts are resolved by OVERWRITE. Conversely, if IGNORE is specified, then the target table has priority and source table change are ignored in a conflict. SITE PRIORITY resolution can be turned off by the field ADDITIONAL_OPTIONS in both DBMS_GOLDENGATE_ADM procedure ADD_AUTO_CDR() and ALTER_AUTO_CDR() setting the bit IGNORE_SITE_PRIORITY. Every Replicat source-target relationship can be set up differently, therefore, convergence is dependent on user setup.

Full support of primary key (PK) updates requires handling conflicts on both the rows represented by the before image of the key and the row represented by the after image of the key. A PK update is an autonomous delete and insert, so, the PK update conflicts must be supported as a delete for conflicts with the before image of the key and inserts with the after image of the key (and row).

Supporting the PK update as a delete of the row represented by the before image of the key means that it should insert into the delete tombstone table as a delete. An update internal trigger is added to insert into the tombstone table when the PK is updated (actually the row identifying key, either the PK if it exists or the chosen UK with at least one non-nullable column). As a PK update may lead to two conflicts, up to two resolutions are attempted at the row level, delete of the row with the original PK and the insert of the row with the new PK.

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. Optional: 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.

BEGIN
  DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR(
    schema_name => 'hr',
    table_name  => 'employees');
END;
/

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;
/

1. Connect to the inbound server database as an 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_DELTA_RES procedure and specify the column on which delta conflict detection and resolution is performed.
4. Repeat the previous steps in each Oracle Database that replicates the table.

BEGIN
  DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR(
    SCHEMA_NAME => 'OE',
    TABLE_NAME  => 'ORDERS');
END;
/

BEGIN
  DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR_DELTA_RES(
    SCHEMA_NAME => 'OE',
    TABLE_NAME  => 'ORDERS',
    COLUMN_NAME => 'ORDER_TOTAL');
END;
/

1. Connect to the inbound server database as the Oracle GoldenGate administrator.
2. Run the ALTER_AUTO_CDR procedure and specify the table to configure for latest timestamp conflict detection and resolution.
3. Repeat all of the previous steps in each Oracle Database that replicates the table.

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

1. Connect to the inbound server database as an Oracle GoldenGate administrator.
2. Run the ALTER_AUTO_CDR_COLUMN_GROUP procedure and specify one or more column groups in the table.
3. Repeat all of the previous steps in each Oracle Database that replicates the table.

BEGIN
  DBMS_GOLDENGATE_ADM.ALTER_AUTO_CDR_COLUMN_GROUP(
    SCHEMA_NAME        => 'HR',
    TABLE_NAME         => 'EMPLOYEES',
    COLUMN_GROUP_NAME  => 'JOB_IDENTIFIER_CG',
    REMOVE_COLUMN_LIST => 'MANAGER_ID');
END;
/

1. Connect to the inbound server database as an Oracle GoldenGate administrator.
2. Run the PURGE_TOMBSTONES procedure and specify the date and time.

EXEC DBMS_GOLDENGATE_ADM.PURGE_TOMBSTONES('2015-12-01 15:00:00.000000 EST');

1. Connect to the inbound server database as an Oracle GoldenGate administrator.
2. Run the REMOVE_AUTO_CDR procedure and specify the table.
3. Repeat all of the previous steps in each Oracle Database that replicates the table.

BEGIN
  DBMS_GOLDENGATE_ADM.REMOVE_AUTO_CDR(
    schema_name => 'hr',
    table_name  => 'employees');
END;
/

1. Connect to the inbound server database as an Oracle GoldenGate administrator.
2. Run the REMOVE_AUTO_CDR_COLUMN_GROUP procedure and specify the name of the column group.
3. Repeat all of the previous steps in each Oracle Database that replicates the table.

BEGIN
  DBMS_GOLDENGATE_ADM.REMOVE_AUTO_CDR_COLUMN_GROUP(
    schema_name       => 'hr',
    table_name        => 'employees',
    column_group_name => 'compensation_cg');
END;
/

1. Connect to the inbound server database as an Oracle GoldenGate administrator.
2. Run the REMOVE_AUTO_CDR_DELTA_RES procedure and specify the column.
3. Repeat all of the previous steps in each Oracle Database that replicates the table.

BEGIN
  DBMS_GOLDENGATE_ADM.REMOVE_AUTO_CDR_DELTA_RES(
    SCHEMA_NAME => 'OE',
    TABLE_NAME  => 'ORDERS',
    COLUMN_NAME => 'ORDER_TOTAL');
END;
/

1. Connect to the database.
2. Query the ALL_GG_AUTO_CDR_TABLES view.

COLUMN TABLE_OWNER FORMAT A15
COLUMN TABLE_NAME FORMAT A15
COLUMN TOMBSTONE_TABLE FORMAT A15
COLUMN ROW_RESOLUTION_COLUMN FORMAT A25

SELECT TABLE_OWNER,
       TABLE_NAME, 
       TOMBSTONE_TABLE,
       ROW_RESOLUTION_COLUMN 
  FROM ALL_GG_AUTO_CDR_TABLES
  ORDER BY TABLE_OWNER, TABLE_NAME;

TABLE_OWNER     TABLE_NAME      TOMBSTONE_TABLE ROW_RESOLUTION_COLUMN
--------------- --------------- --------------- -------------------------
HR              EMPLOYEES       DT$_EMPLOYEES   CDRTS$ROW
OE              ORDERS          DT$_ORDERS      CDRTS$ROW

1. Connect to the database as an Oracle GoldenGate administrator.
2. Query the ALL_GG_AUTO_CDR_COLUMNS view.

COLUMN TABLE_OWNER FORMAT A10
COLUMN TABLE_NAME FORMAT A10
COLUMN COLUMN_GROUP_NAME FORMAT A17
COLUMN COLUMN_NAME FORMAT A15
COLUMN RESOLUTION_COLUMN FORMAT A23

SELECT TABLE_OWNER,
       TABLE_NAME, 
       COLUMN_GROUP_NAME,
       COLUMN_NAME,
       RESOLUTION_COLUMN 
  FROM ALL_GG_AUTO_CDR_COLUMNS
  ORDER BY TABLE_OWNER, TABLE_NAME;

TABLE_OWNE TABLE_NAME COLUMN_GROUP_NAME COLUMN_NAME     RESOLUTION_COLUMN
---------- ---------- ----------------- --------------- -----------------------
HR         EMPLOYEES  COMPENSATION_CG   COMMISSION_PCT  CDRTS$COMPENSATION_CG
HR         EMPLOYEES  COMPENSATION_CG   SALARY          CDRTS$COMPENSATION_CG
HR         EMPLOYEES  JOB_IDENTIFIER_CG MANAGER_ID      CDRTS$JOB_IDENTIFIER_CG
HR         EMPLOYEES  JOB_IDENTIFIER_CG JOB_ID          CDRTS$JOB_IDENTIFIER_CG
HR         EMPLOYEES  JOB_IDENTIFIER_CG DEPARTMENT_ID   CDRTS$JOB_IDENTIFIER_CG
HR         EMPLOYEES  IMPLICIT_COLUMNS$ PHONE_NUMBER    CDRTS$ROW
HR         EMPLOYEES  IMPLICIT_COLUMNS$ LAST_NAME       CDRTS$ROW
HR         EMPLOYEES  IMPLICIT_COLUMNS$ HIRE_DATE       CDRTS$ROW
HR         EMPLOYEES  IMPLICIT_COLUMNS$ FIRST_NAME      CDRTS$ROW
HR         EMPLOYEES  IMPLICIT_COLUMNS$ EMAIL           CDRTS$ROW
HR         EMPLOYEES  IMPLICIT_COLUMNS$ EMPLOYEE_ID     CDRTS$ROW
OE         ORDERS     IMPLICIT_COLUMNS$ ORDER_MODE      CDRTS$ROW
OE         ORDERS     IMPLICIT_COLUMNS$ ORDER_ID        CDRTS$ROW
OE         ORDERS     IMPLICIT_COLUMNS$ ORDER_DATE      CDRTS$ROW
OE         ORDERS     IMPLICIT_COLUMNS$ CUSTOMER_ID     CDRTS$ROW
OE         ORDERS     DELTA$            ORDER_TOTAL
OE         ORDERS     IMPLICIT_COLUMNS$ PROMOTION_ID    CDRTS$ROW
OE         ORDERS     IMPLICIT_COLUMNS$ ORDER_STATUS    CDRTS$ROW
OE         ORDERS     IMPLICIT_COLUMNS$ SALES_REP_ID    CDRTS$ROW

1. Connect to the database as an Oracle GoldenGate administrator.
2. Query the ALL_GG_AUTO_CDR_COLUMN_GROUPS view.

COLUMN TABLE_OWNER FORMAT A15
COLUMN TABLE_NAME FORMAT A15
COLUMN COLUMN_GROUP_NAME FORMAT A20
COLUMN RESOLUTION_COLUMN FORMAT A25

SELECT TABLE_OWNER,
       TABLE_NAME, 
       COLUMN_GROUP_NAME,
       RESOLUTION_COLUMN 
  FROM ALL_GG_AUTO_CDR_COLUMN_GROUPS
  ORDER BY TABLE_OWNER, TABLE_NAME;

TABLE_OWNER     TABLE_NAME      COLUMN_GROUP_NAME    RESOLUTION_COLUMN
--------------- --------------- -------------------- -------------------------
HR              EMPLOYEES       COMPENSATION_CG      CDRTS$COMPENSATION_CG
HR              EMPLOYEES       JOB_IDENTIFIER_CG    CDRTS$JOB_IDENTIFIER_CG