By Franck Pachot

.
In a previous post I presented how Dbvisit replicate can capture changes and write them in an audit table with old and new values.
Here is how to do it wen you want only the new values – with same column names.

What I want to get here is a table with:

  • same column names as source
  • they will contain new values for inserts and update, and old value for delete
  • additional column names for operation, timestamp, and SCN

Here is the part of the setup wizard where I set the new table name (DEMO is audited to DEMO$AUDIT) and declare that I don’t want old values for update:


Rename SCHEMA name for DEMO.DEMO (empty means no rename): [] DEMO
 
Rename TABLE name for DEMO.DEMO (empty means no rename): [] DEMO$AUDIT
 
Filter the data to be replicated? If yes, use single condition for ALL DML operations or use CUSTOM conditions for each operation? (NO/ALL/CUSTOM) [NO]
 
Configure change data capture for change auditing or real-time BI? (NO/YES) [NO] yes
 
Capture DELETE operations? (YES/NO) [YES]
 
Capture UPDATE operations - old values? (YES/NO) [YES] no
 
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.
Prefix for columns with OLD values: []
 
Prefix for columns with NEW values: []
Dbvisit 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]

I can choose the additional columns and name them (here prefixed with AUDIT$):


Add auditing columns? (login user, machine, OS user...) (YES/NO) [NO]
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.
OPERATION: Operation code (U/I/D) (opcol)
SCN: SCN at source (scn)
DATE_CHANGE: Date and time of the change (timestamp_change)
   
Accept these settings? (YES/NO) [YES] no
                                                                                                                                                                                                                            Operation code (U/I/D) [OPERATION] AUDIT$OP
                                                                                                                                                                                                                            SCN at source [SCN] AUDIT$SCN
                                                                                                                                                                                                                            Date and time of the change [DATE_CHANGE] AUDIT$DATE
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.
AUDIT$OP: Operation code (U/I/D) (opcol)
AUDIT$SCN: SCN at source (scn)
AUDIT$DATE: Date and time of the change (timestamp_change)

Now let’s do some SQL on the source table.

SQLcl

I connect with SQLcl because a new release is there and I want to test:


SQLcl: Release 4.2.0.15.278.1216 RC on Tue Oct 06 23:11:11 2015
 
23:13:24 SQL> set sqlformat ansiconsole
23:13:24 SQL> alter session set nls_date_format='hh24:mi:ss';

insert

I do some insters and commit:


23:14:30 SQL> select * from DEMO;
23:14:33 SQL> select * from DEMO$AUDIT;
23:14:33 SQL> insert into DEMO values (1,'A');
1 row inserted.
23:14:39 SQL> insert into DEMO values (2,'B');
1 row inserted.
23:15:48 SQL> insert into DEMO values (3,'C');
1 row inserted.
23:15:48 SQL> commit;
Commit complete.

and check the replication from dbvrep console LIST PROGRESS:


--------------------------------------------------------------------------------------------------------------------------------------------
DEMO.DEMO/DEMO.DEMO$AUDIT:    100%  Mine:3/3             Unrecov:0/0         Applied:3/3         Conflicts:0/0       Last:06/10/2015 23:15:52/OK
--------------------------------------------------------------------------------------------------------------------------------------------

The 3 records are applied. Let’s check the audit table:


23:16:44 SQL> select * from DEMO$AUDIT;
 
ID  TEXT  U  AUDIT$OP  AUDIT$DATE  AUDIT$SCN
1   A        I         23:14:39    5956571
2   B        I         23:14:39    5956571
3   C        I         23:15:48    5957146

I have the new values with the ‘I’ operation code. You have the timestamp and SCN of the insert.

update


23:16:44 SQL> update DEMO set text='a' where id=1;
1 row updated.
23:16:44 SQL> commit;
Commit complete.

delete


23:17:48 SQL> delete from DEMO where id=2;
1 row deleted.
23:17:48 SQL> commit;
Commit complete.

LIST PROGRESS


--------------------------------------------------------------------------------------------------------------------------------------------
DEMO.DEMO/DEMO.DEMO$AUDIT:    100%  Mine:5/5             Unrecov:0/0         Applied:5/5         Conflicts:0/0       Last:06/10/2015 23:17:53/OK
--------------------------------------------------------------------------------------------------------------------------------------------

And here is the audit table:


23:19:13 SQL> select * from DEMO$AUDIT;
 
ID  TEXT  U  AUDIT$OP  AUDIT$DATE  AUDIT$SCN
1   A        I         23:14:39    5956571
2   B        I         23:14:39    5956571
3   C        I         23:15:48    5957146
1   a        U         23:16:44    5957635
2   B        D         23:17:48    5958194

All my operations are there. We logged all changes.

truncate

Finally I wanted to test a truncate. Knowing what is in the redo records, I don’t expect to see a ‘D’ operation for each table rows. What I would like is a ‘T’runcate operation record, but this is not what we have:


23:19:13 SQL> truncate table DEMO;
Table DEMO truncated.

LIST PROGRESS


--------------------------------------------------------------------------------------------------------------------------------------------
DEMO.DEMO/DEMO.DEMO$AUDIT:    100%  Mine:5/5             Unrecov:0/0         Applied:5/5         Conflicts:0/0       Last:06/10/2015 23:17:53/OK
--------------------------------------------------------------------------------------------------------------------------------------------

No new record here…


23:20:09 SQL> select * from DEMO$AUDIT;
 

No rows! The truncate has not been audited but replicated as a truncate on the audit table.
I’ll check with Dbvisit support if it’s the expected feature or not. And will update this blog to let you know.

Update Oct. 9, 2015

It never takes too long with Dbvisit support, so here is the answer.
Because truncate is DDL, it is replicated as-is. But you can expect in future version to get something like the ‘T’ operation as suggested here, so that truncates are tracked.