-【优秀文档】oracleGoldenGate学习笔记（整理版）

1、oracle goldengate 测试文档1 oracle goldengate 介绍goldengate tdm （交易数据管理）软件是一种基于li志的结构化数据复制软件，它通 过解析源数据库在线i志或归档ii志获得数据的增删改变化，再将这些变化应用到目标数据 库，实现源数据库与目标数据库同步、双活。goldengate tdm软件可以在界构的it基础 结构（包括儿乎所有常用操作系统平台和数据库平台）z间实现人量数据亚秒-级的实时复 制，其复制过程简图如下：数据昨ul ii林系统数据库生产系统_如上图所示，goldengate tdm的数据复制过程如卜":®利川捕捉进程

2、（capture process）在源系统端读取online redo log或archive log,然后进行解 析，只提取其中数据的变化如增、删、改操作，并将相关信息转换为goldengatetdm口定 义的中间格式存放在队列文件中。再利用传送进程将队列文件通过tcp/ip传送到目标系统。 捕捉进程在每次读完log中的数据变化并在数据传送到fi标系统后，会写检查点，记录当前 完成捕捉的log位置，检查点的存在可以使捕捉进程在中止并恢复后可从检杳点位置继续复 制；目标系统接受数据变化并缓存到goldengate tdm队列当中，队列为一系列临吋存储数据变 化的文件，等待投递进程读取数据；go

3、ldengatetdm投递进程从队列中读取数据变化并创建对应的sql语句，通过数据库的木 地接口执行，提交到数据库成功后更新口己的检查点，记录已经完成复制的位置，数据的复 制过程最终完成。由此可见，goldengate tdm是一种基于软件的数据复制方式，它从数据库的口志解析 数据的变化（数据量只冇li志的四分z左右）。goldengate tdm将数据变化转化为自己 的格式，宜接通过tcp/ip网络传输，无需依赖于数据库自身的传递方式，而且可以通过高达 9:1的压缩率对数据进行压缩，可以人大降低带宽需求。在目标端，goldengatetdm可以通过交易重组，分批加载等技术手段大大加快数据投递

4、的速度和效率，降低冃标系统的资源山 用，可以在亚秒级实现人量数据的复制，并且目标端数据库是活动的goldengate tdm提供了灵活的应用方案，基于其先进、灵活的技术架构可以根据用户需求组成各种拓扑结构，如图所示:一对一-对多分层结枸舉剧库彌b库忖瞒1骥双向复制丿7wheme多对一goldengatetdm可以提供可靠的数据复制，主要体现在卜-面三点：保证事务一致性goldengatetdm在灾备数据库应用复制数据库交易的顺序与在生产中心数据库上的 顺序相同，并且按照相同的事务环境提交，确保在目标系统上数据的完整性和读一致性，为 实时查询和事务处理创造了条件。检查点机制保障数据无丢失gold

5、engatetdm的抽取和复制进程使用检杏点机制记录完成复制的位置。对于抽取进 程，其检查点记录当前已经抽取口志的位置和写队列文件的位置；对于投递进程，其检查点 记录当前读取队列文件的位置。检查点机制可以保证在系统、网络或goldengatetdm进程 故障重启后数据无丢失。可靠的数据传输机制goldcngatc tdm用应答机制传输交易数据，只冇在得到确认消息后才认为数据传输完 成，否则将白动重新传输数据，从而保证了抽取出的所有数据都能发送到备份端。数据传输 过程中支持128位加密和数据压缩功能。oracle公司的goldengate产品，可以在异构的it基础结构之间实现人址数据的秒一级 的

6、数据捕捉、转换和投递。goldengate可以支持儿乎所有常用操作系统如和数据库平台，如 下表所示：操作系统数据库ms nt, 2000, xp, linux, sun solaris,hp-ux, ibm a1x, hp nonstop, tru64, ibm z/os, os/390oracle, db2, ms sql server, mysql, enscribe, sql/mp, sql/mx, sybase, teradata,其他odbc兼容数据库2. oracle goldengate for oracle(windows 平台)安装goldengate的安装非常简单，将解压缩

