I've been asked similar things before - like comprehensive auditing of a
legacy database with an eye to making a DW out of the results. I grabbed
this from my GoldenGate notes in 2010:
1) Set the INSERTALLRECORDS Replication parameter to insert a new record in
the target table for every change operation made to the source table.
Beware this can eat up a lot of space, but if you need comprehensive
auditing this is probably expected.
2) If you don't already have a CHANGED_BY_USERID and CHANGED_DATE attached
to your records, you can use the Golden Gate functions on the target side
to get this info for the current transaction. Check out the following
functions in the GG Reference Guide:
@GETENV ('TRANSACTION','USERID' )
@GETENV ('TRANSACTION','TIMESTAMP' )
My usual caveat to the developers is to challenge them to make use of this
collection of record change history. The apps are usually more 'chatty'
than they expect with regards to data updates and transactions. How will
they use this data to reconstruct change states of the data? There are no
optimizations available like there may be with a properly designed data
warehouse schema. The typical developer follow up is "Well if we were using
MongoDB...." followed by a heavy DBA sigh.
--
Database Geekery at www.ba6.us / Twitter @ba6dotus
