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

ORACLEMYSQL之間數(shù)據(jù)同步使用將mysql的數(shù)據(jù)同步到oracle數(shù)據(jù)庫(kù)中。mysql5.1oracle11g將mysql的stt庫(kù)的表數(shù)據(jù)同步到oracle的sttmysql服務(wù)器updba安裝[root@updba~]#mkdir/u01/mysql/[root@updba~]#cd/u01/mysql/[root@updbamysql]#unzip inflating:ggs_Linux_x64_MySQL_64bit.tarinflating:OGG_WinUnix_Rel_Notes_.1.pdfinflating:OracleGoldenGate.1README.doc[root@updbamysql]#mkdirggs[root@updbamysql]#cd[root@updbaggs]#tarxf../ggs_Linux_x64_MySQL_64bit.tar[root@updbaggs]#vi~/.bash_profile#.bash_profile#Getthealiasesandfunctionsif[-f~/.bashrc];then.#UserspecificenvironmentandstartupprogramsexportPATH[root@updbaggs]#./ggsciOracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),MySQLEnterpriseonApr23201204:34:25Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights )1>createCreatingsubdirectoriesundercurrentdirectory/u01/mysql/ggsParameterfiles Report Checkpointfiles Processstatusfiles /u01/mysql/ggs/dirpcs:createdSQLscriptfiles /u01/mysql/ggs/dirsql:createdDatabasedefinitionsfiles /u01/mysql/ggs/dirdef:createdExtractdatafiles /u01/mysql/ggs/dirdat:createdTemporaryfiles Stdout )2>editparamsport~~GGSCI(u )3>startmgrManagerstarted. )4>infoManagerisrunning(IP )5>配置mysql[root@updba~]#cp mysql-connector-odbc-5.1.5r1144/mysql-server-5.1.52/[root@updba~]#cp/usr/share/doc/mysql-server-5 cp:overwrite` f'?y[root@updba~]# mentthefollowingifyouwanttologupdates#binaryloggingformat-mixed [root@updba~]#/etc/init.d/mysqld Stopmysqld: Startingmysqld:[ [root@updba~]#[root@updba~]#ps- 023:25 00:00:00/bin/sh/usr/bin/mysqld_safe-- 023:25 00:00:00/usr/libexec/mysqld--basedir=/usr-- 023:25pts/1 [root@updba~]#[root@updba~]#ln -s/var/lib/mysql/mysql.sock [root@updba~]# --2012-06-1523:09:37-- Resolvingd ...4Connectingtod HTTPrequestsent,awaitingresponse...200OKLength:92149(90K)[application/octet-stream]Savingto:world_innodb.sql.zip in2012-06-1523:09:41(56.9KB/s)-world_innodb.sql.zipsaved[92149/92149][root@updba~]#unzipworld_innodb.sql.zip inflating:world_innodb.sql[root@updba~]#etotheMySQLmonitor.Commandsendwith;orYourMySQLconnectionidisServerversion:5.1.52-logSourceCopyright(c)2000,2010,Oracleand/oritsaffiliates.AllrightsThissoftwarecomeswithABSOLUYNOWARRANTY.Thisissoftware,andyouare etomodifyandredistributeitundertheGPLv2licenseType'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>createdatabasestt;QueryOK,1rowaffected(0.00mysql>usesttmysql>source |table_schema| |engine | | |InnoDB| | |InnoDB| |CountryLanguage|InnoDB 3rowsinset(0.00mysql>grantallonstt.*tostt@'updba'identifiedby'oracle';QueryOK,0rowsaffected(0.00sec)mysql>grantallonstt.*tostt@'u 'identifiedby'oracle';QueryOK,0rowsaffected(0.00sec)mysql>grantallonstt.*tostt@' 'identifiedby'oracle';QueryOK,0rowsaffected(0.00sec)mysql>grantallonstt.*tostt@'upgg'identifiedby'oracle';QueryOK,0rowsaffected(0.00sec)mysql>grantreloadon*.*tostt;QueryOK,0rowsaffected(0.00sec)oracle服務(wù)器安裝建立運(yùn)行g(shù)oldengate這里采用oracle軟件運(yùn)行用戶oracle,#.bash_profile#Getthealiasesandfunctionsif[-f~/.bashrc];then.#UserspecificenvironmentandstartupprogramsexportORACLE_BASE=/u01/oracleexportORACLE_HOME=/u01/oracle/11gexportexportPATH初始化建立gg管理用 本例中為SQL>create SQL>create ggmidentifiedbyoracledefaulttablespaceggdata;Usercreated.SQL>grantconnect,resourcetoggm;Grantsucceeded.SQL>grantdbatoggm;Grantsucceeded.配置manager[oracle@upgg~]$OracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights. )1>editparamsport~~~~~GGSCI( )2>startmgrManagerstarted. )3>infoManagerisrunning(IP )4> asSQL*Plus:Release.0ProductiononFriJun1523:47:272012Copyright(c)1982,2011,Oracle. Allrights.ConnectedOracleDatabase11gEnterpriseEditionRelease.0-64bitProductionWiththePartitioning,OracleLabelSecurity,OLAP,DataMining,SQL>grantconnect,resourcetosttidentifiedbyoracle;Grantsucceeded.Namevarchar2(35)NOTNULLPopulationnumberNOT SQL>SQL>Namevarchar2(52)NOTNULLRegionvarchar2(26)NOTNULLSurfaceAreanumber(10,2)NOTNULL Populationnumber(11)NOTNULL GNP GNPOld LocalNamevarchar2(45)NOTNULLernmentFormvarchar2(45)NOTNULLHeadOfState Capital Code2varchar2(2)NOT CountryCodevarchar2(3)NOTNULLLanguagevarchar2(30)NOTNULLIsOfficialvarchar2(1)NOTNULLPercentagenumber(4,1)NOTNULLCONSTRAINTcountryLanguage_ibfk_1FOREIGNKEY(CountryCode)REFERENCESstt.Country OracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),MySQLEnterpriseonApr23201204:34:25Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI(u )1>infomgrManagerisrunning(IPportu GGSCI(u )2>editparamsdefgenSOURCEDBstt@updba,USERIDstt,PASSWORDoracleTABLEstt.City;~~~"dirprm/defgen.prm"5L,146C )3> paramfileOracleGoldenGateTableDefinitionGeneratorforMySQLLinux,x64,64bit(optimized),MySQLEnterpriseonApr23201205:11:37Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.Version#1SMPFriDec918:57:35EST2011,Release2.6.32-300.3.1.el6uek.x86_64Node:uMachine:softlimit hardlimitAddressSpaceSize HeapSize FileSize CPUTime Processid: Runningwiththefollowing DEFSFILE./dirdef/source.def,PURGESOURCEDBstt@updba,USERIDstt,PASSWORD******TABLEstt.City;TABLEstt.Country;RetrievingdefinitionforRetrievingdefinitionforDefinitionsgeneratedfor3tablesin./dirdef/source.def[root@updbaggs]#pwdOracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),MySQLEnterpriseonApr23201204:34:25Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights GGSCI(u )1>addextractemsql,sourceistableEXTRACTadded.GGSCI(u )2>editparamsemsqlextractemsqlsourcedbstt@updba,useridstt,passwordoraclermthostupgg,mgrport7900tablestt.City;tabletable~~"dirprm/emsql.prm"[New]8L,178CwrittenGGSCI(u )3>exit配置進(jìn)oracle服務(wù)器upgg[oracle@upgg~]$scp oracle@updba'spassword: 100% [oracle@upgg~]$OracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights GGSCI( )1>infomgrManagerisrunning(IP GGSCI( )2>addreplicatrora,specialrunREPLICATadded.GGSCI( )3>editparamsrorareplicatrorauseridggm,passwordoraclesourcedefs./dirdef/source.defmap map map ~~"dirprm/rora.prm"[New]7L,240CwrittenGGSCI( )4>exit啟動(dòng)directload遷移數(shù)據(jù)mysql服務(wù)器updba上[root@updbaggs]#./ggsciOracleGoldenGateCommandInterpreterfor Linux,x64,64bit(optimized),MySQLEnterpriseonApr23201204:34:25Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI(u )1>startextractemsqlSendingSTARTrequesttoMANAGER...EXTRACTEMSQLstarting )2>info 2012-06-15 StatusCheckpoint

