News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Oracle GoldenGate Automatic Conflict Detection and Resolution(CDR)

  • Posted by Gavin Soorma
  • On January 10, 2019
  • 1 Comments
  • 12c new features, 18c new feature, Automatic Conflict Detection and Resolution(CDR)., cdr, conflict detection, DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR

Automatic Conflict Detection and Resolution is a new feature that is specific to Oracle GoldenGate 12c (12.3.0.1) and Oracle Database 12c Release 2 (12.2.0.1) and above.

We can now configure and manage Oracle GoldenGate automatic conflict detection and resolution in the Oracle Database via the DBMS_GOLDENGATE_ADM package as well as monitor CDR using a number of data dictionary views.

This is done using the ADD_AUTO_CDR procedure which is part of the Oracle database DBMS_GOLDENGATE_ADM package.

Prior to GoldenGate 12.3 we had to use the Replicat COMPARECOLS and RESOLVECONFLICTS parameters for CDR like for example:

MAP SH.test_cdr, TARGET SH.test_cdr,&
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL ),&
RESOLVECONFLICT (INSERTROWEXISTS,(DEFAULT,OVERWRITE));

There are two methods used for Automatic CDR:

a)Latest Timestamp Conflict Detection and Resolution
b)Delta Conflict Detection and Resolution

This note provides an example of Automatic CDR using Latest Timestamp Conflict Detection and Resolution method.

The environment for this example consists of two CDB’s (CDB1,CDB2) located on the same VirtualBox VM – each containing a single Pluggable Database (PDB1, PDB2). We have configured an Active-Active GoldenGate environment which replicates data between PDB1 and PDB2 and vice-versa.

So on the GoldenGate environment side we have this setup:

EXT1>>PUMP1>>REP1>>PDB2
EXT2>>PUMP2>>REP2>>PDB1

We will simulate a conflict by inserting a single row with the same Primary Key value into the same table in PDB1 and PDB2 – at the same time and we do this via a cronjob which calls a shell script which performs the DML activity.

The table name is TEST_CDR and the schema is HR.

Steps:

Execute the ADD_AUTO_CDR procedure and specify the table to configure for Automatic CDR

Note: we do this for both databases connecting as the GoldenGate administration user we have created C##OGGADMIN.

This will add an invisible column to the TEST_CDR table called CDRTS$ROW as well as create a table which has ‘tombstone’ entries which contains the LCRs of rows which are deleted/inserted to handle conflicts related to DELETE/INSERTs.

In the Replicat parameter file we need to add a parameter – MAPINVISIBLECOLUMNS.
 

SQL> conn c##oggadmin/oracle@pdb1
Connected.

SQL> BEGIN
  DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR(
    schema_name => 'HR',
    table_name  => 'TEST_CDR',
  record_conflicts => TRUE);
END;
/    2    3    4    5    6    7  

PL/SQL procedure successfully completed.


SQL> 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;SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6  

TABLE_OWNER	TABLE_NAME	TOMBSTONE_TABLE ROW_RESOLUTION_COLUMN
--------------- --------------- --------------- -------------------------
HR		TEST_CDR	DT$_TEST_CDR	CDRTS$ROW

 

View Column Group information

A column group is a logical grouping of one or more columns in a replicated table enabled for Automatic CDR where conflict detection and resolution is performed on the columns in the column group separately from the other columns in the table.

When we configure the TEST_CDR table for Automatic CDR with the ADD_AUTO_CDR procedure, all the columns in the table are added to a default column group. To define other column groups for the same table, run the ADD_AUTO_CDR_COLUMN_GROUP procedure.

The documentation states the following about Column Groups:

“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”
 

SQL> 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	   TEST_CDR   IMPLICIT_COLUMNS$ REC_ID		CDRTS$ROW
HR	   TEST_CDR   IMPLICIT_COLUMNS$ REC_DESC	CDRTS$ROW

 
Create two shell scripts which will perform INSERT into the TEST_CDR table and execute both the scripts at the same time via cron
 
Note: the primary key column is REC_ID and we are inserting the row in the table in PDB1 and PDB2 using the same value for REC_ID which is going to cause a conflict which needs to be resolved.
 

