oracle與之間數(shù)據(jù)同步使用goldengate

以下演示部分采用RHELLINUX系統(tǒng)，數(shù)據(jù)庫采用ORACLE主機updba上soctt用戶下數(shù)據(jù)同步到主機upgg上scottupdba,upgg服務(wù)器上scott數(shù)據(jù)一致<<數(shù)據(jù)遷移>>課程中也講述過很多種遷移技巧。大家也可以用goldengate數(shù)據(jù)遷移的辦法。[oracle@updba~]$sqlplus SQL*Plus:Release11.2.0.3.0ProductiononSatJun1618:59:402012Copyright(c)1982,2011, AllrightsConnectedOracleDatabase11gEnterpriseEditionRelease11.2.0.3.0-64bitProductionWiththePartitioning,OracleLabelSecurity,OLAP,DataMining,DisconnectedfromOracleDatabase11gEnterpriseEditionRelease11.2.0.3.0-64bitProductionWiththePartitioning,OracleLabelSecurity,OLAP,DataMining,[oracle@updba~]$[oracle@upgg~]$sqlplus SQL*Plus:Release11.2.0.3.0ProductiononSatJun1619:04:542012Copyright(c)1982,2011, AllrightsConnectedOracleDatabase11gEnterpriseEditionRelease11.2.0.3.0-64bitProductionWiththePartitioning,OracleLabelSecurity,OLAP,DataMining,DisconnectedfromOracleDatabase11gEnterpriseEditionRelease11.2.0.3.0-64bitProductionWiththePartitioning,OracleLabelSecurity,OLAP,DataMining,[oracle@upgg~]$安裝上gg2.1--2.7部分在 與upgg服務(wù)器上均操建立運行g(shù)oldengate這里采用oracle軟件運行用戶oracle,注意修改ORACLE_SID,ORACLE_HOME,LD_LIBRARY_PATH變量#.bash_profile#Getthealiasesandfunctionsif[-f~/.bashrc];then.#UserspecificenvironmentandstartupprogramsexportORACLE_BASE=/u01/oracleexportORACLE_HOME=/u01/oracle/11gexportexportPATH初始化建立gg管理用 本例中為SQL>createtablespace ggdatadatafile'$ORACLE_BASE/oradata/$ORACLE_SID/ggdata01.dbf'size200m;Tablespacecreated.SQL>create ggmidentifiedbyoracledefaulttablespaceggdata;Usercreated.SQL>grantconnect,resourcetoggm;Grantsucceeded.SQL>grantdbatoggm;Grantsucceeded.配置managerupdba[oracle@upgg~]$OracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights GGSCI )1>editparamsport~"dirprm/mgr.prm"1L,10CGGSCI( )2>startmgrManagerstarted.GGSCI )3>infoManagerisrunning(IP GGSCI )配置DML同[oracle@updba~]$OracleGoldenGateCommandInterpreterfor Linux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI(u )1>infomgrManagerisrunning(IPport GGSCI(u )2>addextractescott,tranlog,beginnow,threads1EXTRACTadded.GGSCI )3>infoextract 2012-06-16 StatusCheckpointLag 00:00:00(updated00:00:12ago)LogReadCheckpoint OracleRedoLogs Thread1,Seqno0,RBASCN0.0GGSCI(u )4>editparamsescottextractescottuseridggm,passwordoraclermthostupgg,mgrport7809rmttrail./dirdat/establescott.dept;tablescott.emp;tablescott.bonus;~"dirprm/escott.prm"[New]8L,165CGGSCI(u )5>addrmttrail./dirdat/es,extractescottRMTTRAILadded.GGSCI(u )6>informttrail./dirdat/esExtractTrail:./dirdat/esSeqno:RBA:FileSize:GGSCI )7>infoextract 2012-06-16 StatusCheckpointLag 00:00:00(updated00:02:32ago)LogReadCheckpoint OracleRedoLogs Thread1,Seqno0,RBASCN0.0RemoteTrail Max Extract Not 06-1619:14Current Reportfile /u01/goldengate/112101/dirrpt/ESCOTT.rpt(doesnotyetexist)Parameterfile Checkpointfile Processfile Stdoutfile Errorlog GGSCI )[oracle@upgg~]$OracleGoldenGateCommandInterpreterfor Linux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI( )1>infomgrManagerisrunning(IPport GGSCI( )2>editparams./GLOBALSGGSCHEMAGGM~"./GLOBALS"2L,40CwrittenGGSCI )3>[oracle@upgg~]$OracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),Oracle11gonApr232012Copyright(C)1995,2012,Oracleand/oritsaffiliates.All GGSCI( )1>dbloginuseridggm,passwordoracleSuccessfullyloggedintodatabase.GGSCI( )2>addcheckpointtableckptabSuccessfullycreatedcheckpointtableckptab.GGSCI )3>[oracle@upgg~]$OracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI( )1>addreplicatrscott,exttrail./dirdat/esREPLICATadded.GGSCI( )2>editparamsrscottreplicatrscottuseridggm,passwordmapscott.dept,targetscott.dept;mapscott.emp,targetscott.emp;mapscott.bonus,targetscott.bonus;~"dirprm/rscott.prm"[New]9L,259CwrittenGGSCI( )3>[oracle@upgg~]$OracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI( )1>startreplicatrscottSendingSTARTrequesttoMANAGER...REPLICATRSCOTTstartingGGSCI )2>inforeplicat StatusCheckpointLag 00:00:00(updated00:00:02ago)LogReadCheckpoint File./dirdat/es000000First GGSCI )[oracle@updba~]$OracleGoldenGateCommandInterpreterfor Linux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI(u )1>startextractescottSendingSTARTrequesttoMANAGER...EXTRACTESCOTTstartingGGSCI )2>infoextract CheckpointLag 00:00:00(updated00:23:53ago)LogReadCheckpoint OracleRedoLogs Thread1,Seqno28,SCN0.0GGSCI )DML操作之前SQL>selectsalfromempwhere

