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 CDC/ 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.
...
| Widget Connector | ||
|---|---|---|
|
How to turn on Audit
The CDC/ Audit function is turned on during the setup wizard.
There are 5 steps to turning on the CDC/ Audit functionality:
- Audit will only work for individual tables that are replicated. It is not supported if a whole schema is replicated.
- 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)"
- 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)"
- Choose yes in Step 3 - Replicated tables:"Specify rename name, filter condition, CDC/Audit/ETL for any of the specified tables (yes/no):"
- Choose yes in Step 3 - Replicated tables: "Configure change data capture for change auditing or BI? (yes/no)"
Once CDC/ Audit is turned on, the setup wizard will ask more questions to specifically configure CDC/ Audit for each particular need:
- Capture DELETE operations? (YES/NO) [YES]
- Capture UPDATE operations - old values? (YES/NO) [YES]
- Capture UPDATE operations - new values? (YES/NO) [YES]
- Capture INSERT operations? (YES/NO) [YES]
- 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_ - Prefix for columns with NEW values: [] NEW_
- 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. - Add basic additional information about the changes? (SCN, time, operation type) (YES/NO) [YES]
- Add more transactional information? (transaction id, commit time) (YES/NO) [NO] YES
- Add auditing columns? (login user, machine, OS user...) (YES/NO) [NO] YES
- 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 CDCAudit. - SID: Oracle session ID (sid)
- DATE_CHANGE: Date and time of the change (timestamp_change)
- SERIAL: Oracle session serial# (serial)
- OPERATION: Operation code (U/I/D) (opcol)
- OS_PROG: OS program name (osprog)
- SCN: SCN at source (scn)
- CLIENT_INFO: Client info (cliinfo)
- CURRENT_USER: Current user (cuser)
- OS_TERM: OS terminal (osterm)
- LOGON_USER: Logon user (luser)
- OS_PROC_ID: OS process id (osproc)
- TRANSACTION_ID: Transaction ID (mandatory if timestamp_commit is used; please define an index on transaction ID in such case) (xidcol)
- DATE_COMMIT: Date and time of transaction commit (timestamp_commit)
- MACHINE: Client machine name (machine)
- OS_USER: OS user (osuser)
- Accept these settings? (YES/NO) [YES]
...
| No Format | ||
|---|---|---|
| ||
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.
| Note |
|---|
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 CDC Audit option, this means that the application that reads the CDC 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. |
...
How to purge older
...
Audit inserted data?
Options to purge data out of the CDC Audit inserted staging tables are:
- Delete each record as they are processed and loaded by the ETL process.
- Convert the target CDC Audit inserted staging tables to partitioned tables (if this option is licensed).
...
| Note |
|---|
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 CDC 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.) |