{"id":1418,"date":"2018-05-15T10:19:56","date_gmt":"2018-05-15T02:19:56","guid":{"rendered":"http:\/\/www.royalwzy.com\/?p=1418"},"modified":"2018-05-15T10:19:56","modified_gmt":"2018-05-15T02:19:56","slug":"goldengate%e5%ad%a6%e4%b9%a06-oracle%e5%88%b0sqlserver%e6%95%b0%e6%8d%ae%e5%90%8c%e6%ad%a5","status":"publish","type":"post","link":"http:\/\/www.royalwzy.com\/?p=1418","title":{"rendered":"GoldenGate\u5b66\u4e606–Oracle\u5230SQLServer\u6570\u636e\u540c\u6b65"},"content":{"rendered":"

1.\u76f8\u5173\u73af\u5883;
\nOracle:11.2.0.1 on Linux x64
\nSQLServer:2005 on Windows 2008R2 x64
\nOGG:12.1.2.1.0<\/p>\n

2.\u51c6\u5907\u6e90\u7aef(Oralce\u7aef);
\n2.1\u5f00\u542f\u6570\u636e\u5e93\u7ea7\u522b\u9644\u52a0\u65e5\u5fd7;
\nSELECT supplemental_log_data_min, force_logging FROM v$database;
\nSQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
\nSQL> ALTER DATABASE FORCE LOGGING;
\nSELECT supplemental_log_data_min, force_logging FROM v$database;
\nSQL> ALTER SYSTEM SWITCH LOGFILE;
\n2.2\u521b\u5efa\u7528\u6237\u5e76\u6388\u6743;
\nCREATE USER ggadm IDENTIFIED BY ‘********’;
\nEXEC dbms_goldengate_auth.grant_admin_privilege(‘ggadm’); for Oracle 11.2.0.4 and later
\nEXEC dbms_goldengate_auth.grant_admin_privilege(‘ggadm’,grant_select_privileges=>true); for Oracle 11.2.0.3 or Earlier
\n2.3\u5f00\u542f\u76f8\u5173\u53c2\u6570;
\nALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=true; for an Oracle 11.2.0.4 or greated database
\n2.4\u5728ogg\u4e2d\u914d\u7f6e\u5b89\u5168\u8ba4\u8bc1;
\n.\/ggsci
\nADD CREDENTIALSTORE
\nALTER CREDENTIALSTORE ADD USER ggadm, PASSWORD ******** ALIAS alias ogg
\n2.5\u5f00\u542f\u8868\u7ea7\u522b\u9644\u52a0\u65e5\u5fd7(\u53ef\u9009);
\n.\/ggsci
\nDBLOGIN USERIDALIAS ogg
\nADD TRANDATA [container.]schema.table
\nINFO TRANDATA [container.]schema.table<\/p>\n

3.\u914d\u7f6e\u8868\u7ed3\u6784\u6620\u5c04;
\n3.1\u5728\u6e90\u7aef\u5b9a\u4e49\u9700\u8981\u6620\u5c04\u7684\u8868;
\n.\/ggsci
\nEDIT PARAM DEFGEN
\nDEFSFILE .\/dirdef\/source.def, PURGE
\nUSERIDALIAS alias
\nTABLE schema.table1;
\nTABLE schema.table1;
\n3.2\u751f\u6210\u6620\u5c04\u6587\u4ef6;
\nshell> defgen paramfile .\/dirprm\/defgen.prm
\n3.3\u62f7\u8d1d\u6587\u4ef6\u5230\u76ee\u6807\u7aef\u76f8\u5e94\u7684\u76ee\u5f55;<\/p>\n

4.\u5728\u6e90\u7aef\u548c\u76ee\u6807\u7aef\u5206\u522b\u5f00\u542f\u7ba1\u7406\u8fdb\u7a0b;
\nEDIT PARAMS mgr<\/p>\n

PORT 7809<\/p>\n

START mgr
\nINFO mgr<\/p>\n

5.\u5728\u6e90\u7aef\u914d\u7f6e\u6570\u636e\u6cf5\u62bd\u53d6(Data Pump Extract)\u8fdb\u7a0b;
\nADD EXTRACT EXT1, TRANLOG, BEGIN NOW, THREADS 1
\nEDIT PARAMS ext1<\/p>\n

EXTRACT ext1
\nUSERIDALIAS ogg
\nRMTHOST dst_ip, MGRPORT 7809 ENCRYPT AES192, KEYNAME securekey2
\nRMTTRAIL .\/dirdat\/rt
\n# SEQUENCE schema.sequence_name;
\nTABLE schema.table;<\/p>\n

ADD RMTTRAIL .\/dirdat\/rt, EXTRACT ext1, MEGABYTES 100;
\nINFO RMTTRAIL *<\/p>\n

# START EXTRACT ext1
\n# INFO EXTRACT ext1, DETAIL<\/p>\n

6.\u914d\u7f6e\u76ee\u6807\u7aef(SQLServer\u7aef);
\n6.1\u914d\u7f6eODBC\u6570\u636e\u6e90:\u63a7\u5236\u9762\u677f->\u7ba1\u7406\u5de5\u5177->\u6570\u636e\u6e90(ODBC)->\u7cfb\u7edfDSN(\u6dfb\u52a0),\u62e9\u9a71\u52a8\u7a0b\u5e8f\u7c7b\u578b\u4e3a[SQL Server Native Client],\u6bd4\u5982:\u540d\u5b57\u4e3aogg;
\n6.2\u5728\u76ee\u6807\u7aef\u521b\u5efa\u4e00\u6837\u7684\u8868\u7ed3\u6784;
\n6.3\u6dfb\u52a0\u68c0\u67e5\u70b9\u8868;
\n.\/ggsci
\nEDIT PARAMS .\/GLOBALS<\/p>\n

CHECKPOINTTABLE ggschkpt<\/p>\n

DBLOGIN SOURCEDB ogg, USERID uid, PASSWORD pwd
\nADD CHECKPOINTTABLE
\n\u9000\u51fa\u4e4b\u540e\u518d\u8fdb\u4e00\u6b21\u5ba2\u6237\u7aef;
\n6.4\u5728\u76ee\u6807\u7aef\u914d\u7f6e\u590d\u5236(Change Delivery)\u8fdb\u7a0b;
\nADD REPLICAT rpl1, EXTTRAIL E:\\ogg121\\dirdat\\rt
\nEDIT PARAMS rpl1<\/p>\n

REPLICAT rpl1
\nTARGETDB ogg, USERID uid, PASSWORD pwd
\nHANDLECOLLISIONS
\nSOURCEDEFS E:\\ogg121\\dirdef\\source.def
\nREPERROR DEFAULT, DISCARD
\nDISCARDFILE E:\\ggate\\dirrpt\\rpl1.dsc APPEND
\nGETTRUNCATES
\nMAP schema.table, TARGET db.table;<\/p>\n

# START REPLICAT rpl1
\n# INFO REPLICAT rpl1<\/p>\n

7.\u67e5\u770b\u5bf9\u5e94\u7684\u62a5\u544a;
\n7.1\u67e5\u770b\u62bd\u53d6\u8fdb\u7a0b\u62a5\u544a;
\nSEND EXTRACT EXT1, REPORT
\nVIEW REPORT EXT1
\n7.2\u67e5\u770b\u590d\u5236\u8fdb\u7a0b\u62a5\u544a;
\nSEND REPLICAT RPL1, REPORT
\nVIEW REPORT RPL1<\/p>\n

TIPS:\u5728Windows\u4e0b\u5b89\u88c5ogg\u65f6\u9700\u8981\u6ce8\u518c\u670d\u52a1;
\nC:\\GG_DIR> INSTALL ADDSERVICE
\n.\/ggsci
\nCREATE SUBDIRS<\/p>\n","protected":false},"excerpt":{"rendered":"

1.\u76f8\u5173\u73af\u5883; Oracle:11.2.0.1 on Linux x64 SQLServer:2005 on […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-1418","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/1418","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1418"}],"version-history":[{"count":1,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/1418\/revisions"}],"predecessor-version":[{"id":1419,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/1418\/revisions\/1419"}],"wp:attachment":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1418"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1418"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1418"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}