...
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.
...
Dbvisit Replicate
...
Audit - loading of real-time data warehouses
Example of how the CDC/ Audit function works in Dbvisit Replicate to facilitate the ETL process for real-time loading of data warehouses. Length - 6:10
| 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:
- CDC/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)"
...
- 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 CDC. - 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]
Manually turning on
...
Audit
The CDC/ Audit function can also be turn on manually. This can be done in the dbvrep console.
Example:
To manually turn on CDC/ Audit for a table called AVI.SALES, issue the following commands:
...