ogg單機同步復制操作手冊

概要信息1.1文檔介紹該文檔重要用于指導運用OGG來進行數(shù)據(jù)的同時復制，其中源數(shù)據(jù)庫為單機，目的數(shù)據(jù)庫為單機。OGG有關安裝軟件版本為12c，重要涉及以下：OGG，重要用于源庫與目的庫的數(shù)據(jù)同時OGGVeridata，重要用于同時數(shù)據(jù)校驗溫馨提示：請認真閱讀該文檔注意事項！1.2機器環(huán)境1.2.1源機器信息操作系統(tǒng)版本Redhat6.4數(shù)據(jù)庫版本11.2.0.4.0主機名oggsrcIP地址192.168.1.1861.2.2目的機器信息操作系統(tǒng)版本Redhat6.4集群數(shù)據(jù)庫版本11.2.0.4.0主機名oggtgtIP地址192.168.1.1871.3軟件環(huán)境軟件名稱軟件版本布署節(jié)點備注OracleGoldenGate12.2.0.1.1oggsrc（源庫）

oggtgt（目的庫）OracleADR12.2.1.0.0oggsrc（源庫）

oggtgt（目的庫）JDK1.8oggsrc（源庫）

oggtgt（目的庫）1.4軟件包信息軟件名稱軟件包名稱OracleGoldenGate12fbo_ggs_Linux_x64_shiphome.zipOracleApplicationDevelopmentRuntimeInfrastructure（ADR）fmw_12.2.1.0.0_infrastructure_Disk1_1of1.zipJDKjdk-8u112-linux-x64.rpm1.5注意事項--軟件安裝：對于RAC的OGG，有三種方式選擇:1.oracle推薦直接安裝在共享文獻系統(tǒng)上（共享存儲/OCFS/AFS）；2.也能夠安裝在RAC全部節(jié)點的本地目錄，但必需確保全部節(jié)點的安裝途徑一致；3.還能夠只安裝其中一種節(jié)點，但必需確保安裝OGG的節(jié)點能夠訪問其它節(jié)點的歸檔目錄(本文檔使用)--有關歸檔本文檔中，源庫歸檔目錄為本地目錄--有關IP配備：RAC上的VIP必需為固定IP且與管理IP是同一網(wǎng)段，不能夠是DHCP獲取--有關主機hosts表安裝過程中涉及到的主機名及IP對應關系都配備到每臺主機的hosts表中--有關時間同時：全部節(jié)點必需配有時間同時功效確保全部節(jié)點的操作系統(tǒng)時間一致，由于OGG的EXTRACT進程是通過對比commit時間（即操作系統(tǒng)時間）來決定與否傳輸有關的數(shù)據(jù)--有關COMPATIBLE：確保全部節(jié)點的數(shù)據(jù)庫COMPATIBLE參數(shù)已經(jīng)設立--OGG數(shù)據(jù)寄存途徑將OGG數(shù)據(jù)寄存途徑mount在單邊，避免相似的進程在其它節(jié)點啟動引發(fā)進程沖突--源庫數(shù)據(jù)對象與否支持OGG對數(shù)據(jù)對象支持有一定的限制，能夠通過腳本提前檢查--有關數(shù)據(jù)遷移方式本文檔推薦使用數(shù)據(jù)泵方式進行數(shù)據(jù)遷移，因此需要提前將目的數(shù)據(jù)庫建好OGG搭建環(huán)境準備2.1源庫操作2.1.1啟動歸檔--歸檔目錄為全部節(jié)點共享目錄--節(jié)點SQL>altersystemsetlog_archive_dest_1='location=/arch'scope=spfilesid='*';Systemaltered.SQL>altersystemsetrecyclebin=offscope=spfile;--ForanOracle10gsource,thesystemrecyclebinmustbedisabled.ForOracle11gandlater,itcanbeenabled.Systemaltered.SQL>shutdownimmediateOGGSRC:/home/oracle$exportORACLE_SID=oggsrcOGGSRC:/home/oracle$sqlplus/assysdbaSQL*Plus:Release11.2.0.1.0-ProductiononMonSep1916:29:11Copyright(c)1982,,Oracle.Allrightsreserved.Connectedtoanidleinstance.SQL>startupmount;ORACLEinstancestarted.TotalSystemGlobalAreabytesFixedSize2160600bytesVariableSizebytesDatabaseBuffersbytesRedoBuffers12206080bytesDatabasemounted.SQL>alterdatabasearchivelog;Databasealtered.SQL>archiveloglist;DatabaselogmodeArchiveModeAutomaticarchivalEnabledArchivedestination/archOldestonlinelogsequence4Nextlogsequencetoarchive7Currentlogsequence7SQL>alterdatabaseopen;Databasealtered.SQL>altersystemarchivelogcurrent;Systemaltered.2.1.2啟動附加日志功效和force_logging--節(jié)點SQL>SELECTsupplemental_log_data_min,force_loggingFROMv$database;SUPPLEMEFOR-----------NONOSQL>ALTERDATABASEADDSUPPLEMENTALLOGDATA;Databasealtered.SQL>alterdatabaseforcelogging;Databasealtered.SQL>SELECTsupplemental_log_data_min,force_loggingFROMv$database;SUPPLEMEFOR-----------YESYESSQL>ALTERSYSTEMSETENABLE_GOLDENGATE_REPLICATION=TRUESCOPE=BOTH;Systemaltered.2.2目的庫操作2.2.1啟動歸檔（可不必打開）--節(jié)點，歸檔目錄/arch非共享SQL>altersystemsetlog_archive_dest_1='location=/arch'scope=spfilesid='*';Systemaltered.$exportORACLE_SID=oggtgt$sqlplus/assysdbaSQL>startupmount;ORACLEinstancestarted.TotalSystemGlobalAreabytesFixedSize2160600bytesVariableSizebytesDatabaseBuffersbytesRedoBuffers12206080bytesDatabasemounted.SQL>alterdatabasearchivelog;Databasealtered.SQL>archiveloglist;DatabaselogmodeArchiveModeAutomaticarchivalEnabledArchivedestination/archOldestonlinelogsequence4Nextlogsequencetoarchive7Currentlogsequence7SQL>alterdatabaseopen;Databasealtered.SQL>altersystemarchivelogcurrent;Systemaltered.2.2.2啟動參數(shù)ENABLE_GOLDENGATE_REPLICATION//11.2.0.4版本的必須設立該參數(shù)為trueSQL>ALTERSYSTEMSETENABLE_GOLDENGATE_REPLICATION=TRUESCOPE=BOTH;Systemaltered.OGG搭建3.1OGG軟件安裝--此處只在源庫節(jié)點xxxx和目的庫節(jié)點xxxx上操作，其它節(jié)點不操作！此處只以xxx為例！3.1.1創(chuàng)立OGG目錄--節(jié)點[root@oggsrcmedia]#mkdir-p/u01/ogg/dirdat[root@oggsrcmedia]#chown-Roracle:oinstall/u01/ogg[root@oggsrcmedia]#chmod-R775/u01/ogg[root@oggsrcmedia]#su-oracle3.1.2配備OGG顧客環(huán)境變量--此處用oracle顧客安裝OGG添加或修改oracle顧客環(huán)境變量exportOGG_HOME=/u01/oggexportPATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:$OGG_HOMEexportLIBPATH=$ORACLE_HOME/lib:$OGG_HOME3.1.3安裝OGG軟件[oracle@oggsrc~]$cd/u01/media/[oracle@oggsrcmedia]$ls-lrttotal4616920drwxr-xr-x7oracleoinstall4096Aug26database-rw-r--r--.1oracleoinstallJul1820:43p13390677_112040_Linux-x86-64_2of7.zip-rw-r--r--.1oracleoinstallJul1820:44p13390677_112040_Linux-x86-64_1of7.zip-rw-r--r--1oracleoinstallJul1823:55fmw_12.2.1.0.0_infrastructure_Disk1_1of1.zip-rw-r--r--1oracleoinstallJul1900:00fbo_ggs_Linux_x64_shiphome.zip-rw-r--r--1oracleoinstallJul1900:04jdk-8u112-linux-x64.rpm-rw-r--r--1oracleoinstall210877Jul1905:55pdksh-5.2.14-37.el5_8.1.x86_64.rpm-rw-------1oracleoinstall4194304Jul1906:10core[oracle@oggsrcmedia]$unzip-qfbo_ggs_Linux_x64_shiphome.zip[oracle@oggsrcmedia]$cdfbo_ggs_Linux_x64_shiphome$cdDisk1$lsinstallresponserunInstallerstageOGGSRC:/u01/media/fbo_ggs_AIX_ppc_shiphome/Disk1$./runInstaller3.2源庫OGG配備--配備OGG全部用oracle顧客去操作！3.2.1創(chuàng)立OGG應用子目錄OGGSRC:/home/oracle$cd/u01/oggOGGSRC:/u01/ogg$ggsciOracleGoldenGateCommandInterpreterforOracleVersion12.2.0.1.1OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBOAIX6,ppc,64bit(optimized),Oracle11gonJan2109:52:07OperatingsystemcharactersetidentifiedasISO-8859-1.Copyright(C)1995,,Oracleand/oritsaffiliates.Allrightsreserved.GGSCI(OGGSRC)1>createsubdirsCreatingsubdirectoriesundercurrentdirectory/u01/oggParameterfiles/u01/ogg/dirprm:alreadyexistsReportfiles/u01/ogg/dirrpt:createdCheckpointfiles/u01/ogg/dirchk:createdProcessstatusfiles/u01/ogg/dirpcs:createdSQLscriptfiles/u01/ogg/dirsql:createdDatabasedefinitionsfiles/u01/ogg/dirdef:createdExtractdatafiles/u01/ogg/dirdat:createdTemporaryfiles/u01/ogg/dirtmp:createdStdoutfiles/u01/ogg/dirout:createdGGSCI(OGGSRC)2>exit3.2.2創(chuàng)立OGG顧客OGGSRC:/u01/ogg$sqlplus/assysdbaSQL>createtablespaceoggdatafile'/oradata/oggsrc/ogg01.dbf'size2G;Tablespacecreated.SQL>createuseroggidentifiedbyoggdefaulttablespaceoggtemporarytablespacetempaccountunlock;Usercreated.SQL>grantdbatoogg;Grantsucceeded.3.2.3授權OGG顧客OGGSRC:/u01/ogg$sqlplus/assysdbaSQL>grantexecuteonutl_filetoogg;Grantsucceeded.==SQL>@/u01/ogg/marker_setup.sqlEnterOracleGoldenGateschemaname:oggScriptcomplete.==SQL>@/u01/ogg/ddl_setup.sqlEnterOracleGoldenGateschemaname:oggSTATUSOFDDLREPLICATION------------------------------------------------------------------------------------------------------------------------SUCCESSFULinstallationofDDLReplicationsoftwarecomponentsScriptcomplete.==SQL>@/u01/ogg/role_setup.sqlEnterGoldenGateschemaname:oggRolesetupscriptcompleteGRANTGGS_GGSUSER_ROLETO<loggedUser>==SQL>grantggs_ggsuser_roletooggtgt;Grantsucceeded.==SQL>@/u01/ogg/ddl_enable.sqlTriggeraltered.==SQL>@/u01/ogg/marker_status.sqlPleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:oggSettingschemanametoOGGMARKERTABLE-------------------------------OKMARKERSEQUENCE-------------------------------OK==SQL>@?/rdbms/admin/dbmspoolPackagecreated.Grantsucceeded.==SQL>@/u01/ogg/ddl_pin.sqloggPL/SQLproceduresuccessfullycompleted.PL/SQLproceduresuccessfullycompleted.PL/SQLproceduresuccessfullycompleted.==SQL>@/u01/ogg/sequence.sqlPleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:SettingschemanametoOGGSTATUSOFSEQUENCESUPPORT--------------------------------------------------------------SUCCESSFULinstallationofOracleSequenceReplicationsupport==SQL>grantexecuteonoggtgt.updatesequencetooggtgt;Grantsucceeded.3.2.4創(chuàng)立GLOBALS文獻OGGSRC:/u01/ogg/dirdat$cd$OGG_HOMEOGGSRC:/u01/ogg$ggsciOracleGoldenGateCommandInterpreterforOracleVersion11.2.1.0.1OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230AIX5L,ppc,64bit(optimized),Oracle11gonApr2305:03:51Copyright(C)1995,,Oracleand/oritsaffiliates.Allrightsreserved.GGSCI(OGGSRC)20>editparams./GLOBALS##輸入以下內容##GGSCHEMAogg3.2.5創(chuàng)立Manager配備文獻##編輯MGR配備文獻，GGSCI(OGGSRC)1>editparamsmgr##輸入以下內容##PORT7809DYNAMICPORTLIST7810-7820,7830AUTOSTARTEXTRACT*AUTORESTARTEXTRACT*,RETRIES4,WAITMINUTES2STARTUPVALIDATIONDELAY5PURGEOLDEXTRACTS/u01/ogg/dirdat/*,USECHECKPOINTS,minkeepdays73.2.6創(chuàng)立primaryExtract配備文獻GGSCI(OGGSRC)9>editparamsexee##輸入以下內容##EXTRACTexeeSETENV(ORACLE_SID="oggsrc")SETENV(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)SETENV(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")USERIDogg,PASSWORDoggWARNLONGTRANS2h,CHECKINTERVAL300sFETCHOPTIONSNOUSESNAPSHOTexttrail/u01/ogg/dirdat/ltdiscardfile/u01/ogg/dirrpt/ee.dsc,append,megabytes500gettruncatesddl&includeobjnameMESIF.*&includeobjnameAHBIUSR.*&includeobjtype'USER'ddloptionsaddtrandatatableMESIF.*;sequenceMESIF.*;tableAHBIUSR.*;sequenceAHBIUSR.*;##檢查確認EXEE配備文獻內容無誤OGGSRC:/u01/ogg$/u01/ogg/checkprm/u01/ogg/dirprm/exee.prm-Cextract-mClassic-V3.2.7創(chuàng)立datapump配備文獻GGSCI(OGGSRC)9>editparamsdpee##輸入以下內容##EXTRACTdpeeSETENV(ORACLE_SID="oggsrc")SETENV(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)SETENV(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")RMTHOST192.168.1.187,MGRPORT7809,compressRMTTRAIL/u01/ogg/dirdat/rtgettruncatestableMESIF.*;sequenceMESIF.*;tableAHBIUSR.*;sequenceAHBIUSR.*;##檢查確認DPEE配備文獻內容無誤OGGSRC:/u01/ogg/checkprm/u01/ogg/dirprm/dpee.prm-Cextract-mClassic-V3.2.8為需要抽取數(shù)據(jù)的schema增加trandata##此步根據(jù)對象表數(shù)量的多少需要對應的時間，GGSCI(OGGSRC)13>dbloginuseridoggpasswordoggSuccessfullyloggedintodatabase.GGSCI(OGGSRC)14>addtrandataMESIF.*GGSCI(OGGSRC)14>addtrandataAHBIUSR.*3.2.9配備extract進程GGSCI(OGGSRC)15>addextexee,tranlog,beginnowEXTRACTadded.GGSCI(OGGSRC)16>addexttrail/u01/ogg/dirdat/lt,extexee,megabytes50EXTTRAILadded.3.2.10配備datapump進程GGSCI(OGGSRC)17>addextdpee,exttrailsource/u01/ogg/dirdat/ltEXTRACTadded.GGSCI(OGGSRC)19>addrmttrail/u01/ogg/dirdat/rt,extdpee,megabytes50RMTTRAILadded.3.3目的庫OGG配備--配備OGG全部用oracle顧客去操作！3.3.1創(chuàng)立OGG應用子目錄oggtgt:/home/oracle$cd/u01/oggoggtgt:/u01/ogg$ggsciOracleGoldenGateCommandInterpreterforOracleVersion12.2.0.1.1OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBOAIX6,ppc,64bit(optimized),Oracle11gonJan2109:52:07OperatingsystemcharactersetidentifiedasISO-8859-1.Copyright(C)1995,,Oracleand/oritsaffiliates.Allrightsreserved.GGSCI(oggtgt)1>createsubdirsCreatingsubdirectoriesundercurrentdirectory/u01/oggParameterfiles/u01/ogg/dirprm:alreadyexistsReportfiles/u01/ogg/dirrpt:createdCheckpointfiles/u01/ogg/dirchk:createdProcessstatusfiles/u01/ogg/dirpcs:createdSQLscriptfiles/u01/ogg/dirsql:createdDatabasedefinitionsfiles/u01/ogg/dirdef:createdExtractdatafiles/u01/ogg/dirdat:createdTemporaryfiles/u01/ogg/dirtmp:createdStdoutfiles/u01/ogg/dirout:createdGGSCI(oggtgt)2>3.3.2創(chuàng)立OGG顧客oggtgt:/home/oracle$sqlplus/assysdbaSQL>createtablespaceoggtgtdatafile'/oradata/oggtgt/ogg01.dbf'size2G;Tablespacecreated.SQL>createuseroggidentifiedbyoggdefaulttablespaceoggtemporarytablespacetempaccountunlock;Usercreated.SQL>grantdbatoogg;Grantsucceeded.3.3.3授權OGG顧客oggtgt:/u01/ogg$sqlplus/assysdba==SQL>@/u01/ogg/sequence.sqlPleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:SettingschemanametoOGGSTATUSOFSEQUENCESUPPORT--------------------------------------------------------------SUCCESSFULinstallationofOracleSequenceReplicationsupport==SQL>grantexecuteonogg.replicatesequencetoogg;Grantsucceeded.3.3.4創(chuàng)立GLOBALS配備文獻oggtgt:/u01/ogg/dirdat$cd$OGG_HOMEoggtgt:/u01/ogg$ggsciGGSCI(oggtgt)2>editparams./GLOBALS##輸入以下內容##GGSCHEMAoggCHECKPOINTTABLEogg.ckpttable3.3.5創(chuàng)立Manager配備文獻##編輯MGR配備文獻GGSCI(oggtgt)3>editparamsmgr##輸入以下內容##PORT7809DYNAMICPORTLIST7810-7820,7830AUTOSTARTREPLICAT*AUTORESTARTREPLICAT*,RETRIES4,WAITMINUTES2STARTUPVALIDATIONDELAY5PURGEOLDEXTRACTS/u01/ogg/dirdat/*,USECHECKPOINTS,minkeepdays73.3.6添加checkpointtableGGSCI(oggtgt)5>DBLOGINUSERIDogg,PASSWORDoggSuccessfullyloggedintodatabase.GGSCI(oggtgt)6>ADDCHECKPOINTTABLEogg.ckpttableSuccessfullycreatedcheckpointtableogg.ckpttable.3.3.7創(chuàng)立replicat配備文獻GGSCI(oggtgt)8>editparamsrpee##輸入以下內容##REPLICATrpeeSETENV(ORACLE_SID="oggtgt")SETENV(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)SETENV(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")USERIDogg,PASSWORDogg--handlecollisionsassumetargetdefsallownoopupdatesdiscardfile/u01/ogg/dirrpt/ee.dsc,append,megabytes500gettruncatesddl&includeobjnameMESIF.*&includeobjnameAHBIUSR.*&includeobjtype'USER'DBOPTIONSDEFERREFCONSTmapMESIF.*,targetMESIF.*;mapAHBIUSR.*,targetAHBIUSR.*;##確認RPEE配備文獻內容oggtgt:/u01/ogg$/u01/ogg/checkprm/u01/ogg/dirprm/rpee.prm-Creplicat-mClassic-V3.3.8配備replicat進程GGSCI(oggtgt)9>addreprpee,exttrail/u01/ogg/dirdat/rtCHECKPOINTTABLEogg.ckpttableREPLICATadded.3.4測試啟動OGG--配備OGG全部用oracle顧客去操作！3.4.1目的庫啟動OGGGGSCI(oggtgt)9>startmgrManagerstarted.GGSCI(oggtgt)11>startreplicatrpeeREPLICATRPEEisalreadyrunning.GGSCI(oggtgt)12>infoallProgramStatusGroupLagatChkptTimeSinceChkptMANAGERRUNNINGREPLICATRUNNINGRPEE00:00:0000:00:06--等待章節(jié)3.4.2的DPEE進程啟動正常后，關閉REPLICATrpeeGGSCI(oggtgt)13>stopreplicatrpee3.4.2源庫啟動OGGGGSCI(OGGSRC)17>startmgrManagerstarted.GGSCI(OGGSRC)18>infomgrManagerisrunning(IPportOGGSRC.7809).GGSCI(OGGSRC)68>startEXTRACTexeeSendingSTARTrequesttoMANAGER...EXTRACTEXEEstartingGGSCI(OGGSRC)69>startEXTRACTdpeeSendingSTARTrequesttoMANAGER...EXTRACTDPEEstartingGGSCI(OGGSRC)1>infoallProgramStatusGroupLagatChkptTimeSinceChkptMANAGERRUNNINGEXTRACTRUNNINGDPEE00:00:0000:00:03EXTRACTRUNNINGEXEE00:00:0000:00:10此處不要關閉EXEE進程！遷移數(shù)據(jù)4.1數(shù)據(jù)泵方式4.1.1源庫創(chuàng)立數(shù)據(jù)泵目錄OGGSRC:/home/oracle$exportORACLE_SID=oggsrcOGGSRC:/home/oracle$sqlplus/assysdbaSQL>createorreplacedirectoryexpdpas'/u01/expdp';Directorycreated.SQL>grantread,writeondirectoryexpdptopublic;Grantsucceeded.SQL>colownerfora15SQL>coldirectory_namefora15SQL>coldirectory_pathfora25SQL>select*fromdba_directorieswheredirectory_name='EXPDP';OWNERDIRECTORY_NAMEDIRECTORY_PATH-------------------------------------------------------SYSEXPDP/u01/expdp4.1.2源庫獲取數(shù)據(jù)庫現(xiàn)在SCNSQL>selectdbms_flashback.get_system_change_numberfromdual;GET_SYSTEM_CHANGE_NUMBER------------------------3867034.1.3源庫基于SCN號導出數(shù)據(jù)nohupexpdpMESPRD/MESPRDdirectory=expdirschemas=AHBIUSR,MESERP,MESBIGDATA,MESDMZTMP,MONITORUSER,MESPTS,MESWMS,MESIFdumpfile=mes%U.dmpjob_name=expdpmesparallel=4filesize=7Gflashback_scn=386703COMPRESSION=alllogfile=expdpmes.log&4.1.4目的庫創(chuàng)立數(shù)據(jù)泵目錄oggtgt:/home/oracle$sqlplus/assysdbaSQL>createorreplacedirectoryimpdpas'/u01/impdir';Directorycreated.SQL>grantread,writeondirectoryimpdptopublic;Grantsucceeded.SQL>select*fromdba_directorieswheredirectory_name='IMPDP';SQL>colownerfora15SQL>coldirectory_namefora15SQL>coldirectory_pathfora25SQL>select*fromdba_directorieswheredirectory_name='EXPDP';OWNERDIRECTORY_NAMEDIRECTORY_PATH-------------------------------------------------------SYSEXPDP/u01/expdp4.1.5目的庫導入數(shù)據(jù)把有關的dmp文獻，導出日志文獻從源庫拷貝至目的庫機器oggtgt上,導入腳本以下：exportORACLE_SID=oggtgtexportORACLE_BASE=/u01/app/oracleexportORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1exportPATH=$ORACLE_HOME/bin:$PATHnohupimpdpsystem/oracleoradirectory=expdpdumpfile=mes%U.dmpschemas=AHBIUSR,MESERP,MESBIGDATA,MESDMZTMP,MONITORUSER,MESPTS,MESWMS,MESIFlogfile=impdpmes.logparallel=4job_name=impdpmescluster=N&4.1.6目的庫匹配導入導出數(shù)據(jù)行數(shù)oggtgt:/u01/expdp$grep"rows"expdpmes.log|sort-n|awk'{print$4,$7,$8}'>/tmp/exp.aoggtgt:/u01/expdp$grep"rows"impdpmes.log|sort-n|awk'{print$4,$7,$8}'>/tmp/imp.boggtgt:/u01/expdp$diff/tmp/exp.a/tmp/imp.b#此時沒有成果輸出闡明導入導出數(shù)據(jù)行數(shù)一致目的庫啟動OGG同時5.1禁用目的庫觸發(fā)器#參數(shù)owner_list里面的顧客名請根據(jù)實際狀況修改#禁用約束腳本以下：oggtgt:/home/oracle/ogg$catdisable_cascade.sqlsetserveroutputonsize1000000spool/home/oracle/disable_cascade.logdefineowner_list="in('AHBIUSR','MESIF')"declarecursorcisSELECTA.OWNER,A.TABLE_NAME,A.CONSTRAINT_NAME,C.COLUMN_NAME,A.STATUS,A.DELETE_RULE,B.TABLE_NAMEREFER_TABLEFROMdba_CONSTRAINTSA,dba_CONSTRAINTSB,dba_CONS_COLUMNSCWHEREA.R_CONSTRAINT_NAME=B.CONSTRAINT_NAMEANDA.CONSTRAINT_NAME=C.CONSTRAINT_NAMEANDA.status='ENABLED'ANDA.delete_rulelike'%CASCADE%'andA.owner&owner_list;tempvarchar2(512);begindbms_output.put_line('--BEGINALTERTABLEDISABBLECASCADE--');dbms_output.put_line('--WAITFORAMONENT--');dbms_output.put_line('--...................--');forxinclooptemp:='ALTERTABLE"'||x.OWNER||'"."'||x.TABLE_NAME||'"DISABLECONSTRAINT"'||x.CONSTRAINT_NAME||'"';executeimmediatetemp;dbms_output.put_line('--DISABLECONSTRAINT'||x.OWNER||'.'||x.CONSTRAINT_NAME||'SUCCESSFUL--');endloop;dbms_output.put_line('--ENDALTERTABLEDISABBLECASCADE--');end;/spooloff5.2禁用目的庫約束#參數(shù)owner_list里面的顧客名請根據(jù)實際狀況修改#禁用觸發(fā)器腳本以下：setserveroutputonsize1000000spool/home/oracle/disable_trigger.logdefineowner_list="in('AHBIUSR','MESIF')"declarecursorcisSELECTOWNER,TRIGGER_NAMEFROMdba_triggersWHEREstatus='ENABLED'andowner&owner_list;tempvarchar2(512);begindbms_output.put_line('--BEGINDISABBLETRIGGERS--');dbms_output.put_line('--WAITFORAMONENT--');dbms_output.put_line('--...................--');forxinclooptemp:='ALTERTRIGGER"'||x.OWNER||'"."'||x.TRIGGER_NAME||'"DISABLE';executeimmediatetemp;dbms_output.put_line('--DISABLETRIGGER'||x.OWNER||'.'||x.TRIGGER_NAME||'SUCCESSFUL--');endloop;dbms_output.put_line('--ENDALTERTABLEDISABBLETRIGGERS--');end;/spooloff5.3啟動同時GGSCI(oggtgt)16>startrpee,aftercsn386703SendingSTARTrequesttoMANAGER...REPLICATRPEEstarting5.4測試同時狀況--此處在AHBIUSR顧客下單獨創(chuàng)立了一張測試表throldSQL>createtableahbiusr.throld(idintprimarykey,namevarchar(15)notnull);SQL>insertintoahbiusr.throldvalues(1,'A');1rowcreated.SQL>insertintoahbiusr.throldvalues(2,'B');1rowcreated.SQL>insertintoahbiusr.throldvalues(3,'C');1rowcreated.SQL>commit;--節(jié)點OGGSRCSQL>select*fromahbiusr.throld; IDNAME----------------------------------- 1A 2B 3C--節(jié)點oggtgtSQL>select*fromahbiusr.throld; IDNAME------------------------------------------------------- 1A 2B 3C--節(jié)點oggsrcSQL>updateahbiusr.throldsetname='G'whereid=1;--節(jié)點oggtgtSQL>select*fromahbiusr.throldwhereid=1; IDNAME------------------------------------------------------- 1G--節(jié)點OGGSRCSQL>truncatetableahbiusr.throld;Tabletruncated.SQL>select*fromahbiusr.throld;norowsselected--節(jié)點oggtgtSQL>select*fromahbiusr.throld;norowsselectedOGG同時測試6.1測試環(huán)境賬號信息原始數(shù)據(jù)源數(shù)據(jù)庫信息192.168.1.186:1521/oggsrcOracle服務器顧客賬號/密碼oracle/oracleoraOracle數(shù)據(jù)庫管理員賬號/密碼MESDMZTMP/mesdmztmp#VNC端口/密碼192.168.1.186:2/Ht1234目的數(shù)據(jù)源數(shù)據(jù)庫信息192.168.1.187:1521/oggsrcOracle服務器顧客賬號/密碼oracle/oracleoraOracle數(shù)據(jù)庫管理員賬號/密碼MESDMZTMP/mesdmztmp#VNC端口/密碼192.168.1.186:2/Ht1234注：log日志途徑：/u01/ogg/ggserr.log6.2測試場景6.2.1基本同時功效:建表、增刪改原始庫建表：createtableahbiusr.throld(idintprimarykey,namevarchar(15)notnull);2）插入數(shù)據(jù)： insertintoahbiusr.throldvalues(1,'A');insertintoahbiusr.throldvalues(2,'B');commit;3）原始庫數(shù)據(jù)： select*fromahbiusr.throld; 4）目的庫數(shù)據(jù)查詢： select*fromahbiusr.throld; 5）原始庫修改數(shù)據(jù)： updateahbiusr.throldsetname='C'; commit;6)原始庫數(shù)據(jù)查詢： select*fromahbiusr.throld; 7)目的庫數(shù)據(jù)查詢： select*fromahbiusr.throld; 8）原始庫刪除數(shù)據(jù)： truncatetableahbiusr.throld;9）原始庫數(shù)據(jù)查詢： select*fromahbiusr.throld;10）目的庫數(shù)據(jù)查詢： select*fromahbiusr.throld;6.2.2原始庫(one)與目的庫(more)表構造不同測試原始數(shù)據(jù)庫建表createtableahbiusr.one(IDINTEGERnotnull,NAMEVARCHAR2(15)notnu