We discussed in Sequence Replication with Oracle GoldenGate: when Oracle GoldenGate replicates sequences, it can only make sure the target sequence value is equal or higher than the source sequence number. This can cause problems for active-active replications because there is no way for Oracle GoldenGate replication to keep the condition satisfied in both directions.
2. Managing Sequence in an Active-Active Replication
For active-active replications, the general practice is to keep the sequence handling independent on each database. For active-active replications, you need to make sure the sequence generated on each node are not overlapped. 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 the following 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.
- 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
- 100, 104, 108,112, 116, 120,...
- 101, 105, 109, 113, 117, 121,...
- 102, 106, 110, 114, 118, 122...
- 103, 107, 111, 115, 119, 123,...
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