Home » Server Options » Replication » DML from trigger not propagated (Oracle 10.2.0.4, Windows XP 32-bit)
DML from trigger not propagated [message #424954] Tue, 06 October 2009 12:13 Go to next message
foxius
Messages: 4
Registered: July 2009
Junior Member
Hi.

I am a developer and have little knowledge of Advanced Replication. I came across an issue our DBAs are not able to resolve.

Suppose there are 2 sites - Site1 and Site2.
There are 2 replicated tables at both sides - TableA and TableB. The replication mode used is multi-master.

TableA is used to store some records. TableB is used to change the state of those records only at originating site - that is, TableB has an AFTER trigger that does an UPDATE on TableA using the primary key and site id.

Now suppose we insert a record at Site1 into TableA - the record is successfully propagated to Site2. Everything is fine so far.

Then we insert a record into TableB at Site2 - the after trigger is fired but doesn't update anything because it's not the originating site. Then the record is propagated to Site1 where again an after trigger fires on TableB - this time we run an UPDATE on TableA. The problem is that this UPDATE on TableB is not propagated to Site1, we can't see this transaction in the replication queue.

My guess is that the internal trigger that's used to capture the changes ignores this DML due to DBMS_REPUTIL.FROM_REMOTE being true. Is there a solution for this or at least a safe workaround?

The last resort is to create a deferred procedure call but I don't want to go that far.

Thanks in advance!
Re: DML from trigger not propagated [message #424959 is a reply to message #424954] Tue, 06 October 2009 14:35 Go to previous messageGo to next message
babuknb
Messages: 1703
Registered: December 2005
Location: NJ
Senior Member

Quote:
Then we insert a record into TableB at Site2 - the after trigger is fired but doesn't update anything because it's not the originating site


One question here. Your using advanced replication & also your using confilct resolution? If yes which type your using?

- Babu
Re: DML from trigger not propagated [message #424961 is a reply to message #424959] Tue, 06 October 2009 14:54 Go to previous messageGo to next message
foxius
Messages: 4
Registered: July 2009
Junior Member
For TableA we employ conflict resolution based on the Timestamp (latest timestamp wins) and the State field which is changed by the trigger of TableB (number(2), "maximum" method).

For TableB we employ conflict resolution based on the Timestamp (latest timestamp wins).
Re: DML from trigger not propagated [message #425044 is a reply to message #424961] Wed, 07 October 2009 05:07 Go to previous messageGo to next message
babuknb
Messages: 1703
Registered: December 2005
Location: NJ
Senior Member


Sorry to say. I'm not able to understand. We request to you try to use LATEST TIMESTAMP Method. (Don't use MAXIMUM Method)

-Babu
Re: DML from trigger not propagated [message #425141 is a reply to message #425044] Wed, 07 October 2009 14:10 Go to previous messageGo to next message
foxius
Messages: 4
Registered: July 2009
Junior Member
gentlebabu wrote on Wed, 07 October 2009 05:07

Sorry to say. I'm not able to understand. We request to you try to use LATEST TIMESTAMP Method. (Don't use MAXIMUM Method)

-Babu


We use "LATEST TIMESTAMP" for timestamp fields (though this method calls to the same MAXIMUM procedure, but that's just nitpicking from my side). The problem is not in the conflict resolution, the problem is that the update is not replicated at all.

Let me explain with more details since my first post was a bit sloppy (please ignore syntax errors)

We have 2 tables:

CREATE TABLE bar
(
   bar_id VARCHAR2(32) NOT NULL,
   bar_state NUMBER(2) NOT NULL,
   site_id NUMBER(4) NOT NULL,
   ts DATE NOT NULL,
   CONSTRAINT pk_bar PRIMARY KEY(bar_id)
);

CREATE OR REPLACE TRIGGER trg_bar_time
  BEFORE INSERT OR UPDATE ON bar
  FOR EACH ROW
BEGIN
  IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
    :NEW.ts := sysdate();
  END IF;
END;


CREATE TABLE foo
(
   bar_id VARCHAR2(32) NOT NULL,
   ts DATE NOT NULL,
   CONSTRAINT pk_foo PRIMARY KEY(bar_id)
);

CREATE OR REPLACE TRIGGER trg_foo_time
  BEFORE INSERT OR UPDATE ON foo
  FOR EACH ROW
BEGIN
  IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
    :NEW.ts := sysdate();
  END IF;
END;

CREATE OR REPLACE TRIGGER trg_foo
  AFTER INSERT OR UPDATE ON foo
  FOR EACH ROW
BEGIN
  UPDATE bar
     SET bar_state = 1
   WHERE bar_id = :NEW.bar_id
     AND bar_state = 0
     AND site_id = MYCONFIG.current_site_id;
END;


Basically put, we have some records in BAR and when someone inserts data into FOO we update the state of the record in BAR only at the originating site.

Suppose that we have a multi-master replication configured and running between SITE1 (MYCONFIG.current_site_id = 1234) and SITE2 (MYCONFIG.current_site_id = 4321), FOO and BAR are replicated. MYCONFIG is a user package that contains some configuration data, such as "unique" site id.

1. At SITE1:
INSERT INTO bar (bar_id, bar_state, site_id) VALUES ('00000000000000000000000000000000', 0, 1234);


2. The record is successfully propagated to SITE2.

3. Now at SITE2:
INSERT INTO foo (bar_id) VALUES ('00000000000000000000000000000000');


4. The TRG_FOO tirgger is fired at SITE2 but doesn't change anything in BAR, since site_id is different.

5. The FOO record is successfully propagated to SITE1

6. The TRG_FOO trigger fires at SITE1 and UPDATEs the corresponding record in BAR.

This last UPDATE in step #6 is not captured by advanced replication and not propagated to SITE2 - we now have inconsistent data in BAR table.

I'm not sure whether it's by design or it's just a bug. I couldn't prove either assumption using the official Oracle documentation and user forums. I guess that Oracle AR does not capture DML that is executed in the receiver's context and it's by design...
Re: DML from trigger not propagated [message #425279 is a reply to message #425141] Thu, 08 October 2009 07:00 Go to previous message
babuknb
Messages: 1703
Registered: December 2005
Location: NJ
Senior Member

Quote:
6. The TRG_FOO trigger fires at SITE1 and UPDATEs the corresponding record in BAR.

This last UPDATE in step #6 is not captured by advanced replication and not propagated to SITE2 - we now have inconsistent data in BAR table.


Thanks for your explanation. Basically Advanced Replication only supports by matrialized view objects.

But in your case; your using DBMS_REPUTIL & Database Triggers using this your doning replication. Here we are not seen DBMS_MVIEW or DBMS_DEFER Or DBMS_REPCAT packages.

We would like to recommand to use DBMS_MVIEW.I_AM_REFRESH using Materialized View objects.

Thanks
Previous Topic: materialised views not getting refreshed
Next Topic: when going for materialised views
Goto Forum:
  


Current Time: Fri Apr 18 16:56:53 CDT 2014

Total time taken to generate the page: 3.80045 seconds