ogg单机同步复制操作手册(共30页).docx

《ogg单机同步复制操作手册(共30页).docx》由会员分享，可在线阅读，更多相关《ogg单机同步复制操作手册(共30页).docx（30页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、精选优质文档-倾情为你奉上一、 概要信息1.1文档简介该文档主要用于指导利用OGG来进行数据的同步复制，其中源数据库为单机，目标数据库为单机。OGG相关安装软件版本为12c，主要包括如下：1. OGG，主要用于源库与目标库的数据同步2. OGG Veridata，主要用于同步数据校验温馨提醒：1. 请仔细阅读该文档注意事项！1.2机器环境1.2.1源机器信息操作系统版本Redhat 6.4数据库版本11.2.0.4.0主机名oggsrcIP地址192.168.1.1861.2.2目标机器信息操作系统版本Redhat 6.4集群数据库版本11.2.0.4.0主机名oggtgtIP地址192.16

2、8.1.1871.3软件环境软件名称软件版本部署节点备注Oracle GoldenGate12.2.0.1.1oggsrc（源库） oggtgt（目标库）Oracle ADR12.2.1.0.0oggsrc（源库） oggtgt（目标库）JDK1.8oggsrc（源库） oggtgt（目标库）1.4软件包信息软件名称软件包名称Oracle GoldenGate 12fbo_ggs_Linux_x64_shiphome.zipOracle Application Development Runtime Infrastructure（ADR）fmw_12.2.1.0.0_infrastructur

3、e_Disk1_1of1.zipJDKjdk-8u112-linux-x64.rpm1.5注意事项-软件安装：对于RAC的OGG，有三种方式选择:1.oracle推荐直接安装在共享文件系统上（共享存储/OCFS/AFS）；2.也可以安装在RAC所有节点的本地目录，但必需保证所有节点的安装路径一致；3.还可以只安装其中一个节点，但必需保证安装OGG的节点可以访问其它节点的归档目录(本文档使用)-关于归档本文档中，源库归档目录为本地目录-关于IP配置：RAC上的VIP必需为固定IP且与管理IP是同一网段，不可以是DHCP获取-关于主机hosts表安装过程中涉及到的主机名及IP对应关系都配置到每台主

4、机的hosts表中-关于时间同步：所有节点必需配有时间同步功能保证所有节点的操作系统时间一致，因为OGG的EXTRACT进程是通过对比commit时间（即操作系统时间）来决定是否传输相关的数据-关于COMPATIBLE：确保所有节点的数据库COMPATIBLE参数已经设置-OGG数据存放路径将OGG数据存放路径mount在单边，防止相同的进程在其它节点启动引起进程冲突-源库数据对象是否支持OGG对数据对象支持有一定的限制，可以通过脚本提前检查-关于数据迁移方式本文档推荐使用数据泵方式进行数据迁移，因此需要提前将目标数据库建好二、 OGG搭建环境准备2.1源库操作2.1.1开启归档-归档目录为所

5、有节点共享目录-节点SQL alter system set log_archive_dest_1=location=/arch scope=spfile sid=*; System altered.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

6、OGGSRC:/home/oracle$export ORACLE_SID=oggsrcOGGSRC:/home/oracle$sqlplus / as sysdba SQL*Plus: 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 Glo

7、bal Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers 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 se

8、quence to archive 7Current log 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;Da

9、tabase altered.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 set

10、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 bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes Database mounted. SQL alter d

11、atabase 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 7 SQL alter database open;Database altered.SQL alter system archive log current;Sys

12、tem altered.2.2.2开启参数ENABLE_GOLDENGATE_REPLICATION/11.2.0.4版本的必须设置该参数为trueSQL ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;System altered.三、 OGG搭建3.1 OGG软件安装-此处只在源库节点xxxx和目标库节点xxxx上操作，其它节点不操作！此处只以xxx为例！3.1.1创建OGG目录-节点rootoggsrc media# mkdir -p /u01/ogg/dirdatrootoggsrc media# cho

13、wn -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:$ORA_CRS_HOME/bin:$OGG_HOMEexport LIBPATH=$ORACLE_HOME/lib:$OGG_HOME3.1.3安装OGG软件

14、oracleoggsrc $ cd /u01/media/ oracleoggsrc media$ ls -lrttotal drwxr-xr-x 7 oracle oinstall 4096 Aug 26 2013 database-rw-r-r-. 1 oracle oinstall Jul 18 20:43 p_Linux-x86-64_2of7.zip-rw-r-r-. 1 oracle oinstall Jul 18 20:44 p_Linux-x86-64_1of7.zip-rw-r-r- 1 oracle oinstall Jul 18 23:55 fmw_12.2.1.0.0_

