ogg單機(jī)同步復(fù)制操作手冊

概要信息

1.1文檔簡介

該文檔主要用于指導(dǎo)利用OGG來進(jìn)行數(shù)據(jù)的同步復(fù)制，其中源數(shù)據(jù)庫為單機(jī)，目標(biāo)

數(shù)據(jù)庫為單機(jī)。

OGG相關(guān)安裝軟件版本為12c,主要包括如下：

1.OGG,主要用于源庫與目標(biāo)庫的數(shù)據(jù)同步

2.OGGVeridata,主要用于同步數(shù)據(jù)校驗(yàn)

溫馨提醒：

1.請仔細(xì)閱讀該文檔注意事項(xiàng)！

1.2機(jī)器環(huán)境

1.2.1源機(jī)器信息

操作系統(tǒng)版本Redhat6.4

數(shù)據(jù)庫版本11.2.0.4.0

主機(jī)名oggsrc

192.168.1.186

IP地址

1.2.2目標(biāo)機(jī)器信息

操作系統(tǒng)版本Redhat6.4

集群數(shù)據(jù)庫版本11.2.0.4.0

主機(jī)名oggtgt

IP地址192.168.1.187

1.3軟件環(huán)境

軟件名稱軟件版本部署節(jié)點(diǎn)備注

oggsrc（源庫）

OracleGoldenGate12.2.0.1.1

oggtgt（目標(biāo)庫）

OracleADR12.2.1.0.0oggsrc（源庫）

oggtgt（目標(biāo)庫）

oggsrc（源庫）

JDK1.8

oggtgt（目標(biāo)庫）

1.4軟件包信息

軟件名稱軟件包名稱

OracleGoldenGate12fbo_ggs_Linux_x64_shiphome.zip

OracleApplicationDevelopmentRuntime

fmw_12.2.LO.O_infrastructure_Diskl_lofl.zip

Infrastructure（ADR）

JDKjdk-8ull2-linux-x64.rpm

1.5注意事項(xiàng)

-軟件安裝：

對于RAC的OGG,有三種方式選擇：

l.oracle推薦直接安裝在共享文件系統(tǒng)上（共享存儲(chǔ)/OCFS/AFS）；

2.也可以安裝在RAC所有節(jié)點(diǎn)的本地目錄，但必需保證所有節(jié)點(diǎn)的安裝路徑一致；

3.還可以只安裝其中一個(gè)節(jié)點(diǎn)，但必需保證安裝OGG的節(jié)點(diǎn)可以訪問其它節(jié)點(diǎn)的歸檔目

錄（本文檔使用）

-關(guān)于歸檔

本文檔中.源庫歸檔目錄為本地目錄

-關(guān)于IP配置：

RAC上的VIP必需為固定IP且與管理IP是同一網(wǎng)段，不可以是DHCP獲取

--關(guān)于主機(jī)hosts表

安裝過程中涉及到的主機(jī)名及IP對應(yīng)關(guān)系都配置到每臺(tái)主機(jī)的hosts表中

-關(guān)于時(shí)間同步：

所有節(jié)點(diǎn)必需配有時(shí)間同步功能保證所有節(jié)點(diǎn)的操作系統(tǒng)時(shí)間一致，因?yàn)镺GG的EXTRACT

進(jìn)程是通過對比commit時(shí)間（即操作系統(tǒng)時(shí)間）來決定是否傳輸相關(guān)的數(shù)據(jù)

一關(guān)于COMPATIBLE：

確保所有節(jié)點(diǎn)的數(shù)據(jù)庫COMPATIBLE參數(shù)已經(jīng)設(shè)置

-OGG數(shù)據(jù)存放路徑

將OGG數(shù)據(jù)存放路徑mount在單邊，防止相同的進(jìn)程在其它節(jié)點(diǎn)啟動(dòng)引起進(jìn)程沖突

-源庫數(shù)據(jù)對象是否支持

OGG對數(shù)據(jù)對象支持有一定的限制，可以通過腳本提前檢查

full-schemaCheck

Orade.sql

-關(guān)于數(shù)據(jù)遷移方式

木文檔推存使用數(shù)據(jù)泉方式進(jìn)行數(shù)據(jù)遷移，因此需要提前將目標(biāo)數(shù)據(jù)庫建好

二、OGG搭建環(huán)境準(zhǔn)備

2.1源庫操作

2.1.1開啟歸檔

-歸檔目錄為所有節(jié)點(diǎn)共享目錄

一節(jié)點(diǎn)

SQL>altersystemsetlog_archive_dest_l='location=/arch'scope=spfilesid='*';

Systemaltered.

SQL>altersystemsetrecyclebin=offscope=spfile;

