{"id":5762,"date":"2015-10-07T20:10:21","date_gmt":"2015-10-07T18:10:21","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/dbvisit-replicate-cdc-without-old-values\/"},"modified":"2015-10-07T20:10:21","modified_gmt":"2015-10-07T18:10:21","slug":"dbvisit-replicate-cdc-without-old-values","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/dbvisit-replicate-cdc-without-old-values\/","title":{"rendered":"Dbvisit replicate CDC without old values"},"content":{"rendered":"<h2>By Franck Pachot<\/h2>\n<p>.<br \/>\nIn a previous <a href=\"http:\/\/dbi-services.com\/blog\/oracle-cdc-for-datawarehouse-dbvisit-replicate-as-an-alternative\/\">post<\/a> I presented how Dbvisit replicate can capture changes and write them in an audit table with old and new values.<br \/>\nHere is how to do it wen you want only the new values &#8211; with same column names.<br \/>\n<!--more--><br \/>\nWhat I want to get here is a table with:<\/p>\n<ul>\n<li>same column names as source<\/li>\n<li>they will contain new values for inserts and update, and old value for delete<\/li>\n<li>additional column names for operation, timestamp, and SCN<\/li>\n<\/ul>\n<p>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&#8217;t want old values for update:<\/p>\n<pre><code>\nRename SCHEMA name for DEMO.DEMO (empty means no rename): [] DEMO\n&nbsp;\nRename TABLE name for DEMO.DEMO (empty means no rename): [] DEMO$AUDIT\n&nbsp;\nFilter 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]\n&nbsp;\nConfigure change data capture for change auditing or real-time BI? (NO\/YES) [NO] yes\n&nbsp;\nCapture DELETE operations? (YES\/NO) [YES]\n&nbsp;\nCapture UPDATE operations - old values? (YES\/NO) [YES] no\n&nbsp;\nCapture UPDATE operations - new values? (YES\/NO) [YES]\n&nbsp;\nCapture INSERT operations? (YES\/NO) [YES]\nThe 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.\nPrefix for columns with OLD values: []\n&nbsp;\nPrefix for columns with NEW values: []\nDbvisit 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.\n&nbsp;\nAdd basic additional information about the changes? (SCN, time, operation type) (YES\/NO) [YES]\n&nbsp;\nAdd more transactional information? (transaction id, commit time) (YES\/NO) [NO]\n<\/code><\/pre>\n<p>I can choose the additional columns and name them (here prefixed with AUDIT$):<\/p>\n<pre><code>\nAdd auditing columns? (login user, machine, OS user...) (YES\/NO) [NO]\nSetup 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.\nOPERATION: Operation code (U\/I\/D) (opcol)\nSCN: SCN at source (scn)\nDATE_CHANGE: Date and time of the change (timestamp_change)\n&nbsp;  \nAccept these settings? (YES\/NO) [YES] no\n&nbsp;                                                                                                                                                                                                                           Operation code (U\/I\/D) [OPERATION] AUDIT$OP\n&nbsp;                                                                                                                                                                                                                           SCN at source [SCN] AUDIT$SCN\n&nbsp;                                                                                                                                                                                                                           Date and time of the change [DATE_CHANGE] AUDIT$DATE\nSetup 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.\nAUDIT$OP: Operation code (U\/I\/D) (opcol)\nAUDIT$SCN: SCN at source (scn)\nAUDIT$DATE: Date and time of the change (timestamp_change)\n<\/code><\/pre>\n<p>Now let&#8217;s do some SQL on the source table.<\/p>\n<h3>SQLcl<\/h3>\n<p>I connect with SQLcl because a new release is there and I want to test:<\/p>\n<pre><code>\nSQLcl: Release 4.2.0.15.278.1216 RC on Tue Oct 06 23:11:11 2015\n&nbsp;\n23:13:24 SQL&gt; set sqlformat ansiconsole\n23:13:24 SQL&gt; alter session set nls_date_format='hh24:mi:ss';\n<\/code><\/pre>\n<h3>insert<\/h3>\n<p>I do some insters and commit:<\/p>\n<pre><code>\n23:14:30 SQL&gt; select * from DEMO;\n23:14:33 SQL&gt; select * from DEMO$AUDIT;\n23:14:33 SQL&gt; insert into DEMO values (1,'A');\n1 row inserted.\n23:14:39 SQL&gt; insert into DEMO values (2,'B');\n1 row inserted.\n23:15:48 SQL&gt; insert into DEMO values (3,'C');\n1 row inserted.\n23:15:48 SQL&gt; commit;\nCommit complete.\n<\/code><\/pre>\n<p>and check the replication from dbvrep console LIST PROGRESS:<\/p>\n<pre><code>\n--------------------------------------------------------------------------------------------------------------------------------------------\nDEMO.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\n--------------------------------------------------------------------------------------------------------------------------------------------\n<\/code><\/pre>\n<p>The 3 records are applied. Let&#8217;s check the audit table:<\/p>\n<pre><code>\n23:16:44 SQL&gt; select * from DEMO$AUDIT;\n&nbsp;\n<u>ID  TEXT  U  AUDIT$OP  AUDIT$DATE  AUDIT$SCN<\/u>\n1   A        I         23:14:39    5956571\n2   B        I         23:14:39    5956571\n3   C        I         23:15:48    5957146\n<\/code><\/pre>\n<p>I have the new values with the &#8216;I&#8217; operation code. You have the timestamp and SCN of the insert.<\/p>\n<h3>update<\/h3>\n<pre><code>\n23:16:44 SQL&gt; update DEMO set text='a' where id=1;\n1 row updated.\n23:16:44 SQL&gt; commit;\nCommit complete.\n<\/code><\/pre>\n<h3>delete<\/h3>\n<pre><code>\n23:17:48 SQL&gt; delete from DEMO where id=2;\n1 row deleted.\n23:17:48 SQL&gt; commit;\nCommit complete.\n<\/code><\/pre>\n<p>LIST PROGRESS<\/p>\n<pre><code>\n--------------------------------------------------------------------------------------------------------------------------------------------\nDEMO.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\n--------------------------------------------------------------------------------------------------------------------------------------------\n<\/code><\/pre>\n<p>And here is the audit table:<\/p>\n<pre><code>\n23:19:13 SQL&gt; select * from DEMO$AUDIT;\n&nbsp;\n<u>ID  TEXT  U  AUDIT$OP  AUDIT$DATE  AUDIT$SCN<\/u>\n1   A        I         23:14:39    5956571\n2   B        I         23:14:39    5956571\n3   C        I         23:15:48    5957146\n1   a        U         23:16:44    5957635\n2   B        D         23:17:48    5958194\n<\/code><\/pre>\n<p>All my operations are there. We logged all changes. <\/p>\n<h3>truncate<\/h3>\n<p>Finally I wanted to test a truncate. Knowing what is in the redo records, I don&#8217;t expect to see a &#8216;D&#8217; operation for each table rows. What I would like is a &#8216;T&#8217;runcate operation record, but this is not what we have: <\/p>\n<pre><code>\n23:19:13 SQL&gt; truncate table DEMO;\nTable DEMO truncated.\n<\/code><\/pre>\n<p>LIST PROGRESS<\/p>\n<pre><code>\n--------------------------------------------------------------------------------------------------------------------------------------------\nDEMO.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\n--------------------------------------------------------------------------------------------------------------------------------------------\n<\/code><\/pre>\n<p>No new record here&#8230;<\/p>\n<pre><code>\n23:20:09 SQL&gt; select * from DEMO$AUDIT;\n&nbsp;\n<\/code><\/pre>\n<p>No rows! The truncate has not been audited but replicated as a truncate on the audit table.<br \/>\nI&#8217;ll check with Dbvisit support if it&#8217;s the expected feature or not. And will update this blog to let you know.<\/p>\n<h3>Update Oct. 9, 2015<\/h3>\n<p>It never takes too long with Dbvisit support, so here is the answer.<br \/>\nBecause truncate is DDL, it is replicated as-is. But you can expect in future version to get something like the &#8216;T&#8217; operation as suggested here, so that truncates are tracked.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &#8211; with same column names.<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,59],"tags":[667,614,672,429,673],"type_dbi":[],"class_list":["post-5762","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-oracle","tag-audit","tag-cdc","tag-change-data-capture","tag-dbvisit-replicate","tag-sqlcl"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Dbvisit replicate CDC without old values - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/dbvisit-replicate-cdc-without-old-values\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Dbvisit replicate CDC without old values\" \/>\n<meta property=\"og:description\" content=\"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 &#8211; with same column names.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/dbvisit-replicate-cdc-without-old-values\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-10-07T18:10:21+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dbvisit-replicate-cdc-without-old-values\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dbvisit-replicate-cdc-without-old-values\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Dbvisit replicate CDC without old values\",\"datePublished\":\"2015-10-07T18:10:21+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dbvisit-replicate-cdc-without-old-values\\\/\"},\"wordCount\":359,\"commentCount\":0,\"keywords\":[\"audit\",\"cdc\",\"change data capture\",\"Dbvisit replicate\",\"SQLcl\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dbvisit-replicate-cdc-without-old-values\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dbvisit-replicate-cdc-without-old-values\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dbvisit-replicate-cdc-without-old-values\\\/\",\"name\":\"Dbvisit replicate CDC without old values - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2015-10-07T18:10:21+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dbvisit-replicate-cdc-without-old-values\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dbvisit-replicate-cdc-without-old-values\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dbvisit-replicate-cdc-without-old-values\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Dbvisit replicate CDC without old values\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/oracle-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Dbvisit replicate CDC without old values - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/dbvisit-replicate-cdc-without-old-values\/","og_locale":"en_US","og_type":"article","og_title":"Dbvisit replicate CDC without old values","og_description":"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 &#8211; with same column names.","og_url":"https:\/\/www.dbi-services.com\/blog\/dbvisit-replicate-cdc-without-old-values\/","og_site_name":"dbi Blog","article_published_time":"2015-10-07T18:10:21+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/dbvisit-replicate-cdc-without-old-values\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/dbvisit-replicate-cdc-without-old-values\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Dbvisit replicate CDC without old values","datePublished":"2015-10-07T18:10:21+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/dbvisit-replicate-cdc-without-old-values\/"},"wordCount":359,"commentCount":0,"keywords":["audit","cdc","change data capture","Dbvisit replicate","SQLcl"],"articleSection":["Database Administration &amp; Monitoring","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/dbvisit-replicate-cdc-without-old-values\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/dbvisit-replicate-cdc-without-old-values\/","url":"https:\/\/www.dbi-services.com\/blog\/dbvisit-replicate-cdc-without-old-values\/","name":"Dbvisit replicate CDC without old values - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-10-07T18:10:21+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/dbvisit-replicate-cdc-without-old-values\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/dbvisit-replicate-cdc-without-old-values\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/dbvisit-replicate-cdc-without-old-values\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Dbvisit replicate CDC without old values"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5762","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=5762"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5762\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=5762"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=5762"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=5762"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=5762"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}