OracleGoldenGate精选.doc

Oracle GoldenGate测试文档1 Oracle GoldenGate介绍GoldenGate TDM（交易资料管理）软体是一种基于日志的结构化资料複製软体，它通过解析源资料库在线日志或归档日志获得资料的增删改变化，再将这些变化应用到目标资料库，实现源资料库与目标资料库同步、双活。GoldenGate TDM 软体可以在异构的IT基础结构（包括几乎所有常用作业系统平台和资料库平台）之间实现大量资料亚秒一级的即时複製，其複製过程简图如下： 如上图所示，GoldenGate TDM的资料複製过程如下： 利用捕捉进程(Capture Process)在源系统端读取Online Redo Log或Archive Log，然后进行解析，只提取其中资料的变化如增、删、改操作，并将相关资讯转换爲GoldenGate TDM自定义的中间格式存放在伫列文件中。再利用传送进程将伫列文件通过TCP/IP传送到目标系统。捕捉进程在每次读完log中的资料变化并在资料传送到目标系统后，会写检查点，记录当前完成捕捉的log位置，检查点的存在可以使捕捉进程在中止并恢复后可从检查点位置继续複製； 目标系统接受资料变化并缓存到GoldenGate TDM伫列当中，伫列爲一系列临时存储资料变化的文件，等待投递进程读取资料； GoldenGate TDM投递进程从伫列中读取资料变化并创建对应的SQL语句，通过资料库的本地介面执行，提交到资料库成功后更新自己的检查点，记录已经完成複製的位置，资料的複製过程最终完成。 由此可见，GoldenGate TDM是一种基于软体的资料複製方式，它从资料库的日志解析资料的变化（资料量只有日志的四分之一左右）。GoldenGate TDM将资料变化转化爲自己的格式，直接通过TCP/IP网路传输，无需依赖于资料库自身的传递方式，而且可以通过高达9:1的压缩率对资料进行压缩，可以大大降低带宽需求。在目标端，GoldenGate TDM可以通过交易重组，分批载入等技术手段大大加快资料投递的速度和效率，降低目标系统的资源佔用，可以在亚秒级实现大量资料的複製，并且目标端资料库是活动的GoldenGate TDM提供了灵活的应用方桉，基于其先进、灵活的技术架构可以根据用户需求组成各种拓扑结构，如图所示： GoldenGate TDM 可以提供可靠的资料複製，主要体现在下面三点： 保证事务一致性 GoldenGate TDM 在灾备资料库应用複製资料库交易的顺序与在生産中心资料库上的顺序相同，并且按照相同的事务环境提交，确保在目标系统上资料的完整性和读一致性，爲即时查询和事务处理创造了条件。 检查点机制保障资料无丢失 GoldenGate TDM的抽取和複製进程使用检查点机制记录完成複製的位置。对于抽取进程，其检查点记录当前已经抽取日志的位置和写伫列文件的位置；对于投递进程，其检查点记录当前读取伫列文件的位置。检查点机制可以保证在系统、网路或GoldenGate TDM进程故障重啓后资料无丢失。 可靠的资料传输机制 GoldenGate TDM 用应答机制传输交易资料，只有在得到确认消息后才认爲资料传输完成，否则将自动重新传输资料，从而保证了抽取出的所有资料都能发送到备份端。资料传输过程中支援128位加密和资料压缩功能。Oracle 公司的GoldenGate産品，可以在异构的IT基础结构之间实现大量资料的秒一级的资料捕捉、转换和投递。GoldenGate可以支援几乎所有常用作业系统如和资料库平台，如下表所示： 作业系统 资料库 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 相容资料库 2Oracle GoldenGate For Oracle(windows 平台) 安装GoldenGate 的安装非常简单，将解压缩安装文件到指定目录。比如安装文件解压缩后的路径爲 d:ggs安装步骤如下:1.进入命令行2.切换到ggs目录下3.输入命令 install addservice addevents4.输入ggsci进入GoldenGate的命令行5.输入 create subdirs OK，GoldenGate安装就已经完成了。实际操作如下C:Documents and SettingsPONYd:D:cd ggsD:ggsinstall addservice addeventsOracle GoldenGate messages installed successfully.Service GGSMGR created.Install program terminated normally.D:ggsggsciOracle GoldenGate Command Interpreter for OracleVersion 9 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 directory D:ggsParameter files D:ggsdirprm: createdReport files D:ggsdirrpt: createdCheckpoint files D:ggsdirchk: createdProcess status files D:ggsdirpcs: createdSQL script files D:ggsdirsql: createdDatabase definitions files D:ggsdirdef: createdExtract data files D:ggsdirdat: createdTemporary files D: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: createdVeridata Status files D:ggsdirverstatus: createdVeridata Trace files D:ggsdirvertrace: createdStdout files D:ggsdirout: created3资料库複製实施文档（DML）3.1准备工作准备两台机器，分别爲SourceDB, TargetDB，通过TCP/IP网路进行互连SourceDB ：WIN XP环境，Oracle 10g .0TargetDB ：WIN 2003 环境 Oracle 10g .0必须保证SourceDB是运行在归档模式下。目的 ：将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必须开始最小附加日志模式通过以下语句查看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.*第一条命令表示登录到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命令行中输入如下命令：GGSCI (PONY) 1 add extract ext1,tranlog,begin now EXTRACT added.-新增一个抽取进程，负责抓取SourceDB中变化的资料，基于日志方式，立即生效3.配置抽取进程GGSCI (PONY) 2 edit param ext1此时系统会自动新建一个名爲ext1的参数文件，填入以下内容后保存extract ext1userid goldengate,password goldengatermthost 1, mgrport 7809rmttrail d:ggsdirdatr1dynamicresolutiongettruncatestable 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抽取的资料伫列，注意和抽取进程中配置的rmttrail d:ggsdirdatr1一致。开啓所有进程GGSCI (PONY) 11 start mgrStarting Manager as service (GGSMGR).Service started.GGSCI (PONY) 14 start ext1Sending START request to MANAGER (GGSMGR) .EXTRACT EXT1 starting查看进程的运行情况GGSCI (PONY) 185 info allProgram Status Group Lag Time Since ChkptMANAGER RUNNINGEXTRACT 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:ggsdirdatr1, nodbcheckpoint-新增一个複製进程，将抽取到伫列中的文件解析后写进TargetDB3.配置複製进程GGSCI (PONY) 2 edit param rep1此时系统会自动新建一个参数文件，填入一下内容后保存replicat rep1userid goldengate,password goldengateassumetargetdefsreperror default,discarddiscardfile D:oradatadiscardrepsz.dsc,append,megabytes 100gettruncatemap sajet.*, target sajet.*;map sj.*, target sj.*;map lang.*, target lang.*;map smt.*, target smt.*;-複製进程名 登入TargetDB的账号和密码 两台DB资料结构一致则使用此参数 如果複製出错，则继续，并将错误放进discardfile中 複製truncate操作複製的源表爲sajet，sj，lang，smt用户下的所有表，目标爲TargetDB中对用用户下的所有表开啓mgr和rep进程GGSCI (PONY) 20 start mgrStarting Manager as service (GGSMGR).Service 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操作首先往SourceDB中插入一行C:Documents and SettingsAdministratorsqlplusSQL*Plus: Release .0 - Production on Mon Mar 8 15:33:47 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.Enter user-name: sajet/techhitronConnected to:Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith 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 commit 2 ;Commit complete.看看TargetDB中是否有同样的记录存在呢SQL connect sajet/techgoldengaConnected.SQL select count (*) from sajet.sys_emp 2 where emp_id=10000786; COUNT(*)- 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):* Total statistics since extract started * Operations 40.00 Mapped operations 28.00 Unmapped operations 0.00 Other operations 12.00 Excluded 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 been 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 * Total inserts 3.00 Total updates 0.00 Total deletes 0.00 Total truncates 2.00 Total discards 0.00 Total 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 * No database operations have been performed.* Latest statistics since 2010-03-05 09:51:51 * Total inserts 3.00 Total updates 0.00 Total deletes 0.00 Total truncates 2.00 Total discards 0.00 Total operations 5.00Extracting from SAJET.SYS_EMP to SAJET.SYS_EMP:* Total statistics since 2010-03-05 09:51:51 * Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 2.00* Daily statistics since 2010-03-08 00:00:00 * Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 2.00* Hourly statistics since 2010-03-08 15:00:00 * Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 2.00* Latest statistics since 2010-03-05 09:51:51 * Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 2.00Extracting from SAJET.MLOG$_SYS_EMP to SAJET.MLOG$_SYS_EMP:* Total statistics since 2010-03-05 09:51:51 * Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 2.00* Daily statistics since 2010-03-08 00:00:00 * Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 2.00* Hourly statistics since 2010-03-08 15:00:00 * Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 2.00* Latest statistics since 2010-03-05 09:51:51 * Total inserts 2.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 2.00End of Statistics.相信以上的内容大家都看的懂吧。其他的DML操作在此省略。4 GoldenGate Support DDL安装1.选择一个Schema存放支援DDL的物件，本文选择GoldenGate2.编辑Globals参数文件3.关掉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.运行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 globals填入如下内容后保存GGSCHEMA goldengateD:ggssqlplus/ as sysdbaSQL*Plus: Release .0 - Production on 星期三 3月 3 10:12:06 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL marker_setup.sqlMarker setup scriptYou 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: Stop all DDL replication before starting this installation.Enter GoldenGate schema name:goldengateMarker setup 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 privileges 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 installation.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.txtUsing GOLDENGATE as a GoldenGate schema name, INITIALSETUP as a mode of installation.Working, please wait .RECYCLEBIN must be empty.This ins