[oracle@rac02 ~]$ vi cdb1_dml.sh
 #!/bin/bash
 export ORACLE_HOME=/acfs_oh/product/12.2.0/dbhome_1
 export ORACLE_SID=cdb1_2
 PATH=$PATH:$ORACLE_HOME/bin
 sqlplus -s system/G#vin2407@pdb1<<EOF
 insert into test_cdr (rec_id,rec_desc) values (1,'INSERT @ PDB1');
 commit;
 EOF

[oracle@rac02 ~]$ chmod +x cdb1_dml.sh

[oracle@rac02 ~]$ vi cdb2_dml.sh
 #!/bin/bash
 export ORACLE_HOME=/acfs_oh/product/12.2.0/dbhome_1
 export ORACLE_SID=cdb2_2
 PATH=$PATH:$ORACLE_HOME/bin
 sqlplus -s system/G#vin2407@pdb2<<EOF
 insert into test_cdr (rec_id,rec_desc) values (1,'INSERT @ PDB2');
 commit;
 EOF

[oracle@rac02 ~]$ chmod +x cdb2_dml.sh

[oracle@rac02 ~]$ crontab -e
 20 14 * * * /home/oracle/cdb1_dml.sh
 20 14 * * * /home/oracle/cdb2_dml.sh

[oracle@rac02 ~]$ crontab -l
 20 14 * * * /home/oracle/cdb1_dml.sh
 20 14 * * * /home/oracle/cdb2_dml.sh

 

After the shell scripts have been automatically executed via cron, verify the row which has finally been inserted into the TEST_CDR table in both databases

Note: the row having the values 1,’INSERT @ PDB1′ has been discarded.
 

SQL> conn system/G#vin2407@pdb1
Connected.

SQL> select * from hr.test_cdr;

    REC_ID REC_DESC
---------- --------------------
	 1 INSERT @ PDB2

SQL> conn system/G#vin2407@pdb2
Connected.

SQL> /

    REC_ID REC_DESC
---------- --------------------
	 1 INSERT @ PDB2

 
Note the value of the hidden column CDRTS$ROW09-JAN-19 06.24.02.210285. This is used to resolve the INSERT conflict.
 

SQL> alter table hr.test_cdr  modify CDRTS$ROW visible;

Table altered.

SQL> select * from hr.test_cdr;

    REC_ID REC_DESC
---------- --------------------
CDRTS$ROW
---------------------------------------------------------------------------
	 1 INSERT @ PDB2
09-JAN-19 06.24.02.210285 AM

 

Who has won and who has lost?

If we query the DBA_APPLY_ERROR_MESSAGES view in PDB1 we can see the APPLIED column has the value ‘WON’ while the same column in PDB2 has the value of ‘LOST’.

We can also see that the CDRTS$ROW column has been used to resolve the INSERT ROW EXISTS conflict.

This means that the row was changed in PDB2 has been applied on PDB1 (WON) and the row which was changed on PDB1 (and which was replicated to PDB2) has been discarded at PDB2 (LOST).
 

SQL>  conn system/G#vin2407@pdb1
Connected.

SQL> select OBJECT_NAME, CONFLICT_TYPE,APPLIED_STATE,CONFLICT_INFO
  2  from DBA_APPLY_ERROR_MESSAGES;

OBJECT_NAM CONFLICT_TYPE      APPLIED CONFLICT_INFO
---------- ------------------ ------- --------------------
TEST_CDR   INSERT ROW EXISTS  WON     CDRTS$ROW:W

SQL> conn system/G#vin2407@pdb2
Connected.

SQL> /

OBJECT_NAM CONFLICT_TYPE      APPLIED CONFLICT_INFO
---------- ------------------ ------- --------------------
TEST_CDR   INSERT ROW EXISTS  LOST    CDRTS$ROW:L

 

How was the conflict resolved using the Latest Timestamp Method?

The conflict is resolved using this criteria:

If the timestamp of the row LCR is earlier than the timestamp in the table row, then the row LCR is discarded, and the table values are retained.”

So when a change is made in PDB1, EXT1 extract captures the change and writes to local trail file, PUMP1 transmits trail file over network and it is processed by REP1 inserting into database PDB2.

On the other hand when a change is made in PDB2, EXT2 extract captures the change and writes to local trail file, PUMP2 transmits trail file over network and it is processed by REP2 inserting into database PDB1.

