Programatically creating GoldenGate conflict detection/resolution rules

We initially started with checking all columns in every table for any differences in an active-active environment, and aborting the replicat if it failed. That seems a bit over the top in hindsight, so we changed it to simply discard the offending record and wrote a job to alert on this event.

We used what is below to programatically create the GoldenGate mapping file. It is pretty useful, so just edit it for your needs.

set lines 1000 trims on verify off feedback off
set serveroutput on
spool cols.out
declare
  l_owner varchar2(30) := upper('&owner');
  type t_keys is table of varchar2(30);
  keys t_keys := t_keys();
  j number := 1;
begin
  for tab in (select distinct table_name from dba_tab_columns where data_type like '%LOB' and owner = l_owner) loop
    for r in (select column_name from dba_tab_columns where data_type like '%LOB' and owner = l_owner and table_name = tab.table_name) loop
      keys.extend(1);
      keys(j) := r.column_name;
      j := j + 1;
    end loop;
    j := 1;
    dbms_output.put_line('map ' || l_owner || '.' || tab.table_name || ', target ' || l_owner || '_rep.' || tab.table_name || ', & ');
    for j in 1..keys.count loop
      if j = keys.count then
        if j = 1 then
          dbms_output.put_line('COMPARECOLS (ON UPDATE ALLEXCLUDING (' || keys(j) || ')), & ');
        else
          dbms_output.put_line(',' || keys(j) || ')) & ');
        end if;
      elsif j = 1 then
        dbms_output.put('COMPARECOLS (ON UPDATE ALLEXCLUDING (' || keys(j));
      else
        dbms_output.put(',' || keys(j));
      end if;
    end loop;
    keys.delete();
    dbms_output.put_line('RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, DISCARD));');
    --dbms_output.put_line('RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, DISCARD));');
    dbms_output.put_line(rpad('-',100,'-'));
  end loop;
  for tab in (select table_name from dba_tables where owner = l_owner and table_name not in
               (select distinct table_name from dba_tab_columns where data_type like '%LOB' and owner = l_owner)) loop
    dbms_output.put_line('map ' || l_owner || '.' || tab.table_name || ', target ' || l_owner || '_rep.' || tab.table_name || ', COMPARECOLS (ON UPDATE ALL), & ');
    dbms_output.put_line('RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, DISCARD));');
    --dbms_output.put_line('RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, DISCARD));');
    dbms_output.put_line(rpad('-',100,'-'));
  end loop;
end;
/

1 comment for “Programatically creating GoldenGate conflict detection/resolution rules

  1. oak
    November 19, 2014 at 1:20 PM

    great, thx for sharing, if can programmatically adding rows during cdr to exception tables would be even better, 🙂

Leave a Reply

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

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