(完整版)oracleGoldenGate学习笔记

OracleGoldenGate测试文档1．OracleGoldenGate介绍GoldenGateTDM（交易数据管理）软件是一种基于日志的结构化数据复制软件，它通过解析源数据库在线日志或归档日志获得数据的增删改变化，再将这些变化应用到目标数据库，实现源数据库与目标数据库同步、双活。GoldenGateTDM软件可以在异构的IT基础结构（包括几乎所有常用操作系统平台和数据库平台）之间实现大量数据亚秒一级的实时复制，其复制过程简图如下：如上图所示，GoldenGateTDM的数据复制过程如下：利用捕捉进程(CaptureProcess)在源系统端读取OnlineRedoLog或ArchiveLog，然后进行解析，只提取其中数据的变化如增、删、改操作，并将相关信息转换为GoldenGateTDM自定义的中间格式存放在队列文件中。再利用传送进程将队列文件通过TCP/IP传送到目标系统。捕捉进程在每次读完log中的数据变化并在数据传送到目标系统后，会写检查点，记录当前完成捕捉的log位置，检查点的存在可以使捕捉进程在中止并恢复后可从检查点位置继续复制；目标系统接受数据变化并缓存到GoldenGateTDM队列当中，队列为一系列临时存储数据变化的文件，等待投递进程读取数据；GoldenGateTDM投递进程从队列中读取数据变化并创建对应的SQL语句，通过数据库的本地接口执行，提交到数据库成功后更新自己的检查点，记录已经完成复制的位置，数据的复制过程最终完成。由此可见，GoldenGateTDM是一种基于软件的数据复制方式，它从数据库的日志解析数据的变化（数据量只有日志的四分之一左右）。GoldenGateTDM将数据变化转化为自己的格式，直接通过TCP/IP网络传输，无需依赖于数据库自身的传递方式，而且可以通过高达9:1的压缩率对数据进行压缩，可以大大降低带宽需求。在目标端，GoldenGateTDM可以通1过交易重组，分批加载等技术手段大大加快数据投递的速度和效率，降低目标系统的资源占用，可以在亚秒级实现大量数据的复制，并且目标端数据库是活动的GoldenGateTDM提供了灵活的应用 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 ，基于其先进、灵活的技术架构可以根据用户需求组成各种拓扑结构，如图所示：GoldenGateTDM可以提供可靠的数据复制，主要体现在下面三点：保证事务一致性GoldenGateTDM在灾备数据库应用复制数据库交易的顺序与在生产中心数据库上的顺序相同，并且按照相同的事务环境提交，确保在目标系统上数据的完整性和读一致性，为实时查询和事务处理创造了条件。检查点机制保障数据无丢失GoldenGateTDM的抽取和复制进程使用检查点机制记录完成复制的位置。对于抽取进程，其检查点记录当前已经抽取日志的位置和写队列文件的位置；对于投递进程，其检查点记录当前读取队列文件的位置。检查点机制可以保证在系统、网络或GoldenGateTDM进程故障重启后数据无丢失。可靠的数据传输机制2GoldenGateTDM用应答机制传输交易数据，只有在得到确认消息后才认为数据传输完成，否则将自动重新传输数据，从而保证了抽取出的所有数据都能发送到备份端。数据传输过程中支持128位加密和数据压缩功能。Oracle公司的GoldenGate产品，可以在异构的IT基础结构之间实现大量数据的秒一级的数据捕捉、转换和投递。GoldenGate可以支持几乎所有常用操作系统如和数据库平台，如下表所示：操作系统数据库MSNT,2000,XP,Linux,SunSolaris,Oracle,DB2,MSSQLServer,MySQL,HP-UX,IBMAIX,HPNonStop,TRU64,IBMEnscribe,SQL/MP,SQL/MX,Sybase,z/OS，OS/390Teradata,其他ODBC兼容数据库32．OracleGoldenGateForOracle(windows平台)安装GoldenGate的安装非常简单，将解压缩安装文件到指定目录。比如安装文件解压缩后的路径为d:\ggs安装步骤如下:进入命令行切换到ggs目录下输入命令installaddserviceaddevents输入ggsci进入GoldenGate的命令行输入createsubdirsOK，GoldenGate安装就已经完成了。实际操作如下C:\DocumentsandSettings\PONY>d:D:\>cdggsD:\ggs>installaddserviceaddeventsOracleGoldenGatemessagesinstalledsuccessfully.Service'GGSMGR'created.Installprogramterminatednormally.D:\ggs>ggsciOracleGoldenGateCommandInterpreterforOracleVersion10.4.0.19Build002Windows(optimized),Oracle10onSep18200915:54:55Copyright(C)1995,2009,Oracleand/oritsaffiliates.Allrightsreserved.GGSCI(PONY)1>createsubdirsCreatingsubdirectoriesundercurrentdirectoryD:\ggsParameterfilesD:\ggs\dirprm:created4ReportfilesD:\ggs\dirrpt:createdCheckpointfilesD:\ggs\dirchk:createdProcessstatusfilesD:\ggs\dirpcs:createdSQLscriptfilesD:\ggs\dirsql:createdDatabasedefinitionsfilesD:\ggs\dirdef:createdExtractdatafilesD:\ggs\dirdat:createdTemporaryfilesD:\ggs\dirtmp:createdVeridatafilesD:\ggs\dirver:createdVeridataLockfilesD:\ggs\dirver\lock:createdVeridataOut-Of-SyncfilesD:\ggs\dirver\oos:createdVeridataOut-Of-SyncXMLfilesD:\ggs\dirver\oosxml:createdVeridataParameterfilesD:\ggs\dirver\params:createdVeridataReportfilesD:\ggs\dirver\report:createdVeridataStatusfilesD:\ggs\dirver\status:createdVeridataTracefilesD:\ggs\dirver\trace:createdStdoutfilesD:\ggs\dirout:created53．数据库复制实施文档（DML）3.1准备工作◆准备两台机器，分别为SourceDB,TargetDB，通过TCP/IP网络进行互连SourceDB：WINXP环境，Oracle10g10.2.0.1.0TargetDB：WIN2003环境Oracle10g10.2.0.1.0◆必须保证SourceDB是运行在归档模式下。◆目的：将SourceDB中相关Schema中的数据同步复制到TargetDB相对应的Sechema中本文中要同步的是SouceDB中的SAJET,SJ,SMT,LANG到Target中的SAJET,SJ,SMT,LANG用户下◆首先要保持SourceD,TargetDB相关Schema中的初始数据一致，可以通过Exp/Imp,Rman,冷备等方式实现.◆双方DB建立GoldenGate用户，赋予DBA权限，用于GoldenGate连接DB.◆双方DB安装GoldenGate，安装方式如第二章节所示SourceDB必须开始最小附加日志模式通过以下语句查看DB是否开启了最小附加日志模式SQL>selectSUPPLEMENTAL_LOG_DATA_MINfromv$database;SUPPLEME--------YES如果没有开启数据库的最小附加日志，则通过以下语句开启SQL>alterdatabaseaddsupplementallogdata;◆开启表的最小附加日志，通过GoldenGate命令行来添加GGSCI(PONY)1>dbloginuseridsajetpasswordtechSuccessfullyloggedintodatabase.GGSCI(PONY)2>addtrandatasajet.*第一条命令表示登录到Database第二条命令表示添加Sajet用户下所有表的最小附加日志同样的命令开启SJ,LANG,SMT用户下的表的最小附加日志。63.2配置GoldenGate3.2.1配置SourceDB的GoldenGateSourceDB端需要配置mgr进程，添加一个extract进程，和一个远端队列。1.配置mgr参数GGSCI(PONY)2>editparammgr此时系统自动会新建一个名为mgr的参数文件，填入以下内容后保存PORT7809--mgr进程使用的TCP/IP端口侦听请求2.新增一个抽取进程，在GGSCI命令行中输入如下命令：GGSCI(PONY)1>addextractext1,tranlog,beginnowEXTRACTadded.---新增一个抽取进程，负责抓取SourceDB中变化的数据，基于日志方式，立即生效3.配置抽取进程GGSCI(PONY)2>editparamext1此时系统会自动新建一个名为ext1的参数文件，填入以下内容后保存extractext1useridgoldengate,passwordgoldengatermthost172.17.17.61,mgrport7809rmttraild:\ggs\dirdat\r1dynamicresolutiongettruncatestablesajet.*;tablesj.*;tablesmt.*;tablelang.*;--抽取进程名为ext2连接本机DB的账号和密码远程主机地址以及服务端口号（TargetDB）远程队列的位置(TargetDB)优化参数，动态 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 表结构是否抓取Truncate的数据需要抽取哪些table。4.新增远端队列位置GGSCI(PONY)3>addrmttraild:\ggs\dirdat\r1extractext2RMTTRAILadded.7--新增一个远程队列，位置为在TargetDB的d:\ggs\dirdat\r1，是抽取进程ext1抽取的数据队列，注意和抽取进程中配置的rmttraild:\ggs\dirdat\r1一致。开启所有进程GGSCI(PONY)11>startmgrStartingManagerasservice('GGSMGR')...Servicestarted.GGSCI(PONY)14>startext1SendingSTARTrequesttoMANAGER('GGSMGR')...EXTRACTEXT1starting查看进程的运行情况GGSCI(PONY)185>infoallProgramStatusGroupLagTimeSinceChkptMANAGERRUNNINGEXTRACTRUNNINGEXT100:00:0000:00:06OK，Mgr进程和EXT进程都已经正常运行SouceDB端的配置完成。3.2.2配置TargetDB的GoldenGate1.配置mgr参数GGSCI(PONY)2>editparammgr此时系统自动会新建一个mgr的参数文件，填入以下内容后保存PORT7809DYNAMICPORTLIST7840-7850--mgr进程使用的TCP/IP端口侦听请求接受远端队列的端口列表。2.新增一个复制进程GGSCI(PONY)2>ADDreplicatrep1EXTTRAILd:\ggs\dirdat\r1,nodbcheckpoint--新增一个复制进程，将抽取到队列中的文件解析后写进TargetDB3.配置复制进程8GGSCI(PONY)2>editparamrep1此时系统会自动新建一个参数文件，填入一下内容后保存replicatrep1useridgoldengate,passwordgoldengateassumetargetdefsreperrordefault,discarddiscardfileD:\oradata\discard\repsz.dsc,append,megabytes100gettruncatemapsajet.*,targetsajet.*;mapsj.*,targetsj.*;maplang.*,targetlang.*;mapsmt.*,targetsmt.*;--复制进程名登入TargetDB的账号和密码两台DB数据结构一致则使用此参数如果复制出错，则继续，并将错误放进discardfile中复制truncate操作复制的源表为sajet，sj，lang，smt用户下的所有表，目标为TargetDB中对用用户下的所有表开启mgr和rep进程GGSCI(PONY)20>startmgrStartingManagerasservice('GGSMGR')...Servicestarted.GGSCI(PONY)21>startrep1SendingSTARTrequesttoMANAGER('GGSMGR')...REPLICATREP1starting查看进程的运行情况GGSCI(PONY)22>infoallProgramStatusGroupLagTimeSinceChkptMANAGERRUNNINGREPLICATRUNNINGREP100:00:0000:00:079OK,MGR进程和REP进程运行正常至此，两台DB的GoldenGate都已经配置完成。3.3测试DML操作首先往SourceDB中插入一行C:\DocumentsandSettings\Administrator>sqlplusSQL*Plus:Release10.2.0.1.0-ProductiononMonMar815:33:472010Copyright(c)1982,2005,Oracle.Allrightsreserved.Enteruser-name:sajet/tech@hitronConnectedto:OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-64bitProductionWiththePartitioning,OLAPandDataMiningoptionsSQL>insertintosajet.sys_empvalues(10000786,'TEST1','TEST1','','TEST','1000027','','Y','',0,'',0,'','')2;1rowcreated.SQL>commit2;Commitcomplete.看看TargetDB中是否有同样的记录存在呢SQL>connectsajet/tech@goldengaConnected.SQL>selectcount(*)fromsajet.sys_emp2whereemp_id=10000786;COUNT(*)----------1同步过来了。我们可以看看EXT1进程的相关统计信息10GGSCI(sajet-project)189>statsext1SendingSTATSrequesttoEXTRACTEXT1...StartofStatisticsat2010-03-0815:47:51.DDLreplicationstatistics(foralltrails):***Totalstatisticssinceextractstarted***Operations40.00Mappedoperations28.00Unmappedoperations0.00Otheroperations12.00Excludedoperations0.00Outputtod:\ggs\dirdat\r1:ExtractingfromGOLDENGATE.GGS_MARKERtoGOLDENGATE.GGS_MARKER:***Totalstatisticssince2010-03-0509:51:51***Nodatabaseoperationshavebeenperformed.***Dailystatisticssince2010-03-0800:00:00***Nodatabaseoperationshavebeenperformed.***Hourlystatisticssince2010-03-0815:00:00***Nodatabaseoperationshavebeenperformed.***Lateststatisticssince2010-03-0509:51:51***Nodatabaseoperationshavebeenperformed.ExtractingfromSAJET.GOLDEGATETESTtoSAJET.GOLDEGATETEST:***Totalstatisticssince2010-03-0509:51:51***Totalinserts3.00Totalupdates0.00Totaldeletes0.00Totaltruncates2.00Totaldiscards0.0011Totaloperations5.00***Dailystatisticssince2010-03-0800:00:00***Nodatabaseoperationshavebeenperformed.***Hourlystatisticssince2010-03-0815:00:00***Nodatabaseoperationshavebeenperformed.***Lateststatisticssince2010-03-0509:51:51***Totalinserts3.00Totalupdates0.00Totaldeletes0.00Totaltruncates2.00Totaldiscards0.00Totaloperations5.00ExtractingfromSAJET.SYS_EMPtoSAJET.SYS_EMP:***Totalstatisticssince2010-03-0509:51:51***Totalinserts2.00Totalupdates0.00Totaldeletes0.00Totaldiscards0.00Totaloperations2.00***Dailystatisticssince2010-03-0800:00:00***Totalinserts2.00Totalupdates0.00Totaldeletes0.00Totaldiscards0.00Totaloperations2.00***Hourlystatisticssince2010-03-0815:00:00***Totalinserts2.00Totalupdates0.00Totaldeletes0.00Totaldiscards0.00Totaloperations2.00***Lateststatisticssince2010-03-0509:51:51***Totalinserts2.00Totalupdates0.0012Totaldeletes0.00Totaldiscards0.00Totaloperations2.00ExtractingfromSAJET.MLOG$_SYS_EMPtoSAJET.MLOG$_SYS_EMP:***Totalstatisticssince2010-03-0509:51:51***Totalinserts2.00Totalupdates0.00Totaldeletes0.00Totaldiscards0.00Totaloperations2.00***Dailystatisticssince2010-03-0800:00:00***Totalinserts2.00Totalupdates0.00Totaldeletes0.00Totaldiscards0.00Totaloperations2.00***Hourlystatisticssince2010-03-0815:00:00***Totalinserts2.00Totalupdates0.00Totaldeletes0.00Totaldiscards0.00Totaloperations2.00***Lateststatisticssince2010-03-0509:51:51***Totalinserts2.00Totalupdates0.00Totaldeletes0.00Totaldiscards0.00Totaloperations2.00EndofStatistics.相信以上的内容大家都看的懂吧。其他的DML操作在此省略。134GoldenGateSupportDDL安装1.选择一个Schema存放支持DDL的对象，本文选择GoldenGate2.编辑Globals参数文件3.关掉OracleRecyclebinOnOracle10gandup,systemrecyclebinmustbedisabled.Toturnofftherecyclebin:Oracle10gRelease2andlater:SettheRECYCLEBINinitializationparametertoOFF.Oracle10gRelease1:Setthe_RECYCLEBINinitializationparametertoFALSE.4.在命令行，切换到GGS目录5.运行SQLPLUSrunmarker_setup.sql,enterGoldenGateschema(goldengate)runddl_setup.sqlenterschema:goldengatechooseinstallationmode:INITIALSETUPrunrole_setup.sqlgrantrole(GGS_GGSUSER_ROLE)toallGoldenGateExtractusers;runddl_enable.sql两端都安装完成.具体如下：GGSCI(PONY)23>editparamglobals填入如下内容后保存GGSCHEMAgoldengateD:\ggs>sqlplus/assysdbaSQL*Plus:Release10.2.0.1.0-Productionon星期三3月310:12:062010Copyright(c)1982,2005,Oracle.Allrightsreserved.连接到:14OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-ProductionWiththePartitioning,OLAPandDataMiningoptionsSQL>@marker_setup.sqlMarkersetupscriptYouwillbepromptedforthenameofaschemafortheGoldenGatedatabaseobjects.NOTE:Theschemamustbecreatedpriortorunningthisscript.NOTE:StopallDDLreplicationbeforestartingthisinstallation.EnterGoldenGateschemaname:goldengateMarkersetuptablescriptcomplete,runningverificationscript...PleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:SettingschemanametoGOLDENGATEMARKERTABLE--------------------------------------------------------------OKMARKERSEQUENCE--------------------------------------------------------------OKScriptcomplete.SQL>@ddl_setup.sqlGoldenGateDDLReplicationsetupscriptVerifyingthatcurrentuserhasprivilegestoinstallDDLReplication...YouwillbepromptedforthenameofaschemafortheGoldenGatedatabaseobjects.NOTE:Theschemamustbecreatedpriortorunningthisscript.NOTE:OnOracle10gandup,systemrecyclebinmustbedisabled.NOTE:StopallDDLreplicationbeforestartingthisinstallation.EnterGoldenGateschemaname:goldengateYouwillbepromptedforthemodeofinstallation.ToinstallorreinstallDDLreplication,enterINITIALSETUPToupgradeDDLreplication,enterNORMAL15Entermodeofinstallation:initialsetupWorking,pleasewait...Spoolingtofileddl_setup_spool.txtUsingGOLDENGATEasaGoldenGateschemaname,INITIALSETUPasamodeofinstallation.Working,pleasewait...RECYCLEBINmustbeempty.ThisinstallationwillpurgeRECYCLEBINforallusers.Toproceed,enteryes.Tostopinstallation,enterno.Enteryesorno:yesDDLreplicationsetupscriptcomplete,runningverificationscript...PleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:SettingschemanametoGOLDENGATEDDLORA_GETTABLESPACESIZESTATUS:Line/pos--------------------------------------------------------------------------------Error-----------------------------------------------------------------NoerrorsNoerrorsCLEAR_TRACESTATUS:Line/pos--------------------------------------------------------------------------------Error-----------------------------------------------------------------NoerrorsNoerrors16CREATE_TRACESTATUS:Line/pos--------------------------------------------------------------------------------Error-----------------------------------------------------------------NoerrorsNoerrorsTRACE_PUT_LINESTATUS:Line/pos--------------------------------------------------------------------------------Error-----------------------------------------------------------------NoerrorsNoerrorsINITIAL_SETUPSTATUS:Line/pos--------------------------------------------------------------------------------Error-----------------------------------------------------------------NoerrorsNoerrorsDDLVERSIONSPECIFICPACKAGESTATUS:Line/pos--------------------------------------------------------------------------------Error-----------------------------------------------------------------NoerrorsNoerrors17DDLREPLICATIONPACKAGESTATUS:Line/pos--------------------------------------------------------------------------------Error-----------------------------------------------------------------NoerrorsNoerrorsDDLREPLICATIONPACKAGEBODYSTATUS:Line/pos--------------------------------------------------------------------------------Error-----------------------------------------------------------------NoerrorsNoerrorsDDLHISTORYTABLE----------------------------------------------------------------------OKDDLHISTORYTABLE(1)----------------------------------------------------------------------OKDDLDUMPTABLES----------------------------------------------------------------------OKDDLDUMPCOLUMNS----------------------------------------------------------------------OKDDLDUMPLOGGROUPS----------------------------------------------------------------------OKDDLDUMPPARTITIONS----------------------------------------------------------------------18OKDDLDUMPPRIMARYKEYS----------------------------------------------------------------------OKDDLSEQUENCE----------------------------------------------------------------------OKGGS_TEMP_COLS----------------------------------------------------------------------OKGGS_TEMP_UK----------------------------------------------------------------------OKDDLTRIGGERCODESTATUS:Line/pos--------------------------------------------------------------------------------Error-----------------------------------------------------------------NoerrorsNoerrorsDDLTRIGGERINSTALLSTATUS----------------------------------------------------------------------OKDDLTRIGGERRUNNINGSTATUS--------------------------------------------------------------------------------ENABLEDSTAYMETADATAINTRIGGER--------------------------------------------------------------------------------OFFDDLTRIGGERSQLTRACING19--------------------------------------------------------------------------------0DDLTRIGGERTRACELEVEL--------------------------------------------------------------------------------0LOCATIONOFDDLTRACEFILE--------------------------------------------------------------------------------C:\ORACLE\PRODUCT\10.2.0\ADMIN\TEST\UDUMP/ggs_ddl_trace.logAnalyzinginstallationstatus...STATUSOFDDLREPLICATION--------------------------------------------------------------------------------SUCCESSFULinstallationofDDLReplicationsoftwarecomponentsScriptcomplete.SQL>@role_setup.sqlGGSRolesetupscriptThisscriptwilldropandrecreatetheroleGGS_GGSUSER_ROLETouseadifferentrolename,quitthisscriptandthenedittheparams.sqlscripttochangethegg_roleparametertothepreferredname.(Donotrunthescript.)YouwillbepromptedforthenameofaschemafortheGoldenGatedatabaseobjects.NOTE:Theschemamustbecreatedpriortorunningthisscript.NOTE:StopallDDLreplicationbeforestartingthisinstallation.EnterGoldenGateschemaname:goldengate已写入filerole_setup_set.txtPL/SQL过程已成功完成。Rolesetupscriptcomplete20GrantthisroletoeachuserassignedtotheExtract,GGSCI,andManagerprocesses,byusingthefollowingSQLcommand:GRANTGGS_GGSUSER_ROLETOwhereistheuserassignedtotheGoldenGateprocesses.SQL>GRANTGGS_GGSUSER_ROLETOgoldengate;授权成功。SQL>@ddl_enable.sql触发器已更改SQL>exit安装完成Note：SupportedOracleobjectsandoperationsforDDLreplicationclustersfunctionsindexespackagesproceduretablestablespacesrolessequencessynonymstriggerstypesviewsmaterializedviews21usersNon-supportedOracleobjectsandoperationsforDDLreplicationOracle-reservedschemasOraclerecyclebinLimitationofGGDDLSupportGoldenGatesupportsDDLreplicationforuni-directionalconfigurations.Bi-directionalreplicationofDDLisnotsupported.(只支持单向复制)GoldenGatesupportsDDLsynchronizationonlyinalike-to-likeconfiguration.GoldenGateDDLsupportrequiresthefollowing:Sourceandtargetobjectdefinitionsmustbeidentical.TheASSUMETARGETDEFSparametermustbeusedintheReplicatparameterfile.ReplicatwillabendifobjectsareconfiguredforDDLsupportandtheSOURCEDEFSparameterisbeingused.(必须在Replicat参数文件中指定ASSUMETARGETDEFS参数)22