GoldenGate_Director配置手册

Oracle_GoldenGate软件安装、配置、运维管理整理版目录Oracle_GoldenGate安装、配置、管理整理版 (1)a) Oracle GoldenGate主要组件 (5)GoldenGate for Oracle (8)b) 把源端数据库的数据同步到目标数据库 (13)c) 源数据库的修改实时同步到目标数据库 (34)d) 把源数据库的DDL修改同步到目标数据库中 (42)e) 术语和缩略语 (61)基于命令行的监控 (63)f) 说明 (63)g) 启动GoldenGate进程 (63)h) 停止GoldenGate进程 (64)i) 查看整体运行情况 (66)j) 查看参数设置 (67)k) 查看进程状态 (67)l) 查看延时 (70)m) 查看统计信息 (70)n) 查看运行报告 (71)基于GoldenGate Director的监控 (73)o) GoldenGate Director概述 (73)p) GoldenGate Director的安装部署 (75)部署方案及必需条件 (75)安装GoldenGate Director Server (77)安装GoldenGate Director Client (82)q) 启动GoldenGate Server (83)r) 配置被监控实例 (83)s) 登录Director Web监控界面 (86)t) 监控整体运行情况 (88)u) 监控进程状态 (89)v) 手工配置重点监控列表 (89)w) 查看事件日志 (92)x) Email告警 (93)y) 运行GGSCI命令 (94)日常维护指南 (96)z) 日常维护注意事项 (96)aa) 配置自动删除队列 (96)bb) 配置启动MGR时自动启动Extract和Replicat进程 (97)cc) 配置MGR自动重新启动Extract和Replicat进程 (97)dd) 长事务管理 (98)a) 源端和目标端数据库增减复制表 (101)b) 修改表结构 (104)c) 表的重新再同步 (105)异常处理预案 (106)d) 网络故障 (106)e) RAC环境下单节点失败 (106)f) Extract进程常见异常 (107)g) Replicat进程常见异常 (108)h) 异常处理一般步骤 (108)参考文献 (110)GoldenGate工作原理a) Oracle GoldenGate主要组件● Extract● Data pump● Replicat● Trails or extract files● Checkpoints● Manager● Collector1、Data Pump是一个次级的Extract Group,如果你的GG环境中不使用Data Pump的话，那么Primary Extract Group必须直接将trail通过网络写到Target系统上.参见后面的示例6:配置Data Pump实现一对多拓扑方案.2、默认情况下,Replicat是实时复制的,如果因为某种需要需要延迟复制的话,那么可以通过Replicat Group的如下DEFERAPPLYINTERVAL参数来控制，该参数允许delay的最大时间是7天。

部署GoldenGate PUMP方式支持DDL单向复制DDL复制概述：①Goldengate开启DDL复制后，不需要对原有的表和以后新加的表执行add trandata操作这一点根据经验得出。

②目标端默认支持DDL复制，故目标端Replicat进程参数文件中不需要添加DDL 语句，当然源端Data Pump进程参数文件中也不需要加DDL相关的参数③开启DDL复制后，目标端Replicat进程参数文件中必须配置忽略或者过滤DDLERROR的参数④Goldengate数据库(dblogin userid xxx)用户和Oracle数据库自带的用户的DDL 操作将不会被复制⑤当开启Full DDL支持时，不要在参数文件中使用GETTRUNCATES参数⑥默认情况下ALTER DATABASE、ALTER SYSTEM不会被复制⑦注意：在target DB上，ggsusr必须授予DBA的角色给他，否则dblogin登录不了数据库注：红色字体是不需要操作的步骤下图是这次配置架构图：一、软件安装(这两步源端和目标端步骤一样)安装平台：redhat linux x86 32bitgoldengate：Oracle_GoldenGate_V11.1.1.1.0_for_Oracle_11g_on_Linux_x86 oracle版本：11.2.0.1.0（建议使用oracle用户安装goldengate）解压软件cd /u01/app上传V26188-01.zip到/u01/app目录$unzip V26188-01.zip$mkdir oggchown -R oracle:oinstall fbo_ggs_Linux_x86_ora11g_32bit.tarchmod 755 fbo_ggs_Linux_x86_ora11g_32bit.tarmv fbo_ggs_Linux_x86_ora11g_32bit.tar ./oggcd ogg[oracle@hxzg1 ogg]$ tar –xvf fbo_ggs_Linux_x86_ora11g_32bit.tar设置环境变量export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/u01/app/oggexport PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:/u01/app/ogg[oracle@hxzg1 ~]$ source .bash_profile二、安装goldengate$cd /u01/app/ogg$./ggsci> CREATE SUBDIRS//创建goldengate安装目录> exit至此，goldengate已安装完成。

