Supplemental logging when running add schematrandata

As I have mentioned in past posts, we are currently implementing GoldenGate with ATG. We want to compare all columns in an update to all columns on the target, as we are implementing bi-directional replication. We found that when we issued the “add schematrandata schemaname” in ggsci, it sometimes didn’t log all columns, but sometimes it did. When we issued a SQL trace, we found that it used an ALLKEYS_ON argument to the dbms_capture_adm.PREPARE_SCHEMA_INSTANTIATION procedure. This isn’t even a documented argument value, and…

select * 
  from table(logmnr$always_suplog_columns(upper('&schema_to_check',
                                          upper('&table_name'))
             );

…didn’t return all columns. We opted to run the procedure ourselves rather than rely on add schematrandata, as shown below.

BEGIN 
  dbms_capture_adm.PREPARE_SCHEMA_INSTANTIATION(upper('&schema_to_log','ALL'); 
END;
/

After doing this, all columns showed up as returned by the logmnr$always_suplog_columns pipelined function, and our conflict resolution routines worked.

Your mileage may vary.