-ForanOracle10gsource,thesystemrecyclebinmustbecisabled.ForOracle11gandlater,it

canbeenabled.

Systemaltered.

SQL>shutdownimmediate

OGGSRC:/home/oracle$exportORACLE_SID=oggsrc

OGGSRC:/home/oracle$sqlplus/assysdba

SQL*Plus:Release11.2.0.1.0-ProductiononMonSep1916:29:112016

Copyright(c)1982,2008,Oracle.Allrightsreserved.

Connectedtoanidleinstance.

SQL>startupmount;

ORACLEinstancestarted.

TotalSystemGlobalArea4275781632bytes

FixedSize2160600bytes

VariableSize2365589544bytes

DatabaseBuffers1895825408bytes

RedoBuffers12206080bytes

Databasemounted.

SQL>alterdatabasearchivelog;

Databasealtered.

SQL>archiveloglist;

DatabaselogmodeArchiveMode

AutomaticarchivalEnabled

Archivedestination/arch

Oldestonlinelogsequence4

Nextlogsequencetoarchive7

Currentlogsequence7

SQL>alterdatabaseopen;

Databasealtered.

SQL>altersystemarchivelogcurrent;

Systemaltered.

2.1.2開啟附加日志功能和forcejogging

一節(jié)點(diǎn)

SQL>SELECTsupplemental_log_data_minzforceJoggingFROMv$database;

SUPPLEMEFOR

NONO

SQL>ALTERDATABASEADDSUPPLEMENTALLOGDATA;

Databasealtered.

SQL>alterdatabaseforcelogging;

Databasealtered.

SQL>SELECTsupplemental_log_data_min,forceJoggingFROMv$database;

SUPPLEMEFOR

YESYES

SQL>ALTERSYSTEMSETENABLE_GOLDENGATE_REPLICATICN=TRUESCOPE=BOTH;

Systemaltered.

2.2目標(biāo)庫操作

221開啟歸檔（可不必打開）

一節(jié)點(diǎn)，歸檔目錄/arch非共享

SQL>altersystemsetlog_archive_dest_l='location=/arch'scope=spfilesid='*';

Systemaltered.

$exportORACLE_SID=oggtgt

$sqlplus/assysdba

SQL>startupmount;

ORACLEinstancestarted.

TotalSystemGlobalArea4275781632bytes

FixedSize2160600bytes

VariableSize2365589544bytes

DatabaseBuffers1895825408bytes

RedoBuffers12206080bytes

Databasemounted.

SQL>alterdatabasearchivelog;

Databasealtered.

SQL>archiveloglist;

DatabaselogmodeArchiveMode

AutomaticarchivalEnabled

Archivedestination/arch

Oldestonlinelogsequence4

Nextlogsequencetoarchive7

Currentlogsequence

SQL>alterdatabaseopen;

Databasealtered.

SQL>altersystemarchivelogcurrent;

Systemaltered.

2.2.2開啟參數(shù)ENABLE_GOLDENGATE_REPLICATION

//11.2.0.4版本的必須設(shè)置該參數(shù)為true

SQL>ALTERSYSTEMSETENABLE_GOLDENGATE_REPLICATICN=TRUESCOPE=BOTH;

Systemaltered.

三、OGG搭建

3.1OGG軟件安裝

--此處只在源庫節(jié)點(diǎn)xxxx和目標(biāo)庫節(jié)點(diǎn)xxxx上操作，其它節(jié)點(diǎn)不操作！此處只以XXX為例!

3.1.1倉1J建OGG目錄

--節(jié)點(diǎn)

[root@oggsrcmedia]#mkdir-p/uOl/ogg/dirdat

[root@oggsrcmedia]#chown-Roracle:oinstall/uOl/ogg

(root@oggsrcmedia]#chmod-R775/uOl/ogg

[root@oggsrcmedia]#su-oracle

3.1.2配置OGG用戶環(huán)境變量

--此處用oracle用戶安裝OGG

添加或修改oracle用戶環(huán)境變量

exportOGG_HOME=/u01/cgg

exportPATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:$OGG_HOME

exportLIBPATH=$ORACLEHOME/lib:$OGGHOME

3.1.3安裝OGG軟件

[oracle@oggsrc~]$cd/u01/media/

[oracle@oggsrcmedia]$Is-Irt

total4616920

drwxr-xr-x7oracleoinstall4096Aug262013database

-rw-r-r-.1oracleoinstall1151304589Jul1820:43

pl3390677_112040_Linux-x86-64_2of7.zip

-rw-r-r-.1oracleoinstall1395582860Jul1820:44

pl3390677_112040_Linux-x86-64_lof7.zip

-rw-r-r-1oracleoinstall1534167825Jul1823:55

