Recently, I have been involved in completing a Proof of Concept for adding GoldenGate to an Off the Shelf (OTS) Application to facilitate keeping their DR site in sync.  This post isn’t to discuss the pros or cons of using GoldenGate for this purpose, but rather to share a few items about Auto Conflict Detection and Resolution that will prevent many OTS Applications from using this GoldenGate feature. I have also changed all references to the REAL application to the SAMPLE HR schema to protect the innocent.

This useful feature was first available with Database 12.2 and GoldenGate Version 12.3.  All in all, it is a great feature if you are looking for a database managed Conflict Detection and Resolution Methodology. You can read all about the feature here:
Automatic Conflict Detection and Resolution

One thing I will mention is that while the feature is useful, it does produce a ton of clutter in the schema you choose to deploy this feature in.  As someone who really likes to keep clutter at a minimum, you will notice the following new objects in your schema if the default options are used.  Using simplest form of Auto CDR and the HR.EMPLOYEES table as an example:

• A new hidden column named CDRTS$ROW TIMESTAMP(6) has been added to the table
• A new table called a Tombstone table which keeps track of delete rows DT$_EMPLOYEES is created

Now, as of this article, there have been several bugs logged with reguards to the Tombstone table and its functionality so you can choose to not use it by modifying the option TOMBSTONE_DELETES to FALSE upon running the “DBMS_GOLDENGATE_ADM.ADD_AUTO_CDR” for any given table.

It is these invisible and hidden objects which cause issues with OTS products which utilize SQL Frameworks such as:
Hibernate.  It wasn’t until we rebooted the application that we found that it simply would not come up any longer.  In the beginning we got the usual Java error message as long as your arm and we couldn’t tell what was really wrong until we enabled SQL Tracing for the processes at startup.  It was then that TKPROF showed us exactly where the problem query was:

select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
  nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
  scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
  rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
  nvl(spare3,0), nvl(evaledition#,1),nvl(unusablebefore#,0),
  nvl(unusablebeginning#,0), case when (type# in (1,8,9,96,112)) then
  nvl(collid, 16382) else 0 end case, nvl(collintcol#,0), nvl(acdrrescol#, 0),
  nvl(spare7, 0)
from
col$ where obj#=:1 order by intcol#;

It is this query which the framework uses to build its object-relational mapping. Guess what. It does not filter out INVISIBLE columns as evidenced by the following error message (table / column names changed as appropriate):

ORA-01400: cannot insert NULL into (“HR”.”EMPLOYEES”.”CDRTS$ROW”)

This column is used for conflict detection and maintained by Oracle. Seeing that the application does not know what to do with it, it errors out. Perhaps the a possible remedy to this situation is to propose to the framework author to modify the query which builds the dictionary to include the following additional where clause “and col# 0”. Columns are marked invisible when the col# is 0:

select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
  nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
  scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
  rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
  nvl(spare3,0), nvl(evaledition#,1),nvl(unusablebefore#,0),
  nvl(unusablebeginning#,0), case when (type# in (1,8,9,96,112)) then
  nvl(collid, 16382) else 0 end case, nvl(collintcol#,0), nvl(acdrrescol#, 0),
  nvl(spare7, 0)
from
col$ 
where obj#=:1 and col# != 0 
order by intcol#;

Once your particular SQL framework is able to properly handle invisible columns you should be able to use this useful feature. Unfortunately for my case, it will be another release or 2 before the framework we are using can handle it.