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?
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:
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.
Each sequence will have:
so, my sequences would be