ogg单机同步复制操作手册

1、一、 概要信息1.1文档简介该文档主要用于指导利用OGG来进行数据的同步复制，其中源数据库为单机，目标数据库为单机。OGG相关安装软件版本为12c，主要包括如下：1. OGG，主要用于源库与目标库的数据同步2. OGG Veridata，主要用于同步数据校验温馨提醒：1. 请仔细阅读该文档注意事项！1.2机器环境1.2.1源机器信息操作系统版本Redhat 6.4数据库版本主机名oggsrcIP地址1.2.2目标机器信息操作系统版本Redhat 6.4集群数据库版本主机名oggtgtIP地址1.3软件环境软件名称软件版本部署节点备注Oracle GoldenGateoggsrc（源库） ogg

2、tgt（目标库）Oracle ADRoggsrc（源库） oggtgt（目标库）JDK1.8oggsrc（源库） oggtgt（目标库）1.4软件包信息软件名称软件包名称Oracle GoldenGate 12fbo_ggs_Linux_x64_shiphome.zipOracle Application Development Runtime Infrastructure（ADR）JDKjdk-8u112-linux-x64.rpm1.5注意事项-软件安装：对于RAC的OGG，有三种方式选择:1.oracle推荐直接安装在共享文件系统上（共享存储/OCFS/AFS）；2.也可以安装在RAC所

3、有节点的本地目录，但必需保证所有节点的安装路径一致；3.还可以只安装其中一个节点，但必需保证安装OGG的节点可以访问其它节点的归档目录(本文档使用)-关于归档本文档中，源库归档目录为本地目录-关于IP配置：RAC上的VIP必需为固定IP且与管理IP是同一网段，不可以是DHCP获取-关于主机hosts表安装过程中涉及到的主机名及IP对应关系都配置到每台主机的hosts表中-关于时间同步：所有节点必需配有时间同步功能保证所有节点的操作系统时间一致，因为OGG的EXTRACT进程是通过对比commit时间（即操作系统时间）来决定是否传输相关的数据-关于COMPATIBLE：确保所有节点的数据库COM

4、PATIBLE参数已经设置-OGG数据存放路径将OGG数据存放路径mount在单边，防止相同的进程在其它节点启动引起进程冲突-源库数据对象是否支持OGG对数据对象支持有一定的限制，可以通过脚本提前检查-关于数据迁移方式本文档推荐使用数据泵方式进行数据迁移，因此需要提前将目标数据库建好二、 OGG搭建环境准备2.1源库操作2.1.1开启归档-归档目录为所有节点共享目录-节点SQL> alter system set log_archive_dest_1='location=/arch' scope=spfile sid='*' System altered.

5、SQL> alter system set recyclebin=off scope=spfile;-For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.System altered. SQL> shutdown immediate OGGSRC:/home/oracle$export ORACLE_SID=oggsrcOGGSRC:/home/oracle$sqlplus / as sysdba SQL*Plus

6、: Release 11.2.0.1.0 - Production on Mon Sep 19 16:29:11 2016 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 4275781632 bytes Fixed Size 2160600 bytes Variable Size 2365589544 bytes Databa

7、se Buffers 1895825408 bytes Redo Buffers 12206080 bytes Database mounted. SQL> alter database archivelog;Database altered.SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /archOldest online log sequence 4Next log sequence to archive 7Current log

8、 sequence 7 SQL> alter database open;Database altered.SQL> alter system archive log current;System altered.2.1.2开启附加日志功能和force_logging-节点SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;SUPPLEME FOR- -NO NOSQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;Database altered

9、.SQL> alter database force logging;Database altered.SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;SUPPLEME FOR- -YES YESSQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;System altered.2.2目标库操作2.2.1开启归档（可不必打开）-节点，归档目录/arch非共享SQL> alter system se

10、t log_archive_dest_1='location=/arch' scope=spfile sid='*' System altered. $export ORACLE_SID=oggtgt $sqlplus / as sysdba SQL> startup mount; ORACLE instance started. Total System Global Area 4275781632 bytes Fixed Size 2160600 bytes Variable Size 2365589544 bytes Database Buffers