15、infrastructure_Disk1_1of1.zip-rw-r-r- 1 oracle oinstall Jul 19 00:00 fbo_ggs_Linux_x64_shiphome.zip-rw-r-r- 1 oracle oinstall Jul 19 00:04 jdk-8u112-linux-x64.rpm-rw-r-r- 1 oracle oinstall Jul 19 05:55 pdksh-5.2.14-37.el5_8.1.x86_64.rpm-rw- 1 oracle oinstall Jul 19 06:10 coreoracleoggsrc media$ unzi

16、p -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应用子目录OGGSRC:/home/oracle$cd /u01/oggOGGSRC:/u01/ogg$ggsciOracle G

17、oldenGate Command Interpreter for OracleVersion 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_.1401_FBOAIX 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

18、(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: createdDatabase

19、 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/oggsrc/ogg01.dbf size 2G

20、;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 succeeded.=SQL /u01/ogg/marker_setup.s

21、qlEnter 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_setup.sqlEnter GoldenGate schema name:oggRole setup sc

22、ript completeGRANT GGS_GGSUSER_ROLE TO =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 GoldenGate database objects:oggSetting schema name to OGGMARKER TABLE-OKMARKER SEQUENCE-OK

23、=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.=SQL /u01/ogg/sequence.sqlPlease enter the name of a schema for the GoldenGate database obje

24、cts: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文件OGGSRC:/u01/ogg/dirdat$cd $OGG_HOMEOGGSRC:/u01/ogg$ggsciOracle GoldenGate Command Interpreter f

25、or OracleVersion 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_.0230AIX 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#输入如下内容#GGSCHEMA ogg3.2.5创建Manager配置文件#编辑MGR配置文件，GGSCI (O

26、GGSRC) 1 edit params mgr #输入如下内容#PORT 7809DYNAMICPORTLIST 7810-7820, 7830AUTOSTART EXTRACT *AUTORESTART EXTRACT *, RETRIES 4, WAITMINUTES 2STARTUPVALIDATIONDELAY 5PURGEOLDEXTRACTS /u01/ogg/dirdat/*, USECHECKPOINTS, minkeepdays 73.2.6创建primary Extract配置文件GGSCI (OGGSRC) 9 edit params exee#输入如下内容#EXTRA

27、CT exeeSETENV (ORACLE_SID = oggsrc)SETENV (ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1)SETENV (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)USERID ogg, PASSWORD oggWARNLONGTRANS 2h, CHECKINTERVAL 300sFETCHOPTIONS NOUSESNAPSHOTexttrail /u01/ogg/dirdat/ltdiscardfile /u01/ogg/dirrpt/ee.dsc,append, megabyt

28、es 500gettruncatesddl &include objname MESIF.* &include objname AHBIUSR.* &include objtype USERddloptions addtrandatatable MESIF.*;sequence MESIF.*;table AHBIUSR.*;sequence AHBIUSR.*;#检查确认EXEE配置文件内容无误OGGSRC:/u01/ogg$/u01/ogg/checkprm /u01/ogg/dirprm/exee.prm -C extract -m Classic -V3.2.7创建data pump配

29、置文件GGSCI (OGGSRC) 9 edit params dpee#输入如下内容#EXTRACT dpeeSETENV (ORACLE_SID = oggsrc)SETENV (ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1)SETENV (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)RMTHOST 192.168.1.187, MGRPORT 7809, compressRMTTRAIL /u01/ogg/dirdat/rtgettruncatestable MESIF.*;sequence MESIF.*

30、;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.GGSCI (OGGSRC) 14add trandata MESIF.*GGS

31、CI (OGGSRC) 14add 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 ext dpee, exttrailsource /u01/ogg/dirdat/ltEXTRACT added.

32、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 OracleVersion 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_.1401_FBOAIX 6, p

33、pc, 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 /u01/ogg

34、/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 files /u0

35、1/ogg/dirtmp: createdStdout files /u01/ogg/dirout: createdGGSCI (oggtgt) 23.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 tablespace ogg temporary tablespace t

36、emp 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 SUPPORT-SUCCESSFUL installation of Oracle S

37、equence 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.ckpttable3.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 73.3.6添加checkpointtableGGSCI (oggt