Audit for Real-Time Data Warehousing

Owned by Arjen Visser
Last updated: May 06, 2014

One of the most complex issues in Data Warehousing is how to detect changes on your source system. Most data warehouses do a complete dump and load of all the data during the nightly load. With Dbvisit Replicate and the new Audit functionality, only data that has been changed can be loaded real time into the Data Warehouse. This allows for reduced processing and real time updates of the Data Warehouse.

The following operations are captured and written into a special created Dbvisit Replicate stage table in the target database:

  • Inserts
  • Updates
  • Deletes
  • Merge (this is translated to inserts and updates)

The following operations are NOT captured:

  • Truncate
  • Drop 
  • Create

The Audit function can also be used to capture auditing information for analysis purposes.

The Audit information can be turned on during the setup wizard.

How does Audit work

Dbvisit Replicate creates a copy of the source table as a staging table in the target database and inserts a record into this staging table every time there is a change in the source table (for Update, Delete, Insert). This means that even with an updated or delete in the source system, a new record is created in the target (or staging) table. The staging table holds a new record for every change in the source system. 

The loading of the data warehouse process can use this staging table to process all new updates into the data warehouse. When the record has been processed and loaded into the data warehouse, the record can be removed from the staging table to indicate that the change has been loaded. 

The staging table contains 2 columns for every 1 column on the source table. There is an OLD and NEW value. If a column is updated on the source table, then the replicated staging table will contain both the OLD and the NEW value.

Audit information is also written to the staging table.
This audit information contains:

  1. OPERATION
  2. SCN
  3. DATE_CHANGE
  4. TRANSACTION_ID
  5. DATE_COMMIT
  6. SSID
  7. SERIAL
  8. CURRENT_USER
  9. LOGON_USER
  10. CLIENT_INFO
  11. OS_USER
  12. MACHINE
  13. OS_TERM
  14. OS_PROC_ID
  15. OS_PROG

Example

The source database contains a table called SALES, and we want to capture all changes to this table into a staging table. 

Dbvisit Replicate will create the DDL for the staging table which will contain:

  • Duplicate of all columns with a prefix (this prefix is user specified)
  • Extra auditing information

For every update, delete or insert transaction on the source database, a new record will be inserted into the staging table with the old and new values as they are applicable. 

The OPERATION column of the staging table will contain the operation. This can be:

  • I = INSERT
  • U = UPDATE
  • D = DELETE
The audit columns are filled in by the Dbvisit Replicate process. 
An overview of the replication process is as follows:

Dbvisit Replicate Audit - loading of real-time data warehouses

Example of how the Audit function works in Dbvisit Replicate to facilitate the ETL process for real-time loading of data warehouses. Length - 6:10

How to turn on Audit

The Audit function is turned on during the setup wizard. 

There are 5 steps to turning on the Audit functionality:

  1. Audit will only work for individual tables that are replicated. It is not supported if a whole schema is replicated.
  2. Choose ddl-only in Step 2 - Replication pairs: "Lock and copy the data initially one-by-one or at a single SCN? (one-by-one/single-scn/ddl-only/resetlogs)"
  3. Choose in ddl_file or  ddl_run in Step 2 - Replication pairs: "What data copy script to create? (dp_networklink/dp_exp/exp/ddl_file/ddl_run/none)"
  4. Choose yes in Step 3 - Replicated tables:"Specify rename name, filter condition, CDC/Audit/ETL for any of the specified tables (yes/no):"
  5. Choose yes in Step 3 - Replicated tables: "Configure change data capture for change auditing or BI? (yes/no)"