fmw_12.2.1.0.0_intrastructure_Diskl_lofl.zip

-rw-r-r-1oracleoinstall475611228Jul1900:00fbo_ggs_Linux_x64_shiphome.zip

-rw-r--r--1oracleoinstall167741674Jul1900:04jdk-8ull2-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

$cdDiskl

$ls

installresponseruninstallerstage

OGGSRC:/u01/media/fboggsAIXppcshiphome/Diskl$./runlnstaller

OracleGoldenGate12.2.0.0.0-InstallWizard-Step3of5

Summary

yinstailzionOptiongOracleGoldenCate12.2.0.0.0

gGlobalSettings

YInssl同ionDetails

SourceLocation:/u01/media/fbo_ggs_Lirux_x64_$hiphome

?Sumtnaty

DiskSpace:required786MBcallable46.54CB

yInstallProduct

InstallOption:OracleColdenCateforOraceDatabase11g

2FinishBInstallationDetails

i-SoftwareLocation:/uOl/ogg

StartManager:f+c

iaveResponseFile

|Help|V￡ack?[nsiall|Cancel|

3.2源庫OGG配置

-配置OGG全部用oracle用戶去操作！

3.2.1創(chuàng)建OGG應(yīng)用子目錄

OGGSRC:/home/oracle$cd/uOl/ogg

OGGSRC:/u01/ogg$ggsci

OracleGoldenGateCommandInterpreterforOracle

Version12.2.0.1.1OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

AIX6,ppc,64bit(optimized),Oracle11gonJan21201609:52:07

OperatingsystemcharactersetidentifiedasISO-8859-1.

Copyright(C)1995,2015,Oracleand/oritsaffiliates.Allrightsreserved.

GGSCI(OGGSRC)1>createsubdirs

Creatingsubdirectoriesundercurrentdirectory/uOl/ogg

Parameterfiles/u01/ogg/dirprm:alreadyexists

Reportfiles/uOl/ogg/dirrpt:created

Checkpointfiles/uOl/ogg/dirchk:created

Processstatusfiles/uOl/ogg/dirpcs:created

SQLscriptfiles/uOl/ogg/dirsql:created

Databasedefinitionsfiles/uOl/ogg/dirdef:created

Extractdatafiles/uOl/ogg/dirdat:created

Temporaryfiles/u01/ogg/dirtmp:created

Stdoutfiles/uOl/ogg/dirout:created

GGSCI(OGGSRC)2>exit

3.2.2倉1J建OGG用戶

OGGSRC:/u01/ogg$sqlplus/assysdba

SQL>createtablespaceoggdatafile'/oradata/oggsrc/oggOl.dbf'size2G;

Tablespacecreated.

SQL>createuseroggidentifiedbyoggdefaulttablespaceoggtemporarytablespacetemp

accountunlock;

Usercreated.

SQL>grantdbatoogg;

Grantsucceeded.

3.2.3授權(quán)OGG用戶

OGGSRC:/u01/ogg$sqlplus/assysdba

SQL>grantexecuteonutHiletoogg;

Grantsucceeded.

SOL>@/u01/ogg/marker_setup.sql

EnterOracleGoldenGateschemaname:ogg

Scriptcomplete.

SQL>@/u01/ogg/ddl_setup.sql

EnterOracleGoldenGateschemaname:ogg

STATUSOFDDLREPLICATION

SUCCESSFULinstallationofDDLReplicationsoftwarecomponents

Scriptcomplete.

SQL>@/u01/ugg/role_selup.sql

EnterGoldenGateschemaname:ogg

Rolesetupscriptcomplete

GRANTGGS_GGSUSER_ROLETO<loggedUser>

SQL>grantggs_ggsuser_roletooggtgt;

Grantsucceeded.

SQL>@/u01/ogg/ddl_enable.sql

Triggeraltered.

SQL>@/u01/ogg/marker_status.sql

PleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:

ogg

SettingschemanametoOGG

MARKERTABLE

OK

MARKERSEQUENCE

OK

SQL>@?/rdbms/admin/dbmspool

Packagecreated.

Grantsucceeded.

SQL>@/u01/ogg/ddl_pin.sqlogg

PL/SQLproceduresuccessfullycompleted.

PL/SQLproceduresuccessfullycompleted.

PL/SQLproceduresuccessfullycompleted.

SQL>@/u01/ogg/sequence.sql

PleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:

SettingschemanametoOGG

STATUSOFSEQUENCESUPPORT

SUCCESSFULinstallationofOracleSequenceReplicationsupport

SQL>grantexecuteonoggtgt.updatesequencetooggtgt;

Grantsucceeded.

3.2.4創(chuàng)建GLOBALS文件

OGGSRC:/u01/ogg/dirdat$cd$OGG_HOME

OGGSRC:/uD1/ogg$gg<；ci

OracleGoldenGateCommandInterpreterforOracle

Version11.2.1.0.1OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230

AIX5L,ppc,64bit(optimized),Oracle11gonApr23201205:03:51

Copyright(C)1995,2012,Oracleand/oritsaffiliates.Allrightsreserved.

GGSCI(OGGSRC)20>editparams./GLOBALS

##輸入如下內(nèi)容##

GGSCHEMAogg

3.2.5創(chuàng)建Manager配置文件

##編輯MGR配置文件，

GGSCI(OGGSRC)1>editparamsmgr

##輸入如下內(nèi)容##

PORT7809

DYNAMICPORTLIST7810-7820,7830

AUTOSTARTEXTRACT*

AUTORESTARTEXTRACT*,RETRIES4,WAITMINUTES2

STARTUPVALIDATIONDELAYS

PURGEOLDEXTRACTS/uOl/ogg/dirdat/*,USECHECKPOINTS,minkeepdays7

3.2.6創(chuàng)建primaryExtract配置文件

GGSCI(OGGSRC)9>editparamsexee

##輸入如下內(nèi)容##

EXTRACTexee

SETENV(ORACLE_SID="oggsrc")

SETENV(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_l)

SETENV(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")

USERIDogg,PASSWORDogg

WARNLONGTRANS2h,CHECKINTERVAL300s

FETCHOPTIONSNOUSESNAPSHOT

exttrail/u01/ogg/dirdat/lt

discardfile/uOl/ogg/dirrpt/ee.dsc,append,megabytes500

gettruncates

ddl&

includeobjnameMESIF.*&

includeobjnameAHBIUSR/&

includeobjtype'USER'

ddloptionsaddtrandata

tableMESIF.*;

sequenceMESIF.*;

tableAHBIUSR.*;

sequenceAHBIUSR.*;

##檢查確認(rèn)EXEE配置文件內(nèi)容無誤

OGGSRC:/u01/ogg$/u01/ogg/checkprm/uOl/ogg/dirprm/eKee.prm-Cextract-mClassic-V

3.2.7創(chuàng)建datapump配置文件

GGSCI(OGGSRC)9>editparamsdpee

##輸入如下內(nèi)容##

EXTRACTdpee

SETENV(ORACLE_SID="oggsrc")

SETENV(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_l)

SETENV(NLS_LANG=nAMERICAN_AMERICA.ZHS16GBK")

RMTHOST192.168.1.187,MGRPORT7809,compress

RMTTRAIL/uOl/ogg/dirdat/rt

gettruncates

IdbleMESIF.*;

sequenceMESIF.*;

tableAHBIUSR.*;

sequenceAHBIUSR.*;

##檢查確認(rèn)DPEE配置文件內(nèi)容無誤

OGGSRC:/u01/ogg/checkprm/uOl/ogg/dirprm/dpee.prm-Cextract-mClassic-V

3.2.8為需要抽取數(shù)據(jù)的schema增加trandata

##此步根據(jù)對象表數(shù)量的多少需要相應(yīng)的時(shí)間，

GGSCI(OGGSRC)13>dblognuseridoggpasswordogg

Successfullyloggedintodatabase.

GGSCI(OGGSRC)14>addtrandataMESIF.*

GGSCI(OGGSRC)14>addtrandataAHBIUSR.*

3.2.9配置extract進(jìn)程

GGSCI(OGGSRC)15>addextexee,tranlog,beginnow

EXTRACTadded.

GGSCI(OGGSRC)16>addexttrail/uOl/ogg/dirdat/lt,extexee,megabytes50

EXTTRAILadded.

3.2.10配置datapump進(jìn)程

GGSCI(OGGSRC)17>addextdpee,exttrailsource/uOl/ogg/dirdat/lt

EXTRACTadded.

GGSCI(OGGSRC)19>addrmttrail/uOl/ogg/dirdat/rt,extdpee,megabytes50

RMTTRAILadded.

3.3目標(biāo)庫OGG配置

-配置OGG全部用oracle用戶去操作!

3.3.1創(chuàng)建OGG應(yīng)用子目錄

oggtgt:/home/oracle$cd/uOl/ogg

oggtgt:/u01/ogg$ggsci

OracleGoldenGateCommandInterpreterforOracle

Version12.2.0.1.1OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

AIX6,ppc,64bit(optimized),Oracle11gonJan21201609:52:07

OperatingsystemcharactersetidentifiedasISO-8859-1.

Copyright(C)1995,2015,Oracleand/oritsaffiliates.Allrightsreserved.

GGSCI(oggtgt)1>createsubdirs

Creatingsubdirectoriesundercurrentdirectory/u01/ogg

Parameterfiles/u01/ogg/dirprm:alreadyexists

Reportfiles/uOl/ogg/dirrpt:created

Checkpointfiles/uOl/ogg/dirchk:created

Processstatusfiles/uOl/ogg/dirpcs:created

SQLscriptfiles/uOl/ogg/dirsql:created

Databasedefinitionsfiles/uOl/ogg/dirdef:created

Extractdatafiles/uOl/ogg/dirdat:created

Temporaryfiles/u01/ogg/dirtmp:created

Stdoutfiles/uOl/ogg/dirout:created

GGSCI(oggtgt)2>

3.3.2倉1J建OGG用戶

oggtgt:/home/oracle$sqlplus/assysdba

SQL>createtablespaceoggtgtdatafile'/oradata/oggtgt/oggO1.dbf'size2G;

Tablespacecreated.

SQL>createuseroggidentifiedbyoggdefaulttablespaceoggtemporarytablespacetemp

dccounlunlock;

Usercreated.

SQL>grantdbatoogg;

Grantsucceeded.

3.3.3授權(quán)OGG用戶

oggtgt:/u01/ogg$sqlplus/cSsysdba

SQL>@/u01/ogg/sequence.sql

PleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:

SettingschemanametoOGG

STATUSOFSEQUENCESUPPORT

SUCCESSFULinstallationofOracleSequenceReplicationsupport

SQL>grantexecuteonogg.-eplicatesequencetoogg;

Grantsucceeded.

3.3.4創(chuàng)建GLOBALS配置文件

oggtgt:/u01/ogg/dirdat$cd$OGG_HOME

oggtgt:/u01/ogg$ggsci

GGSCI(oggtgt)2>editparams./GLOBALS

##輸入如卜內(nèi)容##

GGSCHEMAogg

CHECKPOINTTABLEogg.ckpttable

3.3.5創(chuàng)建Manager配置文件

##編輯MGR配置文件

GGSCI(oggtgt)3>editparamsmgr

##輸入如下內(nèi)容##

PORT7809

DYNAMICPORTLIST7810-7820,7830

AUTOSTARTREPLICAT*

AUTORESTARTREPLICAT*,RETRIES4,WAITMINUTES2

STARTUPVALIDATIONDELAYS

PURGEOLDEXTRACTS/uOl/ogg/dirdat/*,USECHECKPOINTS,minkeepdays7

3.3.6添力口checkpointtable

GGSCI(oggtgt)5>DBLOGINUSERIDogg,PASSWORDogg

Successfullyloggedintodatabase.

GGSCI(oggtgt)6>ADDCHECKPOINTTABLEogg.ckpttable

Successfullycreatedcheckpointtableogg.ckpttable.

3.3.7創(chuàng)建replicat配置文件

GGSCI(oggtgt)8>editparamsrpee

相輸入如下內(nèi)容##

REPLICATrpee

SETENV(ORACLE_SID="oggtgt")

SETENV(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_l)

SETENV(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")

USERIDogg,PASSWORDogg

-handlecollisions

assumetargetdefs

allownoopupdates

discardfile/uOl/ogg/dirrpt/ee.ds^append,megabytes500

gettruncates

ddl&

includeobjnameMESIF.*&

includeobjnameAHBIUSR?&

includeobjtype'USER'

DBOPTIONSDEFERREFCONST

mapMESIF.*,targetMESIF?;

mapAHBIUSR.*,targetAHBIUSR.*;

##確認(rèn)RPEE配置文件內(nèi)容

oggtgt:/u01/ogg$/u01/ogg/checkprm/uOl/ogg/dirprm/rpee.prm-Creplicat-mClassic-V

3.3.8配置replicat進(jìn)程

GGSCI(oggtgt)9>addreprpee,exttrail/u01/ogg/dirdat/rtCHECKPOINTTABLEogg.ckpttable

REPLICATadded.

3.4測試啟動(dòng)OGG

-配置OGG全部用oracle用戶去操作！

3.4.1目標(biāo)庫啟動(dòng)OGG

GGSCI(oggtgt)9>startmgr

Managerstarted.

GGSCI(oggtgt)11>startreplicatrpee

REPLICATRPEEisalreadyrunning.

GGSCI(oggtgt)12>infoall

ProgramStatusGroupLagatChkptTimeSinceChkpt

MANAGERRUNNING

REPLICATRUNNINGRPEE00:00:0000:00:06

一等待章節(jié)3.4.2的DPEE進(jìn)程啟動(dòng)正常后,關(guān)閉REPLICATrpee

GGSCI(oggtgt)13>stopreplicatrpee

3.4.2源庫啟動(dòng)OGG

GGSCI(OGGSRC)17>startmgr

Managerstarted.

GGSCI(OGGSRC)18>infomgr

Managerisrunning(IPportOGGSRC.7809).

GGSCI(OGGSRC)68>startEXTRACTexee

SendingSTARTrequesttoMANAGER...

EXTRACTEXEEstarting

GGSCI(OGGSRC)69>startEXTRACTdpee

SendingSTARTrequesttoMANAGER...

EXTRACTDPEEstarting

GGSCI(OGGSRC)1>infoall

ProgramStatusGroupLagatChkptTimeSinceChkpt

MANAGERRUNNING

EXTRACTRUNNINGDPEE00:00:0000:00:03

EXTRACTRUNNINGEXEE00:00:0000:00:10

此處不要關(guān)閉EXEE進(jìn)程!

U!遷移數(shù)據(jù)

4.1數(shù)據(jù)泵方式

4.L1源庫創(chuàng)建數(shù)據(jù)泵目錄

OGGSRC:/home/oracle$exportORACLE_SID=oggsrc

OGGSRC:/home/oracle$sqlplus/assysdba

SQL>createorreplacedirectoryexpdpas'/uOl/expdp';

Directorycreated.

SQL>grantread,writeondirectoryexpdptopublic;

Grantsucceeded.

SQL>colownerforal5

SQL>coldirectory_nameforal5

SQL>coldirectory_pathfora25

SQL>select*fromdba_directorieswheredirectory_name=,EXPDP';

OWNERDIRECTORY_NAMEDIRECTORY_PATH

SYSEXPDP/uOl/expdp

4.1.2源庫獲取數(shù)據(jù)庫當(dāng)前SCN

SQL>selectdbms_flashback.get_system_change_numberfromdual;

GET_SYSTEM_CHANGE_NUMBER

386703

4.1.3源庫基于SCN號(hào)導(dǎo)出數(shù)據(jù)

nohupexpdoMESPRD/MESP^Ddirectory=expdir

schemas=AHBIUSR/MESERFfMESBIGDATA/MESDMZTMP,MOMITORUSER/MESPTSzMESWMS/ME

SIFdumpfile=mes%U.dmpjob_name=expdpmesparallel=4filesize=7Gflashback_scn=386703

COMPRESSION=alllogfile=expdpmes.log&

4.1.4目標(biāo)庫創(chuàng)建數(shù)據(jù)泵目錄

oggtgt:/home/oracle$sqlplus/assysdba

SQL>createorreplacedirectoryimpdpas'/uOl/impdir';

Directorycreated.

SQL>grantread,writeondirectoryimpdptopublic;

Grantsucceeded.

SQL>select*fromdba_directorieswheredirectory_name='IMPDP';

SQL>colownerforal5

SQL>coldirectory_nameforal5

SQL>coldirectory_pathfora25

SQL>select*fromdba_directorieswheredirectory_name=,EXPDP,;

OWNERDIRECTORY_NAMEDIRECTORY_PATH

SYSEXPDP/uOl/expdp

4.1.5目標(biāo)庫導(dǎo)入數(shù)據(jù)

把相關(guān)的dmp文件，導(dǎo)出日志文件從源庫拷貝至目標(biāo)庫機(jī)器oggtgt上，導(dǎo)入腳本如下:-

exportORACLESID=oggtgt

exportORACLE_BASE=/u01/app/oracle

exportORACLE_HOME=/u01/app/oracle/product/11.2.0/db_l

exportPATH=$ORACLE_HOME/bin:$PATH

nohupimpdpsystem/oracleoradirectory=expdpdumpfile=mes%U.dmpschemas=

AHBIUSR,MESERP,MESBIGDATA,MESDMZTMP,MONITORUSER,MESPTS,MESWMS,MESIF

logfile=impdpmes.logparaIel=4jobname=impdpmescluster=N&

4.1.6目標(biāo)庫匹配導(dǎo)入導(dǎo)出數(shù)據(jù)行數(shù)

oggtgt：/u01/expdp$grep"rows"expdpmes.log|sort-n|awk'{print$4,$7,$8}'>/tmp/exp.a

oggtgt:/u01/expdp$grep"rows"impdpmes.log|sort-n|awk'{print$4,$7,$8}'>

/tmp/imp.b

oggtgt:/u01/expdp$diff/tmp/exp.a/tmp/imp.b#此時(shí)沒有結(jié)果輸出說明導(dǎo)入導(dǎo)出數(shù)

據(jù)行數(shù)一致

五、目標(biāo)庫開啟OGG同步

5.1禁用目標(biāo)庫觸發(fā)器

#參數(shù)ownerjist里面的用戶名請根據(jù)實(shí)際情況修改

#禁用約束腳本如下：

oggtgt:/home/oracle/ogg$catdisable_cascade.sql

setserveroutputonsize1000000

spool/home/oracle/disable_cascade.log

defineowner_list="in('AHBIUSR'；MESIF')"

declare

cursorcisSELECTA.OWNER,A.TABLE_NAME,A.CONSTRAINT_NAME,C.COLUMN_NAME,

A.STATUS,A.DELETE_RULE,B.TABLE_NAMEREFER_TABLE

FROMdba_CONSTRAINTSA,dba_CONSTRAINTSBzdba_CONS_COLUMNSC

WHEREA.R_CONSTRAINT_NAME=B.CONSTRAINT_NAME

ANDA.CONSTRAINT_NAME=C.CONSTRAINT_NAME

ANDA.status='ENABLED'

ANDA.delete_rulelike/CASCADE%'

andA.owner&owner_list;

tempvarchar2(512);

begin

dbms_output.put_line('-BEGINALTERTABLEDISABBLECASCADE

dbms_output.put_line('-WAITFORAMONENT

dbms_output.put_linef-....................-')；

forxincloop

temp:='ALTERTABLE""||x.OWNER||||x.TABLE_NAME||DISABLE

CONSTRAINT'"I|x.CONSTRAINT_NAME11"";

executeimmediatetemp;

dbms.output.putJineC-DISABLECONSTRAINT11

x.OWNER11'.'11x.CONSTRAINT_NAME|I'SUCCESSFUL-');

endloop;

dbms_output.put_line('-ENDALTERTABLEDISABBLECASCADE

end;

/

spooloff

5.2禁用目標(biāo)庫約束

#參數(shù)ownerjist里面的用戶名請根據(jù)實(shí)際情況修改

#禁用觸發(fā)器腳本如下：

setserveroutputonsize1C00000

spool/home/oracle/disable_trigger.log

defineowner_list="in('AHBIUSR'；MESIF')"

declare

cursorcisSELECTOWNER,TRIGGER_NAMEFROMdbajriggersWHEREstatus='ENABLED'

andowner&owner_list;

tempvarchar2(512);

begin

dbms_output.put_line('-BEGINDISABBLETRIGGERS

dbms_output.putjinef-WAITFORAMONENT

dbms_output.put_line('—....................—

forxincloop

temp:='ALTERTRIGGER"'||x.OWNER||x.TRIGGER_NAME|DISABLE';

executeimmediatetemp;

dbms_output.put_line('-DISABLETRIGGER'I|x.OWNER||x,TRIGGER_NAME|

SUCCESSFUL-');

endloop;

dbms_output.put_line('-ENDALTERTABLEDISABBLETRIGGERS

end;

/

spooloff

5.3開啟同步

GGSCI(oggtgt)16>startrpee,aftercsn386703

SendingSTARTrequesttoMANAGER...

REPLICATRPEEstarting

5.4測試同步情況

--此處在AHBIUSR用戶下單獨(dú)創(chuàng)建了一張測試表throld

SQL>createtableahbiusr.throld(idintprimarykeynamevaxhar(15)notnull);

SQL>insertintoahbiusr.throldvalues(1/A');

1rowcreated.

SQL>insertintoahbiusr.throldvalues(2,'B');

1rowcreated.

SQL>insertintoahbiusr.throldvalues(3,'C)；

1rowcreated.

SQL>commit;

一節(jié)點(diǎn)OGGSRC

SQL>select*fromahbiusr.throld;

IDNAME

1A

2B

3C

一節(jié)點(diǎn)oggtgt

SQL>select*fromahbiusr.throld;

IDNAME

1A

2B

3C

“節(jié)點(diǎn)oggsrc

SQL>updateahbiusr.throldsetname='G'whereid=l;

一節(jié)點(diǎn)oggtgt

SQL>select*fromahbiusr.throldwhereid=l;

IDNAME

1G

一節(jié)點(diǎn)OGGSRC

SQL>truncatetableahbiusr.throld;

Tabletruncated.

SQL>select*fromahbiusr.throld;

norowsselected

一節(jié)點(diǎn)Oggtgt

SQL>select*fromahbiusr.throld;

norowsselected

六、OGG同步測試

6.1測試環(huán)境賬號(hào)信息

原始數(shù)據(jù)源

數(shù)據(jù)庫信息192.168.1.186:1521/oggsrc

Oracle服務(wù)器用戶賬號(hào)/密碼oracle/oracleora

Oracle數(shù)據(jù)庫管理員賬號(hào)/密碼MESDMZTMP/mesdmztmp#

VNC端口/密碼192.168.1.186:2/Htl234

目標(biāo)數(shù)據(jù)源

數(shù)據(jù)庫信息192.168.1.187:1521/oggsrc

Oracle服務(wù)器用戶賬號(hào)/密碼oracle/oracleora

Oracle數(shù)據(jù)庫管理員賬號(hào)/密碼MESDMZTMP/mesdmztmp#

VNC端口/密碼192.168.1.186:2/Htl234

注:log日志路徑:/uOl/ogg/ggserr.log

6.2測試場景

6.2.1基本同步功能：建表、增刪改

1)原始庫建表：

createtableahbiusr.throld(idintprimarykeynamevarchar(15)notnull);

插入數(shù)據(jù)：

insertintoahbiusr.thrcldvalues（1/A'）;

insertintoahbiusr.thrcldvalues（2/B'）;

commit;

3）原始庫數(shù)據(jù)：

select*fromahbiusr.throld;

SQL>select*fromahbiusr.throld;

IDNAME

1A

2B

4）目標(biāo)庫數(shù)據(jù)查詢：

select*fromahbiusr.throld;

SQL>select*fromahbiusr.throld;

IDNAME

1A

2B

5）原始庫修改數(shù)據(jù);

updateahbiusr.throldsetname='C;

commit;

6）原始庫數(shù)據(jù)查詢：

select*fromahbiusr.throld;

SQL>select*fromahbiusr.throld;

IDNAME

1C

2C

7）目標(biāo)庫數(shù)據(jù)查詢：

select*fromahbiusr.throld;

SQL>select*fromahbiusr.throld;

IDNAME

1C

2C

8）原始庫刪除數(shù)據(jù)：

truncatetableahbiusr.throld;

9）原始庫數(shù)據(jù)查詢：

select*fromahbiusr.throld;

SQL>select*fromahbiusr.throld;

norowsselected

10）目標(biāo)庫數(shù)據(jù)查詢：

select*fromahbiusr.throld;

SQL>select*fromahbiusr.throld;

norowsselected

6.2.2原始庫(one)與目標(biāo)庫(more)表結(jié)構(gòu)不同測試

i)原始數(shù)據(jù)庫建表

createtableahbiusr.one(IDINTEGERnotnull,NAMEVARCHAR2(15)notnull);

2)目標(biāo)數(shù)據(jù)庫建表：

createtableahbiusr.more(CIDINTEGERnotnull,CNAMEVARCHAR2(15)notnull,

COMMENTSVARCHAR2(2O));

3)停止規(guī)則組服務(wù)：

a.在ogg目錄下執(zhí)行如下命令進(jìn)入GGSCI：

?/ggsci

b.輸入以下命令停止服務(wù)：

stoprpmo

4)編輯配置文件：

c.在ogg目錄下執(zhí)行如下命令進(jìn)入GGSQ：

?/ggsci

d.新增或者編輯規(guī)則組：

editparamsrpmo//rpmo是規(guī)則組名字

e.添加如下內(nèi)容：

REPLICATrpmo

SETENV(ORACLE_SID="oggtgt")

SETENV(ORACLE_HOIVE-/u01/app/oracle/product/11.2.0/d3_l)

SETENV(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")

USERIDogg,PASSWORDogg

-handlecollisions

assumetargetdefs

allownoopupdates

discardfile/uOl/ogg/dirrpt/ee.dsc,append,megabytes500

gettruncates

DBOPTIONSDEFERREFCONST

-DDLINCLUDEUNMAPPED

DDLERRORDEFAULTIGNORERETRYOP

mapAHBIUSR.one,targetAHBIUSR.more,COLMAP(cid=id/cname=name);

f.添加進(jìn)程：

addreprpmo,exttrail/uOl/ogg/dirdat/rtCHECKPOINTTABLEogg.ckpttable

g.啟動(dòng)服務(wù)：

startreplicatrpmo

1)原始數(shù)據(jù)庫插入數(shù)據(jù)：

insertintoahbiusr.onevalues(1/A1);

insertintoahbiusr.onevalues(2,B);

2)原始數(shù)據(jù)庫查詢：

select*fromahbiusr.one;

SQL>select*fromahbiusr.one;

IDNAME

1A

2B

3)目標(biāo)數(shù)據(jù)庫查詢：

select*fromahbiusr.more;

SQL>select*fromahbiusr.more;

CIDCNAMECOMMENTS

1AA

2BA

6.2.3原始庫(more)與目標(biāo)庫(one)表結(jié)構(gòu)不同測試

i)原始數(shù)據(jù)庫建表

createtableahbiusr.morel(CIDINTEGERnotnull,CNAMEVARCHAR2(15)notnull,

COMMENTSVARCHAR2(2O));

2)目標(biāo)數(shù)據(jù)庫建表：

createtabl