What happens to an Oracle sequence after disaster recovery?

Question

Suppose an Oracle instance has to be recovered after a disaster. Do sequences get reset to the initial state, or the last saved state, or are cached values preserved?

Thank you very much. :-)

Answer 1

The sequnce values are stored in the SYSTEM.SEQ$ (I think) table, and a cache is maintained in memory of the next values to be used, with the size of that cache being dependent on the CACHE value for the sequence.

When the cache is exhausted the SEQ$ table is updated to a new value (in a non-consistent manner -- ie. without the user session's transacton control applying) and the next say 100 values (if CACHE=100) are read from memory.

Let's suppose that you're using a sequence with a cache size of 20. When you select a certain value from the sequence, say 1400, the SEQ$ table is updated to a value of 1420. Even if you rollback your transaction the SEQ$ still has that value until the next 20 sequence values have been used, at which time SEQ$ gets updated to 1440. If you have then just used value 1423 and an instance crash occurs, then when the system restarts the next value to be read from the sequnce will be 1440.

So, yes the integrity of the sequence will be preserved and numbers will not be "reissued". Note that the same applies to a graceful shutdown -- when you restart you will get a new value of 1440 in the above example. Sequences are not guaranteed to be gap free in practice for this reason (also because using a value and then rolling back does not restore that value to the cache).

Answer 2

Not that I have any experience with this, but I very much assume that a recovery to a consistent system change number state would also return the sequence to the last saved state. Anything else would be fairly useless in terms of recovery.

As for cached values, those are (can be) lost even when the instance shuts downs in an orderly manner (*): Instances cache a number of sequence values in memory (SGA) instead of going to the database every time. Unused sequence values that the instance has reserved can "disappear", leaving you with gaps in the sequence.

(*) 8i documentation mentions that this can happen with parallel instances (RAC), in which case the sequence may not even be strictly ascending (but still unique), 10g docs say that it happens in case of an instance failure.