使用Goldengate 实现Oracle for Oracle 单向数据同步
实验环境
数据源端： host1 ip 192.168.199.163
数据目标端： host2 ip 192.168.199.104
两台机器都安装http://lqding.blog.51cto.com/9123978/1694971文中描述安装配置好了Goldengate 。
要实现数据的同步，Oracle源端必须满足如下设置
Oracle需要运行在归档模式下
SQL>startupmountORACLEinstancestarted.TotalSystemGlobalArea835104768bytesFixedSize2217952bytesVariableSize574621728bytesDatabaseBuffers255852544bytesRedoBuffers2412544bytesDatabasemounted.SQL>alterdatabasearchivelog;Databasealtered.SQL>alterdatabaseopen;Databasealtered.
2. 开启日志附加属性
SQL>ALTERDATABASEADDSUPPLEMENTALLOGDATA;Databasealtered.SQL>ALTERSYSTEMSWITCHLOGFILE;Systemaltered.
ogg安装目录下提供了一些demo的sql
[oracle@localhostogg]$lsdemo_ora_*demo_ora_create.sqldemo_ora_insert.sqldemo_ora_lob_create.sqldemo_ora_misc.sqldemo_ora_pk_befores_create.sqldemo_ora_pk_befores_insert.sqldemo_ora_pk_befores_updates.sql
我们使用demo_ora_create.sql在源和目的端都创建两张表，在源端使用demo_ora_insert.sql插入数据。
host1
SQL>alteruserscottidentifiedbytigeraccountunlock;Useraltered.SQL>grantresourcetoscott;#ggsci登录数据库时需要该权限Grantsucceeded.SQL>grantselectanydictionarytoscott;#addtrandata时需要该权限Grantsucceeded.SQL>connscott/tigerConnected.SQL>@demo_ora_create.sqlSQL>@demo_ora_insert.sql
host2
SQL>alteruserscottidentifiedbytigeraccountunlock;Useraltered.SQL>grantresourcetoscott;#ggsci登录数据库时需要该权限Grantsucceeded.SQL>grantselectanydictionarytoscott;#addtrandata时需要该权限SQL>connscott/tigerConnected.SQL>@demo_ora_create.sql
为需要同步的表，添加附加日志
GGSCI(localhost.localdomain)1>dbloginuseridscott,passwordtigerSuccessfullyloggedintodatabase.GGSCI(localhost.localdomain)2>addtrandatascott.tcustmerLoggingofsupplementalredodataenabledfortableSCOTT.TCUSTMER.GGSCI(localhost.localdomain)3>addtrandatascott.tcustordLoggingofsupplementalredodataenabledfortableSCOTT.TCUSTORD.
一、初始化加载数据
在源端配置一个初始化Extract，用来同步表中现有数据
GGSCI(localhost.localdomain)7>ADDEXTRACTEINILOAD,SOURCEISTABLEEXTRACTadded.
ADD EXTRACT命令用来添加一个EXTRACT, EINILOAD为Extract的group name 。SOURCEISTABLE表示数据源为表。
查看Extract的信息
GGSCI(localhost.localdomain)9>INFOEXTRACT*,TASKSEXTRACTEINILOADInitialized2015-09-1115:25StatusSTOPPEDCheckpointLagNotAvailableLogReadCheckpointNotAvailableFirstRecordRecord0TaskSOURCEISTABLE
配置初始化加载的捕获参数
GGSCI(localhost.localdomain)10>editparamsEINILOAD----GoldenGateInitialDataCapture--forTCUSTMERandTCUSTORD--EXTRACTEINILOADUSERIDsystem,PASSWORD"oracle"RMTHOST192.168.199.104,MGRPORT7809RMTTASKREPLICAT,GROUPRINILOADTABLESCOTT.TCUSTMER;TABLESCOTT.TCUSTORD;
在目标端，配置一个REPLICAT
GGSCI(localhost.localdomain)2>ADDREPLICATRINILOAD,SPECIALRUNREPLICATadded.
查看REPLICAT信息
GGSCI(localhost.localdomain)4>inforeplicat*,tasksREPLICATRINILOADInitialized2015-08-2214:18StatusSTOPPEDCheckpointLag00:00:00(updated00:02:50ago)LogReadCheckpointNotAvailableTaskSPECIALRUN
配置Replicat参数
GGSCI(localhost.localdomain)5>editparamsriniload----GoldenGateInitialLoadDelivery--REPLICATRINILOADASSUMETARGETDEFSUSERIDsystem,PASSWORD"oracle"DISCARDFILE./dirrpt/RINILOAD.dsc,PURGEMAPscott.*,TARGETscott.*;
启动Extract
GGSCI(localhost.localdomain)11>startextracteiniloadSendingSTARTrequesttoMANAGER...EXTRACTEINILOADstarting
查看日志
GGSCI(localhost.localdomain)21>viewreporteiniload
如果有报错，查找原因并解决
ProcessingtableSCOTT.TCUSTMERProcessingtableSCOTT.TCUSTORD**************************************************************************RunTimeStatistics**************************************************************************Reportat2015-09-1116:23:40(activitysince2015-09-1116:23:33)OutputtoRINILOAD:FromTableSCOTT.TCUSTMER:#inserts:2#updates:0#deletes:0#discards:0FromTableSCOTT.TCUSTORD:#inserts:2#updates:0#deletes:0#discards:0REDOLogStatisticsBytesparsed0Bytesoutput574
日志显示，已成功同步数据。
到目的库上验证
SQL>selectcount(*)fromtcustmer;COUNT(*)----------2SQL>selectcount(*)fromtcustord;COUNT(*)----------2
二、配置数据实时同步
源端，配置一个实时Extract
GGSCI(localhost.localdomain)22>ADDEXTRACTEORAKK,TRANLOG,BEGINNOW,THREADS1EXTRACTadded.
编辑Extract的参数文件
GGSCI(localhost.localdomain)23>EDITPARAMSEORAKK----ChangeCaptureparameterfiletocapture--TCUSTMERandTCUSTORDChanges--EXTRACTEORAKKUSERIDsystem,PASSWORDoracleRMTHOST192.168.199.104,MGRPORT7809RMTTRAIL./dirdat/KKTABLESCOTT.TCUSTMER;TABLESCOTT.TCUSTORD;
为Extract添加远端tail file，也就是说这个tail file是在目的端生成的。
GGSCI(localhost.localdomain)24>ADDRMTTRAIL./dirdat/KK,EXTRACTEORAKK,MEGABYTES5RMTTRAILadded.
验证结果
GGSCI(localhost.localdomain)28>INFORMTTRAIL*ExtractTrail:./dirdat/KKExtract:EORAKKSeqno:0RBA:0FileSize:5M
启动Extract 进程
GGSCI(localhost.localdomain)29>startextracteorakkSendingSTARTrequesttoMANAGER...EXTRACTEORAKKstarting
验证结果
GGSCI(localhost.localdomain)30>INFOEXTRACTEORAKK,DETAILEXTRACTEORAKKLastStarted2015-09-1117:07StatusRUNNINGCheckpointLag00:00:00(updated00:00:01ago)LogReadCheckpointOracleRedoLogs2015-09-1117:07:47Thread1,Seqno25,RBA36139008SCN0.1174781(1174781)TargetExtractTrails:RemoteTrailNameSeqnoRBAMaxMB./dirdat/KK010505ExtractSourceBeginEnd/u01/app/oracle/oradata/orcl/redo01.log2015-09-1116:582015-09-1117:07NotAvailable*Initialized*2015-09-1116:58Currentdirectory/u01/app/oggReportfile/u01/app/ogg/dirrpt/EORAKK.rptParameterfile/u01/app/ogg/dirprm/eorakk.prmCheckpointfile/u01/app/ogg/dirchk/EORAKK.cpeProcessfile/u01/app/ogg/dirpcs/EORAKK.pceStdoutfile/u01/app/ogg/dirout/EORAKK.outErrorlog/u01/app/ogg/ggserr.log
GGSCI(localhost.localdomain)31>VIEWREPORTEORAKK
GGSCI(localhost.localdomain)32>INFOALLProgramStatusGroupLagatChkptTimeSinceChkptMANAGERRUNNINGEXTRACTRUNNINGEORAKK00:00:0000:00:06
目标端，配置REPLICAT
安装checkpoint表
配置checkpoint表的名称
GGSCI(localhost.localdomain)33>EDITPARAMS./GLOBALSHECKPOINTTABLEsystem.ggschkpt
生成checkpoint表
GGSCI(localhost.localdomain)1>DBLOGINUSERIDsystem,PASSWORDoracleSuccessfullyloggedintodatabase.GGSCI(localhost.localdomain)2>ADDCHECKPOINTTABLENocheckpointtablespecified,usingGLOBALSspecification(system.ggschkpt)...Successfullycreatedcheckpointtablesystem.ggschkpt.GGSCI(localhost.localdomain)3>
添加Replicat
GGSCI(localhost.localdomain)3>ADDREPLICATRORAKK,EXTTRAIL./dirdat/KKREPLICATadded.
创建Replicat的参数文件
GGSCI(localhost.localdomain)4>EDITPARAMSRORAKK----ChangeDeliveryparameterfiletoapply--TCUSTMERandTCUSTORDChanges--REPLICATRORAKKUSERIDsystem,PASSWORDoracleHANDLECOLLISIONSASSUMETARGETDEFSDISCARDFILE./dirrpt/RORAKK.DSC,PURGEMAPscott.tcustmer,TARGETscott.tcustmer;MAPscott.tcustord,TARGETscott.tcustord;
启动Replicat
GGSCI(localhost.localdomain)5>startreplicatrorakkSendingSTARTrequesttoMANAGER...REPLICATRORAKKstarting
验证结果
GGSCI(localhost.localdomain)6>inforeplicatrorakkREPLICATRORAKKLastStarted2015-08-2215:49StatusRUNNINGCheckpointLag00:00:00(updated00:00:08ago)LogReadCheckpointFile./dirdat/KK000000FirstRecordRBA1050
验证数据同步
在源数据库，执行如下脚本，对两个表进行insert、update、delete操作
[oracle@localhostogg]$sqlplusscott/tigerSQL*Plus:Release11.2.0.1.0ProductiononFriSep1117:58:172015Copyright(c)1982,2009,Oracle.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-64bitProductionWiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptionsSQL>@/u01/app/ogg/demo_ora_misc.sql
查看源表中的数据
SQL>selectcount(*)fromtcustmer;COUNT(*)----------5SQL>selectcount(*)fromtcustord;COUNT(*)----------3
查看目的表的数据
SQL>selectcount(*)fromtcustmer;COUNT(*)----------5SQL>selectcount(*)fromtcustord;COUNT(*)----------3
数据已同步
三、为同步添加data pump
如果没有data pump，Extract将Trail file写到远程机器上。如果为Extract配置了Data Pump。那么Extract将Trail file写到本地。然后Trail 文件由Data Pump传输到远端。
先修改Extract的配置
GGSCI(localhost.localdomain)1>infoallProgramStatusGroupLagatChkptTimeSinceChkptMANAGERRUNNINGEXTRACTRUNNINGEORAKK00:00:0000:00:09GGSCI(localhost.localdomain)2>stopextracteorakkSendingSTOPrequesttoEXTRACTEORAKK...Requestprocessed.GGSCI(localhost.localdomain)3>editparamseorakk----ChangeCaptureparameterfiletocapture--TCUSTMERandTCUSTORDChanges--EXTRACTEORAKKUSERIDsystem,PASSWORDoracleRMTHOST192.168.199.104,MGRPORT7809EXTTRAIL./dirdat/KKTABLESCOTT.TCUSTMER;TABLESCOTT.TCUSTORD;
将参数文件中的RMTTRAIL ./dirdat/kk 改为 EXTTRAIL ./dirdat/KK
2. 删除Extract的远程Trail
GGSCI(localhost.localdomain)7>DELETERMTTRAIL./dirdat/KKDeletingextracttrail./dirdat/KKforextractEORAKK
3. 增加一个本地Trail
GGSCI(localhost.localdomain)10>ADDEXTTRAIL./dirdat/KK,EXTRACTeorakkEXTTRAILadded.
4. 新增Data pump
GGSCI(localhost.localdomain)12>ADDEXTRACTEPMPKK,EXTTRAILSOURCE./dirdat/KKEXTRACTadded.
Data pump本质上也是一个Extract，只是类型不同而已
为data pump配置参数文件
GGSCI(localhost.localdomain)14>editparamsepmpkk----DataPumpparameterfile--EXTRACTEPMPKKPASSTHRURMTHOST192.168.199.104,MGRPORT7809RMTTRAIL./dirdat/KKTABLESCOTT.TCUSTMER;TABLESCOTT.TCUSTORD;
为Data Pump添加远端Trail
GGSCI(localhost.localdomain)15>addrmttrail./dirdat/KK,EXTRACTEPMPKKRMTTRAILadded.
启动Extract、Data Pump
GGSCI(localhost.localdomain)16>startextracteorakkSendingSTARTrequesttoMANAGER...EXTRACTEORAKKstartingGGSCI(localhost.localdomain)17>startextractepmpkkSendingSTARTrequesttoMANAGER...EXTRACTEPMPKKstarting
验证数据同步
在源端，删除两个表的内容
SQL>deletefromtcustmer;5rowsdeleted.SQL>deletefromtcustord;3rowsdeleted.SQL>commit;Commitcomplete.
查看目的端表内容
SQL>select*fromtcustmer;norowsselectedSQL>select*fromtcustord;norowsselected
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
