马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
今天在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 1717: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 RealApplication Testing options SQL> create sequence seq_1 minvalue 1 maxvalue 10000start with 1 increment by 1 cache 100; Sequence created. SQL> selectSEQUENCE_NAME,MIN_VALUE,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER fromuser_sequences; SEQUENCE_NAME MIN_VALUE INCREMENT_BYCACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------------------- ----------- SEQ_1 1 1 100 1 查询这个序列,由于chache设置为100,此时1-100的序列值已经被拿到内存中,LAST_NUMBER被修改为101。 SQL> selectseq_1.nextval from dual; NEXTVAL ---------- 1 SQL> selectSEQUENCE_NAME,MIN_VALUE,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER fromuser_sequences; SEQUENCE_NAME MIN_VALUE INCREMENT_BYCACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------------------- ----------- SEQ_1 1 1 100 101 当序列的值在100以内,LAST_NUMBER不会再被改变。 SQL> select seq_1.nextval from dual; NEXTVAL ---------- 2 SQL> selectSEQUENCE_NAME,MIN_VALUE,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER fromuser_sequences; SEQUENCE_NAME MIN_VALUE INCREMENT_BYCACHE_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> selectSEQUENCE_NAME,MIN_VALUE,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER fromuser_sequences; SEQUENCE_NAME MIN_VALUE INCREMENT_BYCACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------------------- ----------- SEQ_1 1 1 100 3 SQL> select seq_1.nextval from dual; NEXTVAL ---------- 3 SQL> selectSEQUENCE_NAME,MIN_VALUE,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER fromuser_sequences; SEQUENCE_NAME MIN_VALUE INCREMENT_BYCACHE_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> selectSEQUENCE_NAME,MIN_VALUE,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER fromuser_sequences; SEQUENCE_NAME MIN_VALUE INCREMENT_BYCACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------------------- ----------- SEQ_1 1 1 100 103 可见,在shutdownimmediate的情况下,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> selectSEQUENCE_NAME,MIN_VALUE,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER fromuser_sequences; SEQUENCE_NAME MIN_VALUE INCREMENT_BYCACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------------------- ----------- SEQ_1 1 1 100 103 SQL> select seq_1.nextval from dual; NEXTVAL ---------- 103 可见在shutdownabort模式下关闭数据库,数据库并不会自动维护序列,被拿到内存中的数值将被丢弃,也就是会出现段号的情况。 总结:在shutdownimmediate这种一致性关闭数据库的时候,ORACLE会把内存中的数据写到磁盘,这个过程中包含自动维护序列,而shutdown baort这种暴力关闭数据库到时候,ORACLE会舍弃内存中的数据,也包含在内存中的序列的值,因此,在一致性关闭数据库的时候,序列不会段号,而非一致性关闭数据库的时候,序列就会遇到段号的情况(序列的值正巧是内存中最后一个序列值的情况除外)。
来自群组: Oracle DBA交流 |