GoldenGate软件及工作原理介绍 高级技术顾问：

Real-Time Information 公司目标 GoldenGate为客户提供交易数据管理（Transactional Data Management）解决方案. 有效帮助客户优化数据访问性能，提高交易数据的可访问性和可用性，满足关键业务的需要 Real-Time Access to Real-Time Information Our focus is on Transactional Data Management – which means that for your critical business applications, we make sure that you are delivering highest performance, access to, and availability of the data transactions that fuel those systems, and your business. Essentially this means real-time access to real-time information: Real-Time access, meaning your critical data is accessible and available whenever you need it, 24x7 At the same time, we are also ensuring it is.. Real-Time information meaning that data is as current as possible – not 24 hours old, not even 4 hours old.

公司简介 始建于 1995 Oracle公司2009年7月收购 在北美、欧洲、亚太、拉丁美洲设有分公司 24x7 全球技术支持 历史及现状 与多家著名厂商 结为战略合作伙伴 We are a stable, growing, and successful business – more than 11 years in operation with offices worldwide. And because our technology solutions are running on our customers’ critical systems, we offer round-the-clock customer support – and that coverage is globa. Our partnerships are rapidly increasing with major technology players, including database, packaged applications, data warehousing, reporting, and infrastructure vendors. 广泛的客户基础 全球35个国家300 多客户， 超过4000多个安装

交易数据管理Transactional Data Management TDM提供异构环境下交易数据的实时捕捉、变换、投递。 TDM 具有: 实时性 秒一级延迟 异构环境支持 在不同平台和数据库环境下复制数据 以交易为单位复制 维护交易一致性 特点: 高性能 能够以低资源消耗完成每秒数千交易的复制 可扩展 开放的结构使客户适应各种异构数据平台 可靠 保证数据的连续可用 The key requirements of TDM are that it must enable real-time sharing of transactional data; it must support heterogeneous data environments; and it must be designed to ensure the integrity of the transactional data. The combination of those three components differentiate TDM from other traditional data replication and data integration offerings. GoldenGate meets those requirements, and furthermore delivers: High performance – thousands of transactions can be moved in less than a second, enabling the real time enterprise Also, highlight very low impact and overhead on IT infrastructure. Extensibility – versatile solution configurations and the ability to integrate with external applications via open APIs – this allows customers to meet unique business requirements. Reliability – the architecture of GoldenGate TDM provides that systems are continuously available as transactional data is captured, routed, and applied -- and that there is no data loss and that the referencial integrity of the data is maintained.

GoldenGate支持平台… 数据库 操作系统 抽取: Oracle DB2 OS/390 DB2 UDB Microsoft SQL Server Sybase Enscribe SQL/MP SQL/MX Teradata 投递: All listed above MySQL, Ingres and any ODBC compatible databases Windows 2000, 2003, XP Linux Sun Solaris HP-UX IBM AIX HP NonStop TRU64 IBM z/OS, LUW

GoldenGate 解决方案 高可用性与容灾 活动备份 无宕机运行 Active-Active 层次化企业数据 实时数据集成 数据仓库实时数据供给 实时报表 数据整合 (This slide should only take a minute or two to cover, since the following slides provide more detail on each of these. Depending on the prospect’s area(s) of interest, just let them know that these are the different solutions and in the following slides, focus on those most relevant.) GoldenGate focuses on two primary solution areas: High Availability and Disaster Tolerance, and Real-Time Data Integration. Within High Avail and Disaster Tolerance, we offer: Live Standby for an immediate fail-over solution that can later re-synchronize with your primary source Zero-Downtime Operations that eliminates downtime for planned outages involving migrations, upgrades, and ongoing maintenance Database Tiering which gives you high performance and unlimited scalability Active-Active solutions for transaction load distribution between two online systems Within Real-Time Data Integration, we offer: Real-time Data Warehousing which gives you real-time data feeds Transactional Data Integration where you need to eliminate batch processing of data between systems Live Reporting for integrating real-time data with reporting applications, without burdening product systems

