本帖最后由 paulyi 于 2014-2-11 09:38 编辑

Oracle GlodenGate 安装与配置

1 oracle GlodenGate 概述 GlodenGate是oracle 公司收购glodendate的实时异构数据库数据集成产品，目前已经成功应用于美国银行，中华人民共和国海关总署，国家税务总局等。

2 实验环境 两套虚拟机Oracle 10.2.0.1+windows 2003 32 bit源端oracle数据库用户source 主机名为source目标端oracle数据库用户target 主机名为target
3 Oracle GlodenGate for windows安装 进入以下网站下载oracle glodendate安装软件http://www.oracle.com/technology/software/products/goldengate/index.html]http://www.oracle.com/technology/software/products/goldengate/index.html这里为32bit for windows平台下的安装软件GGV18162-01ForOracle10g.zip直接用unzip解压到c:\GG目录下
3.1 创建文件夹 点击运行cmd，进入到c:\gg目录C:\Documents and Settings\Oracle.XP_ONE>cd ..C:\Documents and Settings>cd ..C:\>cd ggC:\gg>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 (source) 1> create subdirsCreating subdirectories under current directory C:\ggParameter files C:\gg\dirprm: createdReport files C:\gg\dirrpt: createdCheckpoint files C:\gg\dirchk: createdProcess status files C:\gg\dirpcs: createdSQL script. files C:\gg\dirsql: createdDatabase definitions files C:\gg\dirdef: createdExtract data files C:\gg\dirdat: createdTemporary files C:\gg\dirtmp: createdVeridata files C:\gg\dirver: createdVeridata Lock files C:\gg\dirver\lock: createdVeridata Out-Of-Sync files C:\gg\dirver\oos: createdVeridata Out-Of-Sync XML files C:\gg\dirver\oosxml: createdVeridata Parameter files C:\gg\dirver\params: createdVeridata Report files C:\gg\dirver\report: createdVeridata Status files C:\gg\dirver\status: createdVeridata Trace files C:\gg\dirver\trace: createdStdout files C:\gg\dirout: createdExit;
3.2 在源库创建数据库source用户给予dba权限 Turn on supplemental logging at the database level.C:\Documents and Settings\Oracle.XP_ONE>sqlplus "/as sysdba"SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 24 00:20:38 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> create user source identified by source;User created.SQL> grant dba,connect,resource to source;Grant succeeded.SQL> sqlplus source/sourceSQL> conn source/sourceConnected.SQL> alter database add supplemental log data;Database altered.SQL> alter system switch logfile;System altered.SQL> exit;Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options
3.3 安装GlodenGate 管理进程 Install the glodendate manager processC:\gg>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 (source) 1> edit params ./GLOBAlS新建个文本文件输入以下内容MGRSERVNAME PAUL,然后保存添加管理进程到windows 服务中，以便随windows操作系统自动启动C:\gg>install addservice addeventsOracle GoldenGate messages installed successfully.Service 'PAUL' created.Install program terminated normally. 然后检查windows服务，已经存在paul这个服务了 PAUL Automatic LOCAL SYSTEM 4 GlodenGate oracle to oracle initial data load using Direct Load方法

