{"id":5952,"date":"2012-09-13T19:37:24","date_gmt":"2012-09-14T00:37:24","guid":{"rendered":"http:\/\/jianmingli.com\/wp\/?p=5952"},"modified":"2012-09-17T08:27:06","modified_gmt":"2012-09-17T13:27:06","slug":"goldengate-colmap-map-to-different-table-name-and-column-name","status":"publish","type":"post","link":"https:\/\/jianmingli.com\/wp\/?p=5952","title":{"rendered":"GoldenGate COLMAP: Map to Different Table Name and Column Name"},"content":{"rendered":"
\n

Contents<\/h2>\n
    \n\t
  1. \n\t\tPrepare Environment<\/a>\n\t<\/li>\n\t
  2. \n\t\tPrepare Database<\/a>\n\t\t
      \n\t\t\t
    1. \n\t\t\t\tCreate Source Table<\/a>\n\t\t\t<\/li>\n\t\t\t
    2. \n\t\t\t\tCreate Target Table<\/a>\n\t\t\t<\/li>\n\t\t\t
    3. \n\t\t\t\tGenerate Source Definitions File<\/a>\n\t\t\t<\/li>\n\t\t<\/ol>\n\t
    4. \n\t\tFor Source User: gg_src<\/a>\n\t\t
        \n\t\t\t
      1. \n\t\t\t\tAdd Trandata<\/a>\n\t\t\t<\/li>\n\t\t\t
      2. \n\t\t\t\tPrepare Extract Group<\/a>\n\t\t\t<\/li>\n\t\t<\/ol>\n\t
      3. \n\t\tFor Target User: gg_tgt<\/a>\n\t\t
          \n\t\t\t
        1. \n\t\t\t\tPrepare Replicat<\/a>\n\t\t\t<\/li>\n\t\t<\/ol>\n\t
        2. \n\t\tTest<\/a>\n\t\t
            \n\t\t\t
          1. \n\t\t\t\tInsert Data to Source Table<\/a>\n\t\t\t<\/li>\n\t\t\t
          2. \n\t\t\t\tCheck Data in Target Table<\/a>\n\t\t\t<\/li>\n\t\t<\/ol>\n\t
          3. \n\t\tReferences<\/a>\n\t<\/li>\n<\/ol>\n<\/ol>\n<\/div>\n
             <\/div>\n

            In this note, GoldenGate is used to replicate data from a source table named GG_TEST_TBL1<\/em> to a target table named GG_TEST_TABLE1<\/em>. A column in the source table named PASS<\/em> is also being replicated to a column in the target table named PASSWORD<\/em>.<\/p>\n

            Prepare Environment<\/h2><\/span>\n

            * See GoldenGate Tutorial: Simplest<\/a> to setup environment.<\/p>\n

            Prepare Database<\/h2><\/span>\n

            Create Source Table<\/h3><\/span>\n
             \r\nsqlplus gg_src\/password\r\n \r\n-- Create src table\r\nCREATE TABLE GG_SRC.GG_TEST_TBL1\r\n(\r\nID VARCHAR2(20) NOT NULL,\r\nNAME VARCHAR2(20),\r\nPASS VARCHAR2(512),\r\nCONSTRAINT GG_TEST_TBL1_PK PRIMARY KEY\r\n(\r\nID\r\n)\r\nENABLE\r\n);\r\n<\/pre>\n
            Create Target Table<\/h3><\/span>\n
             \r\nsqlplus gg_tgt\/password\r\n \r\n-- Create target table\r\nCREATE TABLE GG_TGT.GG_TEST_TABLE1\r\n(\r\nID VARCHAR2(20) NOT NULL,\r\nNAME VARCHAR2(20),\r\nPASSWORD VARCHAR2(512),\r\nCONSTRAINT GG_TEST_TBL1_PK PRIMARY KEY\r\n(\r\nID\r\n)\r\nENABLE\r\n);\r\n<\/pre>\n
            Generate Source Definitions File<\/h3><\/span>\n
            * Source definitions file is needed since source and target table definitions are different
            \n* Create defgen parameter file from GGSCI: EDIT PARAMS gg_src_tbl1<\/em><\/p>\n
             \r\nDEFSFILE dirdef\/gg_src_tbl1.def\r\nUSERID gg_src, PASSWORD password\r\nTABLE gg_src.GG_TEST_TBL1;\r\n<\/pre>\n
            * Generate source definitions file with the defgen <\/em>command:
            \n– From GG_HOME directory:<\/p>\n
             \r\ndefgen paramfile dirprm\/gg_src_tbl1.prm\r\n<\/pre>\n
            For Source User: gg_src<\/h2><\/span>\n
            Add Trandata<\/h3><\/span>\n
             \r\n-- Login source schema\r\nDBLOGIN USERID GG_SRC, PASSWORD password\r\n-- Enable trandata for GG_TEST_TBL1\r\nADD TRANDATA gg_src.GG_TEST_TBL1\r\n<\/pre>\n
            Prepare Extract Group<\/h3><\/span>\n
            * Create extract group<\/p>\n
             \r\nADD EXTRACT e_t1, TRANLOG, BEGIN now\r\n<\/pre>\n
            * Create extract trail<\/p>\n
             \r\nADD EXTTRAIL \/u01\/Oracle\/GoldenGate\/gg1121\/dirdat\/t1, EXTRACT e_t1, MEGABYTES 20\r\n<\/pre>\n
            * Edit extract parameters: EDIT PARAMS e_t1<\/p>\n
             \r\n-- Oracle instance\r\nSETENV (ORACLE_HOME = \"\/usr\/lib\/oracle\/xe\/app\/oracle\/product\/10.2.0\/server\")\r\nSETENV (ORACLE_SID = \"XE\")\r\n \r\n-- Specify extract group\r\nEXTRACT e_t1\r\n \r\n-- Used to login db\r\nUSERID gg_src, PASSWORD password\r\n \r\n-- Specify extract trail file\r\nEXTTRAIL \/u01\/Oracle\/GoldenGate\/gg1121\/dirdat\/t1\r\n \r\n-- Specify tables to extract\r\nTABLE gg_src.GG_TEST_TBL1;\r\n<\/pre>\n
            * Start Extract<\/p>\n
             \r\n-- Start manager process if not already done\r\nstart mgr\r\n \r\n-- Start extract\r\nstart extract e_t1\r\n \r\n-- View extract report\r\nview report e_t1\r\n<\/pre>\n
            For Target User: gg_tgt<\/h2><\/span>\n
            Prepare Replicat<\/h3><\/span>\n
            * Create REPLICAT group<\/p>\n
             \r\nADD REPLICAT r_t1, EXTTRAIL \/u01\/Oracle\/GoldenGate\/gg1121\/dirdat\/t1, NODBCHECKPOINT, BEGIN now\r\n<\/pre>\n
            * Edit REPLICAT parameters: EDIT PARAMS r_t1<\/p>\n
             \r\n-- Oracle instance\r\nSETENV (ORACLE_HOME = \"\/usr\/lib\/oracle\/xe\/app\/oracle\/product\/10.2.0\/server\")\r\nSETENV (ORACLE_SID = \"XE\")\r\n \r\n-- Replicat group\r\nREPLICAT r_t1\r\n \r\n-- Source definitions file\r\nSOURCEDEFS .\/dirdef\/gg_src_tbl1.def\r\n \r\n-- Login DB\r\nUSERID gg_tgt, PASSWORD password\r\n \r\n-- Map tables AND columns\r\n-- USEDEFAULTS: exact same names\r\n-- PASSWORD = PASS: target column name PASSWORD should be mapped to source column named PASS\r\nMAP gg_src.GG_TEST_TBL1, TARGET gg_tgt.GG_TEST_TABLE1, &\r\nCOLMAP (USEDEFAULTS, &\r\nPASSWORD = PASS);\r\n<\/pre>\n
            *Start REPLICAT<\/p>\n
             \r\nstart replicat r_t1\r\nview report r_t1\r\n<\/pre>\n
            Test<\/h2><\/span>\n
            Insert Data to Source Table<\/h3><\/span>\n
             \r\nINSERT INTO GG_SRC.GG_TEST_TBL1 VALUES ('1','One','Pass');\r\ncommit;\r\n<\/pre>\n
            Check Data in Target Table<\/h3><\/span>\n
             \r\nSELECT * FROM GG_TGT.GG_TEST_TABLE1;\r\n \r\nID                   NAME                 PASSWORD\r\n-------------------- -------------------- --------------------\r\n1                    One                  Pass\r\n<\/pre>\n
            References<\/h2><\/span>\n
            * GoldenGate Tutorial: Simplest<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
            In this note, GoldenGate is used to replicate data from a source table named GG_TEST_TBL1 to a target table named GG_TEST_TABLE1. A column in the source table named PASS is also being replicated to a column in the target table … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[88],"tags":[240,241],"class_list":["post-5952","post","type-post","status-publish","format-standard","hentry","category-goldengate","tag-colmap","tag-goldendate"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8cRUO-1y0","_links":{"self":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/5952","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5952"}],"version-history":[{"count":5,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/5952\/revisions"}],"predecessor-version":[{"id":5983,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/5952\/revisions\/5983"}],"wp:attachment":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5952"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5952"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5952"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}