I have three data-centers with 3 oracle databases replicating using Golden gate. Every database has trigger-sequence mechanism to generate Primary keys. We are adding one more data-center to the architecture with a DB.
Logic we are using to avoid conflicts is to increment sequence values by 4 from 3 so that no conflicts are generated when transactions are replicated using Golden gate.
For e.g.
DB1 - SEQUENCE_1 - START WITH 1 - INCREMENT BY 4 - Next value - 5
DB2 - SEQUENCE_1 - START WITH 2 - INCREMENT BY 4 - Next value - 6
DB3 - SEQUENCE_1 - START WITH 3 - INCREMENT BY 4 - Next value - 7
DB4 - SEQUENCE_1 - START WITH 4 - INCREMENT BY 4 - Next value - 8
Problem statement - After cloning DB4, I need to find out the exact number I should start my sequences with so that values generated in DB4 is unique and do not generated conflicts.
Actual Values in DB for a sequence.
DB1 - SEQUENCE_1 - START WITH 33479805 - INCREMENT BY 4 - Next value - 33479809
DB2 - SEQUENCE_1 - START WITH 33318420 - INCREMENT BY 4 - Next value - 33318424
DB3 - SEQUENCE_1 - START WITH 47020627 - INCREMENT BY 4 - Next value - 47020631
DB4 - SEQUENCE_1 - START WITH ???????? - INCREMENT BY 4 - Next value - ???
If someone can help with logic which can be used for all sequences will be very helpful.
Regards, LANCERIQUE.