SQL>selectsalfromempwhereDML操作SQL>updateempsetsal=sal+1;14rowsupdated.SQL>insertintosalgradevalues(6,10000,15000);1rowcreated.SQL>commit;SQL>selectsalfromempwhereSQL>select*from 1234566rows可以看到DML已經(jīng)同步localtrail方式同步[oracle@upgg~]$OracleGoldenGateCommandInterpreterfor Linux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI( )2>stopreplicatrscottSendingSTOPrequesttoREPLICATRSCOTT...Requestprocessed.GGSCI )3>deletereplicatOracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights GGSCI(u )1>stop SendingSTOPrequesttoEXTRACTESCOTT...Requestprocessed.GGSCI ) extract[oracle@updba~]$OracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI )GGSCI(u )2>addextractescott,tranlog,beginnowEXTRACTadded.GGSCI(u )3>addexttrail./dirdat/lt,extractescottEXTTRAILadded.GGSCI(u )4>addextractdpump,exttrailsource./dirdat/ltEXTRACTadded.GGSCI(u )5>editparamsescottextractescottuseridggm,passwordexttrail./dirdat/lttablescott.dept;tablescott.emp;tablescott.bonus;~~"dirprm/escott.prm"7L,139CGGSCI(u )6>addrmttrail./dirdat/rt,extractdpumpRMTTRAILadded.GGSCI(u )7>editparamsdpumpextractdpumpuseridggm,passwordrmttrail./dirdat/rttablescott.dept;tablescott.emp;tablescott.bonus;~~"dirprm/dpump.prm"[New]9L,174CwrittenGGSCI(u )8>[oracle@upgg~]$OracleGoldenGateCommandInterpreterfor Linux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI( )1>addreplicatrscott,exttrail./dirdat/rtREPLICATadded.GGSCI( )2>editparamsrscottreplicatrscottuseridggm,passwordoraclemapscott.emp,targetmapscott.bonus,targetscott.bonus;~"dirprm/rscott.prm"7L,204CGGSCI )[oracle@updba~]$OracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights GGSCI )1>infoManagerisrunning(IPport GGSCI(u )2>viewparamsescottextractescottexttrail./dirdat/lttablescott.dept;tablescott.emp;tablescott.bonus;GGSCI(u )3>startextractescottSendingSTARTrequesttoMANAGER...EXTRACTESCOTTGGSCI )4>infoextract LastStarted2012-06-16 CheckpointLag 00:01:05(updated00:00:08ago)LogReadCheckpoint OracleRedoLogs2012-06-1621:12:26 Seqno29,RBASCN0. GGSCI )5>viewparamsextractuseridggm,passwordoraclermthostupgg,mgrport7809rmttrail./dirdat/rttablescott.dept;tablescott.emp;tablescott.bonus;GGSCI(u )6>startextractdpumpSendingSTARTrequesttoMANAGER...GGSCI )7>infoextract LastStarted2012-06-16 StatusCheckpointLag 00:00:00(updated00:00:00ago)LogReadCheckpoint File./dirdat/lt000000First [oracle@upgg~]$OracleGoldenGateCommandInterpreterfor Linux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI )1>infoManagerisrunning(IP GGSCI( )2>viewparamsrscottreplicatrscottuseridggm,passwordmapscott.emp,targetmapscott.bonus,targetscott.bonus;GGSCI( )3>startreplicatrscottSendingSTARTrequesttoMANAGER...GGSCI )4>inforeplicat StatusCheckpointLag 00:00:00(updated00:00:04ago)LogReadCheckpoint File./dirdat/rt000000 GGSCI )SQL>select*from 1234566rowsSQL>selectsalfromempwheredeptno=10;SQL>select*from 1234566rowsSQL>selectsalfromempwheredeptno=10;SQL>update 14rowsupdated.SQL>deletesalgradewheregrade=6;1rowdeleted.SQL>commit;SQL>select*from 12345SQL>selectsalfromempwheredeptno=10;可以看到DML操作已經(jīng)同步了DDL同updba,upgg上如下打開DDL支持[oracle@updba~]$cd$GG_HOME[oracle@updba112101]$sqlplus SQL*Plus:Release11.2.0.3.0ProductiononSatJun1621:33:442012Copyright(c)1982,2011,Oracle. Allrights.ConnectedOracleDatabase11gEnterpriseEditionRelease11.2.0.3.0-64bitProductionWiththePartitioning,OracleLabelSecurity,OLAP,DataMining,SQL>startmarker_setup.sqlMarkersetupYouwillbepromptedforthenameofaschemafortheOracleGoldenGatedatabaseobjects.NOTE:Theschemamustbecreatedpriortorunningthisscript.NOTE:StopallDDLreplicationbeforestartingthisEnterOracleGoldenGateschemaMarkersetuptablescriptcomplete,runningverificationPleaseenterthenameofaschemafortheGoldenGatedatabaseSettingschemanametoScriptcomplete.SQL>startddl_setupOracleGoldenGateDDLReplicationsetupYouwillbepromptedforthenameofaschemafortheOracleGoldenGatedatabaseNOTE:ForanOracle10gsource,thesystemrecyclebinmustbedisabled.ForOracle11gandlater,itcanbeNOTE:Theschemamustbecreatedpriortorunningthisscript.NOTE:StopallDDLreplicationbeforestartingthisEnterOracleGoldenGateschemaname:ggmWorking,pleasewait...SpoolingtofileCheckingforsessionsthatareholdinglocksonOracleGoldenGatemetadatatables...Checkcomplete.UsingGGMasaOracleGoldenGateschemaname.Working,pleasewait...DDLreplicationsetupscriptcomplete,runningverificationscript...PleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:SettingschemanametoGGMCLEAR_TRACE No No No No No NoINITIAL_SETUP No NoDDLVERSIONSPECIFICPACKAGE No No No NoDDLREPLICATIONPACKAGEBODY No NoDDLIGNORE PACKAGE No NoDDLAUXPACKAGEBODY No No PACKAGE No No PACKAGEBODY No NoDDLDUMPLOGGROUPSDDLTRIGGERCODE No NoDDLTRIGGERRUNNINGSTATUS0LOCATIONOFDDLTRACEyzinginstallationstatus...STATUSOFDDLSUCCESSFULinstallationofDDLReplicationsoftwarecomponentsScriptcomplete.SQL>startGGSRolesetupThisscriptwilldropandrecreatetheroleTouseadifferentrolename,quitthisscriptandthenedittheparams.sqlscripttochangethegg_roleparametertothepreferredname.(Donotrunthescript.)YouwillbepromptedforthenameofaschemafortheGoldenGatedatabaseobjects.NOTE:Theschemamustbecreatedpriortorunningthisscript.NOTE:StopallDDLreplicationbeforestartingthisEnterGoldenGateschemaname:ggmWrotefilerole_setup_set.txtPL/SQLproceduresuccessfullyRolesetupscriptGrantthisroletoeachuserassignedtotheExtract,GGSCI,andManagerprocesses,byusingthefollowingSQLGRANTGGS_GGSUSER_ROLETOwhere<loggedUser>istheuserassignedtotheGoldenGateprocesses.SQL>grantggs_ggsuser_roletoggm;Grantsucceeded.SQL>startddl_enableTriggerSQL>startddl_pinPL/SQLproceduresuccessfullyPL/SQLproceduresuccessfullyPL/SQLproceduresuccessfullycompleted.[oracle@upgg~]$cd [oracle@upgg112101]$sqlplus/ SQL*Plus:Release11.2.0.3.0ProductiononSatJun1621:47:172012Copyright(c)1982,2011,Oracle. Allrights.ConnectedOracleDatabase11gEnterpriseEditionRelease11.2.0.3.0-64bitProductionWiththePartitioning,OracleLabelSecurity,OLAP,DataMining,SQL>startmarker_setupMarkersetupYouwillbepromptedforthenameofaschemafortheOracleGoldenGatedatabaseobjects.NOTE:Theschemamustbecreatedpriortorunningthisscript.NOTE:StopallDDLreplicationbeforestartingthisinstallation.EnterOracleGoldenGateschemaname:ggmMarkersetuptablescriptcomplete,runningverificationPleaseenterthenameofaschemafortheGoldenGatedatabaseSettingschemanametoScriptcomplete.SQL>startddl_setupOracleGoldenGateDDLReplicationsetupYouwillbepromptedforthenameofaschemafortheOracleGoldenGatedatabaseNOTE:ForanOracle10gsource,thesystemrecyclebinmustbedisabled.ForOracle11gandlater,itcanbeNOTE:Theschemamustbecreatedpriortorunningthisscript.NOTE:StopallDDLreplicationbeforestartingthisEnterOracleGoldenGateschemaname:ggmWorking,pleasewait...SpoolingtofileCheckingforsessionsthatareholdinglocksonOracleGoldenGatemetadatatables...Checkcomplete.UsingGGMasaOracleGoldenGateschemaname.Working,pleasewait...DDLreplicationsetupscriptcomplete,runningverificationscript...PleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:SettingschemanametoGGMCLEAR_TRACESTATUS: No No No No No NoINITIAL_SETUP No NoDDLVERSIONSPECIFICPACKAGE No No No NoDDLREPLICATIONPACKAGEBODY No NoDDLIGNORE PACKAGE No NoDDLAUXPACKAGEBODY No No PACKAGE No No PACKAGEBODY No NoDDLDUMPDDLDUMPLOGGROUPSDDLTRIGGERCODE No NoDDLTRIGGERRUNNINGSTATUS0LOCATIONOFDDLTRACEyzinginstallationstatus...STATUSOFDDLSUCCESSFULinstallationofDDLReplicationsoftwarecomponentsScriptcomplete.SQL>startGGSRolesetupThisscriptwilldropandrecreatetheroleTouseadifferentrolename,quitthisscriptandthenedittheparams.sqlscripttochangethegg_roleparametertothepreferredname.(Donotrunthescript.)YouwillbepromptedforthenameofaschemafortheGoldenGatedatabaseobjects.NOTE:Theschemamustbecreatedpriortorunningthisscript.NOTE:StopallDDLreplicationbeforestartingthisEnterGoldenGateschemaname:ggmWrotefilerole_setup_set.txtPL/SQLproceduresuccessfullyRolesetupscriptGrantthisroletoeachuserassignedtotheExtract,GGSCI,andManagerprocesses,byusingthefollowingSQLGRANTGGS_GGSUSER_ROLETOwhere<loggedUser>istheuserassignedtotheGoldenGateSQL>grantggs_ggsuser_roleto Grantsucceeded.Triggeraltered.SQL>startddl_