Real Time Threshold 实时性要求 实时数据 实时访问 高可用性 数据整合 灾难恢复 灾难容忍 无间断运行 灾难恢复 灾难容忍 无间断运行 磁带备份 磁盘镜像 块复制 热备份 Active-Active 硬件 存储 数据库 应用 批处理 即时复制 近似实时复制 实时复制 脚本 ETL工具加脚本 EAI和ETL工具加脚本 GoldenGate TDM Weeks Days Hours Minutes / Seconds Sub-seconds 数据整合 物理结构 数据 交易

为什么选择 GoldenGate TDM? 消除批处理窗口 方便实现整合流程 低资源占用 实时获取数据 基于日志的复制 对源系统影响小 只捕捉数据变化 降低系统负荷 无需额外编程 保证交易完整性和一致性 可以保证在各种平台下数据的完整性和一致性 消除批处理窗口 方便实现整合流程 低资源占用

Transactional Data Management (TDM) Software Platform GoldenGate TDM软件技术概览 Transactional Data Management (TDM) Software Platform

TDM 工作原理: 模块化 “积木” 网络 源数据库 目的数据库 Capture: 实时读取交易日志捕捉数据变化并可实现过滤. 队列文件: 暂存数据变化. 传输: 数据经过压缩和加密传送到目的地. Delivery:执行所需的数据变化，然后将数据变化提交到目的库. Capture Deliver GoldenGate’s TDM platform consists of decoupled modules that are combined to create the best possible solution for your business requirements. At the source database(s): GoldenGate’s CAPTURE module reads data transactions as they occur, by reading the native transaction log, typically the redo log. GoldenGate only moves changed, committed transactional data, which is only a % of all transactions – therefore operating with extremely high performance and very low impact on the data infrastructure. Filtering at the source or target can be done, selectivity can be done at table, column and/or row level. Transformations can be applied at the capture or deliver stages. Advanced queuing (trail files): To move transactional data efficiently and accurately across systems, GoldenGate converts the captured data into a universal data format in “trail” files. With both source and target trail files, GoldenGate’s unique architecture eliminates any single point of failure and ensures data integrity is maintained – even in the event of a system error or outage. Routing: Data is sent via TCP/IP to the target systems. Data compression and encryption are supported. Thousands of transactions can be moved per second, without distance limitations. Delivery to target database(s): The transactional data is delivered and applied to the designated target systems using native SQL calls. Bi-directional: In bi-directional configurations/solutions, this process runs the same in reverse, to concurrently synchronize data between the source and target systems. Manager modules perform administrative functions at each node. Source Trail Target Trail 网络 Deliver Target Trail Capture Source Trail 源数据库 目的数据库 双向复制

逻辑数据复制的几个重要问题（一） 数据变化来源 读取在线日志或归档日志 在信息不足时与数据库交互 只复制提交交易 如何标志记录的唯一性？ 数据字典信息 查询日志不足信息，例如大对象 只复制提交交易 如何标志记录的唯一性？ Rowid 逻辑复制源和目标的rowid是不一致的，除非自行为两端记录建立额外的rowid map 主键 实际应用中主要业务表均建立有主键 唯一索引也可作为唯一标识 无主键表可使用全部列作为唯一标识

逻辑数据复制的几个问题（二） oracle数据库附加日志 缺省状况下只记录变化记录的rowid及对应变化（前影像、后影像） 为了能够在目标投递到对应的记录，需在日志中记录主键或唯一索引 数据库级附加日志 SUPPLEMENTAL_LOG_DATA_MIN （最小附加日志） SUPPLEMENTAL_LOG_DATA_PK/UI SUPPLEMENTAL_LOG_DATA_FK/ALL（10g） 表级附加日志 – GoldenGate建议方式 sqlplus> alter table add supplemental log group… Ggsci> add trandata ctais2.mytable //自动寻找PK/UI Dba_log_groups和dba_log_group_columns 每个log group不能超过32列，超过需要人工分组

