Sequences are usually used in Oracle databases to generate primary keys. Because it's auto-generated, replicating sequence in a bi-directional replication or an active-active replication can cause problems. Let's discuss this and explain how you can manage sequences in an active-active replication.
1. Problems of Replicating Sequences
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:
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
You can use other formulas as long as you can make sure there is no overlap of the sequence numbers on all the nodes in your active-active replication.
Created 7/17/2015, Last Updated: 12/20/2016