1、GoldenGate10.4+LINUX+WINDOWS+ORACLE 10g測試報告2010-9-2目錄1測試環境說明31硬件：32軟件：33環境規劃32源端和目標端通信配置41源端通信配置：42目標端通信配置63通信測試73源端Goldengate安裝84配置DML11源端11目標端Goldengate安裝及配置17測試DML205配置DDL24源端24目標端35測試DDL測試371 測試環境說明1 硬件：筆記本：IBM T60（Genuine Intel(R) CPU T2400 1.83GHz,2GB內存）2 軟件：操作系統：源端：Oracle Enterpries Linux 5.4

2、目標端：Windows Server 2003 Enterprise Edition數據庫版本：源端：Oracle 10.2.0.1目標端：Oracle 10.2.0.1GoldenGate：源端：GoldenGate 10.4目標端：GoldenGate 10.4Goldengate 下載地址： 虛擬機：Vmware workstation 6.03 環境規劃主機名操作系統IP地址用戶密碼數據庫版本SID_NAMEDB_NAME源端dbtest1linux192.168.10.10Oracleoracle10.2.0.1.0test1test1目標端ad123456windows192.16

3、8.10.11administrator12312310.2.0.1.0testtest本機Wallsen-pcWindow xp192.168.10.12源端環境準備安裝Oracle Enterpries Linux 5.4（略）安裝Oracle 10.2.0.1（略）目標端環境準備安裝Windows Server 2003 Enterprise Edition（略）安裝Oracle 10.2.0.1（略）安裝The Microsoft Visual C+ 2005（略）2 源端和目標端通信配置1源端通信配置：rootdbtest1 admin# cat listener.ora# list

4、ener.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) )LISTENER = (DESCRIPTION_LIST = (

5、DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1) (ADDRESS = (PROTOCOL = TCP)(HOST = dbtest1)(PORT = 1521) )rootdbtest1 admin# cat tnsnames.ora# tnsnames.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.TEST1 = (D

6、ESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbtest1)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test1) ) )TEST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ad123456)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test) ) )EXTPROC_CONNECTION_DATA =

7、 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) )2目標端通信配置listener.ora# listener.ora Network Configuration File: C:oracleproduct10.2.0db_1networkadminlistener.ora# Generated by Oracle configuration tools.SID_LIST_LIS

8、TENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = C:oracleproduct10.2.0db_1) (PROGRAM = extproc) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1) (ADDRESS = (PROTOCOL = TCP)(HOST = ad123456)(PORT = 1521) ) )tnsnames.ora# tnsnames.ora N

9、etwork Configuration File: C:oracleproduct10.2.0db_1networkadmintnsnames.ora# Generated by Oracle configuration tools.TEST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ad123456)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test) ) )TEST1 = (DESCRIPTION = (ADDRESS = (PROTO

10、COL = TCP)(HOST = dbtest1)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test1) ) )EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )3通信測試oracledbtest1 $ tnsping test1TN

11、S Ping Utility for Linux: Version 10.2.0.1.0 - Production on 02-SEP-2010 17:06:17Copyright (c) 1997, 2005, Oracle. All rights reserved.Used parameter files:/oracle/product/10.2.0/db_1/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (P

12、ROTOCOL = TCP)(HOST = dbtest1)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test1)OK (20 msec)oracledbtest1 $ tnsping testTNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 02-SEP-2010 17:06:19Copyright (c) 1997, 2005, Oracle. All rights reserved.Used parameter files

13、:/oracle/product/10.2.0/db_1/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ad123456)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test)OK (10 msec)3 源端Goldengate安裝oracledbtest1 gg$ ./ggsc

14、i ./ggsci: error while loading shared libraries: /oracle/product/10.2.0/db_1/lib/libnnz10.so: cannot restore segment prot after reloc: Permission denied#報錯是因為linux開啟了selinux，需要關閉selinux，關閉之后如還是報錯，再執行如下命令#oracledbtest1 gg$ chcon -t texrel_shlib_t /oracle/product/10.2.0/db_1/lib/libnnz10.sooracledbtes

