Re: Values in sequence field during failover

In article <19980921225227.20160.00002958_at_ng17.aol.com>,   danhw_at_aol.com (DanHW) wrote:
>
> >Oracle DBA's:
> >
> >In a high-availability Unix environment, do the values in an Oracle sequence
> >field go crazy when one node fails, and another takes over from it? For
> >example, are duplicate sequence values possible after a failure?
> >
> >Although I am a novice, I have heard that they do, because they are stored in
> >memory until they are written to disk. I heard about one designer who
> had to
> >re-write his application to manually increment the one-up identifiers (keys)
> >for his tables instead of using "sequence."
> >
> >Thank you for your attention.
> >
> >Tom McCready
> >Library of Congress developer
> >
>
> I don't know if this exactly answers the question, but it has been my
> observation that when the database is shutdown, the cached values are 'used
> up'. For example, you have sequence that starts at 1, with a cache of 20. If
> you shut down, the next value available will be 21, not 2.
>
> Dan Hekimian-Williams

Sequence values are not replicated between databases. So if a primary and secondary both have a sequence named emp_id_seq and they both start at 1, then you will reuse those sequence numbers on the secondary when it fails-over.

Since sequence numbers are nothing more than a way to generate a unique number, you can have different schemes on each node (to avoid duplicates.)

I use the following approaches:

1. make one node start at 1 and increment by 2 (i.e. odd numbers) and make the second node start at 2 and increment by 2 (i.e. even numbers)
2. have one node start at 1 and loop back after some high number (say 999,999,999,999) while the second node starts at 1,000,000,000,000 and loops back after 2,000,000,000,000. I believe sequence number can have 38 significant digits, so there's alot of room to work with.

-Frank

P.S. As far as caching number, Im sure Oracle handles it well, although you can just turn this feature off when you create the sequence.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Tue Sep 22 1998 - 08:19:58 CDT