单机+单机之单向OGG配置ddl用友网络科技股份有限公司总部技术支持人：王安宁支持日期：2019年1月25日技术支持服务部–单机+单机的双向OGG配置报告文档控制此文档仅供用友技术支持服务部审阅，不得向与此无关的个人或机构传阅或复制。修改记录日期作者版本修改记录2019-1-25王安宁1.0创建初始版本分发者姓名职位审阅记录姓名职位相关文档技术支持服务部–单机+单机之双向OGG配置报告第2页目录文档控制.....................................................................................................................................2修改记录..............................................................................................................................2分发者..................................................................................................................................2审阅记录..............................................................................................................................2相关文档..............................................................................................................................2目录.............................................................................................................................................31.源端、目标端设计.............................................................................................................41.1IP分配：...................................................................................................................41.2软件安装路径............................................................................................................41.3源端、目标端数据库安装........................................................................................42.源端、目标端设计.............................................................................................................52.1验证之前的单向dml配置，不支持ddl复制.........................................................53.开始配置OGG支持DDL复制（源端操作）......................................................................63.1赋予ogg用户相应的权限，修改全局配置文件，添加ggschema参数...............63.2运行相关的sql脚本................................................................................................63.3源端修改extract参数文件，添加ddlincludeall参数，重启extract进程123.4目标端修改replicat参数文件，添加‘ddlincludeall’和‘ddlerrordefaultignoreretryopmaxretries3retrydelay5’参数，重启replicat进程134.测试DDL:源端和目标端的数据......................................................................................154.1源端..........................................................................................................................164.2目标端......................................................................................................................17技术支持服务部–单机+单机之双向OGG配置报告第3页1.源端、目标端设计1.1IP分配：操作系统内存Ip地址数据库版本ORACLE_SID磁盘大小源端Redhatlinux6.52048m192.0.2.10111.2.0.4.0ogg127g目标端Redhatlinux6.52048m192.0.2.10211.2.0.4.0ogg227g1.2软件安装路径软件安装路径oracle/u01/app/oracle/product/11.2.0.4/db_1ogg/ogg1.3源端、目标端数据库安装单机的数据库安装过程（略）技术支持服务部–单机+单机之双向OGG配置报告第4页2.源端、目标端设计2.1验证之前的单向dml配置，不支持ddl复制1)源端2)目标端3)源端技术支持服务部–单机+单机之双向OGG配置报告第5页3.开始配置OGG支持DDL复制（源端操作）3.1赋予ogg用户相应的权限，修改全局配置文件，添加ggschema参数3.2运行相关的sql脚本如果想使用DDL功能，需要运行支持DDL的相关脚本SQL>altersystemsetrecyclebin=offscope=spfile;--关闭回收站1)marker_setup.sql2)ddl_setup.sql3)role_setup.sql4)altertriggersys.GGS_DDL_TRIGGER_BEFOREdisable;5)grantggs_ggsuser_roletoogg;6)ddl_enable.sql7)?/rdbms/admin/dbmspool.sql8)ddl_pin.sqlogg[oracle@ogg1ogg]$aSQL*Plus:Release11.2.0.4.0ProductiononSatApr1311:27:592019Copyright(c)1982,2013,Oracle.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProductionWiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptionsSYS@ogg1>@marker_setup.sqlMarkersetupscriptYouwillbepromptedforthenameofaschemafortheOracleGoldenGatedatabaseobjects.NOTE:Theschemamustbecreatedpriortorunningthisscript.NOTE:StopallDDLreplicationbeforestartingthisinstallation.EnterOracleGoldenGateschemaname:ogg技术支持服务部–单机+单机之双向OGG配置报告第6页Markersetuptablescriptcomplete,runningverificationscript...PleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:SettingschemanametoOGGMARKERTABLE-------------------------------OKMARKERSEQUENCE-------------------------------OKScriptcomplete.SYS@ogg1>altertriggerSYS.GGS_DDL_TRIGGER_BEFOREdisable;Triggeraltered.SYS@ogg1>grantcreatetable,createsequencetoogg;Grantsucceeded.SYS@ogg1>SYS@ogg1>@ddl_setup.sqlOracleGoldenGateDDLReplicationsetupscriptVerifyingthatcurrentuserhasprivilegestoinstallDDLReplication...YouwillbepromptedforthenameofaschemafortheOracleGoldenGatedatabaseobjects.NOTE:ForanOracle10gsource,thesystemrecyclebinmustbedisabled.ForOracle11gandlater,itcanbeenabled.NOTE:Theschemamustbecreatedpriortorunningthisscript.NOTE:StopallDDLreplicationbeforestartingthisinstallation.EnterOracleGoldenGateschemaname:oggWorking,pleasewait...Spoolingtofileddl_setup_spool.txtCheckingforsessionsthatareholdinglocksonOracleGoldenGatemetadatatables...Checkcomplete.UsingOGGasaOracleGoldenGateschemaname.Working,pleasewait...DDLreplicationsetupscriptcomplete,runningverificationscript...PleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:技术支持服务部–单机+单机之双向OGG配置报告第7页SettingschemanametoOGGCLEAR_TRACESTATUS:Line/posError-------------------------------------------------------------------------------------NoerrorsNoerrorsCREATE_TRACESTATUS:Line/posError-------------------------------------------------------------------------------------NoerrorsNoerrorsTRACE_PUT_LINESTATUS:Line/posError-------------------------------------------------------------------------------------NoerrorsNoerrorsINITIAL_SETUPSTATUS:Line/posError-------------------------------------------------------------------------------------NoerrorsNoerrorsDDLVERSIONSPECIFICPACKAGESTATUS:Line/posError-------------------------------------------------------------------------------------NoerrorsNoerrorsDDLREPLICATIONPACKAGESTATUS:Line/posError-------------------------------------------------------------------------------------NoerrorsNoerrorsDDLREPLICATIONPACKAGEBODYSTATUS:Line/posError-------------------------------------------------------------------------------------NoerrorsNoerrorsDDLIGNORETABLE-----------------------------------OKDDLIGNORELOGTABLE-----------------------------------OKDDLAUXPACKAGESTATUS:Line/posError-------------------------------------------------------------------------------------NoerrorsNoerrorsDDLAUXPACKAGEBODYSTATUS:Line/posError-------------------------------------------------------------------------------------技术支持服务部–单机+单机之双向OGG配置报告第8页NoerrorsNoerrorsSYS.DDLCTXINFOPACKAGESTATUS:Line/posError-------------------------------------------------------------------------------------NoerrorsNoerrorsSYS.DDLCTXINFOPACKAGEBODYSTATUS:Line/posError-------------------------------------------------------------------------------------NoerrorsNoerrorsDDLHISTORYTABLE-----------------------------------OKDDLHISTORYTABLE(1)-----------------------------------OKDDLDUMPTABLES-----------------------------------OKDDLDUMPCOLUMNS-----------------------------------OKDDLDUMPLOGGROUPS-----------------------------------OKDDLDUMPPARTITIONS-----------------------------------OKDDLDUMPPRIMARYKEYS-----------------------------------OKDDLSEQUENCE-----------------------------------OKGGS_TEMP_COLS-----------------------------------OKGGS_TEMP_UK-----------------------------------OKDDLTRIGGERCODESTATUS:Line/posError-------------------------------------------------------------------------------------NoerrorsNoerrorsDDLTRIGGERINSTALLSTATUS-----------------------------------技术支持服务部–单机+单机之双向OGG配置报告第9页OKDDLTRIGGERRUNNINGSTATUS----------------------------------------------------------------------ENABLEDSTAYMETADATAINTRIGGER----------------------------------------------------------------------OFFDDLTRIGGERSQLTRACING----------------------------------------------------------------------0DDLTRIGGERTRACELEVEL----------------------------------------------------------------------0LOCATIONOFDDLTRACEFILE------------------------------------------------------------------------------------------------------------------------/u01/app/oracle/diag/rdbms/ogg1/ogg1/trace/ggs_ddl_trace.logAnalyzinginstallationstatus...STATUSOFDDLREPLICATION------------------------------------------------------------------------------------------------------------------------SUCCESSFULinstallationofDDLReplicationsoftwarecomponentsScriptcomplete.SYS@ogg1>SYS@ogg1>@role_setup.sqlGGSRolesetupscriptThisscriptwilldropandrecreatetheroleGGS_GGSUSER_ROLETouseadifferentrolename,quitthisscriptandthenedittheparams.sqlscripttochangethegg_roleparametertothepreferredname.(Donotrunthescript.)YouwillbepromptedforthenameofaschemafortheGoldenGatedatabaseobjects.NOTE:Theschemamustbecreatedpriortorunningthisscript.NOTE:StopallDDLreplicationbeforestartingthisinstallation.EnterGoldenGateschemaname:oggWrotefilerole_setup_set.txtPL/SQLproceduresuccessfullycompleted.RolesetupscriptcompleteGrantthisroletoeachuserassignedtotheExtract,GGSCI,andManagerprocesses,byusingthefollowingSQLcommand:GRANTGGS_GGSUSER_ROLETOwhereistheuserassignedtotheGoldenGateprocesses.技术支持服务部–单机+单机之双向OGG配置报告第10页SYS@ogg1>grantggs_ggsuser_roletoogg;Grantsucceeded.SYS@ogg1>SYS@ogg1>@ddl_enable.sqlTriggeraltered.SYS@ogg1>@?/rdbms/admin/dbmspool.sqlPackagecreated.Grantsucceeded.SYS@ogg1>@ddl_pin.sqloggPL/SQLproceduresuccessfullycompleted.PL/SQLproceduresuccessfullycompleted.PL/SQLproceduresuccessfullycompleted.SYS@ogg1>3.3源端修改extract参数文件，添加ddlincludeall参数，重启extract进程GGSCI(ogg1)4>infoallProgramStatusGroupLagatChkptTimeSinceChkptMANAGERRUNNINGEXTRACTRUNNINGDPE_DEMO00:00:0000:00:05EXTRACTRUNNINGEXT_DEMO00:22:3100:00:01GGSCI(ogg1)5>editparamsext_demoGGSCI(ogg1)6>viewparamsext_demoextractext_demosetenv(ORACLE_SID=ogg1)setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)useridogg,passwordoggddlincludeallreportcountevery1minutes,ratenumfiles5000discardfile./dirrpt/ext_demo.dsc,append,megabytes1000discardrolloverat3:00exttrail./dirdat/r1,megabytes100dynamicresolutiontranlogoptionsexcludeuseroggtranlogoptionsconvertucs2clobstablescott.*;GGSCI(ogg1)7>技术支持服务部–单机+单机之双向OGG配置报告第11页GGSCI(ogg1)7>stopext_demoSendingSTOPrequesttoEXTRACTEXT_DEMO...Requestprocessed.GGSCI(ogg1)8>startext_demoSendingSTARTrequesttoMANAGER...EXTRACTEXT_DEMOstartingGGSCI(ogg1)9>infoallProgramStatusGroupLagatChkptTimeSinceChkptMANAGERRUNNINGEXTRACTRUNNINGDPE_DEMO00:00:0000:00:03EXTRACTRUNNINGEXT_DEMO00:00:0000:00:10GGSCI(ogg1)10>infoextractext_demoEXTRACTEXT_DEMOLastStarted2019-04-2219:26StatusRUNNINGCheckpointLag00:00:00(updated00:00:07ago)LogReadCheckpointOracleRedoLogs2019-04-2219:26:16Thread1,Seqno11,RBA26599936SCN0.1150289(1150289)GGSCI(ogg1)11>3.4目标端修改replicat参数文件，添加‘ddlincludeall’和‘ddlerrordefaultignoreretryopmaxretries3retrydelay5’参数，重启replicat进程GGSCI(ogg2)1>infoallProgramStatusGroupLagatChkptTimeSinceChkptMANAGERRUNNINGREPLICATABENDEDREP_DEMO00:00:00225:51:59GGSCI(ogg2)2>editparamsrep_demoGGSCI(ogg2)3>viewparamsrep_demoreplicatrep_demosetenv(ORACLE_SID=ogg2)setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)useridogg,passwordoggddlincludeallddlerrordefaultignoreretryopmaxretries3retrydelay5reportcountevery30minutes,ratereperrordefault,abendnumfiles5000--handlecollisionsassumetargetdefsdiscardfile./dirrpt/rep_demo.dsc,append,megabytes1000allownoopupdatesmapscott.*,targetscott.*;技术支持服务部–单机+单机之双向OGG配置报告第12页GGSCI(ogg2)4>stoprep_demoREPLICATREP_DEMOisalreadystopped.GGSCI(ogg2)30>startrep_demoSendingSTARTrequesttoMANAGER...REPLICATREP_DEMOstartingGGSCI(ogg2)31>infoallProgramStatusGroupLagatChkptTimeSinceChkptMANAGERRUNNINGREPLICATRUNNINGREP_DEMO00:00:0000:00:08GGSCI(ogg2)33>inforeplicatrep_demoREPLICATREP_DEMOLastStarted2019-04-2219:50StatusRUNNINGCheckpointLag00:00:00(updated00:00:09ago)LogReadCheckpointFile./dirdat/t1000003FirstRecordRBA2749GGSCI(ogg2)34>技术支持服务部–单机+单机之双向OGG配置报告第13页4.测试DDL:源端和目标端的数据情景：1)ogg1库，先createtablet1(idnumber,addrvarchar2(11));再insertintot1values(1,’bj’);insertintot1values(2,’sh’);commit;2）由于ogg2库的scott用户，没有createtable的权限，故目标库报错：3）故采取ogg2库，从最新的scn，启动replicatrep_demo进程。源端：目标端：4.1源端SCOTT@ogg1>createtablet10(idnumber,addrvarchar(11));技术支持服务部–单机+单机之双向OGG配置报告第14页Tablecreated.SCOTT@ogg1>insertintot10values(1,'bj');1rowcreated.SCOTT@ogg1>insertintot10values(2,'sh');1rowcreated.SCOTT@ogg1>commit;Commitcomplete.SCOTT@ogg1>selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')fromdual;TO_CHAR(SYSDATE,'YY-------------------2019-04-2220:42:19SCOTT@ogg1>droptablet10;Tabledropped.SCOTT@ogg1>selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')fromdual;TO_CHAR(SYSDATE,'YY-------------------2019-04-2220:42:50SCOTT@ogg1>createviewv_empasselect*fromemp;Viewcreated.SCOTT@ogg1>selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')fromdual;TO_CHAR(SYSDATE,'YY-------------------2019-04-2220:46:38SCOTT@ogg1>dropviewv_emp;Viewdropped.SCOTT@ogg1>4.2目标端1）需要先grant授权:grantcreateanytabletoogg;grantcreateanyviewtoogg;grantdropanytabletoogg;grantdropanyviewtoogg;SCOTT@ogg2>select*fromt10;技术支持服务部–单机+单机之双向OGG配置报告第15页IDADDR---------------------1bj2shSCOTT@ogg2>selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')fromdual;TO_CHAR(SYSDATE,'YY-------------------2019-04-2220:42:25SCOTT@ogg2>select*fromt10;select*fromt10*ERRORatline1:ORA-00942:tableorviewdoesnotexistSCOTT@ogg2>selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')fromdual;TO_CHAR(SYSDATE,'YY-------------------2019-04-2220:42:48SCOTT@ogg2>select*fromv_emp;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO------------------------------------------------------------------------------7369SMITHCLERK790217-DEC-802400207499ALLENSALESMAN769820-FEB-811600300307521WARDSALESMAN769822-FEB-811250500307566JONESMANAGER783902-APR-812975207654MARTINSALESMAN769828-SEP-8112501400307698BLAKEMANAGER783901-MAY-812850307782CLARKMANAGER783909-JUN-812450107788SCOTTANALYST756619-APR-873000207839KINGPRESIDENT17-NOV-815000107844TURNERSALESMAN769808-SEP-8115000307876ADAMSCLERK778823-MAY-871100207900JAMESCLERK769803-DEC-81950307902FORDANALYST756603-DEC-813000207934MILLERCLERK778223-JAN-8213001014rowsselected.SCOTT@ogg2>selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')fromdual;TO_CHAR(SYSDATE,'YY-------------------2019-04-2220:46:30SCOTT@ogg2>select*fromv_emp;select*fromv_emp*ERRORatline1:ORA-00942:tableorviewdoesnotexistSCOTT@ogg2>技术支持服务部–单机+单机之双向OGG配置报告第16页技术支持服务部–单机+单机之双向OGG配置报告第17页
