Post

0 followers Follow
0
Avatar

APPLY_SET_TRIGGER_FIRE_ONCE does not work in 11.2.0.4

Question

We are using active-active replication from RAC to single instance.

The table in question has a trigger which populates the PK and LAST_UPDATED column. The problem is that I'm finding that the LAST_UPDATED column is being updated when the row is replicated (so the two rows are not identical). I want to ensure that the trigger is not fired by the APPLY process.

  • select fire_once from user_triggers returns YES.
  • Oracle version is 11.2.0.4.

I tried running the following in the console:

SET *.APPLY_SET_TRIGGER_FIRE_ONCE = YES

And then restarting the processes. The APPLY process now fails to start with errors stating:

FATAL-1652: Failed to enable trigger-fire-once

Answer

In 11.2.0.4 and later versions, Oracle introduced a new parameter to enable trigger's fire_once property. Enabling this parameter requires a Golden Gate license.

A workaround to is add the following condition in the trigger so the trigger will not be executed when the APPLY process executes DMLs:

IF DBVREP.DBRSAPPLY_PKG.is_dbreplicate_session=FALSE then
<Trigger Body>
END IF;

Example

The following trigger populates the LAST_UPDATED column whenever a row is updated.

create or replace trigger trg_temp1 before update on temp1 for each row
begin
:new.last_updated:=systimestamp;
end;
/

Add the trigger execution condition as follows:

create or replace trigger trg_temp1 before update on temp1 for each row
begin
IF DBVREP.DBRSAPPLY_PKG.is_dbreplicate_session=FALSE then
:new.last_updated:=systimestamp;
END IF;
end;
/
Krupesh Desai Answered

Please sign in to leave a comment.