ogg单机同步复制操作手册

概要信息

1.1文档简介

该文档主要用于指导利用OGG来进行数据的同步复制，其中源数据库为单机，目标

数据库为单机。

OGG相关安装软件版本为12c,主要包括如下：

1.OGG,主要用于源库与目标库的数据同步

2.OGGVeridata,主要用于同步数据校验

温馨提醒：

1.请仔细阅读该文档注意事项！

1.2机器环境

1.2.1源机器信息

操作系统版本Redhat6.4

数据库版本11.2.0.4.0

主机名oggsrc

192.168.1.186

IP地址

1.2.2目标机器信息

操作系统版本Redhat6.4

集群数据库版本11.2.0.4.0

主机名oggtgt

IP地址192.168.1.187

1.3软件环境

软件名称软件版本部署节点备注

oggsrc（源库）

OracleGoldenGate12.2.0.1.1

oggtgt（目标库）

OracleADR12.2.1.0.0oggsrc（源库）

oggtgt（目标库）

oggsrc（源库）

JDK1.8

oggtgt（目标库）

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注意事项

-软件安装：

对于RAC的OGG,有三种方式选择：

l.oracle推荐直接安装在共享文件系统上（共享存储/OCFS/AFS）；

2.也可以安装在RAC所有节点的本地目录，但必需保证所有节点的安装路径一致；

3.还可以只安装其中一个节点，但必需保证安装OGG的节点可以访问其它节点的归档目

录（本文档使用）

-关于归档

本文档中.源库归档目录为本地目录

-关于IP配置：

RAC上的VIP必需为固定IP且与管理IP是同一网段，不可以是DHCP获取

--关于主机hosts表

安装过程中涉及到的主机名及IP对应关系都配置到每台主机的hosts表中

-关于时间同步：

所有节点必需配有时间同步功能保证所有节点的操作系统时间一致，因为OGG的EXTRACT

进程是通过对比commit时间（即操作系统时间）来决定是否传输相关的数据

一关于COMPATIBLE：

确保所有节点的数据库COMPATIBLE参数已经设置

-OGG数据存放路径

将OGG数据存放路径mount在单边，防止相同的进程在其它节点启动引起进程冲突

-源库数据对象是否支持

OGG对数据对象支持有一定的限制，可以通过脚本提前检查

full-schemaCheck

Orade.sql

-关于数据迁移方式

木文档推存使用数据泉方式进行数据迁移，因此需要提前将目标数据库建好

二、OGG搭建环境准备

2.1源库操作

2.1.1开启归档

-归档目录为所有节点共享目录

一节点

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

一节点

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目标库操作

221开启归档（可不必打开）

一节点，归档目录/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开启参数ENABLE_GOLDENGATE_REPLICATION

//11.2.0.4版本的必须设置该参数为true

SQL>ALTERSYSTEMSETENABLE_GOLDENGATE_REPLICATICN=TRUESCOPE=BOTH;

Systemaltered.

三、OGG搭建

3.1OGG软件安装

--此处只在源库节点xxxx和目标库节点xxxx上操作，其它节点不操作！此处只以XXX为例!

3.1.1仓1J建OGG目录

--节点