15、t1 gg$ ./ggsci Oracle GoldenGate Command Interpreter for OracleVersion 10.4.0.19 Build 002Linux, x86, 32bit (optimized), Oracle 10 on Sep 25 2009 12:49:31Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.GGSCI (dbtest1) 1創建相關目錄GGSCI (dbtest1) 1 create subdirsCreating subdir

16、ectories under current directory /ggParameter files /gg/dirprm: createdReport files /gg/dirrpt: createdCheckpoint files /gg/dirchk: createdProcess status files /gg/dirpcs: createdSQL script files /gg/dirsql: createdDatabase definitions files /gg/dirdef: createdExtract data files /gg/dirdat: createdT

17、emporary files /gg/dirtmp: createdVeridata files /gg/dirver: createdVeridata Lock files /gg/dirver/lock: createdVeridata Out-Of-Sync files /gg/dirver/oos: createdVeridata Out-Of-Sync XML files /gg/dirver/oosxml: createdVeridata Parameter files /gg/dirver/params: createdVeridata Report files /gg/dirv

18、er/report: createdVeridata Status files /gg/dirver/status: createdVeridata Trace files /gg/dirver/trace: createdStdout files /gg/dirout: created編輯管理進程GGSCI (dbtest1) 2 edit params mgr#進入vi編輯界面，輸入port 7809#保存退出GGSCI (dbtest1) 3 helpGGSCI Command SummarySUBDIRS CREATE SUBDIRSER INFO ER, KILL ER, LAG E

19、R, SEND ER, STATUS ER, START ER, STATS ER, STOP EREXTRACT ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND, START, STATS, STATUS, STOPEXTTRAIL ADD, ALTER, DELETE, INFOGGSEVT VIEWMANAGER INFO, REFRESH, SEND, START, STOP, STATUSMARKER INFOPARAMS EDIT, VIEWREPLICAT ADD, ALTER, CLEANUP, DELETE, INFO,

20、KILL, LAG, SEND, START, STATS, STATUS, STOPREPORT VIEWRMTTRAIL ADD, ALTER, DELETE, INFOTRACETABLE ADD, DELETE, INFOTRANDATA ADD, DELETE, INFODatabase DBLOGIN, LIST TABLES, ENCRYPT PASSWORDDDL DUMPDDLCHECKPOINTTABLE ADD CHECKPOINTTABLE, DELETE CHECKPOINTTABLE, CLEANUP CHECKPOINTTABLE, INFO CHECKPOINT

21、TABLEMiscellaneous FC, HELP, HISTORY, INFO ALL, INFO MARKER, OBEY, SET, SHELL, SHOW, VERSIONS, ! For help on a specific command, type HELP .4 配置DML源端數據庫創建用戶SQLCREATE USER goldengate IDENTIFIED BY goldengate default tablespace users temporary tablespace temp quota unlimited on users;賦予角色GRANT CONNECT

22、 TO goldengate;GRANT ALTER ANY TABLE TO goldengate;GRANT ALTER SESSION TO goldengate;GRANT CREATE SESSION TO goldengate;GRANT FLASHBACK ANY TABLE TO goldengate;GRANT SELECT ANY DICTIONARY TO goldengate;GRANT SELECT ANY TABLE TO goldengate;GRANT RESOURCE TO goldengate;GRANT drop ANY TABLE TO goldenga

23、te;GRANT DBA TO goldengate;創建表#以goldengate用戶登錄數據庫，創建test01表sqlplus goldengate/goldengatetestcreate table test01 (NAME VARCHAR2(10),ADDRESS VARCHAR2(10);開啟最小附加日志查看是否開啟最小附加日志SQL select SUPPLEMENTAL_LOG_DATA_MIN from v$database;SUPPLEMENTAL_LOG-NO顯示為NO表明沒有開啟最小附加日志，使用下面命令開啟SQLalter database add suppleme

24、ntal log data;連接到數據庫GGSCI (dbtest1) 1 dblogin userid goldengate,password goldengate Successfully logged into database添加goldengate用戶下所有表的最小附加日志GGSCI (dbtest1) 3 add trandata goldengate.* 2010-09-01 03:19:47 GGS WARNING 109 No unique key is defined for table TEST01. All viable columns will be used to