逻辑数据复制的几个问题（三） 目标端使用附加日志还原sql 根据附加日志中的主键信息构筑where条件 如果没有附加日志或者没有足够的列则无法执行update和delete操作，而insert不受影响 主键变化要重新添加表的附加日志 无主键表则列的增删均要重新添加表的附加日志 CREATE TABLE tcustmer ( cust_code VARCHAR2(4), name VARCHAR2(30), city VARCHAR2(20), state CHAR(2), PRIMARY KEY (cust_code) USING INDEX ); For Record: (‘a’,’a’,’a’,’a’) Update tcustmer set name=‘b’; Oracle附加日志会记录cust_code列的值’a’到日志中； 目标端根据cust_code=‘a’构筑update 的sql语句； 如果两端主键变为city，而没有重新添加附加日志，则目标端会根据自己的主键city建立附加日志，此时日志中没有该字段值，导致目标update失败 如果修改表但没有修改主键，则附加日志不受影响

逻辑数据复制的几个问题（四） 如何将数据装载到目标库？ 常规加载 直接加载 GoldenGate采用常规加载 通过SQL装载 通过数据块装载 不利用空闲存储空间 不检查外键等约束 装载速度较快 GoldenGate采用常规加载 保证数据安全和一致性 有效提高性能的选项 合并小交易 拆分大交易 批处理的加速

灵活的拓扑结构 单向复制 报表、灾备 双向复制 立即接管 点对点 负载均衡, 高可用性及容灾 广播复制 数据分发 集中复制 数据仓库 多级复制 层次化企业数据 GoldenGate’s modular architecture allows a number of data movement configurations to be supported. Here are popular examples.

Data Pumps 与一对多复制 Database Transaction Logs Extract Process Trail

GoldenGate Veridata™ 快速数据对比方案 能够快速设别和报告在线数据的差异 用途: 降低数据差异带来的风险 简便快捷的对比 无需影响业务 关键特性: 支持大数据量对比 支持在线状态的数据对比 优秀的对比效率和速度 可执行选择性对比 可自动执行无需人工干预 灵活的报表

GoldenGate Director™ 能够使客户定义、配置和管理GoldenGate TDM 各节点和进程，监控运行状态 优化系统管理 关键特性: 集中管理多个GG进程 丰富的客户端和web界面提高可用性 提供报警以及与第三方软件集成的接口 实时获取GG进程信息 对原系统无影响 GoldenGate Director is a centralized server-based graphical enterprise application that offers an intuitive way to define, configure, manage, and report on GoldenGate transactional data management processes. Director is a value added module to centralize management and improve productivity. It has both Web and Client GUIs GoldenGate Director supports all platforms and databases supported by GoldenGate. (The Director Server requires an Oracle or MySQL database for host information and consolidation purposes)

典型案例 希望goldengate的这些优点能给业务带来帮助

案例一：中华人民共和国海关总署 远距离容灾 低带宽 低延时 可随时接管的双业务中心 快速业务切换 异构复制(内部)

案例二：国家体育总局体彩中心 N+1模式容灾 各省市集中备份 远距离 低带宽 H2000 广东建立容灾中心 设想作对等复制，负载均衡 切换特别方便，改成轮换制，一方面做容灾演练，另一方面培训人 利用了goldengate的对等复制，以及高效的网络利用率 远距离容灾 -近2000km 低带宽- 2M 低延时- <3S 可同时开展业务的双中心 快速业务切换 - 3分钟实现 Superdume 16cpu 单机 海关总署数据量: 20G/day 总数据量 : 1T 备份出来：***** , p690 16 ---- p690 16 , 数据库重起一次需要时间：　　crash后重起一次的时间： Ibm工商移行北京－－－上海 , tprc ，切换一次１００分钟，帮问一种

案例三：国家税务总局 N+1集中容灾 远距离 低带宽

案例三：国家税务总局-各试点 涉及应用系统： 一期：CTAIS2系统 二期：JH系统, FWSK系统 远程异地备份 同城备份 生产中心 南海灾备中心 涉及应用系统： 一期：CTAIS2系统 二期：JH系统, FWSK系统 同城备份 异址灾备中心