11、 1895825408 bytes Redo Buffers 12206080 bytes Database mounted. SQL> alter database archivelog;Database altered.SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /archOldest online log sequence 4Next log sequence to archive 7Current log sequence

12、7 SQL> alter database open;Database altered.SQL> alter system archive log current;System altered.2.2.2开启参数ENABLE_GOLDENGATE_REPLICATION版本的必须设置该参数为trueSQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;System altered.三、 OGG搭建3.1 OGG软件安装-此处只在源库节点xxxx和目标库节点xxxx上操作，其它节点不操作！此处

13、只以xxx为例！创建OGG目录-节点rootoggsrc media# mkdir -p /u01/ogg/dirdatrootoggsrc media# chown -R oracle:oinstall /u01/oggrootoggsrc media# chmod -R 775 /u01/ogg rootoggsrc media# su - oracle3.1.2配置OGG用户环境变量-此处用oracle用户安装OGG添加或修改oracle用户环境变量export OGG_HOME=/u01/oggexport PATH=.:$PATH:$HOME/bin:$ORACLE_HOME/bin

14、:$ORA_CRS_HOME/bin:$OGG_HOMEexport LIBPATH=$ORACLE_HOME/lib:$OGG_HOME3.1.3安装OGG软件oracleoggsrc $ cd /u01/media/ oracleoggsrc media$ ls -lrttotal 4616920drwxr-xr-x 7 oracle oinstall 4096 Aug 26 2013 database-rw-r-r-. 1 oracle oinstall 1151304589 Jul 18 20:43 p13390677_112040_Linux-x86-64_2of7.zip-rw-r

15、-r-. 1 oracle oinstall 1395582860 Jul 18 20:44 p13390677_112040_Linux-x86-64_1of7.zip-rw-r-r- 1 oracle oinstall-rw-r-r- 1 oracle oinstall 475611228 Jul 19 00:00 fbo_ggs_Linux_x64_shiphome.zip-rw-r-r- 1 oracle oinstall 167741674 Jul 19 00:04 jdk-8u112-linux-x64.rpm-rw-r-r- 1 oracle oinstall-rw- 1 ora

16、cle oinstall 4194304 Jul 19 06:10 coreoracleoggsrc media$ unzip -q fbo_ggs_Linux_x64_shiphome.ziporacleoggsrc media$ cd fbo_ggs_Linux_x64_shiphome$cd Disk1$lsinstall response runInstaller stageOGGSRC:/u01/media/fbo_ggs_AIX_ppc_shiphome/Disk1$./runInstaller3.2源库OGG配置-配置OGG全部用oracle用户去操作！3.2.1创建OGG应用子

17、目录OGGSRC:/home/oracle$cd /u01/oggOGGSRC:/u01/ogg$ggsciOracle GoldenGate Command Interpreter for OracleAIX 6, ppc, 64bit (optimized), Oracle 11g on Jan 21 2016 09:52:07Operating system character set identified as ISO-8859-1.Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.G

18、GSCI (OGGSRC) 1> create subdirsCreating subdirectories under current directory /u01/oggParameter files /u01/ogg/dirprm: already existsReport files /u01/ogg/dirrpt: createdCheckpoint files /u01/ogg/dirchk: createdProcess status files /u01/ogg/dirpcs: createdSQL script files /u01/ogg/dirsql: create

19、dDatabase definitions files /u01/ogg/dirdef: createdExtract data files /u01/ogg/dirdat: createdTemporary files /u01/ogg/dirtmp: createdStdout files /u01/ogg/dirout: createdGGSCI (OGGSRC) 2> exit3.2.2创建OGG用户OGGSRC:/u01/ogg$sqlplus / as sysdbaSQL> create tablespace ogg datafile '/oradata/ogg