25、represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.Logging of supplemental redo data enabled for table GOLDENGATE.TEST01.新增抽取進程GGSCI (dbtest1) 4 add extract ext1,tranlog,begin nowEXTRACT added.-新增一個抽取進程，負責抓取SourceDB中變化的數據，基于日志方式，立即生效=補充=GGSCI add extract , TRA

26、NLOG, BEGIN NOW, THREADS 其中group name表示這個Extract group的名字TRANLOG 表示使用事務日志的方式BEGIN NOW 表示從現在開始,你也可以指定一個具體的開始時間THREADS 表示從哪個instance上運行,只對RAC環境有用,單機環境比需要指定該Option=配置抽取進程edit param ext1 #(1為數字)#extract ext1 userid goldengate,password goldengatermthost 192.168.10.11, mgrport 7809rmttrail c:ggsdirdatr1dy

27、namicresolutiongettruncatestable goldengate.*;=抽取進程名為 ext1連接本機DB的賬號和密碼遠程主機地址以及服務端口號（TargetDB）遠程隊列的位置(TargetDB)優化參數，動態分析表結構是否抓取Truncate的數據需要抽取哪些table。=新增遠端隊列位置GGSCI (dbtest1) 5 add rmttrail c:ggsdirdatr1 extract ext1RMTTRAIL added.新增一個遠程隊列，位置為在TargetDB的d:ggsdirdatr1，是抽取進程ext1抽取的數據隊列，注意和抽取進程中配置的rmttra

28、il d:ggsdirdatr1一致。=開啟管理進程GGSCI (dbtest1) 6 start mgrManager started.啟動抽取進程GGSCI (dbtest1) 7 start ext1Sending START request to MANAGER .EXTRACT EXT1 starting查看狀態GGSCI (dbtest1) 8 info allProgram Status Group Lag Time Since ChkptMANAGER RUNNING EXTRACT RUNNING EXT1 00:00:00 01:22:54 查看日志GGSCI (dbtes

29、t1) 9 view report ext1* Oracle GoldenGate Capture for Oracle Version 10.4.0.19 Build 002 Linux, x86, 32bit (optimized), Oracle 10 on Sep 25 2009 12:56:40 Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved. Starting at 2010-09-01 04:45:28*Operating System Version:LinuxVersion

30、 #1 SMP Thu Sep 3 02:16:47 EDT 2009, Release 2.6.18-164.el5Node: dbtest1Machine: i686 soft limit hard limitAddress Space Size : unlimited unlimitedHeap Size : unlimited unlimitedFile Size : unlimited unlimitedCPU Time : unlimited unlimitedProcess id: 20932Description: * Running with the following pa

31、rameters *extract ext1 userid goldengate,password *rmthost 192.168.10.11, mgrport 7809rmttrail c:ggsdirdatr1dynamicresolutiongettruncatestable goldengate.*;CACHEMGR virtual memory values (may have been adjusted)CACHEBUFFERSIZE: 64KCACHESIZE: 2GCACHEBUFFERSIZE (soft max): 4MCACHEPAGEOUTSIZE (normal):

32、 4MPROCESS VM AVAIL FROM OS (min): 2.91GCACHESIZEMAX (strict force to disk): 2.67GDatabase Version:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for Linux: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 -

33、 ProductionDatabase Language and Character Set:NLS_LANG = american_america.ZHS16gbk NLS_LANGUAGE = AMERICAN NLS_TERRITORY = AMERICA NLS_CHARACTERSET = WE8ISO8859P1 Warning: your NLS_LANG setting does not match database server language setting.Please refer to user manual for more information.2010-09-

34、01 04:45:29 GGS WARNING Z1-078 No valid default archive log destination directory found for thread 1.2010-09-01 04:45:31 GGS WARNING 150 TCP/IP error 111 (Connection refused).2010-09-01 04:45:41 GGS WARNING 150 TCP/IP error 111 (Connection refused).目標端Goldengate安裝及配置windowns 2003安裝gg=首先安裝Microsoft V

