DBMS_GOLDENGATE_ADM Package

Quickly enable active/active replication while ensuring consistent conflict-detection-resolution (CDR) without modifying application or database structure.

With auto CDR we can now configure and manage Oracle GoldenGate 12.3 to automate conflict detection and resolution when it is configured in Oracle Database 12c Release 2 (12.2) and later.

Auto CDR does not require application changes for the following reasons:

  • Oracle automatically creates and maintains invisible timestamp columns enabled with DBMS_GOLDENGATE_ADM package.
  • Inserts, updates, and deletes use a tombstone log table to determine if a row was deleted.
  • BLOG and CLOB column conflicts can be detected, a huge improvement from prior CDR.

Auto CDR is currently (Jan 2019) only available to Oracle databases.

We use DBMS_GOLDENGATE_ADM package to enable and disable auto CDR.

zzzzz

Some examples on how to invoke the package:

Configuring Latest Timestamp Conflict Detection and Resolution for a Table

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

We can make the column visible with:
SQL>alter table hr.employees modify CDRTS$ROW visible;

Configuring Column Groups 

BEGIN
  DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR_COLUMN_GROUP(
    schema_name       => 'hr',
    table_name        => 'emp',
    column_list       => 'job_id,department_id,manager_id',
    column_group_name => 'job_identifier_cg');
END;
/


Configuring Delta Conflict Detection and Resolution for a 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;
/
Displaying Information About the Tables Configured for Conflict Detection and Resolution

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              EMP             DT$_EMP         CDRTS$ROW
OE              ORDERS          DT$_ORDERS      CDRTS$ROW

Removing Conflict Detection and Resolution for a Table

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

Removing a Column Group

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

Reference and full details:  https://goo.gl/7Vg1dN

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.