[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用户环境变量

--此处用oracle用户安装OGG

添加或修改oracle用户环境变量

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创建OGG应用子目录

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授权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创建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

##输入如下内容##

GGSCHEMAogg

3.2.5创建Manager配置文件

##编辑MGR配置文件，

GGSCI(OGGSRC)1>editparamsmgr

##输入如下内容##

PORT7809

DYNAMICPORTLIST7810-7820,7830

AUTOSTARTEXTRACT*

AUTORESTARTEXTRACT*,RETRIES4,WAITMINUTES2

STARTUPVALIDATIONDELAYS

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

3.2.6创建primaryExtract配置文件

GGSCI(OGGSRC)9>editparamsexee

##输入如下内容##

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.*;

##检查确认EXEE配置文件内容无误

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

3.2.7创建datapump配置文件

GGSCI(OGGSRC)9>editparamsdpee

##输入如下内容##

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.*;

##检查确认DPEE配置文件内容无误

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

3.2.8为需要抽取数据的schema增加trandata

##此步根据对象表数量的多少需要相应的时间，

GGSCI(OGGSRC)13>dblognuseridoggpasswordogg

Successfullyloggedintodatabase.

GGSCI(OGGSRC)14>addtrandataMESIF.*

GGSCI(OGGSRC)14>addtrandataAHBIUSR.*

3.2.9配置extract进程

GGSCI(OGGSRC)15>addextexee,tranlog,beginnow

EXTRACTadded.

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

EXTTRAILadded.

3.2.10配置datapump进程

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

EXTRACTadded.

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

RMTTRAILadded.

3.3目标库OGG配置

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

3.3.1创建OGG应用子目录

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授权OGG用户

oggtgt:/u01/ogg$sqlplus/cSsysdba

SQL>@/u01/ogg/sequence.sql

PleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:

SettingschemanametoOGG

STATUSOFSEQUENCESUPPORT

SUCCESSFULinstallationofOracleSequenceReplicationsupport

SQL>grantexecuteonogg.-eplicatesequencetoogg;

Grantsucceeded.

3.3.4创建GLOBALS配置文件

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

oggtgt:/u01/ogg$ggsci

GGSCI(oggtgt)2>editparams./GLOBALS

##输入如卜内容##

GGSCHEMAogg

CHECKPOINTTABLEogg.ckpttable

3.3.5创建Manager配置文件

##编辑MGR配置文件

GGSCI(oggtgt)3>editparamsmgr

##输入如下内容##

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创建replicat配置文件

GGSCI(oggtgt)8>editparamsrpee

相输入如下内容##

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.*;

##确认RPEE配置文件内容

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

3.3.8配置replicat进程

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

REPLICATadded.

3.4测试启动OGG

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

3.4.1目标库启动OGG

GGSCI(oggtgt)9>startmgr

Managerstarted.

GGSCI(oggtgt)11>startreplicatrpee

REPLICATRPEEisalreadyrunning.

GGSCI(oggtgt)12>infoall

ProgramStatusGroupLagatChkptTimeSinceChkpt

MANAGERRUNNING

REPLICATRUNNINGRPEE00:00:0000:00:06

一等待章节3.4.2的DPEE进程启动正常后,关闭REPLICATrpee

GGSCI(oggtgt)13>stopreplicatrpee

3.4.2源库启动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

此处不要关闭EXEE进程!

U!迁移数据

4.1数据泵方式

4.L1源库创建数据泵目录

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源库获取数据库当前SCN

SQL>selectdbms_flashback.get_system_change_numberfromdual;

GET_SYSTEM_CHANGE_NUMBER

386703

4.1.3源库基于SCN号导出数据

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目标库创建数据泵目录

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目标库导入数据

把相关的dmp文件，导出日志文件从源库拷贝至目标库机器oggtgt上，导入脚本如下:-

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目标库匹配导入导出数据行数

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#此时没有结果输出说明导入导出数

据行数一致

五、目标库开启OGG同步

5.1禁用目标库触发器

#参数ownerjist里面的用户名请根据实际情况修改

#禁用约束脚本如下：

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禁用目标库约束

#参数ownerjist里面的用户名请根据实际情况修改

#禁用触发器脚本如下：

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用户下单独创建了一张测试表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;

一节点OGGSRC

SQL>select*fromahbiusr.throld;

IDNAME

1A

2B

3C

一节点oggtgt

SQL>select*fromahbiusr.throld;

IDNAME

1A

2B

3C

“节点oggsrc

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

一节点oggtgt

SQL>select*fromahbiusr.throldwhereid=l;

IDNAME

1G

一节点OGGSRC

SQL>truncatetableahbiusr.throld;

Tabletruncated.

SQL>select*fromahbiusr.throld;

norowsselected

一节点Oggtgt

SQL>select*fromahbiusr.throld;

norowsselected

六、OGG同步测试

6.1测试环境账号信息

原始数据源

数据库信息192.168.1.186:1521/oggsrc

Oracle服务器用户账号/密码oracle/oracleora

Oracle数据库管理员账号/密码MESDMZTMP/mesdmztmp#

VNC端口/密码192.168.1.186:2/Htl234

目标数据源

数据库信息192.168.1.187:1521/oggsrc

Oracle服务器用户账号/密码oracle/oracleora

Oracle数据库管理员账号/密码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);

插入数据：

insertintoahbiusr.thrcldvalues（1/A'）;

insertintoahbiusr.thrcldvalues（2/B'）;

commit;

3）原始库数据：

select*fromahbiusr.throld;

SQL>select*fromahbiusr.throld;

IDNAME

1A

2B

4）目标库数据查询：

select*fromahbiusr.throld;

SQL>select*fromahbiusr.throld;

IDNAME

1A

2B

5）原始库修改数据;

updateahbiusr.throldsetname='C;

commit;

6）原始库数据查询：

select*fromahbiusr.throld;

SQL>select*fromahbiusr.throld;

IDNAME

1C

2C

7）目标库数据查询：

select*fromahbiusr.throld;

SQL>select*fromahbiusr.throld;

IDNAME

1C

2C

8）原始库删除数据：

truncatetableahbiusr.throld;

9）原始库数据查询：

select*fromahbiusr.throld;

SQL>select*fromahbiusr.throld;

norowsselected

10）目标库数据查询：

select*fromahbiusr.throld;

SQL>select*fromahbiusr.throld;

norowsselected

6.2.2原始库(one)与目标库(more)表结构不同测试

i)原始数据库建表

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

2)目标数据库建表：

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

COMMENTSVARCHAR2(2O));

3)停止规则组服务：

a.在ogg目录下执行如下命令进入GGSCI：

•/ggsci

b.输入以下命令停止服务：

stoprpmo

4)编辑配置文件：

c.在ogg目录下执行如下命令进入GGSQ：

•/ggsci

d.新增或者编辑规则组：

editparamsrpmo//rpmo是规则组名字

e.添加如下内容：

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.添加进程：

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

g.启动服务：

startreplicatrpmo

1)原始数据库插入数据：

insertintoahbiusr.onevalues(1/A1);

insertintoahbiusr.onevalues(2,B);

2)原始数据库查询：

select*fromahbiusr.one;

SQL>select*fromahbiusr.one;

IDNAME

1A

2B

3)目标数据库查询：

select*fromahbiusr.more;

SQL>select*fromahbiusr.more;

CIDCNAMECOMMENTS

1AA

2BA

6.2.3原始库(more)与目标库(one)表结构不同测试

i)原始数据库建表

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

COMMENTSVARCHAR2(2O));

2)目标数据库建表：

createtabl