When using a hot/hot environment with GoldenGate, it is necessary to avoid PK collisions. The standard approach is for one side to use even numbers and the other to use odd.
Here’s a script that will change all the sequences within a schema to either even or odd:
DECLARE
PROCEDURE set_sequence (i_owner IN VARCHAR2,
i_sequence IN VARCHAR2,
i_mod IN NUMBER) IS
v_sql_block VARCHAR2(4000);
v_seq_val NUMBER;
v_counter NUMBER :=0;
BEGIN
IF i_mod NOT IN (0,2) THEN RAISE_APPLICATION_ERROR(-20001,'Must use 0 or 1'); END IF;
EXECUTE IMMEDIATE 'alter sequence '||i_owner||'.'||i_sequence||' increment by 2';
EXECUTE IMMEDIATE 'SELECT '||i_owner||'.'||i_sequence||'.NEXTVAL FROM DUAL' INTO v_seq_val;
WHILE MOD(v_seq_val,2) != i_mod
AND v_counter <= 1000 LOOP
EXECUTE IMMEDIATE 'alter sequence '||i_owner||'.'||i_sequence||' increment by 1';
EXECUTE IMMEDIATE 'SELECT '||i_owner||'.'||i_sequence||'.NEXTVAL FROM DUAL' INTO v_seq_val;
EXECUTE IMMEDIATE 'alter sequence '||i_owner||'.'||i_sequence||' increment by 2';
EXECUTE IMMEDIATE 'SELECT '||i_owner||'.'||i_sequence||'.NEXTVAL FROM DUAL' INTO v_seq_val;
v_counter := v_counter + 1;
END LOOP;
IF MOD(v_seq_val,2) != i_mod
THEN
IF i_mod = 0
THEN
dbms_output.put_line(i_sequence||' is not odd!');
ELSE
dbms_output.put_line(i_sequence||' is not even!');
END IF;
END IF;
END; --set_sequence
BEGIN
FOR rSeq IN (SELECT sequence_owner,
sequence_name
FROM dba_sequences
WHERE sequence_owner = 'YOUR OWNER HERE') LOOP
--Pass 0 for even sequences, 1 to see sequences to odd.
set_sequence(rSeq.sequence_owner,
rSeq.sequence_name,
0);
END LOOP;
END;
/