| |
 |
|
Oracle Streams Replication Sequence Number Verification
Oracle Tips by Kent Crotty |
Question: I read that I can use Streams 2-way
replication instead of RAC, but I don't understand how to manage
shared application components and ensure that I don't have
overlapping sequence numbers.
Answer: Using Oracle Streams instead of RAC for
n-way replication is a great solution for shops that don't have a
high volume of update traffic.
(see
Oracle multi-master replication with Oracle Streams)
The "Oracle
Streams" book has a complete step-by-step guide
for understanding Streams sequence numbers.
For managing sequences, most DBA's will use an even-odd or
positive/negative approach. I like the even/odd, and I define
sequences on one instance as even and odd on the other:
create sequence
customer_sequence start at 2 increment by 2;
create sequence customer_sequence start at 1 increment by 2;
It's always good to have verification, and
you can write a simple PL/SQL procedure to check and ensure that
the sequences are correct, even if your schema has hundreds of
sequences. Note that the Korn shell script below does an
affirmative check to send an alert if the script fails for any
reason, requiring the PL/SQL "successfully completed" message:
check_sequences.sql
set serveroutput on format wrap
set verify off
spool check_seq.lst
declare
seq_test varchar2(4); -- even, odd.
error_chk boolean := FALSE;
begin
seq_test := '&1';
for c1 in
(select
SEQUENCE_NAME,
LAST_NUMBER,
INCREMENT_BY
from
user_sequences)
loop
if (upper(seq_test) = 'EVEN') then -- check sequence
is even number
if (mod(c1.LAST_NUMBER,2)!=0) then -- it is odd
error_chk := TRUE;
end if;
else -- check sequence is odd number
if (mod(c1.LAST_NUMBER,2)=0) then -- it is even
error_chk := TRUE;
end if;
end if;
if error_chk then
dbms_output.put_line( 'ERROR: Sequence
'||c1.sequence_name||' is incorrect!');
dbms_output.put_line( 'Last Number is
'||c1.sequence_name);
dbms_output.put_line( 'It should be '||seq_test||'.');
dbms_output.put_line(' ');
error_chk := FALSE;
end if;
end loop;
end;
/
spool off;
exit
check_sequences.ksh
#!/bin/ksh
# Source the db.env to set the Oracle environment
. /home/oracle/db.env
# Run the scripts to check the sequence numbers. Send
# a paramater 'even' or 'odd' to check
sqlplus -s schema_robert @/home/oracle/scripts/check_seq odd
# If there were errors, the list file will have lines that begin
with ERROR
# so lets check to see how many there were. If more than 0 then
# email the list file to the DBA's
check_success=`grep 'ERROR' /home/oracle/scripts/check_seq.lst|wc
-l`
oracle_num=`expr $check_success`
if [ $oracle_num -ne 0 ]
then
/bin/mailx -s "** Sequence Error **" burleson2@usgov.com <
/home/oracle/scripts/check_seq.lst
fi
# Let's check to make sure the PL/SQL procedure ran correctly. If it
does there
# will be a line that contains 'successfully completed'. If there
isn't, send an email.
check_success=`grep 'successfully completed' /home/oracle/scripts/check_seq.lst|wc
-l`
oracle_num=`expr $check_success`
if [ $oracle_num -ne 1 ]
then
/bin/mailx -s "** Sequence Check Error!**" burleson2@usgov.com
fi
Cron entry
#**********************************************************
# Sequence Number Check
#**********************************************************
00 7 * * * /home/oracle/scripts/check_seq.ksh > /tmp/chkseq.lst 2>&1
 |
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of my favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
and include the URL for the page.