Oracle GoldenGate Performance Tuning for Oracle Database IntegrationCON7773Patricia McElroyDistinguished Product ManagerEnterprise Replication DevelopmentOctober 1st, 2014Safe Harbor StatementThe following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.Program AgendaOracle GoldenGate Overview Diagnostic Tools Performance Recommendations Q&A1234Program AgendaOracle GoldenGate Overview Diagnostic Tools Performance Recommendations Q&A1234Trail: stages and queues data for routing.Pump: distributes data for routing to target(s).Route: data is compressed, encrypted for routing to target(s).Delivery: applies data with transaction integrity,transforming the data as required.Capture TrailPump DeliveryFilesLAN / WAN / InternetSource TargetTrail: stages and queues data for routing.Pump: distributes data for routing to target(s).Route: data is compressed, encrypted for routing to target(s).Delivery: applies data with transaction integrity,transforming the data as required.Capture Delivery TrailFilesPump DeliveryLAN / WAN / InternetSource TargetAlso Known As•Capture = Extract –Integrated Capture –Integrated Extract •Delivery = Replicat –Integrated Delivery –Integrated Replicat –Integrated ApplyLogmining Server ArchitectureLogmining Server (Database Capture Process) Reader : Reads logfile and splits into regions Reader Capture Builder Logmining ServerPreparer NPreparer 2Preparer 1 Oracle DatabaseIntegrated ReplicatInbound Server (Database Apply Process) Replicat Lightweight Streaming API Receiver Preparer Coordinator Inbound Server Applier NApplier 2Applier 1Oracle DatabaseParallel Apply ProcessingKey Features•Dependency scheduling based on Primary Key, Unique Index, and Foreign Key constraints at target database–Supplemental logging at source needed for these columns–Trail file must record these columns•Ordering of transaction commit in apply is configurable•COMMIT_SERIALIZATION–FULL : commit transactions in same order as committed at source database.–DEPENDENT_TRANSACTIONS : commit dependent transactions in correct dependency order (default)BATCHSQL•BATCHSQL supported via inbound server•Parallelism for BATCHSQL supported–Use BATCHTRANSOPS to tune batch size (default is 50)•On error in BATCHSQL,–Batched transaction rolled back–Apply in normal mode (non-batched) via inbound server•Larger batch sizes can cause more transaction dependency waits between apply serversIntegrated Replicat Performance•Integrated Replicat 24% faster than 6 manually configured parallel(and Foreign Keys disabled) Classic Replicats–129,000 vs. 96,000 DMLs/sec–OLTP style workload (modified Swingbench)•Integrated Replicat greatly simplifies configuration without manual partitioning–No need to understand workload (PK/UI/FK constraints)–Foreign Key constraints enabled–No split transaction semantics–Autotunes apply parallelism based on workloadProgram AgendaOracle GoldenGate Overview Diagnostic Tools Performance Recommendations Q&A1234Monitoring Latency AutomaticallyLatency Monitoring•Monitor latency with Manager parameters (mgr.prm): –LAGINFO{SECONDS | MINUTES | HOURS}–LAGREPORT{MINUTES | HOURS}–LAGCRITICAL{SECONDS | MINUTES | HOURS}–Monitor ggserr.log for latency information•Database queries of V$GOLDENGATE_CAPTURE orV$GG_APPLY_COORDINATORMonitoring GoldenGate LatencyLatency Monitoring•ggserr.log example:2014-01-22 18:09:00 WARNING OGG-00947 Oracle GoldenGate Manager for Oracle, mgr.prm: Lag for EXTRACT EXT_1A is 00:01:40 (checkpoint updated 00:00:04 ago)•GoldenGate Management Pack–Stand alone tool or 12c Oracle Enterprise Manager plug-inProcess Report File•List Active Parametersreplicat rep_1auserid soesmall password ********discardfile ./dirrpt/rep_1a.dsc, append, megabytes 20REPORTCOUNT EVERY 1 MINUTES, RATEASSUMETARGETDEFSMAP SOESMALL.* , TARGET SOESMALL.*•Identifies Table Resolution on first Change Record2014-09-19 18:57:15 INFO OGG-06506 Wildcard MAP resolved (entry DEMO.*): MAP "DEMO"."ORDERS" ,TARGET "DEMO"."ORDERS", COMPARECOLS ( ON UPDATE ALL, ON DELETE ALL).2014-09-19 18:57:15 INFO OGG-06511 Using following columns in default map by name: ORDER_NUMBER, PART_NUMBER, STATUS, DELIVERY_DATE.2014-09-19 18:57:15 INFO OGG-06510 Using the following key columns for target table DEMO.ORDERS: ORDER_NUMBER.Process Report Statistics•Monitor throughput per process–REPORTCOUNT EVERY {SECONDS | MINUTES | HOURS}, RATE –Aggressive monitoring can impact process throughput.Extract Report File SEND EXTRACT, LOGSTATSMonitoring Integrated CaptureStreams Performance Advisor (SPADV)•Refer to Oracle Database PL/SQL Packages and Types Reference forUTL_SPADV usage•Example real-time statistics:PATH 2 RUN_ID 15 RUN_TIME 2014-FEB-21 22:32:17 CCA N|<C> OGG$CAP_EXT_1A 305260 305245 790813 LMR 0% 53.3% 33.3% "CPU + Wait for CPU" LMP (2) 0% 0% 146.7% "CPU + Wait for CPU" LMB 73.3% 0% 6.7% "" CAP 20% 0% 73.3% "CPU + Wait for CPU" |<Q>"STREAMSADMIN"."OGG$Q_EXT_1A" 0.01 0.01 0 |<E> EXT_1A 304027 80076708 790813 20% 6.7% 73.3% "CPU + Wait for CPU" |<B> NO BOTTLENECK IDENTIFIED–LMR in flow control, and LMP is 0% idle with high CPU–Confirm with top/CPU stats and increase Capture ParallelismMonitoring Integrated ReplicatStreams Performance Advisor (SPADV)•Refer to Oracle Database PL/SQL Packages and Types Reference forUTL_SPADV usage•Example real-time statistics:PATH 1 RUN_ID 16 RUN_TIME 2014-JAN-12 10:33:59 CCA Y |<R> REP_1A1375 520424 0 0% 62.5% 37.5% |<Q> "SOESMALL"."OGGQ$REP_1A" 1375 0.01 5001|<A> OGG$REP_1A 715 715 -1 APR 31.3% 68.8% 0% "" APC 100% 0% 0% "" APS (7) 68.8% 0% 481.3% "free buffer waits" |<B> OGG$REP_1A APS 1219 22693 93.8% "free buffer waits“–Replicat (REP_1a) in flow control , as is APR so look downstream–APS high waits on free buffer waits–Confirm database contention with AWR reports and remove itPerformance Data Gathering•Automatic Workload Repository (AWR)–SQL used to apply replicated data, so important to gather and analyze –If using DBFS database, gather AWR for it•I/O performance data (i ostat)–Gather for disks database AND trail file locations–Make sure there are enough resources BEFORE increasing parallelism •CPU statistics (top)–Need to see if any processes limited by CPU•Oracle GoldenGate integrated healthcheck (MOS 1448324.1) –Structured HTML file on general integrated GoldenGate healthDB Oracle 12c: GoldenGate AWR reportAWR Replication ExampleOGG HealthCheck Script•SQL script that creates structured formatted HTML –For specific DB release•Summary–Overview information–Advice and Warnings of potential issues with configuration •Analysis–Correlate with other configuration to identify discrepancies–Performance Recommendations–Detail view information for diagnostic purposes•Statistics–Runtime state of Streams processingOGG HealthCheck Usage•Gather healthCheck at–Periodically to provide baselines and snapshots over time –Whenever an error occurs/performance worsens–At intervals during problem times–When the problem has been resolved•Download HealthCheck via MOS Note 1448324.1Navigation Aids For Quick AccessIntegrated Extract Summary ExamplePerformance Recommendation ExampleIntegrated Replicat SummaryAutotunedServerCountIntegrated ReplicatApply ReaderBarrierneededREPERROR ConfigurationApplying Large Transaction Eager Apply Serializes Apply ProcessingProgram AgendaOracle GoldenGate Overview Diagnostic Tools Performance Recommendations Q&A1234Database Configuration•STREAMS_POOL_SIZE–Size appropriately for number of concurrent IE and IR processes–Minimum 1G per IE or IR process then add 25%–If IE or IR parameter MAX_SGA_SIZE configured, use sum of MAX_SGA_SIZE + 25% •ENABLE_GOLDENGATE_REPLICATION–Set to TRUE for DB 11.2.0.4 and 12.1.0.2–Classic or Integrated Extract, Integrated or nonIntegrated Replicat•For performance tuning, use the performance advisor available in $ORACLE_HOME/rdbms/admin/utlspadv.sqlIntegrated ExtractExtract Parameters•LOGALLSUPCOLS instructs extract to write supplemental logged columns to trail file•UPDATERECORDFORMAT COMPACT–Single LCR with both BEFORE and AFTER images–COMPACT reduces the amount of data sent in LCRIntegrated Extract ParametersTRANLOGOPTIONS INTEGRATEDPARAMS•PARALLELISM controls number of preparers for processing logs–Enterprise Edition allows parallelism of preparers (default is set to 2)–Increase parallelism if preparers are CPU bound•Processes down the chain (Builder, Capture) must have bandwidth to handle more work •UTL_SPADV shows this information–Standard Edition does not allow parallelism. Parallelism = 1 only.•MAX_SGA_SIZE controls amount of Streams Pool for specific extract –Minimum recommendation: 1000 (1G)–Maximum recommendation: 3500 (3.5G)Integrated ReplicatReplicat Parameters•GoldenGate parameter BATCHSQL improves apply performance in the majority of workloads–If wait dependencies are high between batched transaction, REDUCE theBATCHTRANSOPS value (default for IR is 50)Example: BATCHSQL BATCHTRANSOPS 12–Table Statistics in Healthcheck can identify specific tables incurring waitsIntegrated Replication ParametersDBOPTIONS INTEGRATEDPARAMS•COMMIT_SERIALIZATION default is DEPENDENT_TRANSACTIONS –Set to FULL, only if apply in source commit order is required•EAGER_SIZE threshold to begin apply of large transactions default is 9500 –Eager apply serializes apply processing–For medium sized large transactions (up to 25000 LCRs), set the threshold to higher value –DBOPTIONS INTEGRATEDPARAMS(EAGER_SIZE 25001)–Make sure that streams_pool_size is appropriate to avoid Waiting For Memory issues •MAX_SGA_SIZE controls amount of memory for IR–Default is INFINITE (Apply allocates memory as needed and available)Integrated Replication ParametersDBOPTIONS INTEGRATEDPARAMS•PARALLELISM controls minimum number of appliers–Default is 4–If PARALLELISM set to 1, apply parallelism is disabled.•MAX_PARALLELISM controls maximum number of appliers–Default is 50 for OGG 12.1.2.1 (30 for OGG 12.1.2.0)•Autotuning of apply parallelism is default–Computed over range of 5 intervals of PARALLELISM_INTERVAL(5) seconds–Unneeded processes marked INACTIVE, INACTIVE processes removed after 5 minutes –If MAX_PARALLELISM=PARALLELISM, autotuning is disabled.Database Object Tuning•GoldenGate uses SQL to apply changes •AWR highlights SQL and SEGMENT issues •ADDM recommendations as appropriateFurther ReadingMOS Notes•MAA white paper Oracle GoldenGate Performance Best Practices /technetwork/database/availability/maa-gg-performance-1969630.pdf•Note 1557031.1 Recommended patches for Integrated Capture/Replicat •Note 1448324.1 OGG Integrated Healthcheck Script•Note 1485620.1 Best Practices Downstream Capture•Note 1488668.1 GoldenGate Performance Data GatheringQuestions and AnswersOracleGoldenGateORCL DataIntegration /dataintData Integration/us/products/middleware/data-integration/overview/index.html。

