{"id":6421,"date":"2015-12-05T12:12:28","date_gmt":"2015-12-05T11:12:28","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/"},"modified":"2015-12-05T12:12:28","modified_gmt":"2015-12-05T11:12:28","slug":"goldengate-12-2-additional-column-on-the-target","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/","title":{"rendered":"GoldenGate 12.2 additional column on the target"},"content":{"rendered":"

My colleague Herv\u00e9 last week posted a blog concerning a bug in GoldenGate 12.1. You can find the blog here<\/a>.<\/p>\n

In fact the problem is that GoldenGate works with the column position and not with the column name. To follow up this bug I tried to reproduce that with GoldenGate 12.2 that was released last week.<\/p>\n

As Herv\u00e9 did I used the schema scott\/tiger. The goal is not to test an initial load.<\/p>\n

Source>@utlsampl.sql\nTarget>@utlsampl.sql<\/pre>\n
Just a small precision, contrary to my colleague, I don’t use a downstream server but just two virtual machines.<\/p>\n
1. Configure SCOTT extract process on the source machine<\/p>\n
GGSCI (goldengate122) 1> dblogin useridalias ggadmin\nSuccessfully logged into database.\n\nGGSCI (goldengate122 as ggadmin@DB1) 2> register extract scott database\n\n2015-12-04 09:40:36  INFO    OGG-02003  Extract SCOTT successfully registered with database at SCN 558330.\n\nGGSCI (goldengate122 as ggadmin@DB1) 3> add extract scott integrated tranlog, begin now\nEXTRACT (Integrated) added.\n\n\nGGSCI (goldengate122 as ggadmin@DB1) 4> add trandata scott.emp\n\nLogging of supplemental redo data enabled for table SCOTT.EMP.\nTRANDATA for scheduling columns has been added on table 'SCOTT.EMP'.\nTRANDATA for instantiation CSN has been added on table 'SCOTT.EMP'.\nGGSCI (goldengate122 as ggadmin@DB1) 5> info all\n\nProgram     Status      Group       Lag at Chkpt  Time Since Chkpt\n\nMANAGER     RUNNING\nEXTRACT     STOPPED     SCOTT       00:00:00      00:00:21\n\n\nGGSCI (goldengate122 as ggadmin@DB1) 6> add exttrail \/u04\/app\/goldengate\/trail\/DB1\/sc, extract SCOTT\nEXTTRAIL added.\n\nGGSCI (goldengate122 as ggadmin@DB1) 7> edit params scott\n\n\n\nGGSCI (goldengate122 as ggadmin@DB1) 8> view params scott\n\nExtract scott\nuseridalias ggadmin\nDDL INCLUDE MAPPED\nTranlogOptions IntegratedParams (max_sga_size 256)\nExttrail \/u04\/app\/goldengate\/trail\/DB1\/sc\nLOGALLSUPCOLS\nUPDATERECORDFORMAT COMPACT\nTable SCOTT.emp;\n\n\nGGSCI (goldengate122 as ggadmin@DB1) 9> start extract scott\n\nSending START request to MANAGER ...\nEXTRACT SCOTT starting\n\n\nGGSCI (goldengate122 as ggadmin@DB1) 10><\/pre>\n
2. Configure Data Pump extract process to transfer trail files<\/p>\n
GGSCI (goldengate122) 1> dblogin useridalias ggadmin\nSuccessfully logged into database.\n\nGGSCI (goldengate122 as ggadmin@DB1) 2> add extract DPSCOTT, EXTTRAILSOURCE \/u04\/app\/goldengate\/trail\/DB1\/sc\nEXTRACT added.\n\n\nGGSCI (goldengate122 as ggadmin@DB1) 3> add rmttrail \/u05\/ggtrail\/DB2\/tc, extract DPSCOTT\nRMTTRAIL added.\n\nGGSCI (goldengate122 as ggadmin@DB1) 4> edit params DPSCOTT\n\n\n\nGGSCI (goldengate122 as ggadmin@DB1) 5> view params DPSCOTT\n\nextract dpscott\nuseridalias ggadmin\nDBOPTIONS ALLOWUNUSEDCOLUMN\nrmthost 192.168.56.109, MGRPORT 7809\nRMTTRAIL \/u05\/ggtrail\/DB2\/tc\nTABLE scott.emp;\n\n\nGGSCI (goldengate122 as ggadmin@DB1) 6> info all\n\nProgram     Status      Group       Lag at Chkpt  Time Since Chkpt\n\nMANAGER     RUNNING\nEXTRACT     STOPPED     DPSCOTT     00:00:00      00:01:25\nEXTRACT     RUNNING     SCOTT       00:00:10      00:00:03\n\n\nGGSCI (goldengate122 as ggadmin@DB1) 7> start extract DPSCOTT\n\nSending START request to MANAGER ...\nEXTRACT DPSCOTT starting\n\n\nGGSCI (goldengate122 as ggadmin@DB1) 8><\/pre>\n
3. Configure SCOTT replicat process on the target machine<\/p>\n
GGSCI (goldengate1222) 1> dblogin useridalias ggadmin\nSuccessfully logged into database.\n\nGGSCI (goldengate1222 as ggadmin@DB2) 2> add replicat repscott, exttrail \/u05\/ggtrail\/DB2\/tc\nREPLICAT added.\n\n\nGGSCI (goldengate1222 as ggadmin@DB2) 3> edit params REPSCOTT\n\n\n\nGGSCI (goldengate1222 as ggadmin@DB2) 4> view params REPSCOTT\n\nREPLICAT REPSCOTT\nASSUMETARGETDEFS\nUSERIDALIAS ggadmin\nDISCARDFILE \/u04\/app\/goldengate\/product\/12.2.0.0\/DB2\/discard\/REPSCOTT_discard.txt, append, megabytes 10\nMAP SCOTT.emp, TARGET SCOTT.emp;\n\n\nGGSCI (goldengate1222 as ggadmin@DB2) 5> info all\n\nProgram     Status      Group       Lag at Chkpt  Time Since Chkpt\n\nMANAGER     RUNNING\nREPLICAT    STOPPED     REPSCOTT    00:00:00      00:00:20\n\n\nGGSCI (goldengate1222 as ggadmin@DB2) 6> start replicat repscott\n\nSending START request to MANAGER ...\nREPLICAT REPSCOTT starting\n\n\nGGSCI (goldengate1222 as ggadmin@DB2) 7><\/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
SQL> connect scott\/tiger\nConnected.\nSQL> alter table emp add TARGET_COL varchar(10) default null;\n\nTable altered.<\/pre>\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.<\/p>\n
SQL> connect scott\/tiger\nConnected.\nSQL> alter table emp add SOURCE_COL varchar(10) default null;\n\nTable altered.<\/pre>\n
Now on target database DB2 <\/strong>we have the 2 additional columns, as described below:<\/p>\n
SQL> 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
SQL> 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
SQL> update emp set source_col='change';\n\n14 rows updated.\n\nSQL> commit;\n\nCommit complete.\n\nSQL> select ename, source_col from emp;\n\nENAME      SOURCE_COL\n---------- ----------\nSMITH      change\nALLEN      change\nWARD       change\n...<\/pre>\n
Until now everything work as my collegue.<\/p>\n
Now on the target database DB2<\/strong> we will check the entry updated on table scott.emp<\/p>\n
SQL> select ename,target_col,source_col from emp;\n\nENAME      TARGET_COL SOURCE_COL\n---------- ---------- ----------\nSMITH                 change\nALLEN                 change\nWARD                  change\n...<\/pre>\n
And contrary at the version 12.1, it is the right column that was updated.<\/p>\n
Conclusion : <\/strong>Now in the version 12.2, GoldenGate works with the column names and not anymore with the column positions. It is not a revolution, other products like dbvisit replicate<\/a> can do this since years. But at least it works now.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
My colleague Herv\u00e9 last week posted a blog concerning a bug in GoldenGate 12.1. You can find the blog here. In fact the problem is that GoldenGate works with the column position and not with the column name. To follow up this bug I tried to reproduce that with GoldenGate 12.2 that was released last […]<\/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],"tags":[],"type_dbi":[],"class_list":["post-6421","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring"],"acf":[],"yoast_head":"\nGoldenGate 12.2 additional column on the target - dbi Blog<\/title>\n<meta name=\"description\" content=\"GoldenGate, 12.2, Additional column\" \/>\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\/goldengate-12-2-additional-column-on-the-target\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"GoldenGate 12.2 additional column on the target\" \/>\n<meta property=\"og:description\" content=\"GoldenGate, 12.2, Additional column\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-12-05T11:12:28+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=\"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\/goldengate-12-2-additional-column-on-the-target\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"GoldenGate 12.2 additional column on the target\",\"datePublished\":\"2015-12-05T11:12:28+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/\"},\"wordCount\":291,\"commentCount\":0,\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/\",\"name\":\"GoldenGate 12.2 additional column on the target - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-12-05T11:12:28+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"GoldenGate, 12.2, Additional column\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"GoldenGate 12.2 additional column on the target\"}]},{\"@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":"GoldenGate 12.2 additional column on the target - dbi Blog","description":"GoldenGate, 12.2, Additional column","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\/goldengate-12-2-additional-column-on-the-target\/","og_locale":"en_US","og_type":"article","og_title":"GoldenGate 12.2 additional column on the target","og_description":"GoldenGate, 12.2, Additional column","og_url":"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/","og_site_name":"dbi Blog","article_published_time":"2015-12-05T11:12:28+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"GoldenGate 12.2 additional column on the target","datePublished":"2015-12-05T11:12:28+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/"},"wordCount":291,"commentCount":0,"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/","url":"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/","name":"GoldenGate 12.2 additional column on the target - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-12-05T11:12:28+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"GoldenGate, 12.2, Additional column","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/goldengate-12-2-additional-column-on-the-target\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"GoldenGate 12.2 additional column on the target"}]},{"@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\/6421","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=6421"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6421\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=6421"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=6421"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=6421"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=6421"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}