GoldenGate安装及其组件 2017年3月13日星期一

系统需求 为获取高可用性，建议安装于共享阵列，可以随集群软件切换到备用节点 复制软件只需不到200M空间大小，考虑到需要缓存队列数据，建议保留相当于数据库归档日志量3-7天的存储空间 复制软件安装目录可位于任何位置 复制软件可使用现有系统用户或者建立专门系统用户，需满足如下条件： 该用户具有为Oracle安装用户组成员，环境变量与Oracle运行用户一致，可访问Oracle数据库 其内存限制应当满足数据复制要求，可以通过ulimit –a输出察看信息，具体配置在/etc/security/limits 文件中。 GoldenGate licenses the use of software components including Extractor and Replicator via an encrypted password file. Licenses are purchased for each system (NSK, NT, Windows, UNIX, Linux, etc.) and each software component. Licenses expire annually, GoldenGate will email a new license file to the corporate contact. Upon receipt of a new license, the ASCII file must be placed into the GoldenGate object directory as a file named “pw”. To receive a demo, or new license contact your sales representative, or email contracts@goldengate.com.

双机环境下的理想配置方案 正常运行状态 备机接管状态 GoldenGate licenses the use of software components including Extractor and Replicator via an encrypted password file. Licenses are purchased for each system (NSK, NT, Windows, UNIX, Linux, etc.) and each software component. Licenses expire annually, GoldenGate will email a new license file to the corporate contact. Upon receipt of a new license, the ASCII file must be placed into the GoldenGate object directory as a file named “pw”. To receive a demo, or new license contact your sales representative, or email contracts@goldengate.com. 正常运行状态 备机接管状态

安装步骤 Unix下的安装步骤 ftp .gz file to GoldenGate安装目录 gzip –d *.gz tar –xvf *.tar Implement the GoldenGate Password license GGSCI> CREATE SUBDIRS 许可文件pw位于 GoldenGate安装目录下 V10.4版本后，不再需要许可文件

复制用户可以使用现有或者新建用户，DML复制要求权限如下： 数据库需求 复制用户可以使用现有或者新建用户，DML复制要求权限如下： 源端：CONNECT/ALTER ANY TABLE/ALTER SESSION/CREATE SESSION /FLASHBACK ANY TABLE/SELECT ANY DICTIONARY/SELECT ANY TABLE/"RESOURCE“ 目标端在源端基础上加以下权限：INSERT ANY TABLE/DELETE ANY TABLE/UPDATE ANY TABLE; 需要打开数据库级最小附加日志 select SUPPLEMENTAL_LOG_DATA_MIN from v$database; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 针对所有复制表打开表级附加日志 GGSCI > add trandata <table name> select * from user_log_group_columns where table_name=' <table name> '; 超过32列无主键表需要手工添加附加日志： Alter table <table> add supplemental log group <group> (column,..) always; GoldenGate licenses the use of software components including Extractor and Replicator via an encrypted password file. Licenses are purchased for each system (NSK, NT, Windows, UNIX, Linux, etc.) and each software component. Licenses expire annually, GoldenGate will email a new license file to the corporate contact. Upon receipt of a new license, the ASCII file must be placed into the GoldenGate object directory as a file named “pw”. To receive a demo, or new license contact your sales representative, or email contracts@goldengate.com.

GoldenGate组件与目录结构 基本组件 Manager 管理进程，管理其它进程活动 Extract 抽取进程，抽取日志（队列）获取数据变化 Replicat 投递进程，向目标数据库投递数据 Collector （server进程），收集由源端extract发送过来的数据 Trail 队列，以通用格式存贮的数据变化 Checkpoint 检查点, dirchk下，记录各进程读写的位置 参数文件，dirprm下，定义各进程的相关参数 文件目录 参数文件目录 dirprm 队列文件目录 dirdat 检查点文件目录 dirchk 进程文件目录 dirpcs 报告文件目录 dirrpt 临时目录 dirtmp 其它目录