OGG简单双向同步.doc

实验目标：利用OGG实现一对一的简单双向同步。把表VIP的数据双向同步拓扑图如下：A库 DB：10G R2 OS：windows server 2003 SP2简体中文IP 192.168.1.141 B库 DB：10G R2 OS：windows server 2003 SP2简体中文IP 192.168.1.151数据库A、B均有表VIPVIP 结构如下：id node name caid node 是联合主键。由其标识会员唯一ID（即是在哪个节点创建的会员）。Name 标识会员名。CA标识生成数据的节点。安装配置过程：1。Install Visual C + 2005 SP1 Redistributable Package（所有节点）* Download from here.* Double click to install.2.Set Env Var（所有节点）* Set ORACLE_HOME and ORACLE_SID environment variables accordingly. 设置ORACLE_HOME和ORACLE_SID环境变量。需要重启才能生效。测试是否生效：C:echo %ORACLE_SID%ORCLC:echo %ORACLE_HOME%C:oracleproduct10.2.0db_13.Install Golden Gate Software（所有节点安装OGG软件）* Unzip Golden Gate download into a local folder (e.g. C:proggg104). Make sure there is no space in folder names.* Open a DOS shell and cd to GoldenGate folder.* Issue command GGSCI to bring up the GoldenGate command interface.* Issue command CREATE SUBDIRS to create needed subdiretories.* Issue command exit to exit command interface.4.配置数据库的日志模式：（所有节点）1.设置A、B库为归档模式, 启用强制日志。启用supplementalloggingSQL shutdown immediateSQL startup mountSQL alter database archivelog;SQL alterdatabaseforcelogging;SQL alter database add supplemental log data;SQL select SUPPLEMENTAL_LOG_DATA_MIN from v$database;SUPPLEMEYESSQL alter database open;SQL altersystem switch logfile;2.关闭数据库的recyblebinSQL alter system set recyclebin=off scope=spfile;5.创建GG的管理用户（所有节点）SQL create user ggate identified by ggate default tablespace users temporary tablespace temp;SQL grant dba to ggate;（为了方便测试，先不管权限的事）SQL grant connect,resource,unlimited tablespace to ggate;SQL grant execute on utl_file to ggate;6.创建测试用户aa和测试表（所有节点）SQL create user aa identified by aa default tablespace users temporary tablespace temp quota unlimited on users;用户已创建。SQL grant connect,resource to aa;创建测试表（所有节点）SQL connect aa/aa已连接。SQL create table vip(id varchar2(10),node varchar2(10),name varchar2(20),ca varchar2(10),primary key(id,node);表已创建。6配置A库GG：1.configure gg managerGGSCI (test) 17 edit params mgr添加以下内容：PORT 7809-add autostart-AUTOSTART EXTRACT ext -上次配置的单向同步extract进程参数保存GGSCI (test) 18 start manager2查看是否启动成功：GGSCI (test) 18 info mgrManager is running (IP port test.7809).3添加一个extract组extaGGSCI (test) 19 add extract exta,tranlog,begin nowEXTRACT added.4编辑extaextract extasetenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)GETENV (NLS_LANG)userid ggate, password ggatetranlogoptions excludeuser ggatermthost 192.168.1.151,mgrport 7809rmttrail ./dirdat/ta table aa.vip;保存5添加远程trailGGSCI (test) 23 add rmttrail ./dirdat/ta,extract extaRMTTRAIL added.6配置Replicat进程：设置检查点表：GGSCI (test) 24 edit params ./GLOBALS内容：checkpointtable ggate.ggschkpt7退出GGSCI，重新登陆以激活GLOBALS参数GGSCI (test) 1 dblogin userid ggate,password ggateSuccessfully logged into database.8添加检查表：GGSCI (test) 2 add checkpointtableNo checkpoint table specified, using GLOBALS specification (ggate.ggschkpt).Successfully created checkpoint table GGATE.GGSCHKPT.9添加replicat组repaGGSCI (test) 3 add replicat repa,exttrail ./dirdat/ra (这里应是replicat repa,exttrail ./dirdat/tb 以对应B库的trail)REPLICAT added.10编辑repareplicat repasetenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)GETENV (NLS_LANG)userid ggate, password ggatehandlecollisionsassumetargetdefsdiscardfile ./dirrpt/repa.dsc,purgemap aa.vip, target aa.vip;查看结果：11GGSCI (test) 5 info allProgram Status Group Lag Time Since ChkptMANAGER RUNNINGEXTRACT ABENDED EXT 00:00:00 12:41:27 -之前配置的单向同步EXTRACT STOPPED EXTA 00:00:00 00:16:01REPLICAT STOPPED REPA 00:00:00 00:02:4612启动 exta,repaGGSCI (test) 11 info allProgram Status Group Lag Time Since ChkptMANAGER RUNNINGEXTRACT ABENDED EXT 00:00:00 12:44:09EXTRACT RUNNING EXTA 00:00:00 00:00:09REPLICAT RUNNING REPA 00:00:00 00:00:0313先停止他们：GGSCI (test) 12 stop extract *EXTRACT EXT is already stopped.Sending STOP request to EXTRACT EXTA .Request processed.GGSCI (test) 13 stop replicat *Sending STOP request to REPLICAT REPA .Request processed.A库配置到此结束！7配置B库GG：1配置managerGGSCI (dest) 3 edit params mgrPORT 7809-AUTOSTART replicat rep保存2添加一个extract组extbGGSCI (dest) 4 add extract extb,tranlog,begin nowEXTRACT added.3 编辑extbGGSCI (dest) 5 edit params extbextract extbsetenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)GETENV (NLS_LANG)userid ggate, password ggatetranlogoptions excludeuser ggatermthost 192.168.1.141,mgrport 7809rmttrail ./dirdat/tbtable aa.vip;保存4添加一个远程trailGGSCI (dest) 6 add rmttrail ./dirdat/tb,extract extbRMTTRAIL added.配置Replicat进程：5设置检查点表：GGSCI (dest) 7 edit params ./GLOBALScheckpointtable ggate.ggschkpt6退出GGSCI，重新登陆以激活GLOBALS参数GGSCI (dest) 1 dblogin userid ggate,password ggateSuccessfully logged into database.7添加检查表：GGSCI (test) 2 add checkpointtableNo checkpoint table specified, using GLOBALS specification (ggate.ggschkpt).Successfully created checkpoint table GGATE.GGSCHKPT.8. 添加replicat组repbGGSCI (dest) 3 add replicat repb,exttrail ./dirdat/rb (这里应是 add replicat repb,exttrail ./dirdat/ta 对应A库的trail)REPLICAT added.9.编辑repbGGSCI (dest) 4 edit params repbreplicat repbsetenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)GETENV (NLS_LANG)userid ggate, password ggatehandlecollisionsassumetargetdefsdiscardfile ./dirrpt/repb.dsc,purgemap aa.vip, target aa.vip;保存查看结果：GGSCI (dest) 5 info allProgram Status Group Lag Time Since ChkptMANAGER RUNNINGEXTRACT STOPPED EXTB 00:00:00 00:10:36REPLICAT STOPPED REP 00:00:00 13:01:11REPLICAT STOPPED REPB 00:00:00 00:02:30启动extb,extbGGSCI (dest) 6 start extract extbSending START request to MANAGER (GGSMGR) .EXTRACT EXTB startingGGSCI (dest) 7 start replicat repbSending START request to MANAGER (GGSMGR) .REPLICAT REPB startingGGSCI (dest) 8 info allProgram Status Group Lag Time Since ChkptMANAGER RUNNINGEXTRACT RUNNING EXTB 00:00:00 00:00:04REPLICAT STOPPED REP 00:00:00 13:02:49REPLICAT RUNNING REPB 00:00:00 00:00:01B库配置到此结束_以下，测试，在A库插入数据。B库是否能同步：先启动exta,repaGGSCI (test) 14 start extract extaSending START request to MANAGER (GGSMGR) .EXTRACT EXTA startingGGSCI (test) 15 start replicat repaSending START request to MANAGER (GGSMGR) .REPLICAT REPA startingGGSCI (test) 16 info allProgram Status Group Lag Time Since ChkptMANAGER RUNNINGEXTRACT ABENDED EXT 00:00:00 13:05:07EXTRACT RUNNING EXTA 00:20:56 00:00:05REPLICAT RUNNING REPA 00:00:00 00:00:03写入一条数据：SQL insert into vip values(001,A,张三,A);已创建 1 行。SQL commit;提交完成。数据未同步！B库没有数据：停止exta.repa. extb,repb进程，重新配置参数A 库：GGSCI (test) 33 alter replicat repa,exttrail ./dirdat/tbREPLICAT altered.B库：GGSCI (dest) 22 alter replicat repb,exttrail ./dirdat/taREPLICAT altered. 重新启动exta.repa. extb,repb进程，B库可以看到A库写入的数据：SQL select * from vip;ID NODE- -NAME CA- -001 A张三 A002 A张A AB库写入数据。测试A库是否能看到：SQL select * from vip;ID NODE- -NAME CA- -001 A张三 A002 A张A A003 B李明 BID NODE- -NAME CA- -004 B李X B无论是在A库、B库插入数据，都能同步到对方数据库中。遇到的问题：.更新了组合主键的一部分。两边的replciat 直接就abended了，死活起不来。最后更改rmttrail解决。即不用之前的trail文件。2012-05-02 09:13:32 WARNING OGG-01396 A complete after image is not available in AA.VIP at rba 989 in file ./dirdat/ta000003, while inserting a row into AA.VIP due to missing target row for a key update operation. NOCOMPRESSUPDATES or FETCHOPTIONS FETCHPKUPDATECOLS may be specified in the EXTRACT parameter file to in clude a complete image for key update operations.因为没有启用表级别的辅助日志，更新无法同步。所有节点操作：GGSCI (dest) 149 ADD TRANDATA aa.*Logging of supplemental redo data enabled for table AA.VIP.启用了trandata后，insert、update、delete均能正常同步到对方数据库。怎样使用data pump1. Use the ADD EXTRACT command to create a primary Extract group. For documentationpurposes, this group is called ext_1.ADD EXTRACT , TRANLOG, BEGIN , THREADS Use TRANLOG as the data source. For DB2 on Z/OS, specify the bootstrap data set (BSDS) name after TRANLOG.2. Use the ADD EXTTRAIL command to add a local trail. For documentation purposes, thistrail is called local_trail_1.添加本地的trail文件。ADD EXTTRAIL , EXTRACT For EXTRACT, specify the ext_1 group to write to this trail.3. Use the EDIT PARAMS command to create a parameter file for the ext_1 group. Include thefollowing parameters plus any others that apply to your database environment- Identify the Extract group:EXTRACT - Specify database login information as needed for the database:SOURCEDB ,USERID , PASSWORD - Specify the local trail that this Extract writes to:EXTTRAIL - Specify sequences to be captured:SEQUENCE ;- Specify tables to be captured:TABLE .*;- Exclude specific tables from capture if needed:TABLEEXCLUDE To configure the data pumpPerform these steps on the active source.1. Use the ADD EXTRACT command to create a data pump group. For documentationpurposes, this group is called pump_1.ADD EXTRACT , EXTTRAILSOURCE , BEGIN For EXTTRAILSOURCE, specify local_trail_1 as the data source.用EXTTRAILSOURCE表示从本地读取taril文件的路径。2. Use the ADD RMTTRAIL command to specify a remote trail that will be created on thestandby system.投递到远程目标的路径ADD RMTTRAIL , EXTRACT For EXTRACT, specify the pump_1 data pump to write to this trail.3. Use the EDIT PARAMS command to create a parameter file for the pump_1 group. Includethe following parameters plus any others that apply to your database environment.- Identify the data pump group:EXTRACT - Specify database login information as needed for the database:SOURCEDB ,USERID , PASSWORD - Specify the name or IP address of the standby system:RMTHOST , MGRPORT - Specify the remote trail on the standby system:RMTTRAIL - Pass data through without mapping, filtering, conversion:PASSTHRU- Specify sequences to be captured:SEQUENCE ;- Specify tables to be captured:TABLE .*;- Exclude specific tab