4.1 在source端配置mgr端口和启动mgr进程
cd c:\ggc:\gg\ggsciGGSCI (source) 2> edit param mgr输入以下文件，然后按保存--GoldenGate Manager parameter fileport 7809输入7809的端口
GGSCI (source) 4> start mgrStarting Manager as service ('PAUL')...Service started.GGSCI (source) 5> info mgrManager is running (IP port source.7809).可以看到启动mgr进程成功 4.2 在source端创建table和加入初始数据 C:\gg>sqlplus source/sourceSQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 24 00:51:57 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> @demo_ora_createTable created.Table created.SQL> @demo_ora_insert1 row created.1 row created.1 row created.1 row created.Commit complete.SQL> select * from tcustmer;CUST NAME CITY ST---- ------------------------------ -------------------- --WILL BG SOFTWARE CO. SEATTLE WAJANE ROCKY FLYER INC. DENVER COSQL> select * from tcustord;CUST ORDER_DA PRODUCT_ ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID---- -------- -------- ---------- ------------- -------------- --------------WILL 94-09-30 CAR 144 17520 3 100JANE 95-11-11 PLANE 256 133300 1 100 4.3 在source端Add supplemental logging GGSCI (source) 2> dblogin userid source,password sourceSuccessfully logged into database.GGSCI (source) 3> add trandata source.tcustmerLogging of supplemental redo data enabled for table SOURCE.TCUSTMER.GGSCI (source) 4> add trandata source.tcustordLogging of supplemental redo data enabled for table SOURCE.TCUSTORD.验证是否添加supplemental logging是否成功GGSCI (source) 6> info trandata source.tcust*Logging of supplemental redo log data is enabled for table SOURCE.TCUSTMERLogging of supplemental redo log data is enabled for table SOURCE.TCUSTORD
4.4 在target端配置mgr进程和启动mgr进程 Install the glodendate manager processC:\gg>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 (source) 1> edit params ./GLOBAlS新建个文本文件输入以下内容MGRSERVNAME PAUL,然后保存添加管理进程到windows 服务中，以便随windows操作系统自动启动C:\gg>install addservice addeventsOracle GoldenGate messages installed successfully.Service 'PAUL' created.Install program terminated normally. 然后检查windows服务，已经存在paul这个服务了 PAUL Automatic LOCAL SYSTEM GGSCI (source) 2> edit param mgr输入以下文件，然后按保存--GoldenGate Manager parameter fileport 7809输入7809的端口
GGSCI (source) 4> start mgrStarting Manager as service ('PAUL')...Service started.GGSCI (source) 5> info mgrManager is running (IP port source.7809).可以看到启动mgr进程成功 4.5 在target端创建target用户和创建表 SQL> create user target identified by target;User created.SQL> grant dba,connect,resource to target;Grant succeeded.SQL>@demo_ora_create Verify the results:SQL> desc tcustmer;SQL> desc tcustord;SQL> exit
4.6 Initial data load using direct load method 在source端GGSCI (source) 7> add extract eini01,sourceistableEXTRACT added.GGSCI (source) 8> info extract *,tasksEXTRACT EINI01 Initialized 2010-01-24 01:18 Status STOPPEDCheckpoint Lag Not AvailableLog Read Checkpoint Not Available First Record Record 0Task SOURCEISTABLEGGSCI (source) 9> edit params eini01输入以下参数，并且保存---- GoldenGate Initial Data Capture-- for TCUSTMER and TCUSTORD--EXTRACT EINI01USERID source, PASSWORD sourceRMTHOST target, MGRPORT 7809RMTTASK REPLICAT, GROUP RINI01TABLE source.TCUSTMER;TABLE source.TCUSTORD;在target端GGSCI (target) 6> ADD REPLICAT RINI01,SPECIALRUNREPLICAT added.GGSCI (target) 7> INFO REPLICAT *, TASKSREPLICAT RINI01 Initialized 2010-01-24 01:23 Status STOPPEDCheckpoint Lag 00:00:00 (updated 00:00:10 ago)Log Read Checkpoint Not AvailableTask SPECIALRUNGGSCI (target) 8> edit params RINI01---- GoldenGate Initial Load Delivery--REPLICAT RINI01ASSUMETARGETDEFSUSERID target, PASSWORD targetDISCARDFILE ./dirrpt/RINI01.dsc, PURGEMAP source.*, TARGET target.*;在source端GGSCI (source) 10> START EXTRACT EINI01Sending START request to MANAGER ('PAUL') ...EXTRACT EINI01 startingview report eini01********************************************************************** ** Run Time Statistics ** ************************************************************************Report at 2010-01-24 01:28:40 (activity since 2010-01-24 01:28:27)Output to RINI01:From Table SOURCE.TCUSTMER: # inserts: 2 # updates: 0 # deletes: 0 # discards: 0From Table SOURCE.TCUSTORD: # inserts: 2 # updates: 0 # deletes: 0 # discards: 0view report rini01********************************************************************** ** Run Time Statistics ** ************************************************************************Report at 2010-01-24 01:29:00 (activity since 2010-01-24 01:28:54)From Table SOURCE.TCUSTMER to TARGET.TCUSTMER: # inserts: 2 # updates: 0 # deletes: 0 # discards: 0From Table SOURCE.TCUSTORD to TARGET.TCUSTORD: # inserts: 2 # updates: 0 # deletes: 0 # discards: 0在target端查看数据SQL> select * from tcustmer;CUST NAME CITY ST---- ------------------------------ -------------------- --WILL BG SOFTWARE CO. SEATTLE WAJANE ROCKY FLYER INC. DENVER COSQL> select * from tcustord;CUST ORDER_DA PRODUCT_ ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID---- -------- -------- ---------- ------------- -------------- --------------WILL 94-09-30 CAR 144 17520 3 100JANE 95-11-11 PLANE 256 133300 1 100可以看到从source端到目标端初始化数据成功5 配置捕捉变化数据进程 Configure Change Capture5.1 在source端ADD the Extract group GGSCI (source) 12> ADD EXTRACT EORA01,TRANLOG, BEGIN NOW, THREADS 1EXTRACT added.GGSCI (source) 13> info extract eora01EXTRACT EORA01 Initialized 2010-01-24 01:39 Status STOPPEDCheckpoint Lag 00:00:00 (updated 00:00:32 ago)Log Read Checkpoint Oracle Redo Logs 2010-01-24 01:39:33 Thread 1, Seqno 0, RBA 05.2 在source端create the extract parameter file GGSCI (source) 14> edit param eora01---- Change Capture parameter file-- TCUSTMER and TCUSTORD Changes--EXTRACT EORA01USERID source, PASSWORD sourceRMTHOST target, MGRPORT 7809RMTTRAIL ./dirdat/01TABLE source.TCUSTMER;TABLE source.TCUSTORD;5.3 在source端define the glodendate trail GGSCI (source) 15> ADD RMTTRAIL ./dirdat/01,extract eora01 MEGABYTES 5RMTTRAIL added.GGSCI (source) 16> INFO RMTTRAIL * Extract Trail: ./dirdat/01 Extract: EORA01 Seqno: 0 RBA: 0 File Size: 5M5.4 在source端Start the capture process GGSCI (source) 17> start extract eora01Sending START request to MANAGER ('PAUL') ...EXTRACT EORA01 starting5.5 在target端编辑globals参数 ggsciEDIT PARAMS ./GLOBALS加入以下参数CHECKPOINTTABLE target.ggschkpt然后退出重新登录ggsci才生效5.6 在target端add a replicat checkpoint table GGSCI (target) 1> dblogin userid target,password targetSuccessfully logged into database.GGSCI (target) 2> add checkpointtableNo checkpoint table specified, using GLOBALS specification (target.ggschkpt)...Successfully created checkpoint table TARGET.GGSCHKPT.5.7 在target端add the replicat group GGSCI (target) 3> ADD REPLICAT RORA01,EXTTRAIL ./dirdat/01REPLICAT added.5.8 Create Replicat parameter file GGSCI (target) 4> edit param rora01---- Change Delivery parameter file to apply-- TCUSTMER and TCUSTORD Changes--REPLICAT RORA01USERID target, PASSWORD targetHANDLECOLLISIONSASSUMETARGETDEFSDISCARDFILE ./dirrpt/RORA01.DSC, PURGEMAP source.tcustmer, TARGET target.tcustmer;MAP source.tcustord, TARGET target.tcustord;5.9 Start the Replicat process GGSCI (target) 6> info replicat rora01REPLICAT RORA01 Last Started 2010-01-24 01:59 Status RUNNINGCheckpoint Lag 00:00:00 (updated 00:00:03 ago)Log Read Checkpoint File ./dirdat/01000000 First Record RBA 8865.10在source端新增加些数据和target端验证数据 C:\gg>sqlplus source/sourceSQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 24 02:00:32 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> @demo_ora_misc1 row created.1 row created.1 row created.Commit complete.1 row created1 row created.1 row created.Commit complete.1 row updated.1 row updated.1 row updated.1 row updated.Commit complete.1 row deleted.1 row deleted.Commit complete.3 rows deleted.Rollback complete.SQL>查看结果：SQL> select * from tcustmer;CUST NAME CITY ST---- ------------------------------ -------------------- --DAVE DAVE'S PLANES INC. TALLAHASSEE FLBILL BILL'S USED CARS DENVER COANN ANN'S BOATS NEW YORK NYWILL BG SOFTWARE CO. SEATTLE WAJANE ROCKY FLYER INC. DENVER COSQL> select * from tcustord;CUST ORDER_DA PRODUCT_ ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID---- -------- -------- ---------- ------------- -------------- --------------BILL 95-12-31 CAR 765 14000 3 100BILL 96-01-01 TRUCK 333 25000 15 100WILL 94-09-30 CAR 144 16520 3 100GGSCI (source) 21> send extract eora01,reportSending REPORT request to EXTRACT EORA01 ...Request processed.GGSCI (source) 22> view report eora01Report at 2010-01-24 02:04:38 (activity since 2010-01-24 02:00:53)Output to ./dirdat/01:From Table SOURCE.TCUSTMER: # inserts: 3 # updates: 1 # deletes: 0 # discards: 0From Table SOURCE.TCUSTORD: # inserts: 3 # updates: 3 # deletes: 2 # discards: 0在target端验证数据是否传过来SQL> select * from tcustmer;CUST NAME CITY ST---- ------------------------------ -------------------- --WILL BG SOFTWARE CO. SEATTLE WAJANE ROCKY FLYER INC. DENVER CODAVE DAVE'S PLANES INC. TALLAHASSEE FLBILL BILL'S USED CARS DENVER COANN ANN'S BOATS NEW YORK NYSQL> select * from tcustord;CUST ORDER_DA PRODUCT_ ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID---- -------- -------- ---------- ------------- -------------- --------------WILL 94-09-30 CAR 144 16520 3 100BILL 95-12-31 CAR 765 14000 3 100BILL 96-01-01 TRUCK 333 25000 15 100GGSCI (target) 7> SEND REPLICAT RORA01,reportSending REPORT request to REPLICAT RORA01 ...Request processed.GGSCI (target) 8> view report rora01Reading ./dirdat/01000000, current RBA 2960, 12 recordsReport at 2010-01-24 02:08:17 (activity since 2010-01-24 02:01:14)From Table SOURCE.TCUSTMER to TARGET.TCUSTMER: # inserts: 3 # updates: 1 # deletes: 0 # discards: 0From Table SOURCE.TCUSTORD to TARGET.TCUSTORD: # inserts: 3 # updates: 3 # deletes: 2 # discards: 0可以看到数据已经实时传送过来 。