20、src/ogg01.dbf' size 2G ;Tablespace created.SQL> create user ogg identified by ogg default tablespace ogg temporary tablespace temp account unlock;User created.SQL> grant dba to ogg;Grant succeeded.3.2.3授权OGG用户OGGSRC:/u01/ogg$sqlplus / as sysdbaSQL> grant execute on utl_file to ogg;Grant

21、 succeeded.=SQL> /u01/ogg/marker_setup.sqlEnter Oracle GoldenGate schema name:oggScript complete.=SQL> /u01/ogg/ddl_setup.sqlEnter Oracle GoldenGate schema name:oggSTATUS OF DDL REPLICATION-SUCCESSFUL installation of DDL Replication software componentsScript complete.=SQL> /u01/ogg/role_set

22、up.sqlEnter GoldenGate schema name:oggRole setup script completeGRANT GGS_GGSUSER_ROLE TO <loggedUser>=SQL> grant ggs_ggsuser_role to oggtgt;Grant succeeded.=SQL> /u01/ogg/ddl_enable.sqlTrigger altered.=SQL> /u01/ogg/marker_status.sqlPlease enter the name of a schema for the GoldenGat

23、e database objects:oggSetting schema name to OGGMARKER TABLE-OKMARKER SEQUENCE-OK=SQL> ?/rdbms/admin/dbmspoolPackage created.Grant succeeded.=SQL> /u01/ogg/ddl_pin.sql oggPL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.=SQ

24、L> /u01/ogg/sequence.sqlPlease enter the name of a schema for the GoldenGate database objects:Setting schema name to OGGSTATUS OF SEQUENCE SUPPORT-SUCCESSFUL installation of Oracle Sequence Replication support=SQL> grant execute on oggtgt.updatesequence to oggtgt;Grant succeeded.3.2.4创建GLOBALS

25、文件OGGSRC:/u01/ogg/dirdat$cd $OGG_HOMEOGGSRC:/u01/ogg$ggsciOracle GoldenGate Command Interpreter for OracleAIX 5L, ppc, 64bit (optimized), Oracle 11g on Apr 23 2012 05:03:51Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.GGSCI (OGGSRC) 20> edit params ./GLOBALS#输入如下内容#G

26、GSCHEMA ogg3.2.5创建Manager配置文件#编辑MGR配置文件，GGSCI (OGGSRC) 1> edit params mgr #输入如下内容#PORT 7809DYNAMICPORTLIST 7810-7820, 7830AUTOSTART EXTRACT *AUTORESTART EXTRACT *, RETRIES 4, WAITMINUTES 2STARTUPVALIDATIONDELAY 5PURGEOLDEXTRACTS /u01/ogg/dirdat/*, USECHECKPOINTS, minkeepdays 7创建primary Extract配置文

27、件GGSCI (OGGSRC) 9> edit params exee#输入如下内容#EXTRACT exeeSETENV (ORACLE_SID = "oggsrc")SETENV (ORACLE_HOME= )SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")USERID ogg, PASSWORD oggWARNLONGTRANS 2h, CHECKINTERVAL 300sFETCHOPTIONS NOUSESNAPSHOTexttrail /u01/ogg/dirdat/ltdiscardfil

28、e /u01/ogg/dirrpt/ee.dsc,append, megabytes 500gettruncatesddl &include objname MESIF.* &include objname AHBIUSR.* &include objtype 'USER'ddloptions addtrandatatable MESIF.*;sequence MESIF.*;table AHBIUSR.*;sequence AHBIUSR.*;#检查确认EXEE配置文件内容无误OGGSRC:/u01/ogg$/u01/ogg/checkprm /u01

29、/ogg/dirprm/exee.prm -C extract -m Classic -V3.2.7创建data pump配置文件GGSCI (OGGSRC) 9> edit params dpee#输入如下内容#EXTRACT dpeeSETENV (ORACLE_SID = "oggsrc")SETENV (ORACLE_HOME= )SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK"), MGRPORT 7809, compressRMTTRAIL /u01/ogg/dirdat/rtgettrunc

