Sequences
Dbvisit Replicate does not update target Oracle sequences.
It does replicate the sequence value that is inserted into the source data from the Oracle sequences (normally a primary key).
So the end result is that the primary key values will be the same on both source and target.
For active-active replication it is necessary to ensure that the sequences do not collide with each other. The way to do that is to set one sequence to only even numbers and the other to only odd numbers. Or to have one sequence start from 1 and the other count down from a large number.Â
Updating target sequences
For zero-down-time migrations it is important that the target sequences are higher than the sequences on the source database, once the migration has been completed and the final cut-over has been made.
The following process can be used to update target sequences for zero-down-time migrations as part of the final cut-over:
On Source
Select the sequences and their max numbers on the source database:
SQL> select sequence_owner, sequence_name, last_number from dba_sequences;
Â
On Target
Reset the sequences use the following procedure:
create or replace procedure Sequence_Reset( p_seq_name in varchar2, p_val in number default 0) is l_current number := 0; l_difference number := 0; l_minvalue user_sequences.min_value%type := 0; begin select min_value into l_minvalue from user_sequences where sequence_name = p_seq_name; execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_current; if p_Val < l_minvalue then l_difference := l_minvalue - l_current; else l_difference := p_Val - l_current; end if; if l_difference = 0 then return; end if; execute immediate 'alter sequence ' || p_seq_name || ' increment by ' || l_difference || ' minvalue ' || l_minvalue; execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_difference; execute immediate 'alter sequence ' || p_seq_name || ' increment by 1 minvalue ' || l_minvalue; end Sequence_Reset;
Â
Call the procedure for each sequence that needs to be reset, passing in the sequence name and the new value.
Â
Example:
declare begin Sequence_reset('SEQ_REPLICATE_ID',25); end; /