Audit Oracle Data Changes With SharePlex's Change History Feature

Instead of implementing Oracle Audit_Trail to capture metadata on data changes like userid, timestamp, type of operation, etc., avoid the overhead and capture more information using SharePlex’s Change History.  SharePlex uses its Change Data Capture (CDC) replication capability that imposes no overhead on the source database and maintains fault tolerance to environment issue like network failure and target server or database failure.  Additionally, old data is preserved as a step-by-step record of change.  The historical data can be queried and analyzed for such purposes as data mining or resolving customer disputes.  By using SharePlex to maintain a history database on a secondary server, the overhead incurred by the SQL to add the history rows and the query & analytical activity would not be imposed on the primary/source database.  Plus the additional storage of the history rows will not take up production storage space.

Each new change row that SharePlex inserts to the target includes the following:

  • The values of the key columns
  • The after image of the changed column
  • (optionally) a set of metadata values that provide context for the change (see list below)
  • (optionally) the before image of update operations

Note that it is also possible to capture the data values of all columns, even those that have not changed by setting a SharePlex parameter.

SharePlex Change History metadata – (* non-default metadata)

  • Time the operation was performed on the source
  • Userid that performed the operation
  • Type of operation (e.g. INSERT, UPDATE, DELETE, TRUNCATE, DROP COLUMN etc.)
  • Source SCN for when the operation was applied
  • Rowid of the row that was changed
  • Transaction ID with which the change is associated
  • Sequence – order of the operation within the transaction (incrementing value for every operation with the same transaction id)
  • * host – name or IP address of the source host
  • * queue – name of the SharePlex queue (see example below)
  • * source – user-defined source identifier (see example below)
  • * changeid – unique sequential id of the operation

Example of using the source and queue metadata columns – define specific SharePlex Post queues to line up with data from specific regions using the target command –

                sp_ctrl>  target target1 queue source718 set source east

                sp_ctrl>  target target1 queue source92  set source south

                sp_ctrl>  target target1 queue source101 set source west

                sp_ctrl>  target target1 queue source75 set source north

any data coming through Post queue named source718 is from the east region, etc.

Configure Change History –

  • Use this syntax in the config file:


   owner.table       !cdc:owner.table             host@c.<target_sid>

  • If collecting metadata, create these columns on the history table(s) with the provided script.  Script will create only the default metadata columns.  /optdir/util/add_change_tracking_columns.sql

For more information, read the SharePlex Knowledge Base article: Is it possible to use change data capture in all replication topologies? (86947)