GoldenGate安装配置GoldenGate支持跨平台的数据实时同步操作：即源和目标可以是不同版本的ORACLE数据库。

GoldenGate需要要在源，目标库机器上都安装，并具是与数据库相匹配的版本：即根据源和目标的ORACLE版本，安装对应的goldenGate。

ogg12的版本是可以兼容11g,12c 版本的数据库，但是10g及以下版本需要找对应的版本，但是最多支持到8i。

GoldenGate是通过读取本地的数据库归档日志，投递给远程，实现同步的。

GoldenGate的日志投递方式有两种：直接投递和数据泵的方式。

原理如下图所示：两种抽取方式的结构示图两种抽取方式的区别：上面的INITIAL LOAD投递方式是直接投递，在源抽取端不缓存捕获队列数据。

而下面的DataPump数据泵方式，会在源库目录下保留抽取数据，在网络服务故障时，能保留未投递的数据不丢失，并在服务正常后能补投数据。

而两种投递方式在接收端在配置上没有区别。

要做双机互备，互为主从，配置两组反向的投递队列就好了。

注：我不推荐做双向同步，在系统故障需要恢复的时候，可能导致数据混乱。

一、操作环境环境二、数据库的准备工作下面的操作，都需要在源和目标数据库上操作。

2.1切换到归档模式SQL> archive log list; 查看归档模式Database log mode No Archive ModeAutomatic archival DisabledArchive destination /soft/u01/app/oracle/product/10.2.0/db_1/dbs/arch Oldest online log sequence 5263Current log sequence 5265如果不是归档模式，需要执行SQL> shutdown immediateSQL> startup mountSQL> alter database archivelog; #修改为归档模式SQL> alter database open;2.2禁用recycle_binSQL> show parameter recyclebin ;禁用recycle binoracle11需要重启才能生效SQL> ALTER SYSTEM SET recyclebin = OFF scope=spfile;对于10g不需要SQL> Alter system set recyclebin=off;2.3处理日志验证未使用并行日志（只针对9i,高级版本无此参数）Sql> show parameter LOG_PARALLELISM。

