{"id":5941,"date":"2012-09-13T16:04:09","date_gmt":"2012-09-13T21:04:09","guid":{"rendered":"http:\/\/jianmingli.com\/wp\/?p=5941"},"modified":"2012-09-17T08:26:49","modified_gmt":"2012-09-17T13:26:49","slug":"goldengate-column-mapping","status":"publish","type":"post","link":"https:\/\/jianmingli.com\/wp\/?p=5941","title":{"rendered":"GoldenGate COLMAP: Map Different 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

            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_COL1\r\n(\r\nID VARCHAR2(20) NOT NULL,\r\nNAME VARCHAR2(20), \r\nPASS VARCHAR2(512), \r\nCONSTRAINT GG_TEST_COL1_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_COL1\r\n(\r\nID VARCHAR2(20) NOT NULL,\r\nNAME VARCHAR2(20), \r\nPASSWORD VARCHAR2(512),\r\nCONSTRAINT GG_TEST_COL1_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<\/em> parameter file: EDIT PARAMS gg_src<\/em><\/p>\n
            \r\nDEFSFILE dirdef\/gg_src.def\r\nUSERID gg_src, PASSWORD password\r\nTABLE gg_src.gg_test_col1;\r\n<\/pre>\n
            * Generate source definitions file with the defgen<\/em> command: <\/p>\n
            \r\ndefgen paramfile dirprm\/gg_src.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_col1\r\nADD TRANDATA gg_src.gg_test_col1\r\n<\/pre>\n
            Prepare Extract Group<\/h3><\/span>\n
            * Create extract group<\/p>\n
            \r\nADD EXTRACT e_c1, TRANLOG, BEGIN now\r\n<\/pre>\n
            * Create extract trail<\/p>\n
            \r\nADD EXTTRAIL \/u01\/Oracle\/GoldenGate\/gg1121\/dirdat\/c1, EXTRACT e_c1, MEGABYTES 20\r\n<\/pre>\n
            * Edit extract parameters: EDIT PARAMS e_c1<\/em><\/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_c1\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\/c1\r\n\r\n-- Specify tables to extract\r\nTABLE gg_src.gg_test_col1;\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_c1\r\n\r\n-- View extract report\r\nview report e_c1\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_c1, EXTTRAIL \/u01\/Oracle\/GoldenGate\/gg1121\/dirdat\/c1, NODBCHECKPOINT, BEGIN now\r\n<\/pre>\n
            * Edit REPLICAT parameters: EDIT PARAMS r_c1<\/em><\/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_c1\r\n\r\n-- Source definitions file\r\nSOURCEDEFS .\/dirdef\/gg_src.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_col1, TARGET gg_tgt.gg_test_col1, &\r\nCOLMAP (USEDEFAULTS, &\r\nPASSWORD = PASS);\r\n<\/pre>\n
            *Start REPLICAT<\/p>\n
            \r\nstart replicat r_c1\r\nview report r_c1\r\n<\/pre>\n
            Test<\/h2><\/span>\n
            Insert Data to Source Table<\/h3><\/span>\n
            \r\nINSERT INTO GG_SRC.gg_test_col1 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_col1;\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":"
            Prepare Environment * See GoldenGate Tutorial: Simplest to setup environment. Prepare Database Create Source Table sqlplus gg_src\/password — Create src table CREATE TABLE GG_SRC.GG_TEST_COL1 ( ID VARCHAR2(20) NOT NULL, NAME VARCHAR2(20), PASS VARCHAR2(512), CONSTRAINT GG_TEST_COL1_PK PRIMARY KEY ( ID ) … 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,587],"class_list":["post-5941","post","type-post","status-publish","format-standard","hentry","category-goldengate","tag-colmap","tag-goldengate"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8cRUO-1xP","_links":{"self":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/5941","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=5941"}],"version-history":[{"count":10,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/5941\/revisions"}],"predecessor-version":[{"id":5982,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/5941\/revisions\/5982"}],"wp:attachment":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5941"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5941"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5941"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}