Thursday, March 26, 2009

Adjust Oracle sequence to use odd and even number respectively on a Production and DR database

Our production databases are replicated to DR databases through SharePlex replcation. Recently, We have a need to enable two-way replication. i.e. in addition to replication from production to DR, we also need DR to prodcution. I was thus assigned a task to adjust the sequences in production to use odd number and sequences in DR to use even number.

The sequneces in production and DR are out-of-sync currently, as we don't replicate sequence from production to DR. That means if on production a sequence's last number is 1000, whereas on DR, the number could be 1.

Testing for a while, I found that when generating the DDL for a sequence through DBMS_METADATA package, the number following the 'start with' clause is the last number of DBA_SEQUENCES view.

For example:



SQL> select sequence_name, last_number from dba_sequences
where sequence_owner=user;

SEQUENCE_NAME LAST_NUMBER
------------------------------ -----------
TEST2_SEQ 107
TEST3_SEQ 107
TEST_SEQ 175


SQL> ;
1* SELECT DBMS_METADATA.GET_DDL(upper('&OBJTYPE'), upper('&OBJNAME') , upper('&OWNER')) ddl_string from dual
SQL> /
Enter value for objtype: sequence
Enter value for objname: TEST_SEQ
Enter value for owner: abc

CREATE SEQUENCE "ABC"."TEST_SEQ" MINVALUE 1 MAXVALUE 1.00000000000000E+27
INCREMENT BY 98 START WITH 175 CACHE 20 NO
ORDER NOCYCLE ;



  

I thus developed the following plan:

1. On Prod, run script seq_odd.sql to change seq number to odd and increment by to 2
2. On prod, run script seq_ddl_gen.sql to generate sequence ddl script: seq_ddl.sql
3. SCP seq_ddl.sql to DR server
4. On DR, run script seq_drop_gen.sql and then seq_drop.sql to drop sequence
5. On DR, run script seq_ddl.sql generated in step 2 to re-create all sequences
6. On DR, run script seq_incr1_gen.sql, seq_incr1.sql to modify sequence increment by to 1;
7. On DR, run script seq_nextval_gen.sql, seq_nextval.sql to make the currval of sequences to be even number,
8. on DR, run script seq_incr2_gen.sql, seq_incr2.sql to modify sequence increment by to 2;

The above scripts are simple execept for seq_odd.sql, I wrote PL/SQL code for this:



---- seq_odd.sql -----
declare
seqown varchar2(30);
seqname varchar2(30);
sqlstmt varchar2(1000);
cval number;
incr number;
cursor seq_cur is
select sequence_owner, sequence_name
from dba_sequences
where sequence_owner is [some_condition];
begin
open seq_cur;
loop
fetch seq_cur into seqown, seqname;
exit when seq_cur%notfound;
sqlstmt := 'select ' seqown '.' seqname '.nextval from dual';
execute immediate sqlstmt into cval;

if ( mod(cval, 2) = 0 )
then
-- ensure the current val is odd number
-- first change increment by 1
sqlstmt := 'alter sequence ' seqown '.' seqname ' increment by 1';
execute immediate sqlstmt;

sqlstmt := 'select ' seqown '.' seqname '.nextval from dual';
execute immediate sqlstmt into cval;
sqlstmt := 'alter sequence ' seqown '.' seqname ' increment by 2';
execute immediate sqlstmt;
else
-- already an odd number
sqlstmt := 'alter sequence ' seqown '.' seqname ' increment by 2';
execute immediate sqlstmt;
end if;
end loop;
close seq_cur;

-- follwing code is to verify ----
---- end of seq_odd.sql --------

No comments: