oracle与之间数据迁移使用goldengate

以下演示部分采用RHELLINUX系统，数据库采用ORACLE源用户scott的表dept,emp,salgrade需要迁移到目标用户s1下。goldengatefororacle11g 64bit导航到 为运行goldengate能产生较大的影响。logging和identificationkeylogging，其主要区别就在于写入redolog中的数据详尽程度不同。所以需要数据库开启supplementallog。安装goldengate建立运行goldengate这里采用oracle软件运行用户oracle,#.bash_profile#Getthealiasesandfunctionsif[-f~/.bashrc];then.#UserspecificenvironmentandstartupprogramsexportORACLE_BASE=/u01/oracleexportORACLE_HOME=/u01/oracle/11gexportexportPATH初始化5建立gg管理用户本例中为ggm源数据用户本例中为scott目标数据用户本例中为s1SQL>createtablespace ggdatadatafile'$ORACLE_BASE/oradata/$ORACLE_SID/ggdata01.dbf'size200m;Tablespacecreated.SQL>create ggmidentifiedbyoracledefaulttablespaceggdata;Usercreated.SQL>grantconnect,resourcetoggm;Grantsucceeded.SQL>grantexecuteonutl_filetoggm;Grantsucceeded.SQL>grantselectanydictionary,selectanytabletoGrantSQL>grantalteranytabletoggm;Grantsucceeded.SQL>grantflashbackanytabletoggm;Grantsucceeded.SQL>grant executeondbms_flashbacktoggm;Grantsucceeded.SQL>grantinsertanytabletoggm;Grantsucceeded.SQL>grantupdateanytabletoggm;Grantsucceeded.SQL>grantdeleteanytabletoggm;Grantsucceeded.6配置manager[oracle@upgg~]$OracleGoldenGateCommandInterpreterfor Linux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights. )1>editparamsport~~~~~GGSCI( )2>startmgrManagerstarted. )3>infoManagerisrunning(IP )端口由你自己指定，默认是7809.如果在同一个服务器上不同goldengate注意端口别哦7准备目标用户与对象SQL>connscott/tigerSQL> *from 4rowsselected.SQL>setlong999SQL>setpagesize99SQL>setheadingoffSQL>setfeedbackoffSQL>spool/tmp/s1.sqlSQL>selectrece(dbms_metadata.get_ddl('TABLE','DEPT'),'SCOTT','S1')||';'fromdual;SQL>selectrece(dbms_metadata.get_ddl('TABLE','EMP'),'SCOTT','S1')||';'fromdual;SQL>selectrece(dbms_metadata.get_ddl('TABLE','SALGRADE'),'SCOTT','S1')||';'fromdual;SQL>spooloffSQL>hosed'/^SQL>/d'-i/tmp/s1.sqlSQL>conn /assysdbaSQL>grantconnect,resourcetoS1identifiedbytiger;SQL>start/tmp/s1.sql[oracle@upgg~]$OracleGoldenGateCommandInterpreterfor Linux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI( )2>dbloginuseridggm@updb,passwordoracleSuccessfullyloggedintodatabase.GGSCI( )3>listtablesscott.*Found7tablesmatchinglist )4>infotrandataLoggingofsupplementalredologdataisdisabledfortableSCOTT.DEPT.GGSCI( )5>addtrandatascott.deptLoggingofsupplementalredodataenabledfortableSCOTT.DEPT.GGSCI( )6>infotrandatascott.empLoggingofsupplementalredologdataisdisabledfortableSCOTT.EMP.GGSCI( )7>addtrandatascott.empLoggingofsupplementalredodataenabledfortableSCOTT.EMP.GGSCI( )8>infotrandatascott.salgradeLoggingofsupplementalredologdataisdisabledfortableSCOTT.SALGRADE.GGSCI( )9>addtrandatascott.salgradeLoggingofsupplementalredodataenabledfortable2012-06-1502:52:16WARNINGOGG-00869Nouniquekeyisdefinedfortable'SALGRADE'.Allviablecolumnswillbeusedtorepresentthekey,butmaynotguaranteeuniqueness.KEYCOLSmaybeusedtodefinethekey.LoggingofsupplementalredodataenabledfortableSCOTT.SALGRADE.GGSCI( )10>infotrandatascott.bonusLoggingofsupplementalredologdataisdisabledfortableSCOTT.BONUS.GGSCI( )11>addtrandatascott.bonus2012-06-1502:52:18WARNINGOGG-00869Nouniquekeyisdefinedfortable'BONUS'.Allviablecolumnswillbeusedtorepresentthekey,butmaynotguaranteeuniqueness.KEYCOLSmaybeusedtodefinethekey.LoggingofsupplementalredodataenabledfortableSCOTT.BONUS.GGSCI( )12> )如果表很多你可以使用addtrandata当然你也可以直接在sqlplus中对表增加附加日志的语法如下：altertabletable_nameaddsupplementalloggroupgroup_a(column_a[nolog],column_b,...)altertablehr.employeesaddsupplementallogdata(all)配置extractGGSCI( )1>addextracteini1,sourceistableEXTRACTadded. )2>editparams~~~~~~extracteini1rmthostupgg,mgrport7809rmttaskreplicat,grouprini1tabletabletable~~"dirprm/eini1.prm"[New]7L,158C)10.配置REPLICAT)3>addreplicat)4>editparams~~~replicatrini1useridggm@updb,passwordoraclediscardfile./dirrpt/rini1.dsc,purgemapSCOTT.EMP,targetS1.EMP;mapSCOTT.BONUS,targetS1.BONUS;~~"dirprm/rini1.prm"[New]7L,200C )迁移scott下的资料到s1用户。迁移前scott用户数据：迁移前s1执行directload )5>startextractSendingSTARTrequesttoMANAGER...EXTRACTEINI1starting )6>infoextract Checkpoint NotLogRead Table Record )7>infoextract Checkpoint NotLogRead Table Record )8>infoextract Checkpoint NotLogRead Table Record )9>infoextract Checkpoint NotLogRead Table Record )10>viewreport2012-06-1505:47:26 WildcardresolutionsettoIMMEDIATEbecauseSOURCEISTABLEisused.OracleGoldenGateCaptureforOracleLinux,x64,64bit(optimized),Oracle11gonApr23201208:42:16Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.Version#1SMPFriDec918:57:35EST2011,Release2.6.32-300.3.1.el6uek.x86_64Machine:softhardAddressSpace Heap File CPUTime Processid:3495 Runningwiththefollowing 2012-06-1505:47:26 OperatingsystemcharactersetidentifiedasUTF-8.Locale:en_US,LC_ALL:.extract2012-06-1505:47:27 snotmatchdatabasecharacterset,ornotset.Usingdatabasecharactersetvalueofrmthostupgg,mgrport7809rmttaskreplicat,grouprini1tableSCOTT.DEPT;UsingthefollowingkeycolumnsforsourcetableSCOTT.DEPT:tableUsingthefollowingkeycolumnsforsourcetableSCOTT.EMP:EMPNO.tableSCOTT.SALGRADE;2012-06-1505:47:35 WARNINGOGG-00869Nouniquekeyisdefinedfortable'SALGRADE'.Allviablecolumnswillbeusedtorepresentthekey,butmaynotguaranteeu KEYCOLSmaybeusedtodefinetheUsingthefollowingkeycolumnsforsourcetableSCOTT.SALGRADE:GRADE,LOSAL,HIS2012-06-1505:47:36 anonalloc:mmap(MAP_ANON) anon:munmapfilealloc:mmap(MAP_SHARED) file:munmaptargetdirectories:CACHEMGRvirtualmemoryvalues(mayhavebeenadjusted) CACHEPAGEOUTSIZE(normal): PROCESSVMAVAILFROMOS(min): to DatabaseOracleDatabase11gEnterpriseEditionRelease.0-64bitProductionPL/SQLRelease.0-Production TNSforLinux:Version.0-ProductionNLSRTLVersion.0-ProductionDatabaseLanguageandCharacter =".AL32UTF8" ="AMERICA"NLS_CHARACTERSET="AL32UTF8"ProcessingtableSCOTT.DEPTProcessingtableSCOTT.EMP **RunTimeStatistics Reportat2012-06-1505:47:47(activitysince2012-06-1505:47:30)Outputtorini1:FromTable#0#0#0#0FromTable#4#0#0#0FromTable##0#0#0FromTable#5#0#0#0REDOLogBytes Bytes )13>SQL>selectcount(*)from4SQL>selectcount(*)from5SQL>selectcount(*)fromSQL>selectcount(*)from0数据已经成功迁移到s1在项目中我们也可以使用其他办法完成数据的迁移。我们在<<数据迁移>>课程中也讲述过很多种迁移技goldengateoracleoracle迁移。也可需要把一台服务器上s1用户数据迁移到另一台服务器上s1用户下。实现updba主机的scott用户变化安装上gg 与upgg建立运行goldengate这里采用oracle软件运行用户oracle,#.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.配置manager[oracle@upgg~]$OracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights )1>editparamsport~~~~~GGSCI( )2>startmgrManagerstarted. )3>infoManagerisrunning(IP )端口由你自己指定，默认是7809.如果在同一个服务器上不同goldengate注意端口别哦updba[oracle@updba~]$OracleGoldenGateCommandInterpreterfor Linux,x64,64bit(optimized),Oracle11gonApr232012Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI(u )1>infomgrManagerisrunning(IPport GGSCI(u )2>add EXTRACTadded.GGSCI(u )3>editparamseupdbaextracteupdbauseridggm,passwordoraclermthostupgg,mgrport7809rmttaskreplicat,grouprupggtables1.*;~~GGSCI(u )4>viewparamseupdbaextracteupdbauseridggm,passwordoraclermthostupgg,mgrport7809rmttaskreplicat,grouprupggtables1.*; )配置进upgg[oracle@upgg~]$OracleGoldenGateCommandInterpreterfor Linux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights. )1>infoManagerisrunning(IP GGSCI( )2>addreplicatrupgg,specialrunREPLICATadded. )3>editparamsreplicatmaps1.*,target~~"dirprm/rupgg.prm"[New]4L,80CGGSCI( )4>viewparams replicatrupggmaps1.*,target )updba上[oracle@updba~]$OracleGoldenGateCommandInterpreterfor Linux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI(u )1>dbloginuseridggm,passwordoracleSuccessfullyloggedintodatabase. )2>addtrandata2012-06-1520:18:44WARNINGOGG-00869Nouniquekeyisdefinedfortable'BONUS'.Allviablecolumnswillbeusedtorepresentthekey,butmaynotguaranteeuniqueness.KEYCOLSmaybeusedtodefinethekey.LoggingofsupplementalredodataenabledfortableS1.BONUS.LoggingofsupplementalredodataenabledfortableS1.DEPT.LoggingofsupplementalredodataenabledfortableS1.EMP.2012-06-1520:18:44WARNINGOGG-00869Nouniquekeyisdefinedfortable'SALGRADE'.Allviablecolumnswillbeusedtorepresentthekey,butmaynotguaranteeuniqueness.KEYCOLSmaybeusedtodefinethekey.LoggingofsupplementalredodataenabledfortableS1.SALGRADE.GGSCI(u )3>infotrandatas1.*LoggingofsupplementalredologdataisenabledfortableColumnssupplementallyloggedfortableS1.BONUS:ENAME,JOB,SAL,COMM.LoggingofsupplementalredologdataisenabledfortableS1.DEPT.ColumnssupplementallyloggedfortableS1.DEPT:DEPTNO.LoggingofsupplementalredologdataisenabledfortableS1.EMP.ColumnssupplementallyloggedfortableS1.EMP:EMPNO.LoggingofsupplementalredologdataisenabledfortableColumnssupplementallyloggedfortableS1.SALGRADE:GRADE,LOSAL,HISAL.GGSCI(u )4>启动directload[oracle@updba~]$OracleGoldenGateCommandInterpreterfor Linux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights. )1>startextractSendingSTARTrequesttoMANAGER...EXTRACTEUPDBAstarting )2>infoextract 2012-06-15 StatusCheckpointLag LogRead Not Record )3>infoextract LastStarted2012-06-15 CheckpointLag NotAvailableLogRead Record )4>infoextract LastStarted2012-06-15 Checkpoint NotLogRead Table Record )5>viewreport2012-06-1520:20:34 WildcardresolutionsettoIMMEDIATEbecauseSOURCEISTABLEisOracleGoldenGateCaptureforOracleLinux,x64,64bit(optimized),Oracle11gonApr232012Copyright(C)1995,2012,Oracleand/oritsaffiliates.All Version#1SMPFriDec918:57:35EST2011,Release2.6.32-300.3.1.el6uek.x86_64Node:uMachine:softlimit hardlimitAddressSpaceSize HeapSize FileSize CPUTime Processid:4450 Runningwiththefollowing 2012-06-1520:20:34 OperatingsystemcharactersetidentifiedasUTF-8.Locale:en_US,LC_ALL:.extract2012-06-1520:20:34 WARNING:NLS_LANGenvironmentvariabledoesnotmatchsecharacterset,ornotset.UsingdatabasecharactersetvalueofAL32UTF8.rmthostupgg,mgrport7809tables1.*;table"S1"."BONUS";2012-06-1520:20:35 WARNINGOGG-00869Nouniquekeyisdefinedfortable'BONUS'.Allviablecolumnswillbeusedtorepresentthekey,butmaynotguaranteeuniqueness. KEYCOLSmaybeusedtodefinetheUsingthefollowingkeycolumnsforsourcetableS1.BONUS:ENAME,JOB,SAL,COMM.WildcardTABLEresolved(entrys1.*):tableUsingthefollowingkeycolumnsforsourcetableS1.DEPT:table"S1"."EMP";UsingthefollowingkeycolumnsforsourcetableS1.EMP:table"S1"."SALGRADE";2012-06-1520:20:36 WARNINGOGG-00869Nouniquekeyisdefinedfortable'SALGRADE'.Allviablecolumnswillbeusedtorepresentthekey,butmaynotguaranteeuniqueness. KEYCOLSmaybeusedtodefinetheUsingthefollowingkeycolumnsforsourcetableS1.SALGRADE:GRADE,LOSAL,2012-06-1520:20:36 anonalloc:mmap(MAP_ANON) :munmapfilealloc:mmap(MAP_SHARED) targetdirectories:CACHEMGRvirtualmemoryvalues(mayhavebeenadjusted) CACHEPAGEOUTSIZE(normal): PROCESSVMAVAILFROMOS(min): CACHESIZEMAX(strictto DatabaseOracleDatabase11gEnterpriseEditionRelease.0-64bitProductionPL/SQLRelease.0-Production TNSforLinux:Version.0-ProductionNLSRTLVersion.0-ProductionDatabaseLanguageandCharacterSet: =".AL32UTF8" ="AMERICA"NLS_CHARACTERSET="AL32UTF8"ProcessingtableS1.DEPTProcessingtableS1.EMP **RunTimeStatistics Reportat2012-06-1520:20:48(activitysince2012-06-1520:20:35)Outputtorupgg:FromTable#4#0#0#0FromTable##0#0#0FromTable#5#0#0#0REDOLogBytes Bytes [oracle@upgg~]$OracleGoldenGateCommandInterpreterfor Linux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights. )1>inforeplicat 2012-06-15 StatusCheckpointLag 00:00:00(updated00:12:33ago)LogReadCheckpoint NotAvailable )2>view OracleGoldenGateDeliveryforOracleLinux,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 Processid:4724 Runningwiththefollowing 2012-06-1520:27:11 OperatingsystemcharactersetidentifiedasUTF-8.Locale:en_US,LC_ALL:.replicatmaps1.*,target2012-06-1520:27:11 VirtualMemoryFacilitiesanonalloc:mmap(MAP_ANON) :munmapfilealloc:mmap(MAP_SHARED) targetdirectories:CACHEMGRvirtualmemoryvalues(mayhavebeenCACHEPAGEOUTSIZE(normal): toDatabaseOracleDatabase11gEnterpriseEditionRelease.0-64bitProductionPL/SQLRelease.0-Production TNSforLinux:Version.0-ProductionNLSRTLVersion.0-ProductionDatabaseLanguageandCharacter =".AL32UTF8" ="AMERICA"NLS_CHARACTERSET="AL32UTF8" RunTime WildcardMAPresolved(entrys1.*):Usingfollowingcolumnsindefaultmapbyname:DEPTNO,DNAME,LOCUsingthefollowingkeycolumnsfortargettableS1.DEPT:WildcardMAPresolved(entrys1.*):UsingfollowingcolumnsindefaultmapbyUsingthefollowingkeycolumnsfortargettableS1.EMP:WildcardMAPresolved(entry2012-06-1520:27:18 WARNINGOGG-00869Nouniquekeyisdefinedfortable'SALGRADE'.Allviablecolumnswillbeusedtorepresentthekey,butmaynotguaranteeuniqueness. KEYCOLSmaybeusedtodefinetheUsingfollowingcolumnsindefaultmapbyname:GRADE,LOSAL,HISALUsingthefollowingkeycolumnsfortargettableS1.SALGRADE:GRADE,LOSAL, **RunTimeStatistics Reportat2012-06-1520:27:23(activitysince2012-06-1520:27:11)FromTableS1.DEPTtoS1.DEPT:#4#0#0#0FromTableS1.EMPto##0#0#0#5#0#0#0CACHEOBJECTMANAGERstatisticsCACHEMANAGERVMUSAGEvm vmanonqueues vmanoninuse vm vmused ==>CACHECACHEcachesize paging=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 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 0 )[oracle@upgg~]$sqlplus-ss1/tigerselectcount(*)fromdept;4selectcount(*)fromselectcount(*)fromsalgrade;5selectcount(*)from0oracleoraclegoldengatedirectload形式数将oracle的数据迁移到Mysql数据库中。mysql5.1把oraclettmysqltt库下表中。在oracle服务器updba[oracle@updba~]$sqlplus-s/ selectlog_modefromv$database;Databaseclosed.Databasedismounted.startupmountTotalSystemGlobal Fixed Variable Database Redo DatabaseDatabaseDatabaseselectlog_modefrom安装操作全部在updba建立运行goldengate这里采用oracle软件运行用户oracle,#.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.配置managerOracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights )1>editparamsport~~~~~GGSCI(u )2>startmgrManagerstarted. )3>infoManagerisrunning(IP )4>[root@updba~]# -[oracle@updba~]$cp$ORACLE_HOME/rdbms/admin/utlsampl.sql./[oracle@updba~]$sed's/SCOTT/TT/g'-iutlsampl.sql[oracle@updba~]$sqlplus -s/ assysdba@utlsampl.sql[oracle@updba~]$sqlplus select*from selectcount(*)from4selectcount(*)fromselectcount(*)fromsalgrade;5selectcount(*)from0[oracle@updba~]$OracleGoldenGateCommandInterpreterfor Linux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI(u )1>dbloginuseridggm,passwordoracleSuccessfullyloggedintodatabase. )2>listtablesFound4tablesmatchinglist )3>addtrandata2012-06-1521:03:49WARNINGOGG-00869Nouniquekeyisdefinedfortable'BONUS'.Allviablecolumnswillbeusedtorepresentthekey,butmaynotguaranteeuniqueness.KEYCOLSmaybeusedtodefinethekey.LoggingofsupplementalredodataenabledfortableTT.BONUS.LoggingofsupplementalredodataenabledfortableTT.DEPT.LoggingofsupplementalredodataenabledfortableTT.EMP.2012-06-1521:03:50WARNINGOGG-00869Nouniquekeyisdefinedfortable'SALGRADE'.Allviablecolumnswillbeusedtorepresentthekey,butmaynotguaranteeuniqueness.KEYCOLSmaybeusedtodefinethekey.LoggingofsupplementalredodataenabledfortableTT.SALGRADE.GGSCI(u )4>infotrandatatt.*LoggingofsupplementalredologdataisenabledfortableColumnssupplementallyloggedfortableTT.BONUS:ENAME,JOB,SAL,COMM.LoggingofsupplementalredologdataisenabledfortableTT.DEPT.ColumnssupplementallyloggedfortableTT.DEPT:DEPTNO.LoggingofsupplementalredologdataisenabledfortableTT.EMP.ColumnssupplementallyloggedfortableTT.EMP:EMPNO.LoggingofsupplementalredologdataisenabledfortableColumnssupplementallyloggedfortableTT.SALGRADE:GRADE,LOSAL,HISAL.GGSCI(u )5>注意上面的所有操作全部在updba在mysql服务器upgg安装[root@upgg~]#cd/u01/mysql/[root@upggmysql]#unzip inflating:ggs_Linux_x64_MySQL_64bit.tarinflating:OGG_WinUnix_Rel_Notes_.1.pdfinflating:OracleGoldenGate.1README.doc[root@upggmysql]#mkdirggs[root@upggmysql]#cd[root@upggggs]#tarxf../ggs_Linux_x64_MySQL_64bit.tar[root@upggggs]#vi~/.bash_profile[root@upggggs]#cat~/.bash_profile#.bash_profile#Getthealiasesandfunctionsif[-f~/.bashrc];then.#UserspecificenvironmentandstartupprogramsexportPATH[root@upggggs]#.~/.bash_profile[root@upggggs]#./ggsciOracleGoldenGateCommandInterpreterfor Linux,x64,64bit(optimized),MySQLEnterpriseonApr23201204:34:25Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights. )1>createCreatingsubdirectoriesundercurrentdirectory 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( )3>startmgrManagerstarted. )4>infoManagerisrunning(IP )5>[root@upggggs]#etotheMySQLmonitor.Commandsendwith;orYourMySQLconnectionidisServerversion:5.1.52SourceCopyright(c)2000,2010,Oracleand/oritsaffiliates.AllrightsThissoftwarecomeswithABSOLU YNOWARRANTY.Thisissoftware,andyouare etomodifyandredistributeitundertheGPLv2licenseType'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputmysql>createdatabaseQueryOK,1rowaffected(0.00mysql>usettmysql>createtabledept(deptnoint,dnamevarchar(14),locvarchar(13))engine=innodb;QueryOK,0rowsaffected(0.01sec)mysql>createtableemp(empnoint,enamevarchar(10),jobvarchar(9),mgrint,hiredatedate,sal int,deptnoint)engine=innodb;QueryOK,0rowsaffected(0.00mysql>createtablesalgrade(gradeint,losalint,hisalint)engine=innodb;QueryOK,0rowsaffected(0.01sec)mysql>createtablebonus(enamevarchar(10),jobvarchar(9),sal mint)engine=innodb;QueryOK,0rowsaffected(0.01sec)授予必mysql>grantallontt.* tott@localhostidentifiedby'tiger';QueryOK,0rowsaffected(0.05sec)mysql>grantallontt.* tott@' 'identifiedby'tiger';QueryOK,0rowsaffected(0.00sec)mysql>grantallontt.* tott@'upgg'identifiedby'tiger';QueryOK,0rowsaffected(0.00sec)mysql>grantinsertontt.*tott@'u 'identifiedby'tiger';QueryOK,0rowsaffected(0.00sec)mysql>grantinsertontt.*tott@'updba'identifiedby'tiger';QueryOK,0rowsaffected(0.00sec)mysql>grantallon*.*tott@'u 'withgrantoption;QueryOK,0rowsaffected(0.00sec)mysql>grantallon*.*tott@'updba'withgrantoption;QueryOK,0rowsaffected(0.00sec)mysql>grantreloadon*.*tott@'updba';QueryOK,0rowsaffected(0.02sec)mysql>grantreloadon*.*tott@'u QueryOK,0rowsaffected(0.00sec)updba[oracle@updba~]$OracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights GGSCI(u )1>addextracteora,sourceistableEXTRACTadded.GGSCI(u )2>editparamseoraextracteorarmthostupgg,mgrport7809tablett.dept;tablett.emp;tablett.bonus;~~"dirprm/eora.prm"[New]8L,159CGGSCI(u )3>editparamsdefgendefsfile./dirdef/source.def,purgetablett.dept;tablett.emp;tablett.bonus;~~"dirprm/defgen.prm"[New]6L,125CwrittenGGSCI(u )4>exit[oracle@updba~]$cd OracleGoldenGateTableDefinitionGeneratorforOracleLinux,x64,64bit(optimized),Oracle11gonApr23201205:08:19Copyright(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,purgetablett.dept;RetrievingdefinitionfortableRetrievingdefinitionfortableRetrievingdefinitionfor2012-06-1521:27:47WARNINGOGG-00869Nouniquekeyisdefinedfortable'SALGRADE'.Allviablecolumnswillbeusedtorepresentthekey,butmaynotguaranteeuniqueness.KEYCOLSmaybeusedtodefinethekey.tableRetrievingdefinitionfor2012-06-1521:27:47WARNINGOGG-00869Nouniquekeyisdefinedfortable'BONUS'.Allviablecolumnswillbeusedtorepresentthekey,butmaynotguaranteeuniqueness.KEYCOLSmaybeusedtodefinethekey.Definitionsgeneratedfor4tablesin./dirdef/source.def[oracle@updba112101]$pwd配置进upgg[root@upggggs]#[root@upggggs]#scp Theauthenticityofhost'updba(0)'can'tbeestablished.RSAkeyfingerprintis75:59:db:1c:c8:5b:eb:b6:82:8d:20:3b:1d:ea:34:2d.Areyousureyouwanttocontinueconnecting(yes/no)?yesWarning:Permanentlyadded'updba,0'(RSA)tothelistofknownhosts.root@updba'spassword: 100% OracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),MySQLEnterpriseonApr23201204:34:25Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI( )1>addreplicatrmsql,specialrunREPLICATadded.GGSCI( )2>editparamsrmsqlreplicatrmsqlsourcedefs./dirdef/source.defmap"TT"."DEPT",targettt.dept;map"TT"."EMP",targettt.emp;map"TT"."BONUS",targettt.bonus;~~"dirprm/rmsql.prm"[New]8L,252CwrittenGGSCI( )3>exit[root@upggggs]#ps-ef|grep 020:40 00:00:00/bin/sh/usr/bin/mysqld_safe-- 020:40 00:00:01/usr/libexec/mysqld--basedir=/usr-- 021:21? 00:00:00./mgrPARAMFILE/u01/mysql/ggs/dirprm/mgr.prmREPORTFILE/u01/mysql/ggs/dirrpt/MGR.rptPROCEMGRPORT7809 021:44pts/1 [root@upggggs]#[root@upggggs]#ln-s/var/lib/mysql/mysql.sock [root@upggggs]#./ggsciOracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),MySQLEnterpriseonApr23201204:34:25Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights.GGSCI( )1>dbloginsourcedbtt@upgg,useridtt,passwordtigerSuccessfullyloggedintodatabase.GGSCI( )2>listtablestt.*Found4tablesmatchinglistcriteria.GGSCI( )3>g.启动directloadupdbaOracleGoldenGateCommandInterpreterforLinux,x64,64bit(optimized),Oracle11gonApr23201208:32:14Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrights GGSCI(u )1>startextracteoraSendingSTARTrequesttoMANAGER...EXTRACTEORAstarting )2>infoextract2012-06-15StatusCheckpointNotLogRead Not