35、isual C+ 2005 Redistributable=安裝GoldengateC:ggsinstall addservice addeventsOracle GoldenGate messages installed successfully.Service GGSMGR created.Install program terminated normally.數據庫創建用戶SQLCREATE USER goldengate IDENTIFIED BY goldengate default tablespace users temporary tablespace temp quota u

36、nlimited on users;賦予用戶角色GRANT CONNECT TO goldengate;GRANT ALTER ANY TABLE TO goldengate;GRANT ALTER SESSION TO goldengate;GRANT CREATE SESSION TO goldengate;GRANT FLASHBACK ANY TABLE TO goldengate;GRANT SELECT ANY DICTIONARY TO goldengate;GRANT SELECT ANY TABLE TO goldengate;GRANT RESOURCE TO golden

37、gate;GRANT drop ANY TABLE TO goldengate;GRANT DBA TO goldengate;創建表sqlplus goldengate/goldengatetestcreate table test01 (NAME VARCHAR2(10),ADDRESS VARCHAR2(10),PHONE VARCHAR2(15);#以goldengate用戶登錄數據庫，創建test01表C:ggsggsci.exeOracle GoldenGate Command Interpreter for OracleVersion 10.4.0.19 Build 002Win

38、dows (optimized), Oracle 10 on Sep 18 2009 15:54:55Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.創建相關目錄GGSCI (ad123456) 1 create subdirsCreating subdirectories under current directory C:ggsParameter files C:ggsdirprm: createdReport files C:ggsdirrpt: createdCheckpoint f

39、iles C:ggsdirchk: createdProcess status files C:ggsdirpcs: createdSQL script files C:ggsdirsql: createdDatabase definitions files C:ggsdirdef: createdExtract data files C:ggsdirdat: createdTemporary files C:ggsdirtmp: createdVeridata files C:ggsdirver: createdVeridata Lock files C:ggsdirverlock: cre

40、atedVeridata Out-Of-Sync files C:ggsdirveroos: createdVeridata Out-Of-Sync XML files C:ggsdirveroosxml: createdVeridata Parameter files C:ggsdirverparams: createdVeridata Report files C:ggsdirverreport: createdVeridata Status files C:ggsdirverstatus: createdVeridata Trace files C:ggsdirvertrace: cre

41、atedStdout files C:ggsdirout: created編輯管理進程edit param mgrPORT 7809 DYNAMICPORTLIST 7840-7850 新增復制進程GGSCI (ad123456) 7 ADD replicat rep1 EXTTRAIL c:ggsdirdatr1,nodbcheckpointREPLICAT added.-新增一個復制進程，將抽取到隊列中的文件解析后寫進TargetDB-配置復制進程edit param rep1replicat rep1userid goldengate,password goldengateassumet

42、argetdefsHANDLECOLLISIONSreperror default,discarddiscardfile c:ggsdirdatrepsz.dsc,append,megabytes 100map goldengate.*, target goldengate.*;=-復制進程名 登入TargetDB的賬號和密碼 兩臺DB數據結構一致則使用此參數 如果復制出錯，則繼續，并將錯誤放進discardfile中 復制truncate操作復制的源表為sajet，sj，lang，smt用戶（此處是goldengate用戶）下的所有表，目標為TargetDB中對用用戶下的所有表=開啟管理進程

43、GGSCI (ad123456) 9 start mgrStarting Manager as service (GGSMGR).Service started.開啟復制進程GGSCI (ad123456) 19 start rep1Sending START request to MANAGER (GGSMGR) .REPLICAT REP1 starting查看狀態GGSCI (ad123456) 20 info allProgram Status Group Lag Time Since ChkptMANAGER RUNNINGREPLICAT RUNNING REP1 00:00:00 00:00:06測試DML在源端test01表中插入兩條數據SQL insert into test01 values (wallsen,guangzhou;1 row created.SQLcommit;SQL insert into test01 values (wxy,beijing;SQLcommit;查看表中內容SQL select * from test01;NAME ADDRESS PHTONE- - -wallse