7、安装文件到指定目录。比如安装文件解压缩后 的路径为d:ggs安装步骤如下：1. 进入命令行2. 切换到ggsfl录下3. 输入命令install addservice addevents4. 输入ggsci进入goldengate的命令行5. 箔了 入 create subdirsok, goldengate &装就已经完成了。实际操作如下c:documents and settingspony>d:d:>cd ggsd:ggs>install addscrvicc addcvcntsoracle goldengate messages installed succe

8、ssfully.service ggsmgr? created.install program terminated normally.d:ggs>ggscioracle 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)

9、1> create subdirscreating subdirectories under current directory d:ggsparameter filesd:ggsdirprm: createdreport filesd:ggsdirrpt: createdcheckpoint filesd:ggsdirchk: createdprocess status filesd:ggsdirpcs: createdsql script filesd:ggsdirsql: createddatabase definitions filesd:ggsdirdef: createdex

10、tract data filesd:ggsdirdat: createdtemporary filesd:ggsdirtmp: createdveridata filesd:ggsdirver: createdveridata lock filesd:ggsdirverlock: createdveridata out-of-sync filesd:ggsdirveroos: createdveridata out-of-sync xml files d:ggsdirveroosxml: createdveridata parameter filesd:ggsdirvcrparams: cre

11、atedveridata report filesd :ggsdirverreport: createdveridata status filesd :ggsdirverstatus: createdveridata trace filesd:ggsdirvertrace: createdstdout filesd:ggsdirout: created3数据库复制实施文档(dml)3.1准备工作准备两台机器，分别为sourcedb, targetdb,通过tcp/ip网络进行互连sourcedb : win xp 环境,oracle 10g 10.2.0. 1.0targetdb : win

12、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/tmp, rman,冷备等方式实现.双方db建立goldengate用户，赋予dba权限，用于goldengate连接db.双方db安装gold

13、engate,安装方式如第二章节所示 sourcedb必须开始最小附加口志模式通过以下语句查看db是否开启了最小附加日志模式sql> select supplemental_log_data_min from v$database;supplemeyes如果没有开启数据库的最小附加tl志，则通过以下语句开启sql>alter database add supplemental log data;开川表的最小附加日在，通过goldengate命令行来添加ggsci (pony) 1> dblogin userid sajet password tech successfully

14、 logged into database.ggsci (pony) 2> add trandata sajet.*第一条命令表示登录到database 第二条命令表示添加sajet用户下所有表的最小附加fl志 同样的命令开启sj, lang, smt用户下的表的最小附加日志。3.2 配置 goldengate3.2.1 配置 sourcedb 的 goldengatesourcedb端需要配置mgr进程，添加一个extract进程，和一个远端队列。1. 配置mgr参数ggsci (pony) 2> edit param mgr此时系统自动会新建一个名为mgr的参数文件，填入以下内

15、容后保存port 7809-mgr进程使用的tcp/ip端口侦听请求2. 新增一个抽取进程，在ggsci命令行小输入如下命令：ggsci (pony) 1> add extract extl9tranlog,begin nowextract added新增一个抽取进程，负责抓取sourcedb中变化的数据，基于日志方式，立即生效3. 配置抽収进程ggsci (pony) 2> edit param extl此时系统会自动新建一个名为extl的参数文件，填入以下内容后保存extract extluserid goldengate,password goldengaternithost

