[SOLVED] ID Generation by Sequence On Oracle Golden Gate

ID Generation by Sequence On Oracle Golden Gate

On my Oracle 19c Golden Gate cluster, two instances on different geographical locations run at the same time. (active-active)

In my application code, I need to generate ID, for which I prefer to use Oracle Sequence (nextval function). Oracle golden gate does not guarantee to generate different values on different sites. Since there is a risk of generating same nextval on different sites, I am looking for alternative approaches.

For now I have two options which I do NOT prefer either of them:

Each site can have its own different seed value and increment by two.
Each site can reserve a range and generate value in its own range.

Do you have any other suggestions rather than these options?

Solution

The general practice, and I believe the best one, is to keep the sequence handling independent on each database.

For example, for an active-active replication with two databases, you can let the sequence on one side generate only odd numbers and the sequence on the other side generate only even numbers.

For multi-master replications, you can use a different formula:

Sequence Number = n*d+m+offset.

Where n is the sequence order number, d is the dimensions of the multi-master replication, m ranges from 0 to n-1 is the number assigned to each node in the replication, and offset is the number to offset the sequence numbers.

Let's say I have a setup with a 4-ways multi-master replication where m=4, y is in (0, 1, 2, 3), and offset is 100.

Node #1 (m=0) :Sequence number = n*4+100
Node #2 (m=1): Sequence number = n*4+101
Node #3 (m=2): Sequence number = n*4+102
Node #4 (m=3): Sequence number = n*4+103

Each sequence will have:

100, 104, 108,112, 116, 120,...
101, 105, 109, 113, 117, 121,...
102, 106, 110, 114, 118, 122...
103, 107, 111, 115, 119, 123,...

so, my sequences would be

SQL>create sequence member_seq_1 start with 100 increment by 4 cache 100;
Sequence created.

SQL>create sequence member_seq_2 start with 101 increment by 4 cache 100;
Sequence created.

SQL>create sequence member_seq_3 start with 102  increment by 4 cache 100;
Sequence created.

SQL>create sequence member_seq_4 start with 103 increment by 4 cache 100;
Sequence created.

SQL>select member_seq_1.nextval, member_seq_2.nextval, member_seq_3.nextval, 
member_seq_4.nextval from dual;

   NEXTVAL    NEXTVAL    NEXTVAL    NEXTVAL
---------- ---------- ---------- ----------
       100        101        102        103

SQL>select member_seq_1.nextval, member_seq_2.nextval, member_seq_3.nextval, 
member_seq_4.nextval from dual;

   NEXTVAL    NEXTVAL    NEXTVAL    NEXTVAL
---------- ---------- ---------- ----------
       104        105        106        107