30、atestable MESIF.*;sequence MESIF.*;table AHBIUSR.*;sequence AHBIUSR.*;#检查确认DPEE配置文件内容无误OGGSRC:/u01/ogg/checkprm /u01/ogg/dirprm/dpee.prm -C extract -m Classic -V3.2.8为需要抽取数据的schema增加trandata#此步根据对象表数量的多少需要相应的时间，GGSCI (OGGSRC) 13> dblogin userid ogg password oggSuccessfully logged into database.GG

31、SCI (OGGSRC) 14>add trandata MESIF.*GGSCI (OGGSRC) 14>add trandata AHBIUSR.*3.2.9配置extract进程GGSCI (OGGSRC) 15> add ext exee,tranlog,begin nowEXTRACT added.GGSCI (OGGSRC) 16> add exttrail /u01/ogg/dirdat/lt, ext exee, megabytes 50EXTTRAIL added.3.2.10配置data pump进程GGSCI (OGGSRC) 17> add

32、 ext dpee, exttrailsource /u01/ogg/dirdat/ltEXTRACT added.GGSCI (OGGSRC) 19> add rmttrail /u01/ogg/dirdat/rt, ext dpee, megabytes 50RMTTRAIL added.3.3目标库OGG配置-配置OGG全部用oracle用户去操作！3.3.1创建OGG应用子目录oggtgt:/home/oracle$cd /u01/oggoggtgt:/u01/ogg$ggsciOracle GoldenGate Command Interpreter for OracleAIX

33、 6, ppc, 64bit (optimized), Oracle 11g on Jan 21 2016 09:52:07Operating system character set identified as ISO-8859-1.Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.GGSCI (oggtgt) 1> create subdirsCreating subdirectories under current directory /u01/oggParameter files

34、 /u01/ogg/dirprm: already existsReport files /u01/ogg/dirrpt: createdCheckpoint files /u01/ogg/dirchk: createdProcess status files /u01/ogg/dirpcs: createdSQL script files /u01/ogg/dirsql: createdDatabase definitions files /u01/ogg/dirdef: createdExtract data files /u01/ogg/dirdat: createdTemporary

35、files /u01/ogg/dirtmp: createdStdout files /u01/ogg/dirout: createdGGSCI (oggtgt) 2>3.3.2创建OGG用户oggtgt:/home/oracle$sqlplus / as sysdbaSQL> create tablespace oggtgt datafile '/oradata/oggtgt/ogg01.dbf' size 2G ;Tablespace created.SQL> create user ogg identified by ogg default tables

36、pace ogg temporary tablespace temp account unlock;User created.SQL> grant dba to ogg;Grant succeeded.3.3.3授权OGG用户oggtgt:/u01/ogg$sqlplus / as sysdba=SQL> /u01/ogg/sequence.sqlPlease enter the name of a schema for the GoldenGate database objects:Setting schema name to OGGSTATUS OF SEQUENCE SUPP

37、ORT-SUCCESSFUL installation of Oracle Sequence Replication support=SQL> grant execute on ogg.replicatesequence to ogg;Grant succeeded.3.3.4创建GLOBALS配置文件oggtgt:/u01/ogg/dirdat$cd $OGG_HOMEoggtgt:/u01/ogg$ggsciGGSCI (oggtgt) 2> edit params ./GLOBALS#输入如下内容#GGSCHEMA oggCHECKPOINTTABLE ogg.ckpttab

38、le3.3.5创建Manager配置文件#编辑MGR配置文件GGSCI (oggtgt) 3> edit params mgr #输入如下内容#PORT 7809DYNAMICPORTLIST 7810-7820, 7830AUTOSTART REPLICAT *AUTORESTART REPLICAT *, RETRIES 4, WAITMINUTES 2STARTUPVALIDATIONDELAY 5PURGEOLDEXTRACTS /u01/ogg/dirdat/*, USECHECKPOINTS, minkeepdays 7添加checkpointtableGGSCI (oggt