16、 172.17.17.61, mgrport 7809rmttrail d:ggsdirdatrldynamicresolutiongettruncatestable sajet.*;table sj.*;table smt.*;table lang.*;抽取进程名为ext2连接木机db的账号和密码远程主机地址以及服务端口号(targe(db) 远程队列的位g(targetdb) 优化参数，动态分析表结构 是否抓取truncate的数据需要抽取哪些tableo4. 新增远端队列位置ggsci (pony) 3> add rmttrail d:ggsdirdatrl extract ext

17、2 rmttrail added.新增一个远程队列，位置为在targetdb的d:ggsdirdatrl,是抽取进程extl抽取的数据队列, 注意和抽取进程中配置的rmttrail d:ggsdirdatrl 一致。开启所有进程ggsci (pony) 11> start mgrstarting manager as service cggsmgr').service startedggsci (pony) 14> start extlsending start request to manager ('ggsmgr').extract ext1 start

18、ing查看进程的运行情况ggsci (pony) 185> info allprogramstatusgrouplagtime since chkptmanagerrunningextractrunningext100:00:00 00:00:06ok, mgr进程和ext进程都已经正常运行 soucedb端的配置完成。3.2.2 配置 targetdb 的 goldengate1. 配置吨!参数ggsci (pony) 2> edit param mgr此时系统白动会新建一个mgr的参数文件，填入以下内容麻保存port 7809dynamicportlist 7840-7850-

19、mgr进程使用的tcp/ip端口侦听请求接受远端队列的端口列表。2. 新增一个复制进程ggsci (pony) 2>add replicat repl exttrail d:ggsdirdatrl, nodbcheckpoint新增一个复制进程,将抽取到队列中的文件解析后写进targetdb3 配置复制进程ggsci (pony) 2> edit param repl此时系统会白动新建一个参数文件，填入一下内容后保存replicat repluserid goldengatepassword goldengateassumetargetdefsreperror defaultdis

20、carddiscardfile 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操作复制的源表为sajct, sj, lang, smt用户下的所冇表，h标为targetdb中对用用

21、户下的所 有表开启mgr和rep进程ggsci (pony) 20> start mgrstarting manager as service ('ggsmgr')service startedggsci (pony) 21> start replsending start request to manager ( ggsmgr ).replicat rep1 starting杳看进程的运行情况ggsci (pony) 22> info allprogram status grouplagtime since chkptmanager runningrepli

22、cat running rep100:00:0000:00:07ok,mgr进程和rep进程运行正常至此，两台db的goldengate都已经配置完成。3.3测试dml操作首先往sourcedb中插入一行c:documents and settingsadministrator>sqlplussql*plus: release 10.2.0.1.0 - production on mon mar 8 15:33:47 2010copyright (c) 1982, 2005, oracle. all rights reserved.enter user-name: sajet/techh

23、itronconnected to:oracle database log enterprise edition release 1020.1.0 - mbit productionwith the partitioning, olap and data mining optionssql> insert into sajet.sys_emp values (10000786,testi testr；f；test71000021 row created.sql> commit2 ;commit complete.看看targetdb屮是否有同样的记录存在呢sql> conne

24、ct sajet/techgoldengaconnected sql> select count (*) from sajet.sys_emp2 where emp_id= 10000786;同步过來了。我们可以看看ext1进程的和关统计信息ggsci (sajct-projcct) 189> stats cxtlsending stats request to extract ext1 .start of statistics at 2010-03-08 15:47:51.ddl replication statistics (for all trails):* total st

25、atistics since extract started *operations40.00mapped operations2&00unmapped operations0.00other operations12.00excluded operations0.00output to d:ggsdirdatrl:extracting from goldengate.ggs_marker to goldengate.ggs_marker:* total statistics since 2010-03-05 09:51:51 *no database operations have

26、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 performedextracting from s

27、ajet.goldegatetest to sajet.goldegatetest:* total statistics since 2010-03-05 09:51:51 *total inserts3.00total updates0.00total deletes0.00total truncates2.00total discards0.005.00total operations* daily statistics since 2010-03-08 00:00:00 latest statistics since 2010-03-05 09:51:51 *total insertst

28、otal updatesno database operations have been performed.* hourly statistics since 2010-03-08 15:00:00 *2.000.00no database operations have been performed.* latest statistics since 2010-03-05 09:51:51 *total inserts3.00total updates0.00total deletes0.00total truncates2.00total discards0.00total operat

29、ions5.00extracting from sajet.sys_emp to sajet.sys_emp:* total statistics since 2010-03-05 09:51:51 *total inserts2.00total updates0.00total deletes0.00total discards0.00total operations2.00* daily statistics since 2010-03-08 00:00:00 *total inserts2.00total updates0.00total deletes0.00total discard

30、s0.00total operations2.00* hourly statistics since 2010-03-08 15:00:00 *total inserts2.00total updates0.00total deletes0.00total discards0.00total operations2.00total deletes().00total discards0.00total operations2.00extracting from sajet.mlog$_sys_emp to sajet.mlog$_sys_emp:* total statistics since

31、 2010-03-05 09:51:51 *total inserts2.00total updates0.00total deletes0.00total discards0.00total operations2.00* daily statistics since 2010-03-08 00:00:00 *total inserts2.00total updates0.00total deletes0.00total discards0.00total operations2.00* hourly statistics since 2010-03-08 15:00:00 *total i

32、nserts2.00total updates0.00total deletes0.00total discards0.00total operations2.00* latest statistics since 2010-03-05 09:51:51 *total inserts2.00total updates0.00total deletes0.00total discards0.00total operations2.00end of statistics.相信以上的内容人家都看的吧。具他的dml操作在此省略。4 goldengate support ddl 安装1 选择一个sche

33、ma存放支持ddl的对象，木文选择goldengate2. 编辑globals参数文件3. 关掉 oracle recycle binon oraclelogand up, system recycle bin must be disabled.to turn off the recycle bin: oracle 1 ogrelease 2 and later: set the recyclebin initialization parameter tooff. oracle 1 ogrelease 1: set the _recyclebin initialization paramete

34、r to false. 4在命令行，切换到ggs目录5. 运行 sqlplus6. run marker_setup.sql, enter goldengate schema(goldengate)7. run ddl_setup. sqlenter schema:goldengatechoose installation mode: initialsetup& run role_setup.sql9. grant role(ggs_ggsuser_role) to all goldengate extract users;10. run ddl_enable.sql两端都安装完成.具

35、体如下：ggsci (pony) 23> edit param globals填入如下内容后保存ggschema goldengated:ggs>sqlplus/ 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 log enterprise edition release 10.2.0.1.0 - productionwith th

36、e 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 scriptnote: stop all ddl replication before starting this installation.enter

37、 goldengate schema namc:goldcngatcmarker setup table script complete, running verification script.please enter the name of a schema for the goldengate database objects:setting schema name to goldengatemarker tableokmarker sequenceokscript complete.sql> ddl_setup<sqlgoldengate ddl replication s

38、etup scriptverifying 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 log and up, system recycle bin must be disabled.note: stop

39、 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 wa

40、it spooling to file ddl_setup_spool.txtusing goldengate as a goldcngatc 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:yesdd

41、l replication setup script complete, running verification script.please enter the name of a schema for the goldengate database objects: setting schema name to goldengateddlora_gettablespacesize status:line/poserrorno errorsno errorsclear_trace status:line/poserrorcreate_trace status:errorno errorsno

42、 errorstrace_put_line status:line/poserrorno errorsno errorsinitial_setup status:line/poserrorno errorsno errorsddlversionspec1fic package status:line/poserrorddlreplication package status:errorno errorsno errorsddlreplication package body status:line/poserrorno errorsno errorsddl history tableokddl

43、 history table(1)okddl dump tablesokddl dump columnsokddl dump log groupsokddl dump partitionsokddl dump primary keysokddlsequenceokggs temp colsokggs temp ukokddl trigger code status:line/poserrorno errorsno errorsddl trigger install statusokddl trigger running statusenabledstaymetadata in triggero

44、ffddl trigger sql tracing00location of ddl trace filec:oracleproduct10.2.0admintestudump/ggs_ddl_trace.loganalyzing installation status.status of ddl replicationsuccessful installation of ddl replication software componentsscript completesql> role_setup.sqlggs role setup scriptthis script will drop and recreate the role ggs_ggsuser_roleto use a different role name, quit this script and then edit the params.sql scri pt to change the gg.role parameter to the prefened name(do not run the script )you will be