In active-active Oracle GoldenGate environments, where data is replicated in both directions between two or more databases, maintaining data consistency is critical. One often-overlooked source of conflict is Oracle SEQUENCE objects. If not handled correctly, sequences can generate duplicate values across replicated sites, resulting in data integrity issues, constraint violations, or silent data corruption.
In this post, we’ll cover a proven best practice to eliminate this risk: configuring sequences to generate non-overlapping values using odd-even partitioning.
The Problem: Sequence Conflicts in Active-Active Replication
Oracle SEQUENCE objects are commonly used to auto-generate primary key values. In a unidirectional GoldenGate setup, this poses no issue since the source is the sole generator of new values. However, in bi-directional (active-active) setups, both sites can generate new rows independently. If both sites use the exact sequence definition, collisions are inevitable.
For example:
- Site A inserts a new row using MY_SEQ.NEXTVAL → value: 1001
- Site B inserts a new row using the same MY_SEQ.NEXTVAL → value: 1001
- Conflict occurs when replication attempts to insert the same value in both sites
The Solution: Odd-Even Sequence Partitioning
To avoid this, each site should generate values from disjoint sets. A simple and effective technique is to:
- Set INCREMENT BY 2 on all sequences
- Set Site A to use odd starting values
- Set Site B to use even starting values
This way, Site A will generate: 1001, 1003, 1005…
And Site B will generate: 1002, 1004, 1006…
Since the two sets of values are mutually exclusive, no conflict can occur.
To streamline this process and minimize manual intervention, I developed a script that automatically generates DDLs to convert existing sequences into a format suitable for bi-directional replication.
The script analyzes each sequence’s current value and cache settings, calculates the appropriate starting point to avoid overlaps, and recreates the sequence with an INCREMENT BY 2. It then generates two versions of each sequence: one configured to produce odd values for Site A and another for even values for Site B. This ensures that each site generates non-overlapping values, effectively preventing primary key conflicts during active-active replication.
Here is the example script, you can always improve it for multiple sites, as I will describe below.
DROP SEQUENCE MY_SEQ;
CREATE SEQUENCE MY_SEQ START WITH 253 INCREMENT BY 1 CACHE 20 NOCYCLE;
DROP SEQUENCE MY_SEQ2;
CREATE SEQUENCE MY_SEQ2 START WITH 23075 INCREMENT BY 1 CACHE 20 NOCYCLE;
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
v_owner VARCHAR2(128) := 'HR'; -- Change to your schema
v_nextval NUMBER;
v_cache_size NUMBER;
v_reserved_max NUMBER;
v_odd_start NUMBER;
v_even_start NUMBER;
v_odd_ddl VARCHAR2(1000);
v_even_ddl VARCHAR2(1000);
BEGIN
FOR seq_rec IN (
SELECT sequence_name, cache_size
FROM all_sequences
WHERE sequence_owner = UPPER(v_owner)
ORDER BY sequence_name
)
LOOP
-- Dynamically get NEXTVAL for the sequence
EXECUTE IMMEDIATE
'SELECT ' || v_owner || '.' || seq_rec.sequence_name || '.NEXTVAL FROM dual'
INTO v_nextval;
v_cache_size := seq_rec.cache_size;
v_reserved_max := v_nextval + (v_cache_size - 1);
-- Compute next safe ODD/EVEN values
IF MOD(v_reserved_max, 2) = 0 THEN
v_even_start := v_reserved_max + 2;
v_odd_start := v_reserved_max + 1;
ELSE
v_even_start := v_reserved_max + 1;
v_odd_start := v_reserved_max + 2;
END IF;
-- Generate DDL statements
v_odd_ddl := 'CREATE SEQUENCE ' || seq_rec.sequence_name ||
' START WITH ' || v_odd_start ||
' INCREMENT BY 2 CACHE ' || v_cache_size || ' NOCYCLE;';
v_even_ddl := 'CREATE SEQUENCE ' || seq_rec.sequence_name ||
' START WITH ' || v_even_start ||
' INCREMENT BY 2 CACHE ' || v_cache_size || ' NOCYCLE;';
-- Output
DBMS_OUTPUT.PUT_LINE('-- Sequence: ' || seq_rec.sequence_name);
DBMS_OUTPUT.PUT_LINE(' Current NEXTVAL : ' || v_nextval);
DBMS_OUTPUT.PUT_LINE(' Cached values reserved up to : ' || v_reserved_max);
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('-- Site A (ODD)');
DBMS_OUTPUT.PUT_LINE(' ' || v_odd_ddl);
DBMS_OUTPUT.PUT_LINE('-- Site B (EVEN)');
DBMS_OUTPUT.PUT_LINE(' ' || v_even_ddl);
DBMS_OUTPUT.PUT_LINE('------------------------------------------------');
END LOOP;
END;
/
Example of the Script Output
Sequence MY_SEQ dropped.
Sequence MY_SEQ created.
Sequence MY_SEQ2 dropped.
Sequence MY_SEQ2 created.
-- Sequence: DEPARTMENTS_SEQ
Current NEXTVAL : 310
Cached values reserved up to : 309
-- Site A (ODD)
CREATE SEQUENCE DEPARTMENTS_SEQ START WITH 311 INCREMENT BY 2 CACHE 0 NOCYCLE;
-- Site B (EVEN)
CREATE SEQUENCE DEPARTMENTS_SEQ START WITH 310 INCREMENT BY 2 CACHE 0 NOCYCLE;
------------------------------------------------
-- Sequence: EMPLOYEES_SEQ
Current NEXTVAL : 210
Cached values reserved up to : 209
-- Site A (ODD)
CREATE SEQUENCE EMPLOYEES_SEQ START WITH 211 INCREMENT BY 2 CACHE 0 NOCYCLE;
-- Site B (EVEN)
CREATE SEQUENCE EMPLOYEES_SEQ START WITH 210 INCREMENT BY 2 CACHE 0 NOCYCLE;
------------------------------------------------
-- Sequence: LOCATIONS_SEQ
Current NEXTVAL : 3600
Cached values reserved up to : 3599
-- Site A (ODD)
CREATE SEQUENCE LOCATIONS_SEQ START WITH 3601 INCREMENT BY 2 CACHE 0 NOCYCLE;
-- Site B (EVEN)
CREATE SEQUENCE LOCATIONS_SEQ START WITH 3600 INCREMENT BY 2 CACHE 0 NOCYCLE;
------------------------------------------------
-- Sequence: MY_SEQ
Current NEXTVAL : 253
Cached values reserved up to : 272
-- Site A (ODD)
CREATE SEQUENCE MY_SEQ START WITH 273 INCREMENT BY 2 CACHE 20 NOCYCLE;
-- Site B (EVEN)
CREATE SEQUENCE MY_SEQ START WITH 274 INCREMENT BY 2 CACHE 20 NOCYCLE;
------------------------------------------------
-- Sequence: MY_SEQ2
Current NEXTVAL : 23075
Cached values reserved up to : 23094
-- Site A (ODD)
CREATE SEQUENCE MY_SEQ2 START WITH 23095 INCREMENT BY 2 CACHE 20 NOCYCLE;
-- Site B (EVEN)
CREATE SEQUENCE MY_SEQ2 START WITH 23096 INCREMENT BY 2 CACHE 20 NOCYCLE;
------------------------------------------------
PL/SQL procedure successfully completed.
Multi-Site Sequence Generation Strategy
When working with three or more active-active GoldenGate sites, the odd/even strategy no longer applies. Instead, you need a general solution that scales with the number of participating sites. The best practice is to define sequences using an increment equal to the number of sites, and assign each site a unique starting offset.
Use INCREMENT BY “N” Where “N” = Number of Sites
Each site should:
- Use the same sequence name
- Set INCREMENT BY to the total number of active-active sites
- Use a unique START WITH value between 1 and N
This ensures each site generates values that are disjoint and non-conflicting.
Example: 3-Site Active-Active Setup
| Site | START WITH | INCREMENT BY | Values Generated |
|---|---|---|---|
| A | 1 | 3 | 1, 4, 7, 10, … |
| B | 2 | 3 | 2, 5, 8, 11, … |
| C | 3 | 3 | 3, 6, 9, 12, … |
Example: 4-Site Active-Active Setup
| Site | START WITH | INCREMENT BY | Values Generated |
|---|---|---|---|
| A | 1 | 4 | 1, 5, 9, 13, … |
| B | 2 | 4 | 2, 6, 10, 14, … |
| C | 3 | 4 | 3, 7, 11, 15, … |
| D | 4 | 4 | 4, 8, 12, 16, … |
I would love to see someone improve the script to accommodate N number of sites. :-)
Conclusion
When deploying Oracle GoldenGate in active-active replication mode, sequence conflict prevention is not optional; it’s a must. By applying this odd-even partitioning technique, you can prevent subtle data corruption issues and maintain a robust and conflict-free replication architecture. With more than two sites in an active-active GoldenGate topology, it’s critical to partition sequence values using a scalable method. Configuring sequences with INCREMENT BY “N” unique starting offsets allows you to prevent ID collisions cleanly and predictably. This pattern ensures reliable insert operations while maintaining simplicity in your replication architecture.
Alex Lima 
Leave a comment