《OGG简单双向同步.doc》由会员分享，可在线阅读，更多相关《OGG简单双向同步.doc（12页珍藏版）》请在三一文库上搜索。
1、实验目标：利用 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 n ame caid node是联合主键。由其标识会员唯一ID （即是在哪个节点创建的会员）。Name标识会员名。CA标识生成数据的节点。安装配置过程：1。Install Visual C
2、+ 2005 SP1 Redistributable Package （所有节点）* Download from here.* Double click to i nstall.2.Set Env Var（所有节点）* Set ORACLE_HOME and ORACLE_SID environmen t variables accordi ngly.设置ORACLE_HOMEORACLE_SID境变量。需要重启才能生效。测试是否生效：:echo %ORACLEORCLC:echo %ORACLE_HOME%C:oracleproduct1020db 13.I nstall Golde n G
3、ate Software（所有节点安装OGG件）* Un zip Golde n Gate dow nl oad into a local folder （e.g. C:proggg104）. Make sure there is no space in folder n ames.* Ope n a DOS shell and cd to Golde nGate folder.* Issue comma nd GGSC I to bring up the Golde nGate comma nd in terface.* Issue comma nd CREATE SUBDIRS to cr
4、eate needed subdiretories.* Issue comma nd exit to exit comma nd in terface.4. 配置数据库的日志模式：（所有节点）1. 设置A、B库为归档模式,启用强制日志。 启用supplemental loggingSQL shutdow n immediateSQL startup mountSQL alter database archivelog;SQL alter database force logging;SQL alter database add suppleme ntal log data;SQL select
5、 SUPPLEMENTAL_LOG_DATA_MIN from v$database;SUPPLEMEYESSQL alter database ope n;SQL alter system switch logfile;2. 关闭数据库的recyblebinSQL alter system set recycleb in=off scope=spfile;5. 创建GG的管理用户（所有节点）SQL create user ggate ide ntified by ggate default tablespace users temporary tablespace temp;SQL gra
6、nt dba to ggate;（为了方便测试，先不管权限的事）SQL grant conn ect,resource,u nlimited tablespace to ggate;SQL grant execute on utl_file to ggate;6. 创建测试用户aa和测试表（所有节点）SQL create user aa ide ntified by aa default tablespace users temporary tablespace temp quota un limited on users;用户已创建。SQL grant conn ect,resource t
7、o aa;创建测试表（所有节点）SQL connect aa/aa已连接。SQL create table vip(id varchar2(10), node varchar2(10), name varchar2(20),ca varchar2(10),primary key(id, no de);表已创建。6 .配置A库GG :1.c on figure gg man agerGGSCI (test) 17 edit params mgr添加以下内容：PORT 7809-add autostart-AUTOSTART EXTRACT ext - 上次配置的单向同步extract进程参数保存
8、GGSCI (test) 18 start man ager2查看是否启动成功：GGSCI (test) 18 info mgrMan ager is running (IP port test.7809).3 添加一个 extract 组 extaGGSCI (test) 19 add extract exta,tra nlog,begin nowEXTRACT added.4编辑extaextract extasete nv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)GETENV (NLS_LANG)userid ggate, password ggatetr
9、anl ogopti ons 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
10、 参数 GGSCI (test) 1 dblogin userid ggate,password ggate Successfully 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 组 repa以对应BGGSCI (test) 3
11、add replicat repa,exttrail ./dirdat/ra (这里应是 replicat repa,exttrail ./dirdat/tb库的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.
12、vip;查看结果：11GGSCI (test) 5 info allProgramStatusGroupLagTime Since ChkptMANAGERRUNNINGEXTRACTABENDEDEXT00:00:0012:41:27 -之前配置的单向同步EXTRACTSTOPPEDEXTA00:00:0000:16:01REPLICATSTOPPEDREPA00:00:0000:02:4612 启动 exta,repaGGSCI (test) 11 info allProgramStatusGroupLagTime Since ChkptMANAGERRUNNINGEXTRACTABEND
13、EDEXT00:00:0012:44:09EXTRACTRUNNINGEXTA00:00:0000:00:09REPLICATRUNNINGREPA00:00:0000: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 process
14、ed.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 g
15、gate, password ggatetranlogoptions excludeuser ggatermthost 192.168.1.141,mgrport 7809 rmttrail ./dirdat/tb | table aa.vip;保存4添加一个远程trailGGSCI (dest) 6 add rmttrail ./dirdat/tb,extract extbRMTTRAIL added.配置Replicat进程：5设置检查点表：GGSCI (dest) 7 edit params ./GLOBALS checkpointtable ggate.ggschkpt6退出GGSCI
16、，重新登陆以激活 GLOBALS参数 GGSCI (dest) 1 dblogin userid ggate,password ggate Successfully 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
17、 (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 ggatehandlecollisionsassumetargetdefsdiscardfi
18、le ./dirrpt/repb.dsc,purgemap aa.vip, target aa.vip;保存查看结果：GGSCI (dest) 5 info allProgram StatusGroupLagTime Since ChkptMANAGER RUNNINGEXTRACTSTOPPEDEXTB00:00:0000:10:36REPLICATSTOPPEDREP00:00:0013:01:11REPLICATSTOPPEDREPB00:00:0000:02:30启动 extb,extbGGSCI (dest) 6 start extract extbSending START req
19、uest to MANAGER (GGSMGR) .EXTRACT EXTB startingGGSCI (dest) 7 start replicat repbSending START request to MANAGER (GGSMGR) .REPLICAT REPB startingGGSCI (dest) 8 info allProgramStatusGroupLagTime Since ChkptMANAGERRUNNINGEXTRACTRUNNINGEXTB00:00:0000:00:04REPLICATSTOPPEDREP00:00:0013:02:49REPLICAT RUN
20、NINGREPB00:00:0000: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 allProgramStat
21、usGroupLagTime Since ChkptMANAGERRUNNINGEXTRACTABENDEDEXT00:00:0013:05:07EXTRACTRUNNINGEXTA00:20:5600:00:05REPLICATRUNNINGREPA00:00:0000: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,extt
22、rail ./dirdat/tbREPLICAT altered.B库：GGSCI (dest) 22 alter replicat repb,exttrail ./dirdat/taREPLICAT altered.A 库写入的数据：重新启动 exta.repa. extb,repb 进程， B 库可以看到SQL select * from vip;IDNODENAMECA001A张三A002A张AAB 库写入数据。测试 A 库是否能看到：SQL select *from vip;IDNODENAMECA001A张三A002A张AA003B李明BIDNODENAMECA004B李XB无论是在
23、 A 库、 B 库插入数据，都能同步到对方数据库中。遇到的问题：1.更新了组合主键的一部分。两边的 replciat直接就abended 了，死活起不来。最后更改rmttrail解决即不用之前的 trail 文件。2012-05-02 09:13:32 WARNING OGG-01396 A complete after image is not available in AA.VIP at rba989 in file ./dirdat/ta000003, while inserting a row into AA.VIP due to missing target row for a ke
24、y update operation. NOCOMPRESSUPDATES or FETCHOPTIONS FETCHPKUPDATECOLS may be specified in the EXTRACT parameter file to in clude a complete image for key update operations.2 .因为没有启用表级别的辅助日志，更新无法同步所有节点操作：GGSCI (dest) 149 ADD TRANDATA aa.*Logging of supplemental redo data enabled for table AA.VIP.启用
25、了 trandata 后， insert 、update 、 delete 均能正常同步到对方数据库。怎样使用 data pump1. Use the ADD EXTRACT command to create a primary Extract group. For documentation purposes, 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
26、data set (BSDS) name after TRANLOG .2. Use the ADD EXTTRAIL command to add a local trail. For documentation purposes, this trail 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 p
27、arameter file for the ext_1 group. Include the following 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 writ
28、es 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 docume
29、ntation purposes, 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 the standby system. 投递到远程目标的路径ADD R
30、MTTRAIL , 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 follow ing parameters plus any others that apply to your database en vir onment.-Identify the data pump group:EXTRACT vpump
31、_1-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 seq
32、uences to be captured:SEQUENCE ;-Specify tables to be captured:TABLE .*;-Exclude specific tables from capture if needed:TABLEEXCLUDE NOTE PASSTHRU mode is assumed because source and target data structures are usually identical in a live standby configuration. In this mode, no column mapping, filteri
33、ng, SQLEXEC functions, transformation, or other data manipulation can be performed.注意：直通模式，假定源和目标数据结构完全相同。(in a live standby )。在此模式中,没有列映射、过滤、执行SQLEXEC函数、转换或其他数据的操作。结合上述说明，可以将之前配置的的改成如下：A库：ADD EXTRACT EXTA,TRANLOG,BEGEIN NOWADD TRANDATA VIP .*ADD EXTTRAIL ./dirdat/ta , EXTRACT EXTA -添加本地 trail 文件EDI
34、T PARAMS EXTAextract extasete nv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)GETENV (NLS_LANG)userid ggate, password ggate tranl ogopti ons excludeuser ggate -rmthost 192.168.1.151,mgrport 7809 exttrail ./dirdat/taADD EXTRACT pump_1, EXTTRAILSOURCE ./dirdat/ta , BEGIN NOWADD RMTTRAIL ./dirdat/ta, EXTRACT pump_1EXTRACT pump_1userid ggate, password ggate tranl ogopti ons excludeuser ggatermthost 192.168.1.151,mgrport 7809RMTTRAIL ./dirdat/ta-Pass data through without mapping, filtering, conversion: PASSTHRU-Specify tables to be captured:TABLE aa.vip;