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;
/
great, thx for sharing, if can programmatically adding rows during cdr to exception tables would be even better, 🙂