Home » Server Options » Replication » conflict resolution involving TIMESTAMP (Oracle Enterprise, 11.2.0.3, Solaris 20)
conflict resolution involving TIMESTAMP [message #602122] Fri, 29 November 2013 12:17 Go to next message
markhooper99
Messages: 19
Registered: October 2013
Location: Calgary
Junior Member
I have 2 databases in different time zones.
I will be using LATEST TIMESTAMP for the update conflict resolution method on the tables involved in the replication.
Everything I read indicates that this resolution method does not understand time zone differences (i.e. LATEST TIMESTAMP, not LATEST TIMESTAMP WITH TIME ZONE).

The data in my CST timezone database will *always* have a more recent timestamp than data in my MST timezone database - if you don't take the time zone into account.

I was considering adding a column to the tables involved in the replication that would store a TIMESTAMP but with a consistent time zone, such as SYSDATE in GMT or something. A 'before update' trigger would look after that stamping.
I'd then configure the LATEST TIMESTAMP update conflict method using this new column.
This way, if there were an update conflict the LATEST TIMESTAMP would work properly.

Does this seem reasonable or is there a better way ?

Thanks in advance.
Re: conflict resolution involving TIMESTAMP [message #602289 is a reply to message #602122] Mon, 02 December 2013 14:52 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Is it bi-directional advanced replication ? Or Using golden gate
Re: conflict resolution involving TIMESTAMP [message #602379 is a reply to message #602289] Tue, 03 December 2013 15:12 Go to previous messageGo to next message
markhooper99
Messages: 19
Registered: October 2013
Location: Calgary
Junior Member
multi-master replication
Re: conflict resolution involving TIMESTAMP [message #603363 is a reply to message #602379] Fri, 13 December 2013 18:11 Go to previous message
markhooper99
Messages: 19
Registered: October 2013
Location: Calgary
Junior Member
Update: we've added a third multi-master replication site in London, England - so now we have three sites in the mix.

We've decided to go with a new column in each of the replicated tables, called UTC_TIMESTAMP, declared as a TIMESTAMP data type.
We've created a 'before update' trigger for each of the replicated tables to set the new column to SYS_EXTRACT_UTC(SYSTIMESTAMP).

Example:
create or replace trigger k_ba_contact_info_r1
before update on ba_contact_info
for each row
begin
  if dbms_reputil.from_remote() = true then
    return;
  end if;
  select sys_extract_utc(systimestamp) into :new.utc_timestamp from dual;
end;
/
So, no matter which database updates the records the UTC_TIMESTAMP column will always have the UTC date/time (all the database servers sync their clocks with timeservers so they all have the same minute/second).

We refer to this new column in the conflict resolution mechanism and we're golden.
Previous Topic: Materialized View Geometry Column
Next Topic: direct path load and mview refresh
Goto Forum:
  


Current Time: Thu Mar 28 18:50:52 CDT 2024