灾难恢复后 Oracle 序列会发生什么情况？
假设灾难发生后必须恢复 Oracle 实例。 序列是否重置为初始状态或上次保存的状态，或者是否保留缓存值？
非常感谢。 :-)
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. :-)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
序列值存储在 SYSTEM.SEQ$ （我认为）表中，并且在内存中维护下一个要使用的值的缓存，该缓存的大小取决于序列的 CACHE 值。
当缓存耗尽时，SEQ$ 表会更新为新值（以不一致的方式，即不应用用户会话的事务控制），然后从内存中读取接下来的 100 个值（如果 CACHE=100） 。
假设您使用的序列的缓存大小为 20。当您从序列中选择某个值（例如 1400）时，SEQ$ 表将更新为值 1420。即使您回滚事务，SEQ$ 表也会更新为 1420。仍然具有该值，直到使用了接下来的 20 个序列值，此时 SEQ$ 更新为 1440。如果您刚刚使用了值 1423 并且发生实例崩溃，那么当系统重新启动时，将读取下一个值序列将为 1440。
因此，是的，序列的完整性将被保留，并且号码不会“重新发布”。 请注意，这同样适用于正常关闭 - 当您重新启动时，您将在上面的示例中获得新值 1440。 由于这个原因，在实践中不能保证序列是无间隙的（也因为使用一个值然后回滚不会将该值恢复到缓存）。
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).
并不是说我有任何这方面的经验，但我非常假设恢复到一致的系统更改编号状态也会将序列返回到最后保存的状态。 其他任何东西对于恢复来说都是毫无用处的。
至于缓存的值，即使实例有序关闭，这些值也会（可能）丢失（*）：实例在内存（SGA）中缓存多个序列值，而不是每次都访问数据库。 实例保留的未使用的序列值可能会“消失”，从而在序列中留下间隙。
(*) 8i 文档提到这种情况可能会在并行实例 (RAC) 中发生，在这种情况下，序列甚至可能不是严格升序的（但仍然是唯一的），10g 文档说这种情况会在实例失败的情况下发生。
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.