Goldengate Parameter SUPPRESSTRIGGERS & DEFERREFCONST

 

SUPPRESSTRIGGERS

Trigger的抑制和Cascading Deletes

在复制的时候,由于应用以及Trigger会出现影响DB的一致性的情况
“SUPPRESSTRIGGERS”选项用于抑制在数据复制时对目标段对象的Trigger启动
DBOPTIONS SUPPRESSTRIGGERS

缺省值为不抑制 (NOSUPPRESSTRIGGERS)
可使用的DB版本
Oracle 10.2.0.5 以上
Oracle 11.2.0.2 以上
Replicat的用户必须有Streams的管理权限
dbms_goldengate_auth.grant_admin_privilege

 

DEFERREFCONST 约束生效的延迟

    • 可以用DEFERREFCONST选项来代替手动设置约束无效
      • Database 9.2.0.7以上
    • 一直到Replicat的事务提交、DEFERREFCONST会延迟完整性约束的确认与生效
    • DBOPTIONS DEFERREFCONST

      不支持的版本则会忽略DEFERREFCONST参数。忽略的话,也不会写GoldenGate的log。

 

 

DBOPTIONS SUPPRESSTRIGGERS for delete cascade constraint on the target side (REPLICAT) in 11.1.0.7 is missing.

Ct was using OGG ver 10.4 initially for replicating from 9.2.0.8 on Sun Solaris to 11.1.0.7 on AIX.
Ct ran into issues as they had 232 tables with 250 DELETE CASCADE constraints while replicating delete records.
We gave the recommendation of disabling the constraint which obviously worked but the ct does not want
to disable the constraint and involves lot of manual work.
In working thru’ the issues with GG support, it was mentioned that OGG ver 11.1. would have a parameter
that was to be set in the REPLICAT which would fix this issue.
In reading thru’ the Release notes
http://download.oracle.com/docs/cd/E18101_01/doc.1111/e18165.pdf
(Page 6).

the parameter SUPPRESSTRIGGERS is not available for 11gR1, 11.1.0.7.

I was adivsed to open an SR with GG support to check if there will be an additional build
on top of 11.1.1 so that this parameter becomes available for 11gR1. Otherwise the ct will not be
very happy as initially, we were told the ct that the parameter will be available for 11.1.0.7, but it didnt make it as per the doc.
If we can build the same for 11.1.0.7 it will go a long way in maintaining this high profile ct.

check the OGG v11 guides and ensure that you are looking for SUPPRESSTRIGGERS or DEFERREFCONST.

SUPPRESSTRIGGERS
********************
Valid for Replicat for Oracle. Prevents triggers from firing on target objects that are configured for replication with Oracle GoldenGate. You can use this parameter for Oracle 10.2.0.5 and later patches, and for Oracle 11.2.0.2 and later, instead of manually
disabling the triggers.

DEFERREFCONST
****************
Valid for Replicat for Oracle. Delays referential integrity constraint checking and enforcement by the database until the Replicat transaction is committed. You can use this parameter instead of disabling the constraints on the target tables if the database is
Oracle version 9.2.0.7 and later.

When coming to SUPPRESSTRIGGERS, we have some packages added to 10.2.0.5 or 11.2.0.2 and above. Those packages are needed for this to work.

For 10.2.0.5, we need to use dbms_streams_auth.grant_admin_privilege and For 11.2.0.2, we use dbms_goldengate_auth.grant_admin_privilege.


Posted

in

by

Tags:

Comments

One response to “Goldengate Parameter SUPPRESSTRIGGERS & DEFERREFCONST”

Leave a Reply

Your email address will not be published. Required fields are marked *