1.
适用版本
操作方法在任何平台的Oracle GoldenGate - Version: 10.4.0.46
及以后的版本用有效。
2.
目标
实现向已经上线运行的
Oracle GoldenGate
的环境中增加新的需要复制数据的表。
3.
方法
数据复制工具
Oracle GoldenGate
提供了两种增加方法,我们这里逐个介绍。
3.1
第一种:使用
handlecollision
参数
参数
handlecollision
应用于复制进程,就是目标库上的
replicat
进程的配置。
如果在
replicat
上配置了参数,那么复制进程工作时,将会对重复数据和丢失的数据做错误处理。
第一、
我们在加新表的
OGG
同步环境中时,能保证源库上对此表不做任何
DML
操作,当然
DDL
操作也不能有。
第二、
我们加了新表后,目标库上的数据不管,手工校验一次,发现不一致再手工处理也可以。
以下步骤是ORACLE
官网提供的,这里不做翻译了。
i)using handlecollisions
----------------------------
1)stop the extract,pump and replicat
once the extract is stopped, wait for the pump to catch up before stopping it.
once the pump is stopped, wait for the replicat to catch up before stopping it.
2)include the tables that you need to add into the extract parameter file and save it
3)start the extract
4)include the tables that you need to add into the extract pump parameter file and save it
5)start the pump
6)do the initial load for the the new tables( ie you can take the export and import of the new tables that need to to added for replication from source the target database)
7)Wait for the initial load(export and import) to be completed and then include the tables that you need to add into the replicat parameter file with HANDLECOLLISIONS parameter
eg: MAP hr.dep, TARGET hr.dep, HANDLECOLLISIONS;
MAP hr.country, TARGET hr.country, HANDLECOLLISIONS;
6) start the replicat
7) once the lag becomes zero remove the HANDLECOLLISIONS from the replicat parameter file and restart the replicat
eg :-
MAP hr.dep, TARGET hr.dep;
MAP hr.country, TARGET hr.country;
note:- 4 and 5th step can be skipped if the pump is not configured.
3.2
第二种:不使用
handlecollision
参数
这种方法是通用的。因为在实际项目中,我们既不能保证源表的数据在操作
OGG
时无变化,由不能要求项目上接受数据差异或者丢失。
在同步完成后,可以除掉关于
SCN
的同步配置。方法简明扼要,很容易理解。
以下步骤是ORACLE
官网提供的,这里也不做翻译了。
ii)without using handlecollision
--------------------------------------
1) stop the extract,pump and replicat
once the extract is stopped, wait for the pump to catch up before stopping it.
once the pump is stopped, wait for the replicat to catch up before stopping it.
2)add the new table in extract parameter file and save it
3)start the extract
4)add the new table in extract pump parameter file and save it
5)start the extract pump
6)get the current SCN from the source database
eg:-
SQL> select current_scn from v$database;
CURRENT_SCN
------------------------
5343407
7) Check that there are no open DML transactions against the table. If there are open transactions, make sure that their starting SCN is higher than the one obtained in step 4) , i.e. 5343407
8)re-sync the the newly added table from source to target(using normal export/import).
Make sure to use FLASHBACK_SCN parameter for the export.
9) Add the table in the replicat parameter file including the below option( FILTER ( @GETENV ("TRANSACTION", "CSN") > <scn_number obtained from source db>) )as shown in the below example
eg:-
MAP source.test1, TARGET target.test1 ,FILTER ( @GETENV ("TRANSACTION", "CSN") > 5343407);
MAP source.test2, TARGET target.test2 ,FILTER ( @GETENV ("TRANSACTION", "CSN") > 5343407);
10)start the replicat
11)verify the tables on source and table and once the lag is zero remove the filter parameter from the replicat parameter file and restart.
4.
参数说明
HANDLECOLLISIONS | NOHANDLECOLLISIONS
Use the HANDLECOLLISIONS and NOHANDLECOLLISIONS parameters to control whether or not Replicat tries to resolve duplicate-record and missing-record errors when applying SQL on the target. These errors can occur during an initial load, when data from source tables is being loaded to target tables while GoldenGate is replicating transactional changes that are being made to those tables. When GoldenGate applies the replicated changes after the load is finished, HANDLECOLLISIONS causes Replicat to overwrite duplicate records in the target tables and provides alternate handling of errors for missing records.
You can use HANDLECOLLISIONS and NOHANDLECOLLISIONS in the following ways:
● You can use either HANDLECOLLISIONS or NOHANDLECOLLISIONS at the root level of the parameter file to affect all MAP statements.
● You can use HANDLECOLLISIONS and NOHANDLECOLLISIONS as on-off switches for groups of tables to enable or disable error handling as needed. One remains in effect for all subsequent MAP statements until the other is encountered.
●
You can use HANDLECOLLISIONS and NOHANDLECOLLISIONS within a MAP statement to enable and disable the functionality for a specific table. See page 204.
Any of the preceding methods can be combined. The use within a MAP statement overrides other settings. The use as a toggle overrides a global setting. For example, you could have a global NOHANDLECOLLISIONS setting, and then use HANDLECOLLISIONS within MAP statements to enable it only for certain tables.
分享到:
相关推荐
Oracle Golden Gate软件是一种基于日志的结构化数据复制备份软件,它通过解析源数据库在线日志或归档日志获得数据的增量变化,再将这些变化应用到目标数据库,从而实现源数据库与目标数据库同步。Oracle Golden Gate...
Windows Oracle Golden Gate 单向数据同步 , 重要用于数据库容灾备份以及数据分发。本文基于Windows系统操作。
Oracle Golden Gate使用
Oracle Golden Gate软件是一种基于日志的结构化数据复制备份软件,它通过解析源数据库在线日志或归档日志获得数据的增量变化,再将这些变化应用到目标数据库,从而实现源数据库与目标数据库同步,以后可能是oracle...
此文档包含了 Oracle Golden Gate 从Windows下Oracle同步到Linux下的Kafka的部署说明。
oracle12c-golden gate install oracle 12C 容灾最好工具就是golden
Oracle Golden Gate图文并茂快速掌握,将帮助你在最短时间内掌握Oracle高级复制技术,从原理到实战的全生命周期式精彩呈现,手把手教学,包教包会版!
ogg,ORACLE数据库复制安装配置详解,个人经实测后整理,如有错误请指出
PPT介绍了oracle GoldenGate与IBM CDC的原件架构,并且对两个增量数据捕获软件进行综合的比较,以列表的方式列出各比较项,直观、客观地分析两个软件。
Oracle Golden Gate Introduction
作者经过无数个通宵学习Oracle Golden Gate,查了很多博客和Oracle的资料,精心总结了一份Ogg知识点,大家有空了可以参阅下,很有价值!
大牛出品Oracle Golden Gate知识点总结
公司内部培训使用ppt。Oracle golden gate 提供异构环境间事物数据的实时、低影响的捕获、转换、路由和交付。
oracle goldgate,强大的非入侵式数据复制工具,最新资料
极好的 Oracle GoldenGate 总结资料,按照文章内容配置,快速掌握GG
Golden Gate for MySQL to Oracle
OracleGoldenGate针对表没有主键或唯一索引的解决方案[归类].pdf
Oracle Golden Gate知识点总结
分享一篇详细的ORACLE GOLDEN GATE介绍文档,配合官方文档可以更好的理解和应用ogg。