1、1Oracle GoldenGate测试文档http:/21 Oracle GoldenGate介绍GoldenGate TDM（交易资料管理）软体是一种基于日志的结构化资料複製软体，它通过解析源资料库在线日志或归档日志获得资料的增删改变化，再将这些变化应用到目标资料库，实现源资料库与目标资料库同步、双活。GoldenGate TDM 软体可以在异构的 IT基础结构（包括几乎所有常用作业系统平台和资料库平台）之间实现大量资料亚秒一级的即时複製，其複製过程简图如下： 如上图所示，GoldenGate TDM 的资料複製过程如下： 利用捕捉进程(Capture Process)在源系统端读取 On
2、line Redo Log或 Archive Log，然后进行解析，只提取其中资料的变化如增、删、改操作，并将相关资讯转换爲 GoldenGate TDM自定义的中间格式存放在伫列文件中。再利用传送进程将伫列文件通过 TCP/IP传送到目标系统。捕捉进程在每次读完 log中的资料变化并在资料传送到目标系统后，会写检查点，记录当前完成捕捉的 log位置，检查点的存在可以使捕捉进程在中止并恢复后可从检查点位置继续複製； 目标系统接受资料变化并缓存到 GoldenGate TDM伫列当中，伫列爲一系列临时存储资料变化的文件，等待投递进程读取资料； GoldenGate TDM投递进程从伫列中读取资料
3、变化并创建对应的 SQL语句，通过资料库的本地介面执行，提交到资料库成功后更新自己的检查点，记录已经完成複製的位置，资料的複製过程最终完成。 由此可见，GoldenGate TDM是一种基于软体的资料複製方式，它从资料库的日志解析资料的变化（资料量只有日志的四分之一左右）。GoldenGate TDM将资料变化转化爲自己的格式，直接通过 TCP/IP网路传输，无需依赖于资料库自身的传递方式，而且可以通过高达 9:1的压缩率对资料进行压缩，可以大大降低带宽需求。在目标端，GoldenGate TDM可以通过3交易重组，分批载入等技术手段大大加快资料投递的速度和效率，降低目标系统的资源佔用，可以在
4、亚秒级实现大量资料的複製，并且目标端资料库是活动的GoldenGate TDM提供了灵活的应用方桉，基于其先进、灵活的技术架构可以根据用户需求组成各种拓扑结构，如图所示： GoldenGate TDM 可以提供可靠的资料複製，主要体现在下面三点： 保证事务一致性 GoldenGate TDM 在灾备资料库应用複製资料库交易的顺序与在生産中心资料库上的顺序相同，并且按照相同的事务环境提交，确保在目标系统上资料的完整性和读一致性，爲即时查询和事务处理创造了条件。 检查点机制保障资料无丢失 GoldenGate TDM的抽取和複製进程使用检查点机制记录完成複製的位置。对于抽取进程，其检查点记录当前已
5、经抽取日志的位置和写伫列文件的位置；对于投递进程，其检查点记录当前读取伫列文件的位置。检查点机制可以保证在系统、网路或 GoldenGate TDM进程故障重啓后资料无丢失。 可靠的资料传输机制 GoldenGate TDM 用应答机制传输交易资料，只有在得到确认消息后才认爲资料传输完成，否则将自动重新传输资料，从而保证了抽取出的所有资料都能发送到备份端。资料传输过程中支援 128位加密和资料压缩功能。Oracle 公司的 GoldenGate産品，可以在异构的 IT基础结构之间实现大量资料的秒一级的资料捕捉、转换和投递。GoldenGate 可以支援几乎所有常用作业系统如和资料库平台，如下表
6、所示： 4作业系统 资料库 MS NT, 2000, XP, Linux, Sun Solaris, HP-UX, IBM AIX, HP NonStop, TRU64, IBM z/OS，OS/390 Oracle, DB2, MS SQL Server, MySQL, Enscribe, SQL/MP, SQL/MX, Sybase, Teradata, 其他 ODBC 相容资料库 52Oracle GoldenGate For Oracle(windows 平台) 安装GoldenGate 的安装非常简单，将解压缩安装文件到指定目录。比如安装文件解压缩后的路径爲 d:ggs安装步骤如下:
7、1. 进入命令行2. 切换到 ggs目录下3. 输入命令 install addservice addevents4. 输入 ggsci进入 GoldenGate的命令行5. 输入 create subdirsOK，GoldenGate 安装就已经完成了。实际操作如下C:Documents and SettingsPONYd:D:cd ggsD:ggsinstall addservice addeventsOracle GoldenGate messages installed successfully.Service GGSMGR created.Install program termin
8、ated normally.D:ggsggsciOracle GoldenGate Command Interpreter for OracleVersion 10.4.0.19 Build 002Windows (optimized), Oracle 10 on Sep 18 2009 15:54:55Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.GGSCI (PONY) 1 create subdirsCreating subdirectories under current dire
9、ctory D:ggsParameter files D:ggsdirprm: createdReport files D:ggsdirrpt: createdCheckpoint files D:ggsdirchk: created6Process status files D:ggsdirpcs: createdSQL script files D:ggsdirsql: createdDatabase definitions files D:ggsdirdef: createdExtract data files D:ggsdirdat: createdTemporary files D:
10、ggsdirtmp: createdVeridata files D:ggsdirver: createdVeridata Lock files D:ggsdirverlock: createdVeridata Out-Of-Sync files D:ggsdirveroos: createdVeridata Out-Of-Sync XML files D:ggsdirveroosxml: createdVeridata Parameter files D:ggsdirverparams: createdVeridata Report files D:ggsdirverreport: crea
11、tedVeridata Status files D:ggsdirverstatus: createdVeridata Trace files D:ggsdirvertrace: createdStdout files D:ggsdirout: created73资料库複製实施文档（DML）3.1准备工作准备两台机器，分别爲 SourceDB, TargetDB，通过 TCP/IP网路进行互连SourceDB ：WIN XP 环境，Oracle 10g 10.2.0.1.0TargetDB ：WIN 2003 环境 Oracle 10g 10.2.0.1.0必须保证 SourceDB是运行在归
12、档模式下。目的 ：将 SourceDB中相关 Schema中的资料同步複製到 TargetDB相对应的 Sechema中本文中要同步的是 SouceDB中的 SAJET,SJ,SMT,LANG到 Target中的 SAJET,SJ,SMT,LANG用户下首先要保持 SourceD, TargetDB相关 Schema中的初始资料一致，可以通过Exp/Imp,Rman,冷备等方式实现.双方 DB建立 GoldenGate用户，赋予 DBA许可权，用于 GoldenGate连接 DB.双方 DB安装 GoldenGate，安装方式如第二章节所示SourceDB 必须开始最小附加日志模式通过以下语句
13、查看 DB是否开啓了最小附加日志模式SQL select SUPPLEMENTAL_LOG_DATA_MIN from v$database;SUPPLEME-YES如果没有开啓资料库的最小附加日志，则通过以下语句开啓SQLalter database add supplemental log data; 开啓表的最小附加日志，通过 GoldenGate命令行来添加GGSCI (PONY) 1 dblogin userid sajet password techSuccessfully logged into database.GGSCI (PONY) 2 add trandata sajet
14、.*第一条命令表示登录到 Database第二条命令表示添加 Sajet用户下所有表的最小附加日志同样的命令开啓 SJ,LANG,SMT用户下的表的最小附加日志。3.2配置 GoldenGate3.2.1配置 SourceDB的 GoldenGateSourceDB端需要配置 mgr进程，添加一个 extract进程，和一个远端伫列。1.配置 mgr参数GGSCI (PONY) 2 edit param mgr此时系统自动会新建一个名爲 mgr的参数文件，填入以下内容后保存PORT 7809 -mgr进程使用的 TCP/IP埠侦听请求2.新增一个抽取进程，在 GGSCI命令行中输入如下命令：G
15、GSCI (PONY) 1 add extract ext1,tranlog,begin now EXTRACT added.8-新增一个抽取进程，负责抓取 SourceDB中变化的资料，基于日志方式，立即生效3.配置抽取进程GGSCI (PONY) 2 edit param ext1此时系统会自动新建一个名爲 ext1的参数文件，填入以下内容后保存extract ext1userid goldengate,password goldengatermthost 172.17.17.61, mgrport 7809rmttrail d:ggsdirdatr1dynamicresolutionge
16、ttruncatestable sajet.*;table sj.*;table smt.*;table lang.*;-抽取进程名爲 ext2连接本机 DB的账号和密码远端主机地址以及服务埠号（TargetDB）远端伫列的位置(TargetDB)优化参数，动态分析表结构是否抓取 Truncate的资料需要抽取哪些 table。4.新增远端伫列位置GGSCI (PONY) 3 add rmttrail d:ggsdirdatr1 extract ext2RMTTRAIL added.-新增一个远端伫列，位置爲在 TargetDB的 d:ggsdirdatr1，是抽取进程 ext1抽取的资料伫列
17、，注意和抽取进程中配置的 rmttrail d:ggsdirdatr1一致。开啓所有进程GGSCI (PONY) 11 start mgrStarting Manager as service (GGSMGR).Service started.GGSCI (PONY) 14 start ext1Sending START request to MANAGER (GGSMGR) .9EXTRACT EXT1 starting查看进程的运行情况GGSCI (PONY) 185 info allProgram Status Group Lag Time Since ChkptMANAGER RUNN
18、INGEXTRACT RUNNING EXT1 00:00:00 00:00:06OK，Mgr 进程和 EXT进程都已经正常运行SouceDB端的配置完成。3.2.2 配置 TargetDB的 GoldenGate1.配置 mgr参数GGSCI (PONY) 2 edit param mgr此时系统自动会新建一个 mgr的参数文件，填入以下内容后保存PORT 7809 DYNAMICPORTLIST 7840-7850 -mgr进程使用的 TCP/IP埠侦听请求接受远端伫列的埠列表。2.新增一个複製进程GGSCI (PONY) 2ADD replicat rep1 EXTTRAIL d:ggs
19、dirdatr1, nodbcheckpoint-新增一个複製进程，将抽取到伫列中的文件解析后写进 TargetDB3.配置複製进程GGSCI (PONY) 2 edit param rep1此时系统会自动新建一个参数文件，填入一下内容后保存replicat rep1userid goldengate,password goldengateassumetargetdefsreperror default,discarddiscardfile D:oradatadiscardrepsz.dsc,append,megabytes 100gettruncatemap sajet.*, target
20、sajet.*;map sj.*, target sj.*;map lang.*, target lang.*;10map smt.*, target smt.*;-複製进程名登入 TargetDB的账号和密码两台 DB资料结构一致则使用此参数如果複製出错，则继续，并将错误放进 discardfile中複製 truncate操作複製的源表爲 sajet，sj，lang，smt 用户下的所有表，目标爲 TargetDB中对用用户下的所有表开啓 mgr和 rep进程GGSCI (PONY) 20 start mgrStarting Manager as service (GGSMGR).Servi
21、ce started.GGSCI (PONY) 21 start rep1Sending START request to MANAGER (GGSMGR) .REPLICAT REP1 starting查看进程的运行情况GGSCI (PONY) 22 info allProgram Status Group Lag Time Since ChkptMANAGER RUNNINGREPLICAT RUNNING REP1 00:00:00 00:00:07OK,MGR进程和 REP进程运行正常至此，两台 DB的 GoldenGate都已经配置完成。3.3 测试 DML操作首先往 SourceD
22、B中插入一行C:Documents and SettingsAdministratorsqlplusSQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 8 15:33:47 201011Copyright (c) 1982, 2005, Oracle. All rights reserved.Enter user-name: sajet/techhitronConnected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith
23、 the Partitioning, OLAP and Data Mining optionsSQL insert into sajet.sys_emp values (10000786,TEST1,TEST1,TEST,1000027,Y,0,0,)2 ;1 row created.SQL commit2 ;Commit complete.看看 TargetDB中是否有同样的记录存在呢SQL connect sajet/techgoldengaConnected.SQL select count (*) from sajet.sys_emp2 where emp_id=10000786;CO
24、UNT(*)-1同步过来了。我们可以看看 EXT1进程的相关统计资讯GGSCI (sajet-project) 189 stats ext1Sending STATS request to EXTRACT EXT1 .Start of Statistics at 2010-03-08 15:47:51.DDL replication statistics (for all trails):12* Total statistics since extract started *Operations 40.00Mapped operations 28.00Unmapped operations 0
25、.00Other operations 12.00Excluded operations 0.00Output to d:ggsdirdatr1:Extracting from GOLDENGATE.GGS_MARKER to GOLDENGATE.GGS_MARKER:* Total statistics since 2010-03-05 09:51:51 *No database operations have been performed.* Daily statistics since 2010-03-08 00:00:00 *No database operations have b
26、een performed.* Hourly statistics since 2010-03-08 15:00:00 *No database operations have been performed.* Latest statistics since 2010-03-05 09:51:51 *No database operations have been performed.Extracting from SAJET.GOLDEGATETEST to SAJET.GOLDEGATETEST:* Total statistics since 2010-03-05 09:51:51 *T
27、otal inserts 3.00Total updates 0.00Total deletes 0.00Total truncates 2.00Total discards 0.00Total operations 5.00* Daily statistics since 2010-03-08 00:00:00 *No database operations have been performed.* Hourly statistics since 2010-03-08 15:00:00 *13No database operations have been performed.* Late
28、st statistics since 2010-03-05 09:51:51 *Total inserts 3.00Total updates 0.00Total deletes 0.00Total truncates 2.00Total discards 0.00Total operations 5.00Extracting from SAJET.SYS_EMP to SAJET.SYS_EMP:* Total statistics since 2010-03-05 09:51:51 *Total inserts 2.00Total updates 0.00Total deletes 0.
29、00Total discards 0.00Total operations 2.00* Daily statistics since 2010-03-08 00:00:00 *Total inserts 2.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 2.00* Hourly statistics since 2010-03-08 15:00:00 *Total inserts 2.00Total updates 0.00Total deletes 0.00Total discards 0.
30、00Total operations 2.00* Latest statistics since 2010-03-05 09:51:51 *Total inserts 2.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 2.00Extracting from SAJET.MLOG$_SYS_EMP to SAJET.MLOG$_SYS_EMP:14* Total statistics since 2010-03-05 09:51:51 *Total inserts 2.00Total updat
31、es 0.00Total deletes 0.00Total discards 0.00Total operations 2.00* Daily statistics since 2010-03-08 00:00:00 *Total inserts 2.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 2.00* Hourly statistics since 2010-03-08 15:00:00 *Total inserts 2.00Total updates 0.00Total delete
32、s 0.00Total discards 0.00Total operations 2.00* Latest statistics since 2010-03-05 09:51:51 *Total inserts 2.00Total updates 0.00Total deletes 0.00Total discards 0.00Total operations 2.00End of Statistics.相信以上的内容大家都看的懂吧。其他的 DML操作在此省略。4 GoldenGate Support DDL安装1.选择一个 Schema存放支援 DDL的物件，本文选择 GoldenGate
33、2.编辑 Globals参数文件153.关掉 Oracle Recycle binOn Oracle10gand up, system recycle bin must be disabled.To turn off the recycle bin:Oracle10gRelease 2 and later: Set the RECYCLEBIN initialization parameter to OFF.Oracle10gRelease 1: Set the _RECYCLEBIN initialization parameter to FALSE.4.在命令行，切换到 GGS目录5.运行
34、 SQLPLUS6. run marker_setup.sql, enter GoldenGate schema(goldengate)7. run ddl_setup.sqlenter schema:goldengatechoose installation mode: INITIALSETUP8. run role_setup.sql9. grant role(GGS_GGSUSER_ROLE) to all GoldenGate Extract users;10. run ddl_enable.sql两端都安装完成.具体如下：GGSCI (PONY) 23 edit param glob
35、als填入如下内容后保存GGSCHEMA goldengateD:ggssqlplus/ as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on 星期三 3 月 3 10:12:06 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining option
36、sSQL marker_setup.sqlMarker setup scriptYou will be prompted for the name of a schema for the GoldenGate database objects.16NOTE: The schema must be created prior to running this script.NOTE: Stop all DDL replication before starting this installation.Enter GoldenGate schema name:goldengateMarker set
37、up table script complete, running verification script.Please enter the name of a schema for the GoldenGate database objects:Setting schema name to GOLDENGATEMARKER TABLE-OKMARKER SEQUENCE-OKScript complete.SQL ddl_setup.sqlGoldenGate DDL Replication setup scriptVerifying that current user has privil
38、eges to install DDL Replication.You will be prompted for the name of a schema for the GoldenGate database objects.NOTE: The schema must be created prior to running this script.NOTE: On Oracle 10g and up, system recycle bin must be disabled.NOTE: Stop all DDL replication before starting this installa
39、tion.Enter GoldenGate schema name:goldengateYou will be prompted for the mode of installation.To install or reinstall DDL replication, enter INITIALSETUPTo upgrade DDL replication, enter NORMALEnter mode of installation:initialsetupWorking, please wait .Spooling to file ddl_setup_spool.txt17Using GO
40、LDENGATE as a GoldenGate schema name, INITIALSETUP as a mode of installation.Working, please wait .RECYCLEBIN must be empty.This installation will purge RECYCLEBIN for all users.To proceed, enter yes. To stop installation, enter no.Enter yes or no:yesDDL replication setup script complete, running ve
41、rification script.Please enter the name of a schema for the GoldenGate database objects:Setting schema name to GOLDENGATEDDLORA_GETTABLESPACESIZE STATUS:Line/pos-Error-No errorsNo errorsCLEAR_TRACE STATUS:Line/pos-Error-No errorsNo errors18CREATE_TRACE STATUS:Line/pos-Error-No errorsNo errorsTRACE_PUT_LINE STATUS:Line/pos-Error-No errorsNo errorsINITIAL_SETUP STATUS:Line/pos-Error-No errorsNo errorsDDLVERSIONSPECIFIC PACKAGE STATUS:Line/pos-19Error-No errorsNo errorsDDLREPLICATION PACKAGE STATUS:Line/pos-Error-No errorsNo errorsDDLREPLICATION PACKAGE BODY STATUS:Line/pos-Erro
