Add new comment

If you want to record copies of changed records on a target system you can do this with Golden Gate Software and not incur much in the way of source side resource drain.

To capture changes you configure a Golden Gate Extract process. The Extract process scrapes the redo logs for transactions referring to a list of tables you are interested in. No changes to the source database are required. Golden Gate is merely an 'observer' of the redo stream. Golden Gate writes changes to a set of Trail Files. The Trail Files can be applied to a different schema on the same database or shipped to a target system and applied there. This is ideal for reducing load on your source system.

Once you get the trail file to the target system there are some configuration tweaks you can set 1 option to perform auditing and invoke 2 Golden Gate functions to get info about the transaction:

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:
GGHEADER("USERID")
GGHEADER("TIMESTAMP")

So no its not free (requires Licensing through Oracle), and will require some effort to spin up, but probably a lot less effort/cost than rolling your own, and you have the added benefit of shipping the data to a remote system so you can guarantee minimal impact on your source database.