Once Audit is turned on, the setup wizard will ask more questions to specifically configure Audit for each particular need:

  1. Capture DELETE operations? (YES/NO) [YES]
  2. Capture UPDATE operations - old values? (YES/NO) [YES]
  3. Capture UPDATE operations - new values? (YES/NO) [YES]
  4. Capture INSERT operations? (YES/NO) [YES]
  5. The columns at the target table can be called the same as on the source table, or they can be prefixed to indicate whether they contain old or new values. As
    both new and old values for update are specified, at least one prefix has to be defined. Prefix for columns with OLD values: [] OLD_
  6. Prefix for columns with NEW values: [] NEW_
  7. Replicate can include additional information into the target table; this can be used to identify type of change, when it occurred, who initiated the change
    etc.
  8. Add basic additional information about the changes? (SCN, time, operation type) (YES/NO) [YES]
  9. Add more transactional information? (transaction id, commit time) (YES/NO) [NO] YES
  10. Add auditing columns? (login user, machine, OS user...) (YES/NO) [NO] YES
  11. Setup wizard chose following default names for the columns; you can use them or choose own names. Note that empty answer confirms the proposed default; use
    "-" (minus) to remove the column from the Audit.
  12. SID: Oracle session ID (sid)
  13. DATE_CHANGE: Date and time of the change (timestamp_change)
  14. SERIAL: Oracle session serial# (serial)
  15. OPERATION: Operation code (U/I/D) (opcol)
  16. OS_PROG: OS program name (osprog)
  17. SCN: SCN at source (scn)
  18. CLIENT_INFO: Client info (cliinfo)
  19. CURRENT_USER: Current user (cuser)
  20. OS_TERM: OS terminal (osterm)
  21. LOGON_USER: Logon user (luser)
  22. OS_PROC_ID: OS process id (osproc)
  23. TRANSACTION_ID: Transaction ID (mandatory if timestamp_commit is used; please define an index on transaction ID in such case) (xidcol)
  24. DATE_COMMIT: Date and time of transaction commit (timestamp_commit)
  25. MACHINE: Client machine name (machine)
  26. OS_USER: OS user (osuser)
  27. Accept these settings? (YES/NO) [YES]

Manually turning on Audit

The Audit function can also be turn on manually. This can be done in the dbvrep console.

Example:

To manually turn on Audit for a table called AVI.SALES, issue the following commands:

PREPARE TABLE AVI.SALES
CDCAUDIT TABLE AVI.SALES INSERT YES
CDCAUDIT TABLE AVI.SALES DELETE YES
CDCAUDIT TABLE AVI.SALES UPDATE YES
CDCAUDIT TABLE AVI.SALES OLDCOL_PREFIX OLD_
CDCAUDIT TABLE AVI.SALES NEWCOL_PREFIX NEW_
CDCAUDIT TABLE AVI.SALES OPCOL OPERATION
CDCAUDIT TABLE AVI.SALES SCN SCN
CDCAUDIT TABLE AVI.SALES TIMESTAMP_CHANGE DATE_CHANGE
CDCAUDIT TABLE AVI.SALES XIDCOL TRANSACTION_ID
CDCAUDIT TABLE AVI.SALES TIMESTAMP_COMMIT DATE_COMMIT
CDCAUDIT TABLE AVI.SALES SID SID
CDCAUDIT TABLE AVI.SALES SERIAL SERIAL
CDCAUDIT TABLE AVI.SALES CUSER CURRENT_USER
CDCAUDIT TABLE AVI.SALES LUSER LOGON_USER
CDCAUDIT TABLE AVI.SALES CLIINFO CLIENT_INFO
CDCAUDIT TABLE AVI.SALES OSUSER OS_USER
CDCAUDIT TABLE AVI.SALES MACHINE MACHINE
CDCAUDIT TABLE AVI.SALES OSTERM OS_TERM
CDCAUDIT TABLE AVI.SALES OSPROC OS_PROC_ID
CDCAUDIT TABLE AVI.SALES OSPROG OS_PROG

This also turns on all auditing columns available.

The changes are inserted as they are fed from mine, i.e. they can stay inserted but uncommitted until the transaction commits on the source database. When using the Audit option, this means that the application that reads the Audit data must not rely solely on the SCN/time of the change to determine whether a record was processed (that's what the commit SCN is for). And it also means that the purging of old partitions must not be too aggressive, as they can still contain uncommitted rows.
Note that the commit SCN is inserted as NULL and updated only when the commit actually comes in. (This means the transaction id should be indexed if commit SCN column is used and that you need row movement enabled if you use that as partition key.)

How to purge older Audit inserted data?

Options to purge data out of the Audit inserted staging tables are:

  1. Delete each record as they are processed and loaded by the ETL process.
  2. Convert the target Audit inserted staging tables to partitioned tables (if this option is licensed).

 

The changes are inserted as they are fed from mine, i.e. they can stay inserted but uncommitted until the transaction commits on the source database. This means that the application that reads the Audit data must not rely solely on the SCN/time of the change to determine whether a record was processed (that's what the commit SCN is for). And it also means that the purging of old partitions must not be too aggressive, as they can still contain uncommitted rows. The commit SCN is inserted as NULL and updated only when the commit actually comes in. (This means the transaction id should be indexed if commit SCN column is used and that you need row movement enabled if you use that as partition key.)