×××公司Oracle Goldengate安装手册目录1Goldengate安装说明 (3)1.1 总体说明 (3)1.2 添加环境变量 (3)1.3 GG软件的安装（源和目标系统均需要安装） (4)1.4 开启源端数据库附加日志 (5)1.5数据库11.2.0.4版本参数修改 (6)1.6 配置GLOBALS (6)1.7 配置定义文件 (6)1.8 建立OGG进程和队列 (6)2数据初始同步 (11)2.1 将源端数据导入之中间库 (11)2.2 将中间库数据导入至Hadoop (11)2.3 目标启动replicat进程并追加增量数据 (11)1 Goldengate安装说明1.1 总体说明Goldengate软件目录的属主为oracle:dba，权限为775;Goldengate目录一般是建立在共享存储上，这样实际上goldengate的软件和相应的文件如参数文件，队列文件等都是在共享存储上的。

这样做的好处是，例如在RAC环境下，如果一台服务区宕机，则可以将$GG_HOME mount到另外一个正常的rac节点上，启动goldengate各个进程就可以了，不需要做任何其他的动作;本次测试过程是部署在gpfs文件系统上。

1.2 添加环境变量在oracle用户下增加环境变量：源端：export GG_HOME=/data/oggoexport PATH=$PATH:$GG_HOMEexport LIBPATH=$GG_HOME:$ORACLE_HOME/lib目标端bigdata：要求必须jdk1.7export GG_HOME=/data/oggtexport PATH=$PATH:$GG_HOMEexportLD_LIBRARY_PA TH=$GG_HOME:/usr/lib/jvm/java-1.7.0-openjdk-1.7.0.45.x86_64/jre/lib/amd 64/serverPlatform Environment variableIBM AIX LIBPATHHP-UX SHLIB_PA THSun Solaris LD_LIBRARY_PATHHP Tru64 (OSF/1)LINUX1.3 GG软件的安装（源和目标系统均需要安装）1)使用Oracle用户（如非此用户，请自行取代，下同）通过ftp上传GGS软件到安装目录下并解压缩：unzip fbo_ggs_Linux_x64_shiphome.zip.zip修改参数文件：cd fbo_ggs_Linux_x64_shiphome/Disk1/response执行vi oggcore.rsp修改一下参数：INSTALL_OPTION=ORA11gSOFTWARE_LOCATION=/data/oggoDATABASE_LOCATION=/usr/lib/oracle/u01/app/oracle执行runInstaller开始安装：./runInstaller -silent -nowait -responseFile /data/oggo/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp2)在/goldengate目录下运行GGS命令行控制界面：./ggsci3)在GGS命令行界面下创建子目录：GGSCI> create subdirs1.4 开启源端数据库附加日志1）检查附加日志情况使用以下sql语句检查数据库附加日志的打开状况:SelectSUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DA TA_PK,SUPPLEMENTAL_LOG_DA TA_UI,SUPPLEMENTAL_LOG_DA TA_FK,SUPPLEMENTAL_LOG_DA TA_ALL, FORCE_LOGGING from v$database;2）打开数据库附加日志打开附加日志并切换日志（保证Online redo log和Archive log一致）在mount状态下打开归档日志：Alter database archivelog;alter database add supplemental log data ;或全部打开：alter database add supplemental log data(primary key,unique) columns;alter database force logging;3）创建Goldengate用户create tablespace ogg datafile ‘/data/oradata/orcl/ogg.dbf’ size 1G;create user ogg identified by ogg default tablespace ogg;GRANT execute on DBMS_SQL to ogg;grant EXEMPT ACCESS POLICY to ogg;grant create table to ogg;grant create sequence to ogg;alter user ogg quota unlimited on ogg;GRANT CONNECT TO ogg;GRANT RESOURCE TO ogg;GRANT DBA TO ogg;GRANT CREATE SESSION TO ogg;GRANT ALTER SESSION TO ogg;GRANT SELECT ANY DICTIONARY TO ogg;GRANT SELECT ANY TABLE TO ogg;GRANT FLASHBACK ANY TABLE TO ogg;GRANT ALTER ANY TABLE TO ogg;GRANT ALTER SYSTEM TO ogg;GRANT SELECT on dba_clusters TO ogg;GRANT EXECUTE ON utl_file TO ogg;BEGINDBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(Grantee => 'OGG',privilege_type => 'CAPTURE',grant_select_privileges => TRUE,do_grants => TRUE);END;/4)打开表级附加日志alter table ogg.test add supplemental log data(all) columns;alter table SMODEL_JLJ.CM_PORT add supplemental log data(all) columns;alter table SMODEL_JLJ.CM_LINK add supplemental log data(all) columns;…….1.5 数据库11.2.0.4版本参数修改从数据库11.2.0.4版本开始，需要在源和目标端数据库上同时修改ENABLE_GOLDENGATE_REPLICATION参数：sqlplus / as sysdbaalter system set ENABLE_GOLDENGATE_REPLICATION=true scope=both;1.6 配置GLOBALS编辑GLOBALS：EDIT PARAMS ./GLOBALSGGSCHEMA ogg1.7 配置定义文件./ggsci…….1.8 建立OGG进程和队列（1）MGR参数和进程（源和目标均需要配置）cd $GG_HOME./ggsciggsci>edit param mgr --源端PORT 7809DYNAMICPORTLIST 7840-7914--AUTORESTART EXTRACT *, RETRIES 5, W AITMINUTES 3, RESETMINUTES 60 PURGEOLDEXTRACTS ./dirdat/*, usecheckpoints, minkeepdays 1LAGREPORTHOURS 1LAGINFOMINUTES 30LAGCRITICALMINUTES 45ggsci>edit param mgr --目标端PORT 17809DYNAMICPORTLIST 17810-17820--AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 3, RESETMINUTES 60 PURGEOLDEXTRACTS ./dirdat/*, usecheckpoints, minkeepdays 1LAGREPORTHOURS 1LAGINFOMINUTES 30LAGCRITICALMINUTES 45配置完成后，源和目标均启动：ggsci> start mgr（2）源端配置extract和pump进程（根据用户实际环境填写）配置服务器A捕获进程cd $GG_HOME./ggsciggsci>edit param ext_jl具体ext_jl.prm内容如下：EXTRACT ext_jlSETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")USERID ogg, PASSWORD oggDISCARDFILE ./dirrpt/ext_jl.dsc, APPEND, MEGABYTES 100TRANLOGOPTIONS dblogreaderDBOPTIONS ALLOWUNUSEDCOLUMNWARNLONGTRANS 2h,CHECKINTERV AL 3mFETCHOPTIONS NOUSESNAPSHOTTRANLOGOPTIONS CONVERTUCS2CLOBS--ddl include mappedEXTTRAIL ./dirdat/jl--TRANLOGOPTIONS ALTARCHIVELOGDEST PRIMARY INSTANCE oraXPAD1 /gg/sarch1, ALTARCHIVELOGDEST INSTANCE oraXPAD2 /gg/sarch2WILDCARDRESOLVE DYNAMICdynamicresolutionGETUPDATEBEFORESNOCOMPRESSUPDATESNOCOMPRESSDELETESgettruncatesTABLE SMODEL_JLJ.*, tokens (INDICATOR=@GETENV('GGHEADER','BEFOREAFTERINDICATOR'));TABLE gg.tcustmer, tokens (INDICATOR=@GETENV('GGHEADER','BEFOREAFTERINDICATOR'));配置传输进程cd $GG_HOME./ggsciggsci>edit param dpe_jl具体dpe_jl.prm内容如下：EXTRACT dpe_jlPASSTHRURMTHOST 10.142.96.16, MGRPORT 17809RMTTRAIL ./dirdat/jlDYNAMICRESOLUTIONGETTRUNCATESTABLE SMODEL_JLJ.*;table gg.tcustmer;增加ext和pump进程以及队列：cd $GG_HOME./ggsciADD EXTRACT ext_jl, TRANLOG, BEGIN NOWADD EXTTRAIL ./dirdat/jl, EXTRACT ext_jl, MEGABYTES 200ADD EXTRACT dpe_jl, EXTTRAILSOURCE ./dirdat/jlADD RMTTRAIL ./dirdat/jl, EXTRACT dpe_jl, MEGABYTES 200（3）配置hdfs投递进程创建hdfs目录：su – hjpthadoop fs –mkdir /data/hjpt/itf/hqit/oss/jlhadoop fs –ls /data/hjpt/itf/hqit/oss/jlhadoop fs -chmod -R +w /data/hjpt/itf/hqit/oss/oss/jl设置HDFS_HOME环境变量./ggsciggsci>edit param hive_jl具体hive_jl.prm内容如下：REPLICAT hive_jlTARGETDB LIBFILE libggjava.so SET property=dirprm/hdfs_jl.props GETUPDATEBEFORESgettruncatesDISCARDFILE ./dirrpt/ hive_jl.dsc, purge REPORTCOUNT EVERY 1 MINUTES, RATE GROUPTRANSOPS 2000MAXTRANSOPS 5000MAP gg.*, TARGET gg.*;MAP SMODEL_JLJ.*, TARGET SMODEL_JLJ.*;配置hdfs_jl.props内容如下：gg.handlerlist=hdfsgg.handler.hdfs.type=hdfsgg.handler.hdfs.includeTokens=truegg.handler.hdfs.maxFileSize=256mgg.handler.hdfs.rootFilePath=/data/hjpt/itf/hqit/oss/jlgg.handler.hdfs.fileRollInterval=0gg.handler.hdfs.inactivityRollInterval=0gg.handler.hdfs.fileSuffix=.txtgg.handler.hdfs.partitionByTable=truegg.handler.hdfs.rollOnMetadataChange=truegg.handler.hdfs.authType=nonegg.handler.hdfs.format=delimitedtextgg.handler.hdfs.format.includeColumnNames=falsegg.handler.hdfs.mode=tx#gg.handler.hdfs.format.includeOpTimestamp=truegg.handler.hdfs.format.includeCurrentTimestamp=truegg.handler.hdfs.format.insertOpKey=Igg.handler.hdfs.format.updateOpKey=Ugg.handler.hdfs.format.deleteOpKey=Dgg.handler.hdfs.format.truncateOpKey=Tgg.handler.hdfs.format.encoding=UTF-8gg.handler.hdfs.format.fieldDelimiter=CDA TA[\u0001]#gg.handler.hdfs.format.fieldDelimiter='|'gg.handler.hdfs.format.lineDelimiter=CDA TA[\n]gg.handler.hdfs.format.includeTableName=truegg.handler.hdfs.format.keyValueDelimiter=CDATA[=]#gg.handler.hdfs.format.kevValuePairDelimiter=CDATA[,] gg.handler.hdfs.format.pkUpdateHandling=updategg.handler.hdfs.format.nullValueRepresentation=gg.handler.hdfs.format.missingValueRepresentation=CDATA[] gg.handler.hdfs.format.includePosition=false#erexit.timestamp=utcerexit.writers=javawriterjavawriter.stats.display=TRUEjavawriter.stats.full=TRUEgg.log=log4jgg.log.level=INFOgg.report.time=30secgg.classpath=/etc/hadoop/conf.empty:/usr/lib/hadoop/*:/usr/lib/hadoop-hdfs/lib/*:/usr/lib/hadoop-hdfs/*:/usr/lib/hadoop/lib/*javawriter.bootoptions=-Xmx8200m –Xms8192m -Djava.class.path=ggjava/ggjava.jar增加目标端hive投递进程：cd $GG_HOME./ggsciGGSCI> add replicat hive_jl, EXTTRAIL ./dirdat/jl2 数据初始同步在不允许停源端数据库的情况下，需要准备一个临时中间数据库。

GoldenGate Director安装及配置文档目录1.1概述 (2)1。

2G OLDEN G ATE D IRECTOR概述 (2)1。

3G OLDEN G ATE D IRECTOR的安装 (3)1.3.1部署方案必需条件 (3)1。

3。

1.1 ............................................................................................................ Director服务器安装前提31。

3。

1.2 ............................................................................................................ Director客户端安装前提31。

3。

2安装GoldenGate Director Server (4)1.3。

3安装GoldenGate Director Client (7)1.3.4启动GoldenGate Director Server/Client (7)1。

4G OLDEN G ATE D IRECTOR的配置使用 (8)1.4.1Director Admin (8)1。

4.2基于Web的Director客户端 (11)1。

4。

2.1 ...................................................................................................................... 监控整体运行情况121。

4。

2。

2 ........................................................................................................................... 监控进程状态131.4。

GOLDENGATE运维手册2014 年 4 月文档修改记录版本号日期说明编写者审核者2014/4文档编写目录1 OGG常用监控命令--------------------------------------------- 错误 ! 未定义书签。

说明 ----------------------------------------- 错误 !未定义书签。

启动 GoldenGate 进程 --------------------------- 错误 !未定义书签。

停止 GoldenGate 进程 --------------------------- 错误 !未定义书签。

查看整体运行情况 ------------------------------ 错误 !未定义书签。

查看参数设置 - --------------------------------- 错误 !未定义书签。

查看进程状态 - --------------------------------- 错误 !未定义书签。

查看延时- ------------------------------------- 错误 !未定义书签。

查看统计信息 - --------------------------------- 错误 !未定义书签。

查看运行报告 - --------------------------------- 错误 !未定义书签。

2 OGG的常见运维任务指南 --------------------------------------- 错误 ! 未定义书签。

配置自动删除队列 ------------------------------ 错误 !未定义书签。

配置启动 MGR时自动启动 Extract 和 Replicat 进程 - 错误 !未定义书签。

配置 MGR自动重新启动 Extract 和 Replicat 进程 --- 错误 !未定义书签。

GOLDEN GATE配置示例安装1.解压zip文件2.将解压后的文件夹转移到目标文件夹，注意目标文件夹名称中不能包含“空格”。

源端配置数据库及系统配置1.在源端，创建GoldenGate系统用户，建议使用oracle用户。

a.创建GoldenGate数据库用户goldengateCREATE USER goldengate IDENTIFIED BY goldengate default tablespace xxx ;GRANT dba TO goldengate;2.检查源端数据库是否为归档模式，若为非归档模式，将其改为归档模式SQL>archive log list;3.检查源端数据库附加日志是否打开SQL>select supplemental_log_data_min from v$database;将数据库附加日志打开SQL>alter database add supplemental log data;切换日志以使附加日志生效：SQL〉alter system archive log current;创建golden gate所需的子文件夹配置管理进程参数输入MGRSERVNAME $severname,保存文件。

安装管理进程在DOS命令行下运行INSTALL ADDSERVICE ADDEVENTS配置mgr管理进程参数输入port 7809，保存文件。

7809为默认端口，需要确保该端口未被占用。

其它参数启动mgr进程Manager must be running before you start other GoldenGate processesTo start Manager from GGSCI1. From the GoldenGate directory, run GGSCI.2. In GGSCI, issue the following command.START MANAGERTo stop Manager1. From the GoldenGate directory, run GGSCI.2. In GGSCI, issue the following command.STOP MANAGER [!]Where: ! stops Manager without user confirmation.配置ExtractIn the Manager parameter file, use the PURGEOLDEXTRACTS parameter to control the purging of files from the trailTo configure the primary Extract group on the source1.ADD EXTRACT <ext_1>, TRANLOG, BEGIN <time> [, THREADS <n>]2.On the source, use the ADD EXTTRAIL command to create a local trailADD EXTTRAIL <local_trail_1>, EXTRACT <ext>e the EDIT PARAMS command to create a parameter file for the ext_1 Extract group.-- Identify the Extract group:EXTRACT <ext_1>-- Specify database login information as needed for the database:[SOURCEDB <dsn_1>,][USERID <user>[, PASSWORD <pw>]]-- Specify the local trail that this Extract writes to:EXTTRAIL <local_trail_1>-- Specify tables to be captured:TABLE <owner>.<table>;示例命令如下：●ADD EXTRACT extsp, TRANLOG,BEGIN now●add exttrail ./dirdat/ex, extract extsp, megabytes 10●edit param exts pEXTRACT extspUSERID xgt,PASSWORD xgtEXTTRAIL ./dirdat/exTABLE xgt.TBLDICT;To configure the data pump on the sourcee the ADD EXTRACT command to create a data pump groupADD EXTRACT <pump_1>, EXTTRAILSOURCE <local_trail_1>, BEGIN <time>e the ADD RMTTRAIL command to specify a remote trail that will be created on the second system in thecascadeADD RMTTRAIL <remote_trail_1>, EXTRACT <pump_1>Use the EXTRACT argument to link the remote trail to the pump_1 data pump group. The linked data pump writes to this trail.e the EDIT PARAMS command to create a parameter file for the pump_1 data pump-- Identify the data pump group:EXTRACT <pump_1>-- Specify database login information as needed for the database:[SOURCEDB <dsn_1>,][USERID <user>[, PASSWORD <pw>]]-- Specify the name or IP address of second system in cascade:RMTHOST <target_1>, MGRPORT <portnumber>-- Specify the remote trail on the second system:RMTTRAIL <remote_trail_1>-- Allow mapping, filtering, conversion or pass data through as-is: [PASSTHRU | NOPASSTHRU]-- Specify tables to be captured:TABLE <owner>.<table>;示例命令如下：●add extract extpump,exttrailsource ./dirdat/ex,begin now●ADD RMTTRAIL ./dirdat/sp, EXTRACT extpump,megabytes 10●edit param extpumpEXTRACT extpumpdynamicresolutionUSERID xgt,PASSWORD xgtRMTHOST 192.168.139.128,MGRPORT 7809RMTTRAIL ./dirdat/spnumfiles 300TABLE xgt.TBLDICT;启动ExtractStart extspStart extpump目标端配置创建golden gate所需的子文件夹配置管理进程参数输入MGRSERVNAME $severname,CHECKPOINTTABLE target.ggschkpt保存文件。

目录1综述------------------------------------------------------------------------- 3 1.1目的 ----------------------------------------------------------------------------------------------- 31.2原则 ----------------------------------------------------------------------------------------------- 32OGG设计原则----------------------------------------------------------------- 4 2.1OGG的版本及下载 --------------------------------------------------------------------------- 4 2.2GoldenGate运行用户------------------------------------------------------------------------ 4 2.3为GoldenGate运行用户配置环境变量 ------------------------------------------------ 4 2.4网络设置----------------------------------------------------------------------------------------- 4 2.5GoldenGate安装位置------------------------------------------------------------------------ 4 2.6OGG DDL复制 ---------------------------------------------------------------------------------- 5 2.7OGG数据复制链路结构 -------------------------------------------------------------------- 52.8OGG进程命名规则 --------------------------------------------------------------------------- 62.8.1总述------------------------------------------------------------------------------------------------------------ 62.8.2主Extract进程命名规则 -------------------------------------------------------------------------------- 62.8.3本地队列命名规则---------------------------------------------------------------------------------------- 62.8.4Data Pump Extract进程命名规则--------------------------------------------------------------------- 72.8.5本地队列命名规则---------------------------------------------------------------------------------------- 72.8.6Replicat进程命名规则 ----------------------------------------------------------------------------------- 72.9OGG进程数量设计 --------------------------------------------------------------------------- 82.9.1主Extract进程设计--------------------------------------------------------------------------------------- 82.9.2Data Pump进程设计 ------------------------------------------------------------------------------------- 82.9.3Replicat进程设计------------------------------------------------------------------------------------------ 93GG实施步骤----------------------------------------------------------------- 10 3.1准备 --------------------------------------------------------------------------------------------- 10 3.2创建运行GoldenGate的用户 ----------------------------------------------------------- 11 3.3安装GoldenGate ---------------------------------------------------------------------------- 11 3.4添加变量--------------------------------------------------------------------------------------- 12 3.5增加附加日志 -------------------------------------------------------------------------------- 123.6OGG源端配置-------------------------------------------------------------------------------- 123.6.1MGR进程参数模板 ------------------------------------------------------------------------------------- 123.6.2主Extract进程参数模板 ------------------------------------------------------------------------------ 133.6.3Data Pump传输进程参数模板 ---------------------------------------------------------------------- 153.7OGG目标端配置 ---------------------------------------------------------------------------- 163.7.1安装GoldenGate----------------------------------------------------------------------------------------- 163.7.2添加变量 --------------------------------------------------------------------------------------------------- 163.7.3MGR进程配置-------------------------------------------------------------------------------------------- 173.7.4数据入库Replicat进程参数模板------------------------------------------------------------------- 173.8启动GoldenGate进程 --------------------------------------------------------------------- 193.8.1启动源端管理进程-------------------------------------------------------------------------------------- 193.8.2启动目标端管理进程----------------------------------------------------------------------------------- 193.8.3启动源端抽取进程-------------------------------------------------------------------------------------- 193.8.4启动源端传输进程-------------------------------------------------------------------------------------- 193.8.5启动目标端入库进程----------------------------------------------------------------------------------- 194GG初始化说明--------------------------------------------------------------- 20 4.1数据库说明------------------------------------------------------------------------------------ 20 4.2数据库信息------------------------------------------------------------------------------------ 204.3Goldengate同步内容 ---------------------------------------------------------------------- 204.3.1 个税内部Goldengate进程说明 -------------------------------------------------------------------------- 204.3.2 与外部其他系统Goldengate进程说明 ---------------------------------------------------------------- 20 4.4附件 ----------------------------------------------------------------------错误!未定义书签。