oracle goldengate安装与配置.doc

oracle goldengate安装与配置1 goldengate安装1.1安装GoldenGate环境源数据库平台：64位AIX5300-08 Oracle.0 64位版本目标数据库平台：64位AIX5300-08 Oracle.0 64位版本源数据库：服务器名称：源数据库，ip：5目标数据库：服务器名称：目标数据库， ip：7源数据库：归档模式，ORACLE_SID:efss2 用户：GG_45 goldengate专属用户：GG_45目标数据库：归档模式，ORACLE_SID:efss2 用户：GG_47 goldengate专属用户：GG_47源数据库：监听端口：1521，gg manager管理进程端口：7809目标数据库：监听端口：1521，gg manager管理进程端口：78091.2设置环境变量在源端操作系统上，设置该用户环境变量（ORACLE_SID,ORACLE_HOME等），用oracle用户安装运行GoldenGate，无需建立新用户。在/home2/oracle/.profile中设置LIBPATHLIBPATH=$ORACLE_HOME/lib; export LIBPATH1.3在源库上打开日志归档及辅助日志打开数据库归档sqlplus / as sysdbashutdown immediate; -或shutdown normalstartup mount;alter database archivelog; -关闭数据归档alter database noarchivelog; alter database open;检查数据库有没有归档sqlplus / as sysdbaSQL archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /oraarchOldest online log sequence 2341Next log sequence to archive 2343Current log sequence 2343打开源端数据库的辅助日志sqlplus / as sysdbaalter database add supplemental log data;alter system switch logfile;检查数据库的辅助日志有没有打开sqlplus / as sysdbaselect SUPPLEMENTAL_LOG_DATA_MIN from v$database;为YES，说明已打开，为NO，说明还没有打开1.4在源端数据库上安装goldengate包。将ggs_aix53_ppc_ora102_64bit_v10_4_0_34_001.tar.gz上传到源端主机。su oraclecd $HOMEmkdir ggsgzip d ggs_aix53_ppc_ora102_64bit_v10_4_0_34_001.tar.gzcd ggstar xvf ./ ggs_aix53_ppc_ora102_64bit_v10_4_0_34_001.tar进入gg命令行建立所需目录$ ggsciOracle GoldenGate Command Interpreter for OracleVersion 4 Build 001AIX 5L, ppc, 64bit (optimized), Oracle 10 on Jan 15 2010 07:33:59Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.GGSCI (52A) 1 create subdirsCreating subdirectories under current directory /home2/oracle/ggsParameter files /home2/oracle/ggs/dirprm: createdReport files /home2/oracle/ggs/dirrpt: createdCheckpoint files /home2/oracle/ggs/dirchk: createdProcess status files /home2/oracle/ggs/dirpcs: createdSQL script files /home2/oracle/ggs/dirsql: createdDatabase definitions files /home2/oracle/ggs/dirdef: createdExtract data files /home2/oracle/ggs/dirdat: createdTemporary files /home2/oracle/ggs/dirtmp: createdVeridata files /home2/oracle/ggs/dirver: createdVeridata Lock files /home2/oracle/ggs/dirver/lock: createdVeridata Out-Of-Sync files /home2/oracle/ggs/dirver/oos: createdVeridata Out-Of-Sync XML files /home2/oracle/ggs/dirver/oosxml: createdVeridata Parameter files /home2/oracle/ggs/dirver/params: createdVeridata Report files /home2/oracle/ggs/dirver/report: createdVeridata Status files /home2/oracle/ggs/dirver/status: createdVeridata Trace files /home2/oracle/ggs/dirver/trace: createdStdout files /home2/oracle/ggs/dirout: created2 goldengate配置及验证2.1把源端数据库的数据同步到目标数据库1、在源数据库上建数据库用户GG_45sqlplus / as sysdbacreate user GG_45 identified by GG_45default tablespace OGGtemporary tablespace TEMPquota unlimited on OGG;grant connect,resource,dba to GG_45;2、在源数据库上建数据库用户GG_45的表TCUSTMER、TCUSTORD，并插入记录conn GG_45/GG_45demo_ora_create.sqldemo_ora_insert.sql3、在目标数据库上建数据库用户GG_47sqlplus / as sysdbacreate user GG_47 identified by GG_47default tablespace OGGtemporary tablespace TEMPquota unlimited on OGG;grant connect,resource,dba to GG_47;4、在目标数据库上建数据库用户GG_47的表TCUSTMER、TCUSTORD，不需要插入记录conn GG_47/GG_47demo_ora_create.sql5、在源数据上配置管理进程MGREDIT PARAMS MGRPORT 7809START MGR在目标数据库上配置管理进程MGREDIT PARAMS MGRPORT 7809START MGR6、在源数据库上增加需要传输的用户表DBLOGIN USERID GG_45, PASSWORD GG_45ADD TRANDATA GG_45.TCUSTMERADD TRANDATA GG_45.TCUSTORDINFO TRANDATA GG_45.*7、在源数据库上配置extract进程 EINI01ADD EXTRACT EINI01, SOURCEISTABLEINFO EXTRACT *, TASKSEDIT PARAMS EINI01EXTRACT EINI01USERID GG_45, PASSWORD GG_45RMTHOST 7, MGRPORT 7809RMTTASK REPLICAT, GROUP RINI01TABLE GG_45.TCUSTMER;TABLE GG_45.TCUSTORD;8、在目标数据库上配置replicat进程 RINI01ADD REPLICAT RINI01, SPECIALRUNINFO REPLICAT *, TASKSEDIT PARAMS RINI01REPLICAT RINI01ASSUMETARGETDEFSUSERID GG_47, PASSWORD GG_47DISCARDFILE ./dirrpt/RINI01.dsc, PURGEMAP GG_45.TCUSTMER, TARGET GG_47.TCUSTMER;MAP GG_45.TCUSTORD, TARGET GG_47.TCUSTORD;9、在源数据库上执行初始化过程START EXTRACT EINI01VIEW REPORT EINI01(在源数据上查看是否有报错)VIEW REPORT RINI01(在目标数据上查看是否有报错)10、在目标数据库上检查数据是否同步了conn GG_47/GG_47SELECT * FROM TCUSTMER;SELECT * FROM TCUSTORD;2.2源数据库的修改实时同步到目标数据库1、在源数据上配置extract捕获进程 EXT01-捕获进程EXT01配置参数文件EDIT PARAMS EXT01EXTRACT EXT01USERID GG_45, password GG_45EXTTRAIL ./dirdat/et-DDLTABLE GG_45.TCUSTMER;TABLE GG_45.TCUSTORD;-添加捕获进程EXT01ADD EXTRACT EXT01, TRANLOG, BEGIN NOW (direct load方式：ADD EXTRACT EXT01, SOURCEISTABLE)-添加队列文件，并将该队列文件指定给相应的捕获进程ADD EXTTRAIL ./dirdat/et, EXTRACT EXT01, MEGABYTES 5INFO EXTRACT EXT01, DETAIL2、在源数据上配置pump传输进程 PUMP01-传输进程PUMP01配置参数文件EDIT PARAMS PUMP01EXTRACT PUMP01PASSTHRURMTHOST 7, MGRPORT 7809RMTTRAIL ./dirdat/etTABLE GG_45.TCUSTMER;TABLE GG_45.TCUSTORD;-添加传输进程PUMP01，并指明它的源数据是刚才建立的队列文件./dirdat/etADD EXTRACT PUMP01, EXTTRAILSOURCE ./dirdat/et-创建远程队列文件，并将其指定给传输进程PUMP01ADD RMTTRAIL ./dirdat/et, EXTRACT PUMP01, MEGABYTES 53、关闭源数据库的recyclebinSQL show parameter recyclebinSQL alter system set recyclebin=off;由于要进行双向复制，因此需要在两端做额外的配置来保证不发生死循环复制等意外。在ggsci下用DBLOGIN登入数据库，添加一个默认的Trace table。DBLOGIN USERID GG_45, PASSWORD GG_45ADD TRACETABLE4、在源数据库上启动data pump capture process 注意：在启动传输进程前，必须确保目标端的manager进程已经启动，否则会启动失败START EXTRACT EXT01START EXTRACT PUMP01INFO ALL5、在目标数据库的全局文件中添加checkpoint表，用来实现一致性的EDIT PARAMS ./GLOBALSCHECKPOINTTABLE GG_47.GGS_CHECKPOINT注意：要退出GGSCI命令行，再进入GGSCI命令行，使得全局文件生效。6、目标数据库配置replicat进程 REP01-配置复制进程REP01参数文件EDIT PARAMS REP01REPLICAT REP01USERID GG_47, PASSWORD GG_47HANDLECOLLISIONSASSUMETARGETDEFSDISCARDFILE ./dirrpt/REP01.dsc, PURGEMAP GG_45.TCUSTMER, TARGET GG_47.TCUSTMER;MAP GG_45.TCUSTORD, TARGET GG_47.TCUSTORD;-增加复制进程REP01，并指定存放的队列文件,CHECKPOINTTABLE实现一致性。DBLOGIN USERID GG_47, PASSWORD GG_47ADD CHECKPOINTTABLEADD REPLICAT REP01, EXTTRAIL ./dirdat/et-启动目标数据库的复制进程START REPLICAT REP01INFO ALLVIEW REPORT REP017、检查同步结果1在源数据库插入数据conn GG_45/GG_45demo_ora_misc.sql如果出错，可以查看源数据库EXT01、PUMP01，或者目标数据库上的REP01进程的错误。如：VIEW REPORT EXT01VIEW REPORT PUMP01VIEW REPORT REP01如果修改进程的配置文件，需要关闭进程，然后重新启动才有效。2.3把源数据库的DDL修改同步到目标数据库中1、在源数据库端关闭数据库的回收站show parameter recyclealter system set recyclebin=off;2、编辑源数据库参数文件GLOBALSEDIT PARAMS ./GLOBALSGGSCHEMA GG_45 -GG_45支持DDL同步的用户注意：要退出GGSCI命令行，再进入GGSCI命令行，使得全局文件生效。3、在源数据库上创建GG_45所需要的表sqlplus / as sysdbaGRANT EXECUTE ON UTL_FILE TO GG_45;marker_setup 选择