Search code examples
oraclesequenceoracle-golden-gateid-generation

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