NotAvailableFirstRecord

Record )3>info LastStarted2012-06-16 CheckpointLag LogRead Table Record )4>info Checkpoint NotLogRead Table Record )5>viewreport2012-06-1600:06:53 WildcardresolutionsettoIMMEDIATEbecauseSOURCEISTABLEisOracleGoldenGateCaptureforMySQL Linux,x64,64bit(optimized),MySQLEnterpriseonApr23201205:23:34Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.OperatingSystemVersion:Version#1SMPFriDec918:57:35EST2011,Release2.6.32-300.3.1.el6uek.x86_64Node:uMachine:softlimit hardlimitAddressSpaceSize HeapSize FileSize CPUTime Processid:6885 Runningwiththefollowing 2012-06-1600:06:53 OperatingsystemcharactersetidentifiedasUTF-8.Locale:en_US,LC_ALL:.extractsourcedbstt@updba,useridstt,password******rmthostupgg,mgrport7900tablestt.City;Usingthefollowingkeycolumnsforsourcetablestt.City:tableUsingthefollowingkeycolumnsforsourcetablestt.Country:tableUsingthefollowingkeycolumnsforsourcetablestt.CountryLanguage:CountryCode,2012-06-1600:06:53 anonalloc:mmap(MAP_ANON) anon:munmapfilealloc:mmap(MAP_SHARED) file:munmaptargetdirectories:CACHEMGRvirtualmemoryvalues(mayhavebeenadjusted) CACHEPAGEOUTSIZE(normal): PROCESSVMAVAILFROMOS(min): CACHESIZEMAX(strictto DatabaseVersion:6.0.0HostConnection:updbaviaTCP/IPProtocolVersion:10Processingtablestt.CityProcessingtable **RunTimeStatistics Reportat2012-06-1600:07:05(activitysince2012-06-1600:06:53)Outputtorora:FromTable##0#0#0FromTable##0#0#0FromTable##0#0#0GGSCI )upgg[oracle@upgg~]$OracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights )1>info 2012-06-16 StatusCheckpointLag 00:00:00(updated00:09:19ago)LogReadCheckpoint NotAvailable )2>viewreportOracleGoldenGateDeliveryforOracleLinux,x64,64bit(optimized),Oracle11gonApr23201208:48:07Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.Version#1SMPFriDec918:57:35EST2011,Release2.6.32-300.3.1.el6uek.x86_64Machine:softlimit hardlimitAddressSpaceSize HeapSize FileSize CPUTime Runningwiththefollowing 2012-06-1600:13:28 OperatingsystemcharactersetidentifiedasUTF-8.Locale:en_US,LC_ALL:.replicat2012-06-1600:13:29 WARNING:NLS_LANGenvironmentvariableisinvalidornot.UsingoperatingsystemcharactersetvalueofAL32UTF8.sourcedefs./dirdef/source.defmap map map 2012-06-1600:13:30 anonalloc:mmap(MAP_ANON) :munmapfilealloc:mmap(MAP_SHARED) file:munmaptargetdirectories:CACHEMGRvirtualmemoryvalues(mayhavebeenCACHESIZEMAX(stricttoDatabaseOracleDatabase11gEnterpriseEditionRelease.0-64bitProductionPL/SQLRelease.0-Production TNSforLinux:Version.0-ProductionNLSRTLVersion.0-ProductionDatabaseLanguageandCharacter =".AL32UTF8" ="AMERICA"NLS_CHARACTERSET="AL32UTF8" RunTime MAPresolved(entrystt.City):map UsingfollowingcolumnsindefaultmapbyUsingthefollowingkeycolumnsfortargettableSTT.CITY:2012-06-1600:13:32 WARNINGOGG-03504NLS_LANGcharactersetAL32UTF8onthetargetisdifferentromthesourcedatabasecharactersetWE8ISO8859P1.Replicationmaynotbevalidifthesourcedatahasan patiblecharacterforthetargetNLS_LANGcharacterset.MAPresolved(entrystt.Country):mapstt.Country,targetstt.country;UsingfollowingcolumnsindefaultmapbyCODE,NAME,CONTINENT,REGION,SURFACEAREA,INDEPYEAR,POPULATION,CAPITAL,CODE2UsingthefollowingkeycolumnsfortargettableSTT.COUNTRY:MAPresolved(entrymapstt.CountryLanguage,targetstt.countrylanguage;Usingfollowingcolumnsindefaultmapbyname:UsingthefollowingkeycolumnsfortargettableSTT.COUNTRYLANGUAGE:COUNTRYCODE, **RunTimeStatistics Reportat2012-06-1600:13:40(activitysince2012-06-1600:13:32)FromTablestt.CitytoSTT.CITY: #0#0#0FromTablestt.Countryto##0#0#0FromTablestt.CountryLanguageto##0#0#0vm=0vmanonqueuesvm=0vmanonqueues0vmanoninuse0vm 0vmusedmax CACHECONFIGURATIONcachesize cachepaging=3.41Gbuffermin = bufferhighwater pageouteligiblesize= RUNTIMESTATSFORSUPERPOOLtrans non-zerototal trans CACHEFiledisk disk disk filecached fileretrieves CACHEbuffer 0anon=0dunmaps0=0cached 0out=0AllocationRequest<000|0000 0000|0000 0000 0000|0000 0CachedTransactionSize0<000 0000|0000 0000 0000 0000 0000|0000 0000 000000CUMULATIVESTATSFORSUPERPOOLtrans non-zerototal trans CACHEFiledisk disk disk filecached fileretrieves CACHEbuffer 0anon=0dunmaps0=0cached 0out=0AllocationRequest<000|0000 0000|0000 0000 0000|0000 0CachedTransactionSize0<000 0000|0000 0000 0000 0000 0000|0000 0000 000000num=defaultindex0cur=0=0qvm=0vm=0q q queueq 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 RUNTIMESTATSFORCACHEPOOL#0 group: id:trans=00trans=0(0 last =AllocationRequest<000|0000 0000|0000 0000 0000|0000 0CUMULATIVESTATSFORCACHEPOOL#0 group: id:trans=00trans=0(0 last =AllocationRequest<000|0000 0000|0000 0000 0000|0000 0num=defaultindex0cur=0=0qvm=0vm=0q q queueq 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000RUNTIMESTATSFORCACHEPOOL#0 group: id:trans=00trans=0(0 last =AllocationRequest<000|0000 0000|0000 0000 0000|0000 0CUMULATIVESTATSFORCACHEPOOL#0 group: id:trans=00trans=0(0 last =AllocationRequest<000|0000 0000|0000 0000 0000|0000 0 )oracle服務(wù)器upgg SQL*Plus:Release.0ProductiononSatJun1600:19:102012Copyright(c)1982,2011,Oracle. Allrights ConnectedOracleDatabase11gEnterpriseEditionRelease.0-64bitProductionWiththePartitioning,OracleLabelSecurity,OLAP,DataMining,SQL>selectcount(*)fromcity;SQL>selectcount(*)fromcountry;SQL>selectcount(*)fromcountrylanguage;可以看到mysql庫(kù)stt中表數(shù)據(jù)已經(jīng)成功遷移到oracle數(shù)據(jù)庫(kù)的stt配置數(shù)據(jù)DMLmysql服務(wù)器updbaOracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),MySQLEnterpriseonApr23201204:34:25Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI(u )1>addextractextmsql,tranlog,beginnowEXTRACTadded.GGSCI(u )2>editparamextmsqlextractextmsqldboptionshostupdba,connectionportsourcedbstt@updba,useridstt,passwordoraclermthostupgg,mgrport7900tablestt.City;tabletable~~"dirprm/extmsql.prm"9L,269CGGSCI(u )3>addrmttrail./dirdat/lt,extractextmsqlRMTTRAILadded. )oracle服務(wù)器upgg[oracle@upgg~]$OracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights GGSCI( )1>editparams GGSCHEMAGGM~~"./GLOBALS"2L,41CGGSCI( )2>dbloginuseridggm,passwordoracleSuccessfullyloggedintodatabase.GGSCI( )3>addcheckpointtable;ERROR:Invalidcommand.GGSCI( )4>addcheckpointta