39、gt) 5> DBLOGIN USERID ogg, PASSWORD oggSuccessfully logged into database.GGSCI (oggtgt) 6> ADD CHECKPOINTTABLE ogg.ckpttableSuccessfully created checkpoint table ogg.ckpttable.创建replicat配置文件GGSCI (oggtgt) 8> edit params rpee#输入如下内容#REPLICAT rpeeSETENV (ORACLE_SID = "oggtgt")SETENV

40、 (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")USERID ogg, PASSWORD ogg-handlecollisionsassumetargetdefsallownoopupdatesdiscardfile /u01/ogg/dirrpt/ee.dsc,append, megabytes 500gettruncatesddl &include objname MESIF.* &include objnam

41、e AHBIUSR.* &include objtype 'USER'DBOPTIONS DEFERREFCONSTmap MESIF.*, target MESIF.*;map AHBIUSR.*, target AHBIUSR.*;#确认RPEE配置文件内容oggtgt:/u01/ogg$/u01/ogg/checkprm /u01/ogg/dirprm/rpee.prm -C replicat -m Classic -V配置replicat进程GGSCI (oggtgt) 9> add rep rpee,exttrail /u01/ogg/dirdat/rt

42、 CHECKPOINTTABLE ogg.ckpttableREPLICAT added.3.4测试启动OGG-配置OGG全部用oracle用户去操作！3.4.1目标库启动OGGGGSCI (oggtgt) 9> start mgrManager started.GGSCI (oggtgt) 11> start replicat rpeeREPLICAT RPEE is already running.GGSCI (oggtgt) 12> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUN

43、NING REPLICAT RUNNING RPEE 00:00:00 00:00:06 -等待章节3.4.2的DPEE进程启动正常后，关闭REPLICAT rpeeGGSCI (oggtgt) 13> stop replicat rpee3.4.2源库启动OGGGGSCI (OGGSRC) 17> start mgrManager started.GGSCI (OGGSRC) 18> info mgrManager is running (IP port OGGSRC.7809).GGSCI (OGGSRC) 68> start EXTRACT exeeSending

44、 START request to MANAGER .EXTRACT EXEE startingGGSCI (OGGSRC) 69> start EXTRACT dpeeSending START request to MANAGER .EXTRACT DPEE startingGGSCI (OGGSRC) 1> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING DPEE 00:00:00 00:00:03 EXTRACT RUNNING EXEE 00

45、:00:00 00:00:10 此处不要关闭EXEE进程！ 四、 迁移数据4.1数据泵方式4.1.1源库创建数据泵目录OGGSRC:/home/oracle$export ORACLE_SID=oggsrcOGGSRC:/home/oracle$sqlplus / as sysdbaSQL> create or replace directory expdp as '/u01/expdp'Directory created.SQL> grant read,write on directory expdp to public;Grant succeeded.SQL&g

46、t; col owner for a15SQL> col directory_name for a15SQL> col directory_path for a25SQL> select * from dba_directories where directory_name='EXPDP'OWNER DIRECTORY_NAME DIRECTORY_PATH- - -SYS EXPDP /u01/expdp4.1.2源库获取数据库当前SCNSQL> select dbms_flashback.get_system_change_number from d

47、ual;GET_SYSTEM_CHANGE_NUMBER- 3867034.1.3源库基于SCN号导出数据nohup expdp MESPRD/MESPRD directory=expdir schemas=AHBIUSR,MESERP,MESBIGDATA,MESDMZTMP,MONITORUSER,MESPTS,MESWMS,MESIF dumpfile=mes%U.dmp job_name=expdpmes parallel=4 filesize=7G flashback_scn=386703 COMPRESSION=all logfile=expdpmes.log &4.1.4

48、目标库创建数据泵目录oggtgt:/home/oracle$sqlplus / as sysdbaSQL> create or replace directory impdp as '/u01/impdir'Directory created.SQL> grant read,write on directory impdp to public;Grant succeeded.SQL> select * from dba_directories where directory_name='IMPDP'SQL> col owner for a15SQL> col directory_name for a15SQL> col directory_path for a25SQL> select * from dba_directories where directory_name='EXPDP'OWNER DIRECTORY_NAME DIRECTORY_PAT