{"id":1553,"date":"2010-04-20T14:57:19","date_gmt":"2010-04-20T19:57:19","guid":{"rendered":"http:\/\/jianmingli.com\/wp\/?p=1553"},"modified":"2012-09-08T09:33:22","modified_gmt":"2012-09-08T14:33:22","slug":"goldengate-tutorial-simplest","status":"publish","type":"post","link":"https:\/\/jianmingli.com\/wp\/?p=1553","title":{"rendered":"GoldenGate Tutorial: Simplest"},"content":{"rendered":"
\n

Contents<\/h2>\n
    \n\t
  1. \n\t\tOverview<\/a>\n\t<\/li>\n\t
  2. \n\t\tSetup Environment Variables<\/a>\n\t<\/li>\n\t
  3. \n\t\tEnable Supplemental Log<\/a>\n\t<\/li>\n\t
  4. \n\t\tCreate Source User<\/a>\n\t<\/li>\n\t
  5. \n\t\tCreate Target User<\/a>\n\t<\/li>\n\t
  6. \n\t\tAdd Trandata<\/a>\n\t<\/li>\n\t
  7. \n\t\tPrepare Extract Group<\/a>\n\t\t
      \n\t\t\t
    1. \n\t\t\t\tCreate Extract Group<\/a>\n\t\t\t<\/li>\n\t\t\t
    2. \n\t\t\t\tCreate Extract Trail<\/a>\n\t\t\t<\/li>\n\t\t\t
    3. \n\t\t\t\tEdit Extract Parameters<\/a>\n\t\t\t<\/li>\n\t\t\t
    4. \n\t\t\t\tStart Extract<\/a>\n\t\t\t<\/li>\n\t\t<\/ol>\n\t
    5. \n\t\tPrepare Replicat Group<\/a>\n\t\t
        \n\t\t\t
      1. \n\t\t\t\tCreaet Replicat Group<\/a>\n\t\t\t<\/li>\n\t\t\t
      2. \n\t\t\t\tEdit Replicat Parameters<\/a>\n\t\t\t<\/li>\n\t\t\t
      3. \n\t\t\t\tStart Replicat<\/a>\n\t\t\t<\/li>\n\t\t<\/ol>\n\t
      4. \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<\/ol>\n<\/ol>\n<\/ol>\n<\/div>\n
           <\/div>\n

          Overview<\/h2><\/span>\n

          * This is a simplest scenario where data are replicated from one user (GG_SRC) to another (GG_TGT) on the same database instance using identical table structure (GG_TEST).
          \n* See           this post<\/a> on how to install GoldenGate on a Unix machine.<\/p>\n

          Setup Environment Variables<\/h2><\/span>\n

          This should already have been setup during GoldenGate installation.<\/p>\n

          \r\nORACLE_HOME=\/opt\/oracle\/product\/10.2; export ORACLE_HOME\r\nORACLE_SID=ggdb; export ORACLE_SID\r\n\r\nJAVA_HOME=\/usr\/jdk\/instances\/jdk1.5.0; export JAVA_HOME\r\nGG_HOME=\/home\/oracle\/goldengate\/goldengate1; export GG_HOME\r\nPATH=$GG_HOME:$PATH; export PATH\r\nLD_LIBRARY_PATH=$GG_HOME:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH\r\n<\/pre>\n
          Enable Supplemental Log<\/h2><\/span>\n
          * This should be done once for each database.
          \n* Login as sysdba: sqlplus \/ as sysdba<\/em> and run following SQL statements:<\/p>\n
          \r\n-- Enable\r\nALTER DATABASE ADD SUPPLEMENTAL LOG DATA;\r\n-- Start\r\nALTER SYSTEM SWITCH LOGFILE;\r\n-- Should return 'YES'\r\nSELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;\r\n<\/pre>\n
          \r\n[oracle@wlbox ~]$ sqlplus \/ as sysdba\r\n\r\nSQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;\r\n\r\nDatabase altered.\r\n\r\nSQL> ALTER SYSTEM SWITCH LOGFILE;\r\n\r\nSystem altered.\r\n\r\nSQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;\r\n\r\nSUPPLEME\r\n--------\r\nYES\r\n\r\nSQL> \r\n<\/pre>\n
          Create Source User<\/h2><\/span>\n
          This is the source user whose data will be replicated from<\/strong>.
          \n* Login as sysdba <\/em>and create src user:<\/p>\n
          \r\n-- Create GG tablespace\r\nCREATE SMALLFILE  TABLESPACE \"GG_TABLESPACE\" \r\nDATAFILE 'path\/to\/oradata\/GG_TABLESPACE.dbf' \r\nSIZE 100M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED \r\nLOGGING \r\nEXTENT MANAGEMENT LOCAL \r\nSEGMENT SPACE MANAGEMENT AUTO;\r\n\r\n-- Create src user\r\nCREATE USER GG_SRC IDENTIFIED BY password \r\nDEFAULT TABLESPACE GG_TABLESPACE\r\nTEMPORARY TABLESPACE TEMP;\r\n\r\ngrant connect,resource to GG_SRC;\r\ngrant select any dictionary, select any table to GG_SRC;\r\ngrant create table to GG_SRC;\r\ngrant flashback any table to GG_SRC;\r\n<\/pre>\n
          * Login as newly created src user and create test table:<\/p>\n
          \r\nsqlplus gg_src\/password\r\n\r\n-- Create src table\r\nCREATE TABLE GG_SRC.GG_TEST\r\n(\r\nID VARCHAR2(20) NOT NULL,\r\nNAME VARCHAR2(20)\r\n, CONSTRAINT GG_TEST_PK PRIMARY KEY\r\n(\r\nID\r\n)\r\nENABLE\r\n);\r\n<\/pre>\n
          Create Target User<\/h2><\/span>\n
          This is the target user where data will be replicated to<\/strong>.
          \n* Login as sysdba <\/em>and create dest user:<\/p>\n
          \r\n-- Create target user\r\nCREATE USER GG_TGT IDENTIFIED BY password \r\nDEFAULT TABLESPACE GG_TABLESPACE\r\nTEMPORARY TABLESPACE TEMP;\r\n\r\ngrant connect,resource to GG_TGT;\r\ngrant select any dictionary, select any table to GG_TGT;\r\ngrant create table to GG_TGT;\r\ngrant flashback any table to GG_TGT;\r\n<\/pre>\n
          * Login as gg_tgt user and create test table:<\/p>\n
          \r\nsqlplus gg_tgt\/password\r\n\r\n-- Create target table\r\nCREATE TABLE GG_TGT.GG_TEST\r\n(\r\nID VARCHAR2(20) NOT NULL,\r\nNAME VARCHAR2(20)\r\n, CONSTRAINT GG_TEST_PK PRIMARY KEY\r\n(\r\nID\r\n)\r\nENABLE\r\n);\r\n<\/pre>\n
          Add Trandata<\/h2><\/span>\n
          From GGSCI:<\/p>\n
          \r\n-- Login source schema\r\nDBLOGIN USERID GG_SRC, PASSWORD password\r\n-- Enable trandata for gg_test\r\nADD TRANDATA gg_src.gg_test\r\n<\/pre>\n
          * Example:<\/p>\n
          \r\nGGSCI (wlbox) 13> DBLOGIN USERID GG_SRC, PASSWORD password\r\nSuccessfully logged into database.\r\n\r\nGGSCI (wlbox) 14> ADD TRANDATA gg_src.gg_test\r\n\r\nLogging of supplemental redo data enabled for table GG_SRC.GG_TEST.\r\n<\/pre>\n
          Prepare Extract Group<\/h2><\/span>\n
          Create Extract Group<\/h3><\/span>\n
          From GGSCI:<\/p>\n
          \r\nADD EXTRACT e_gt, TRANLOG, BEGIN now\r\n<\/pre>\n
          Create Extract Trail<\/h3><\/span>\n
          From GGSCI:<\/p>\n
          \r\nADD EXTTRAIL \/home\/oracle\/goldengate\/goldengate1\/dirdat\/gt, EXTRACT e_gt, MEGABYTES 20\r\n<\/pre>\n
          Edit Extract Parameters<\/h3><\/span>\n
          From GGSCI:<\/p>\n
          \r\nEDIT PARAMS e_gt\r\n<\/pre>\n
          From vi:<\/p>\n
          \r\n-- Set some envs\r\nSETENV (ORACLE_HOME = \"\/opt\/oracle\/product\/10.2\")\r\nSETENV (ORACLE_SID = \"ggdb\")\r\n\r\n-- Specify extract group\r\nEXTRACT e_gt\r\n\r\n-- Used to login db\r\nUSERID gg_src, PASSWORD password\r\n\r\n-- Specify extract trail file\r\nEXTTRAIL \/home\/oracle\/goldengate\/goldengate1\/dirdat\/gt\r\n\r\n-- Specify tables to extract\r\nTABLE gg_src.gg_test;<\/pre>\n
          Start Extract<\/h3><\/span>\n
          \r\n-- Start manager process if not already done\r\nstart mgr\r\nstart extract e_gt\r\nview report e_gt\r\n<\/pre>\n
          Prepare Replicat Group<\/h2><\/span>\n
          Creaet Replicat Group<\/h3><\/span>\n
          \r\nADD REPLICAT r_gt, EXTTRAIL \/home\/oracle\/goldengate\/goldengate1\/dirdat\/gt, NODBCHECKPOINT, BEGIN now<\/pre>\n
          Edit Replicat Parameters<\/h3><\/span>\n
          From GGSCI:<\/p>\n
          \r\nEDIT PARAMS r_gt<\/pre>\n
          From vi:<\/p>\n
          \r\nSETENV (ORACLE_HOME = \"\/opt\/oracle\/product\/10.2\")\r\nSETENV (ORACLE_SID = \"ggdb\")\r\n\r\nREPLICAT r_gt\r\n-- Target table has identical structure as source\r\nASSUMETARGETDEFS\r\nUSERID gg_tgt, PASSWORD password\r\n-- Map tables\r\nMAP gg_src.gg_test, TARGET gg_tgt.gg_test;\r\n<\/pre>\n
          Start Replicat<\/h3><\/span>\n
          \r\nstart replicat r_gt<\/pre>\n
          Test<\/h2><\/span>\n
          Insert Data to Source Table<\/h3><\/span>\n
          \r\ninsert into GG_SRC.gg_test values ('1','One');\r\ncommit;\r\nselect * from gg_test;\r\nID                   NAME\r\n--------------- --------------------\r\n1                    One\r\n<\/pre>\n
          Check Data in Target Table<\/h3><\/span>\n
          \r\nselect * from GG_TGT.gg_test;\r\n-- You should see\r\nID                   NAME\r\n--------------- --------------------\r\n1                    One\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
          Overview * This is a simplest scenario where data are replicated from one user (GG_SRC) to another (GG_TGT) on the same database instance using identical table structure (GG_TEST). * See this post on how to install GoldenGate on a Unix … 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":[587,228,227],"class_list":["post-1553","post","type-post","status-publish","format-standard","hentry","category-goldengate","tag-goldengate","tag-simplest","tag-tutorial"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8cRUO-p3","_links":{"self":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/1553","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=1553"}],"version-history":[{"count":16,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/1553\/revisions"}],"predecessor-version":[{"id":1555,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/1553\/revisions\/1555"}],"wp:attachment":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1553"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1553"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1553"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}