Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to solve the problem of OGG synchronous replication and compatible triggers, with certain reference value, interested friends can refer to, I hope you have a lot of gains after reading this article, let Xiaobian take you to understand.

When setting up OGG, trigger is usually disabled. Sometimes, due to specific reasons, in addition to synchronizing data, business operations need to be performed on the target library, and triggers implement a lot of business logic. In this case, you cannot use

alter trigger tr_dept disable mode directly blocked. However, if enabled, it may cause data problems. For example, a table is operated in the trigger, but in fact, that table has been synchronized through OGG. In this way, it will cause repeated operation of data.

This leads to inconsistencies between the data and the master database.

Our implementation requires OGG to synchronize some business data to the target library, and triggers on the target library cannot be stopped (a lot of business logic in our ERP system is implemented in triggers) because the customer wants to do some business on the target library.

Possible solutions (here scott schema as an example, OGG construction process omitted):

1. Trigger Code:

Click here to fold or open

CREATE OR REPLACE TRIGGER tr_dept

BEFORE INSERT OR UPDATE ON dept

FOR EACH ROW

DECLARE

v_ind PLS_INTEGER;

BEGIN

SELECT COUNT(*)

INTO v_ind

FROM tmp_disable_trigger

WHERE table_name = 'dept';

IF v_ind > 0 THEN

RETURN;

END IF;

INSERT INTO dept_log (id, dt) VALUES (seq_dept_log.nextval, SYSDATE);

END;

tmp_disable_trigger is a transaction-level global temporary table, which is equivalent to making a switch, and insert dept_log is equivalent to extra business logic.

Click here to fold or open

create global temporary table tmp_disable_trigger(

table_name varchar2(100)

) on commit delete rows;

2. Modify the target-side replication process parameters, keyword sqlexec

GGSCI55> view params r_oggdb

replicat r_oggdb

SETENV (ORACLE_SID = "oggdb")

userid ggs,password ggs

assumetargetdefs

reperror default,discard

discardfile ./ dirrpt/r_oggdb.dsc,append,megabytes 50

dynamicresolution

MAP scott.*, target scott.*, SQLEXEC (SPNAME scott.sp_disable_trigger, ID sp_disable_trigger, PARAMS (p_tablename = 'dept'));

It has been tested that new data is not inserted into dept_log when synchronizing scott.dept tables. Here, the trigger is logically bypassed (it is actually triggered, but the code in the trigger is not executed)

It should be noted that if there are multiple places to perform the same process, you need to specify the ID, and the IDs of different rows cannot be the same, for example:

MAP scott.dept, target scott.dept, SQLEXEC (SPNAME scott.sp_disable_trigger, ID sp_disable_trigger1, PARAMS (p_tablename = 'dept'));

MAP scott.emp, target scott.emp, SQLEXEC (SPNAME scott.sp_disable_trigger, ID sp_disable_trigger2, PARAMS (p_tablename = 'dept'));

Otherwise, the following errors may occur:

2017-06-13 11:23:30 ERROR OGG-00303 Oracle GoldenGate Delivery for Oracle, R_ERP_FY.prm: Duplicate stored procedure name (scott.sp_disable_trigger)

Thank you for reading this article carefully. I hope that Xiaobian's article "How to solve the problem of OGG synchronous replication and compatible triggers" will be helpful to everyone. At the same time, I hope that everyone will support you a lot and pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.