...
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:
| No Format | ||
|---|---|---|
| ||
SQL> select sequence_owner, sequence_name, last_number from dba_sequences; |
On Target
Reset the sequences use the following procedure:
| No Format | ||
|---|---|---|
| ||
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:
| No Format | ||
|---|---|---|
| ||
declare
begin
Sequence_reset('SEQ_REPLICATE_ID',25);
end;
/ |