今天在ITPUB看到一个关于序列在数据库重启之后是否会丢失cache部分的帖子，感觉很有意思，一时手痒也做了个实验。实验环境是CentOS 6.3操作系统，11.2.0.4.0版本的数据库，实验过程如下：

普通用户登录数据库，创建一个序列。

[oracle@db98 dbs]$ sqlplus dbdream/dbdream

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 17 17:58:28 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create sequence seq_1 minvalue 1 maxvalue 10000 start with 1 increment by 1 cache 100;

Sequence created.

SQL> select SEQUENCE_NAME,MIN_VALUE,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER from user_sequences;

SEQUENCE_NAME                   MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ------------ ---------- -----------
SEQ_1                                   1            1        100           1

查询这个序列，由于chache设置为100，此时1-100的序列值已经被拿到内存中，LAST_NUMBER被修改为101。

SQL>  select seq_1.nextval from dual;

   NEXTVAL
----------
         1

SQL> select SEQUENCE_NAME,MIN_VALUE,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER from user_sequences;

SEQUENCE_NAME                   MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ------------ ---------- -----------
SEQ_1                                   1            1        100         101
当序列的值在100以内，LAST_NUMBER不会再被改变。
SQL> select seq_1.nextval from dual;

   NEXTVAL
----------
         2

SQL> select SEQUENCE_NAME,MIN_VALUE,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER from user_sequences;

SEQUENCE_NAME                   MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ------------ ---------- -----------
SEQ_1                                   1            1        100         101

这是如果重启数据库会发生什么情况，内存中的序列值是否会被丢弃？下面看下shutdown immediate和shutdown abort两种场景下，序列将怎样维护。

shutdown immediate

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1607008256 bytes
Fixed Size                  1364928 bytes
Variable Size             452987968 bytes
Database Buffers         1140850688 bytes
Redo Buffers               11804672 bytes
Database mounted.
Database opened.

SQL> select SEQUENCE_NAME,MIN_VALUE,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER from user_sequences;

SEQUENCE_NAME                   MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ------------ ---------- -----------
SEQ_1                                   1            1        100           3

SQL> select seq_1.nextval from dual;

   NEXTVAL
----------
         3
SQL> select SEQUENCE_NAME,MIN_VALUE,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER from user_sequences;

SEQUENCE_NAME                   MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ------------ ---------- -----------
SEQ_1                                   1            1        100         103

SQL> select seq_1.nextval from dual;

   NEXTVAL
----------
         4

SQL> /

   NEXTVAL
----------
         5

SQL> /

   NEXTVAL
----------
         6

SQL> /

   NEXTVAL
----------
         7

SQL> select SEQUENCE_NAME,MIN_VALUE,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER from user_sequences;

SEQUENCE_NAME                   MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ------------ ---------- -----------
SEQ_1                                   1            1        100         103

可见，在shutdown immediate的情况下，ORACLE会自动维护序列，在关闭数据库的过程中会将last number修改成next value的值，这样在重启后，再次使用序列的时候，就不会出现段号的情况。

shutdown abort

SQL> conn / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1607008256 bytes
Fixed Size                  1364928 bytes
Variable Size             452987968 bytes
Database Buffers         1140850688 bytes
Redo Buffers               11804672 bytes
Database mounted.
Database opened.
SQL> conn dbdream/dbdream
Connected.
SQL> select SEQUENCE_NAME,MIN_VALUE,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER from user_sequences;

SEQUENCE_NAME                   MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ------------ ---------- -----------
SEQ_1                                   1            1        100         103

SQL> select seq_1.nextval from dual;

   NEXTVAL
----------
       103

可见在shutdown abort模式下关闭数据库，数据库并不会自动维护序列，被拿到内存中的数值将被丢弃，也就是会出现断号的情况。

总结：在shutdown immediate这种一致性关闭数据库的时候，ORACLE会把内存中的数据写到磁盘，这个过程中包含自动维护序列，而shutdown baort这种暴力关闭数据库到时候，ORACLE会舍弃内存中的数据，也包含在内存中的序列的值，因此，在一致性关闭数据库的时候，序列不会断号，而非一致性关闭数据库的时候，序列就会遇到段号的情况(序列的值正巧是内存中最后一个序列值的情况除外)。

本文固定链接: http://www.dbdream.com.cn/2015/04/sequence%e5%9c%a8%e6%95%b0%e6%8d%ae%e5%ba%93%e9%87%8d%e5%90%af%e4%b9%8b%e5%90%8e%e6%98%af%e5%90%a6%e4%bc%9a%e4%b8%a2%e5%a4%b1cache%e9%83%a8%e5%88%86%e7%9a%84%e5%ae%9e%e9%aa%8c/ | 信春哥，系统稳，闭眼上线不回滚！