Record )3>infoextract Checkpoint NotLogRead Table Record )4>viewreport2012-06-1522:05:14 WildcardresolutionsettoIMMEDIATEbecauseSOURCEISTABLEisused.OracleGoldenGateCaptureforOracle Linux,x64,64bit(optimized),Oracle11gonApr23201208:42:16Copyright(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:5558 Runningwiththefollowing 2012-06-1522:05:14 OperatingsystemcharactersetidentifiedasUTF-8.Locale:en_US,LC_ALL:.extract2012-06-1522:05:14 WARNING:NLS_LANGenvironmentvariabledoesnotmatchdatabasecharacterset,ornotset.UsingdatabasecharactersetvalueofAL32UTF8.rmthostupgg,mgrport7809tablett.dept;UsingthefollowingkeycolumnsforsourcetableTT.DEPT:tableUsingthefollowingkeycolumnsforsourcetableTT.EMP:EMPNO.tablett.salgrade;2012-06-1522:05:14 WARNINGOGG-00869Nouniquekeyisdefinedfortable'SALGRADE'.Allviablecolumnswillbeusedtorepresentthekey,butmaynotguarantee KEYCOLSmaybeusedtodefinetheUsingthefollowingkeycolumnsforsourcetableTT.SALGRADE:GRADE,LOSAL,HISAL.tablett.bonus;2012-06-1522:05:14 WARNINGOGG-00869 Nouniquekeyisdefinedfortable'BONUS'.Allviablecolumnswillbeusedtorepresentthekey,butmaynotguarantee KEYCOLSmaybeusedtodefinetheUsingthefollowingkeycolumnsforsourcetableTT.BONUS:ENAME,JOB,SAL,2012-06-1522:05:14 anonalloc:mmap(MAP_ANON) :munmapfilealloc:mmap(MAP_SHARED) file:munmaptargetdirectories:CACHEMGRvirtu