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可以支持几乎所有常用操作系统如和数据库平台，如下表所示：
2．Oracle GoldenGate For Oracle(windows 平台) 安装
GoldenGate 的安装非常简单，将解压缩安装文件到指定目录。比如安装文件解压缩后的路径为d:\ggs
安装步骤如下:
1.进入命令行
2.切换到ggs目录下
3.输入命令install addservice addevents
4.输入ggsci进入GoldenGate的命令行
5.输入create subdirs
OK，GoldenGate安装就已经完成了。
实际操作如下
C:\Documents and Settings\PONY>d:
D:\>cd ggs
D:\ggs>install addservice addevents
Oracle GoldenGate messages installed successfully.
Service 'GGSMGR' created.
Install program terminated normally.
D:\ggs>ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Windows (optimized), Oracle 10 on Sep 18 2009 15:54:55
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
GGSCI (PONY) 1> create subdirs
Creating subdirectories under current directory D:\ggs
Parameter files D:\ggs\dirprm: created
Report files D:\ggs\dirrpt: created Checkpoint files D:\ggs\dirchk: created Process status files D:\ggs\dirpcs: created
SQL script files D:\ggs\dirsql: created Database definitions files D:\ggs\dirdef: created
Extract data files D:\ggs\dirdat: created Temporary files D:\ggs\dirtmp: created Veridata files D:\ggs\dirver: created Veridata Lock files D:\ggs\dirver\lock: created Veridata Out-Of-Sync files D:\ggs\dirver\oos: created Veridata Out-Of-Sync XML files D:\ggs\dirver\oosxml: created Veridata Parameter files D:\ggs\dirver\params: created Veridata Report files D:\ggs\dirver\report: created Veridata Status files D:\ggs\dirver\status: created Veridata Trace files D:\ggs\dirver\trace: created Stdout files D:\ggs\dirout: created
3．数据库复制实施文档（DML）
3.1准备工作
◆准备两台机器，分别为SourceDB, TargetDB，通过TCP/IP网络进行互连
SourceDB ：WIN XP环境，Oracle 10g 10.2.0.1.0
TargetDB ：WIN 2003 环境 Oracle 10g 10.2.0.1.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_DA TA_MIN from v$database;
SUPPLEME
--------
YES
如果没有开启数据库的最小附加日志，则通过以下语句开启
SQL>alter database add supplemental log data;
◆开启表的最小附加日志，通过GoldenGate命令行来添加
GGSCI (PONY) 1> dblogin userid sajet password tech
Successfully logged into database.
GGSCI (PONY) 2> add trandata sajet.*
第一条命令表示登录到Database
第二条命令表示添加Sajet用户下所有表的最小附加日志
同样的命令开启SJ,LANG,SMT用户下的表的最小附加日志。
3.2配置GoldenGate
3.2.1配置SourceDB的GoldenGate
SourceDB端需要配置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 ext1
userid goldengate,password goldengate
rmthost 172.17.17.61, mgrport 7809
rmttrail d:\ggs\dirdat\r1
dynamicresolution
gettruncates
table sajet.*;
table sj.*;
table smt.*;
table lang.*;
--
抽取进程名为ext2
连接本机DB的账号和密码
远程主机地址以及服务端口号（TargetDB）
远程队列的位置(TargetDB)
优化参数，动态分析表结构
是否抓取Truncate的数据
需要抽取哪些table。
4.新增远端队列位置
GGSCI (PONY) 3> add rmttrail d:\ggs\dirdat\r1 extract ext2
RMTTRAIL added.
--
新增一个远程队列，位置为在TargetDB的d:\ggs\dirdat\r1，是抽取进程ext1抽取的数据队列，注意和抽取进程中配置的rmttrail d:\ggs\dirdat\r1一致。
开启所有进程
GGSCI (PONY) 11> start mgr
Starting Manager as service ('GGSMGR')...
Service started.
GGSCI (PONY) 14> start ext1
Sending START request to MANAGER ('GGSMGR') ...
EXTRACT EXT1 starting
查看进程的运行情况
GGSCI (PONY) 185> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:06
OK，Mgr进程和EXT进程都已经正常运行
SouceDB端的配置完成。
3.2.2 配置TargetDB的GoldenGate
1.配置mgr参数
GGSCI (PONY) 2> edit param mgr
此时系统自动会新建一个mgr的参数文件，填入以下内容后保存
PORT 7809
DYNAMICPORTLIST 7840-7850
--mgr进程使用的TCP/IP端口侦听请求
接受远端队列的端口列表。
2.新增一个复制进程
GGSCI (PONY) 2>ADD replicat rep1 EXTTRAIL d:\ggs\dirdat\r1, nodbcheckpoint --新增一个复制进程，将抽取到队列中的文件解析后写进TargetDB
3.配置复制进程
GGSCI (PONY) 2> edit param rep1
此时系统会自动新建一个参数文件，填入一下内容后保存
replicat rep1
userid goldengate,password goldengate
assumetargetdefs
reperror default,discard
discardfile D:\oradata\discard\repsz.dsc,append,megabytes 100
gettruncate
map 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 mgr
Starting Manager as service ('GGSMGR')...
Service started.
GGSCI (PONY) 21> start rep1
Sending START request to MANAGER ('GGSMGR') ...
REPLICAT REP1 starting
查看进程的运行情况
GGSCI (PONY) 22> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:07
OK,MGR进程和REP进程运行正常
至此，两台DB的GoldenGate都已经配置完成。
3.3 测试DML操作
首先往SourceDB中插入一行
C:\Documents and Settings\Administrator>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 8 15:33:47 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sajet/tech@hitron
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> insert into sajet.sys_emp values (10000786,'TEST1','TEST1','','TEST','10000 27','','Y','',0,'',0,'','')
2 ;
1 row created.
SQL> commit
2 ;
Commit complete.
看看TargetDB中是否有同样的记录存在呢
SQL> connect sajet/tech@goldenga
Connected.
SQL> select count (*) from sajet.sys_emp
2 where emp_id=10000786;
COUNT(*)
----------
1
同步过来了。
我们可以看看EXT1进程的相关统计信息
GGSCI (sajet-project) 189> stats ext1
Sending 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.00
Output to d:\ggs\dirdat\r1:
Extracting from GOLDENGATE.GGS_MARKER to GOLDENGA TE.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.GOLDEGA TETEST 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.00 Extracting 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.00
Extracting 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.00
End of Statistics.
相信以上的内容大家都看的懂吧。
其他的DML操作在此省略。
4 GoldenGate Support DDL安装
1.选择一个Schema存放支持DDL的对象，本文选择GoldenGate
2.编辑Globals参数文件
3.关掉Oracle Recycle bin
On Oracle10g and 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.运行SQLPLUS
6. run marker_setup.sql, enter GoldenGate schema(goldengate)
7. run ddl_setup.sql
enter schema:goldengate
choose installation mode: INITIALSETUP
8. run role_setup.sql
9. grant role(GGS_GGSUSER_ROLE) to all GoldenGate Extract users;
10. run ddl_enable.sql
两端都安装完成.
具体如下：
GGSCI (PONY) 23> edit param globals
填入如下内容后保存
GGSCHEMA goldengate
D:\ggs>sqlplus/ as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三3月3 10:12:06 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @marker_setup.sql
Marker setup script
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: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:goldengate
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGA TE
MARKER TABLE
--------------------------------------------------------------
OK
MARKER SEQUENCE
--------------------------------------------------------------
OK
Script complete.
SQL> @ddl_setup.sql
GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
You will be prompted for the name of a schema for the GoldenGate database object s.
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:goldengate
You will be prompted for the mode of installation.
To install or reinstall DDL replication, enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
Enter mode of installation:initialsetup
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Using GOLDENGATE as a GoldenGate schema name, INITIALSETUP as a mode of installa tion.
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:yes
DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGA TE
DDLORA_GETTABLESPACESIZE STATUS:
Line/pos
--------------------------------------------------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
CLEAR_TRACE STATUS:
Line/pos
--------------------------------------------------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
CREATE_TRACE STA TUS:
Line/pos
--------------------------------------------------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
TRACE_PUT_LINE STATUS:
Line/pos
--------------------------------------------------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
INITIAL_SETUP STATUS:
Line/pos
--------------------------------------------------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos
--------------------------------------------------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos
--------------------------------------------------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
DDLREPLICATION PACKAGE BODY STA TUS:
Line/pos
--------------------------------------------------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
DDL HISTORY TABLE
----------------------------------------------------------------------
OK
DDL HISTORY TABLE(1)
----------------------------------------------------------------------
OK
DDL DUMP TABLES
----------------------------------------------------------------------
OK
DDL DUMP COLUMNS
----------------------------------------------------------------------
OK
DDL DUMP LOG GROUPS
----------------------------------------------------------------------
OK
DDL DUMP PARTITIONS
----------------------------------------------------------------------
OK
DDL DUMP PRIMARY KEYS
----------------------------------------------------------------------
OK
DDL SEQUENCE
----------------------------------------------------------------------
OK
GGS_TEMP_COLS
----------------------------------------------------------------------
OK
GGS_TEMP_UK
----------------------------------------------------------------------
OK
DDL TRIGGER CODE STA TUS:
Line/pos
--------------------------------------------------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors
DDL TRIGGER INSTALL STA TUS
----------------------------------------------------------------------
OK
DDL TRIGGER RUNNING STATUS
-------------------------------------------------------------------------------- ENABLED
STAYMETADATA IN TRIGGER
-------------------------------------------------------------------------------- OFF
DDL TRIGGER SQL TRACING
--------------------------------------------------------------------------------
DDL TRIGGER TRACE LEVEL
--------------------------------------------------------------------------------
LOCATION OF DDL TRACE FILE
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ADMIN\TEST\UDUMP/ggs_ddl_trace.log
Analyzing installation status...
STA TUS OF DDL REPLICATION
--------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL> @role_setup.sql
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql scri
pt to change the gg_role parameter to the preferred name. (Do not run the script .)
You will be prompted for the name of a schema for the GoldenGate database object s.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:goldengate
已写入file role_setup_set.txt
PL/SQL 过程已成功完成。
Role setup script complete