If we look at the trail file processed by REP1 (which inserts into PDB2) using logdump utility, we can see the timestamp value of the CDRTS$ROW column is:2019-01-09:06:24:02.210285000
 

2019/01/09 14:24:02.000.630 Insert               Len    65 RBA 2771 
Name: PDB2.HR.TEST_CDR  (TDR Index: 2) 
After  Image:                                             Partition 12   G  s   
 0000 0500 0000 0100 3101 0011 0000 000d 0049 4e53 | ........1........INS  
 4552 5420 4020 5044 4232 0200 1f00 0000 3230 3139 | ERT @ PDB2......2019  
 2d30 312d 3039 3a30 363a 3234 3a30 322e 3231 3032 | -01-09:06:24:02.2102  
 3835 3030 30                                      | 85000  
Column     0 (x0000), Len     5 (x0005)  
 0000 0100 31                                      | ....1  
Column     1 (x0001), Len    17 (x0011)  
 0000 0d00 494e 5345 5254 2040 2050 4442 32        | ....INSERT @ PDB2  
Column     2 (x0002), Len    31 (x001f)  
 0000 3230 3139 2d30 312d 3039 3a30 363a 3234 3a30 | ..2019-01-09:06:24:0  
 322e 3231 3032 3835 3030 30                       | 2.210285000  

If we look at the trail file processed by REP2 (which inserts into PDB1) using logdump utility, we can see the timestamp value of the CDRTS$ROW column is:2019-01-09:06:24:02.205639000
 

2019/01/09 14:24:02.000.529 Insert               Len    65 RBA 3394 
Name: PDB1.HR.TEST_CDR  (TDR Index: 2) 
After  Image:                                             Partition 12   G  s   
 0000 0500 0000 0100 3101 0011 0000 000d 0049 4e53 | ........1........INS  
 4552 5420 4020 5044 4231 0200 1f00 0000 3230 3139 | ERT @ PDB1......2019  
 2d30 312d 3039 3a30 363a 3234 3a30 322e 3230 3536 | -01-09:06:24:02.2056  
 3339 3030 30                                      | 39000  
Column     0 (x0000), Len     5 (x0005)  
 0000 0100 31                                      | ....1  
Column     1 (x0001), Len    17 (x0011)  
 0000 0d00 494e 5345 5254 2040 2050 4442 31        | ....INSERT @ PDB1  
Column     2 (x0002), Len    31 (x001f)  
 0000 3230 3139 2d30 312d 3039 3a30 363a 3234 3a30 | ..2019-01-09:06:24:0  
 322e 3230 3536 3339 3030 30                       | 2.205639000  

 
So the value of the CDRTS$ROW in the table is higher than the value of the column as contained in the trail file, so it is ignored here and not applied to the database – whereas in the other case timestamp value in trail file file is higher than that of the database column value and it was overwritten and replaced.
 

Check the CDR statistics

 

GGSCI (rac01.localdomain) 4> stats rep2 latest reportcdr

Sending STATS request to REPLICAT REP2 ...

Start of Statistics at 2019-01-09 15:04:40.


Integrated Replicat Statistics:

	Total transactions            		           1.00
	Redirected                    		           0.00
	Replicated procedures         		           0.00
	DDL operations                		           0.00
	Stored procedures             		           0.00
	Datatype functionality        		           0.00
	Event actions                 		           0.00
	Direct transactions ratio     		           0.00%

Replicating from PDB2.HR.TEST_CDR to PDB1.HR.TEST_CDR:

*** Latest statistics since 2019-01-09 14:24:26 ***
	Total inserts                   	           1.00
	Total updates                   	           0.00
	Total deletes                   	           0.00
	Total discards                  	           0.00
	Total operations                	           1.00
	Total CDR conflicts                    	           1.00
	CDR resolutions succeeded              	           1.00
	CDR INSERTROWEXISTS conflicts          	           1.00

End of Statistics.
 

1 Comments

Mohammed Ajaz
  • Jan 12 2019
Well explained Bhai. This is a really nice feature. This feature eliminats the extra coding in Replicat for CDR

Leave Reply

Your email address will not be published. Required fields are marked *