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: