OGG TIP: How to create an audit of changes in a table(s)

OBJECTIVE

Create a running log of DML activities on number of source tables noting type of DML (insert, delete, update), user, commit time, SCN of source transaction etc.

EXERCISE

We want to track all changes for all changes for schema HR in addition to replicating the tables for this schema.

On the TARGET, create a corresponding set of tables for each table in HR without any keys as we are going to insert all records into these audit tables.

For example table 'jobs' gets a corresponding table 'jobs_audit' containing additional audit columns. For this exercise we shall track the operation type, time of source transaction, SCN of the source transaction and the user.