{"id":6339,"date":"2015-11-27T15:52:34","date_gmt":"2015-11-27T14:52:34","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/"},"modified":"2015-11-27T15:52:34","modified_gmt":"2015-11-27T14:52:34","slug":"with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/","title":{"rendered":"With Oracle Goldengate take care of additional column creation on the replicated database"},"content":{"rendered":"
\n

This week I worked on a GoldenGate 12.1.2.1.10 POC setup and was facing an issue which for me is a serious drawback of the Oracle GoldenGate product.If you want to create additional columns on the target database online in a GoldenGate configuration you have to be aware of the below situation which can happen in your setup:The below demo was created on a Oracle GoldenGate Downstream server<\/p>\n

For the test, I have created the schema scott\/tiger on both the source and target databases, thus no initial load is needed<\/p>\n<\/div>\n

\n

1.Create SCOTT on source database DB1 and target database DB2 using utlsampl.sql script.<\/p>\n

Source>@utlsampl.sql\nTarget>@utlsampl.sql<\/pre>\n
First we have to configure the replication for the SCOTT user<\/p>\n<\/div>\n
\n
2.Configure SCOTT extract process on downstream server<\/p>\n
GGSCI (srv01) 1> dblogin useridalias ggsource\nSuccessfully logged into database.\n\nGGSCI (srv01 as goldengate@DB1) 2> miningdblogin useridalias ggcap\nSuccessfully logged into mining database.\n\nGGSCI (srv01 as goldengate@DB1) 3> register extract scott database\nExtract SCOTT successfully registered with database at SCN 277324431694.\n\nGGSCI (srv01 as goldengate@DB1) 5> add extract scott integrated tranlog, begin now\nEXTRACT added.\n\nGGSCI (srv01 as goldengate@DB1) 6> add trandata scott.emp\n\nGGSCI (srv01 as goldengate@DB1) 6> info all\n\nProgram     Status      Group       Lag at Chkpt  Time Since Chkpt\n\nMANAGER     RUNNING\nEXTRACT     STOPPED     SCOTT       00:00:00      00:00:07\n\nGGSCI (srv01 as goldengate@DB1) 7> add exttrail \/u01\/directories\/ggtrail\/POCGGP15\/es, extract SCOTT\nEXTTRAIL added.\n\nGGSCI (srv01 as goldengate@DB1) 12> view params scott\n\nEXTRACT SCOTT\nUSERIDALIAS ggsource\nDBOPTIONS ALLOWUNUSEDCOLUMN\nDDL INCLUDE ALL\nTRANLOGOPTIONS MININGUSERALIAS ggcap\nTRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine Y)\nEXTTRAIL \/u01\/directories\/ggtrail\/POCGGP15\/es\nTABLE SCOTT.EMP;\n\nGGSCI (srv01 as goldengate@DB1) 4> start extract scott\n\nSending START request to MANAGER ...\nEXTRACT SCOTT starting<\/pre>\n
3.Configure the replicat process on downstream server<\/p>\n
GGSCI (srv01) 1> add replicat repscott, exttrail \/u01\/directories\/ggtrail\/POCGGP15\/es\nREPLICAT added.\n\nGGSCI (srv01 as goldengate@DB2) 10> view params repscott\n\nREPLICAT REPSCOTT\nuseridalias ggtarget\nDISCARDFILE \/u01\/app\/goldengate\/product\/12.1.2.1\/discard\/REPSCOTT_DISCARD.txt,APPEND,megabytes 10\nASSUMETARGETDEFS\nDBOPTIONS NOSUPPRESSTRIGGERS\nMAP SCOTT.EMP,TARGET SCOTT.EMP;\n\nGGSCI (srv01) 3> dblogin useridalias ggtarget\nSuccessfully logged into database.<\/pre>\n
Extract current_scn on source database\n\nsys@GMAS2> select current_scn from v$database;\n\n   CURRENT_SCN\n--------------\n  277324550446\n\nGGSCI (srv01 as goldengate@DB2) 5> start replicat repscott, afterscn 277324550446\n\nSending START request to MANAGER ...\nREPLICAT REPSCOTT starting\n\nGGSCI (srv01 as goldengate@DB2) 9> info all\n\nProgram     Status      Group       Lag at Chkpt  Time Since Chkpt\n\nMANAGER     RUNNING\nEXTRACT     RUNNING     SCOTT           00:00:03      00:00:05\nREPLICAT    RUNNING     REPSCOTT        00:00:00      00:02:08<\/pre>\n
Now we have a running GoldenGate replication for the table scott.emp including the DDL<\/p>\n
======START DEMO =======<\/p>\n
On the target database DB2 <\/strong>we create an additional column<\/p>\n<\/div>\n
\n
scott@DB2> alter table emp add TARGET_COL varchar(10) default null;\n\nTable altered.<\/pre>\n<\/div>\n
On the source database DB1 <\/strong>after that, we create an additional column on the source database, which will be replicated to the target database.<\/div>\n
\n
scott@DB1> alter table emp add SOURCE_COL varchar(10) default null;\n\nTable altered.<\/pre>\n<\/div>\n
\n
Now on target database DB2 <\/strong>we have the 2 additional columns, as described below:<\/p>\n
scott@DB2> select ename,target_col,source_col from emp;\n\nENAME      TARGET_COL SOURCE_COL\n---------- ---------- ----------\nSMITH\nALLEN\nWARD\n...<\/pre>\n
And<\/strong> on the source database DB1\u00a0<\/strong> there is only one additional column<\/p>\n<\/div>\n
\n
scott@DB1> select ename, source_col from emp;\n\nENAME      SOURCE_COL\n---------- ----------\nSMITH\nALLEN\nWARD\n...<\/pre>\n
Now on the source database DB1<\/strong> its time to update the entry for the additional column<\/p>\n<\/div>\n
\n
scott@DB1> update emp set source_col='change';\n\n14 rows updated.\n\nscott@DB1> commit;\n\nCommit complete.\n\nscott@DB1> select ename, source_col from emp;\n\nENAME      SOURCE_COL\n---------- ----------\nSMITH      change\nALLEN      change\nWARD       change<\/pre>\n
Until now everything work as expected.<\/p>\n
But now on the target database DB2<\/strong> we will check the entry updated on table scott.emp<\/p>\n
scott@DB2> select ename,target_col,source_col from emp;\n\nENAME      TARGET_COL SOURCE_COL\n---------- ---------- ----------\nSMITH      change\nALLEN      change\nWARD       change<\/pre>\n
!!!!! TARGET_COL\u00a0 is updated, and not the SOURCE_COL Column !!!!<\/p>\n
GoldenGate works with the column order and not explicitly with the column names. Thus if you create additional columns on the target database and the column type is compatible with the value of the source database, GoldenGate will automatically put the insert\/update in identical column number as on the source database :-(((. Without generating ANY warning or error.<\/p>\n
With a sourcedef file you will have the same issue because the sourcedef file is not aware about the additional column created on the target database.<\/p>\n<\/div>\n
Conclusion<\/strong>: A Solution exist: You’ll have to install Oracle GoldenGate release 12.2 which is available since this week, next blog with the solution will come soon \ud83d\ude42<\/div>\n
<\/div>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
This week I worked on a GoldenGate 12.1.2.1.10 POC setup and was facing an issue which for me is a serious drawback of the Oracle GoldenGate product.If you want to create additional columns on the target database online in a GoldenGate configuration you have to be aware of the below situation which can happen in […]<\/p>\n","protected":false},"author":8,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[],"type_dbi":[],"class_list":["post-6339","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring"],"acf":[],"yoast_head":"\nWith Oracle Goldengate take care of additional column creation on the replicated database - 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\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"With Oracle Goldengate take care of additional column creation on the replicated database\" \/>\n<meta property=\"og:description\" content=\"This week I worked on a GoldenGate 12.1.2.1.10 POC setup and was facing an issue which for me is a serious drawback of the Oracle GoldenGate product.If you want to create additional columns on the target database online in a GoldenGate configuration you have to be aware of the below situation which can happen in […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-11-27T14:52:34+00:00\" \/>\n<meta name=\"author\" content=\"Herv\u00e9 Schweitzer\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Herv\u00e9 Schweitzer\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 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\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/\"},\"author\":{\"name\":\"Herv\u00e9 Schweitzer\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7fb08fbefcb9b2c146ef4533cfee00c7\"},\"headline\":\"With Oracle Goldengate take care of additional column creation on the replicated database\",\"datePublished\":\"2015-11-27T14:52:34+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/\"},\"wordCount\":380,\"commentCount\":0,\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/\",\"name\":\"With Oracle Goldengate take care of additional column creation on the replicated database - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-11-27T14:52:34+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7fb08fbefcb9b2c146ef4533cfee00c7\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"With Oracle Goldengate take care of additional column creation on the replicated database\"}]},{\"@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\/7fb08fbefcb9b2c146ef4533cfee00c7\",\"name\":\"Herv\u00e9 Schweitzer\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g\",\"caption\":\"Herv\u00e9 Schweitzer\"},\"description\":\"Herv\u00e9 Schweitzer has more than ten years of experience in database and infrastructure management, engineering, and optimization. He is specialized in Oracle technologies such as standardisation, Backup and Recovery, Tuning, and in High Availability solutions such as Oracle Data Guard, Oracle Grid Infrastructure, Oracle Real Application Clusters (RAC), Oracle GoldenGate, and Oracle Failsafe. Herv\u00e9 Schweitzer is \\\"Oracle Certified Master 11g (OCM 11g)\\\". Prior to joining dbi services, Herv\u00e9 Schweitzer was Senior Consultant at Trivadis in Basel. He also worked as an IT Administrator and Oracle DBA at Crossair in Basel. Herv\u00e9 Schweitzer holds a BTS degree in Information Systems from France. His branch-related experience covers Pharma, Transport and Logistics, Banking, Energy, Automotive etc.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/herve-schweitzer\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"With Oracle Goldengate take care of additional column creation on the replicated database - 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\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/","og_locale":"en_US","og_type":"article","og_title":"With Oracle Goldengate take care of additional column creation on the replicated database","og_description":"This week I worked on a GoldenGate 12.1.2.1.10 POC setup and was facing an issue which for me is a serious drawback of the Oracle GoldenGate product.If you want to create additional columns on the target database online in a GoldenGate configuration you have to be aware of the below situation which can happen in […]","og_url":"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/","og_site_name":"dbi Blog","article_published_time":"2015-11-27T14:52:34+00:00","author":"Herv\u00e9 Schweitzer","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Herv\u00e9 Schweitzer","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/"},"author":{"name":"Herv\u00e9 Schweitzer","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7fb08fbefcb9b2c146ef4533cfee00c7"},"headline":"With Oracle Goldengate take care of additional column creation on the replicated database","datePublished":"2015-11-27T14:52:34+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/"},"wordCount":380,"commentCount":0,"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/","url":"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/","name":"With Oracle Goldengate take care of additional column creation on the replicated database - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-11-27T14:52:34+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7fb08fbefcb9b2c146ef4533cfee00c7"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/with-oracle-goldengate-take-care-of-additional-column-creation-on-the-replicated-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"With Oracle Goldengate take care of additional column creation on the replicated database"}]},{"@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\/7fb08fbefcb9b2c146ef4533cfee00c7","name":"Herv\u00e9 Schweitzer","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0730552c0cfc0a9297c56f2efe56dadd8de399885ca6161a2bee83aebe291afc?s=96&d=mm&r=g","caption":"Herv\u00e9 Schweitzer"},"description":"Herv\u00e9 Schweitzer has more than ten years of experience in database and infrastructure management, engineering, and optimization. He is specialized in Oracle technologies such as standardisation, Backup and Recovery, Tuning, and in High Availability solutions such as Oracle Data Guard, Oracle Grid Infrastructure, Oracle Real Application Clusters (RAC), Oracle GoldenGate, and Oracle Failsafe. Herv\u00e9 Schweitzer is \"Oracle Certified Master 11g (OCM 11g)\". Prior to joining dbi services, Herv\u00e9 Schweitzer was Senior Consultant at Trivadis in Basel. He also worked as an IT Administrator and Oracle DBA at Crossair in Basel. Herv\u00e9 Schweitzer holds a BTS degree in Information Systems from France. His branch-related experience covers Pharma, Transport and Logistics, Banking, Energy, Automotive etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/herve-schweitzer\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6339","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\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=6339"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6339\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=6339"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=6339"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=6339"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=6339"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}