Oralce normal shutdown database sequence cache is not 0 sequence not jump

Sequence is used very frequently in Oracle, and most of the use is in conjunction with the cache, that in the process of using Sequence+cache, if the database is properly shut down sequence.nextval if changed, How does sequence.nextval change if the database shuts down abnormally? Here through the test proved that the conclusion is: in the cache sequence, the normal shutdown database sequence will not jump, abnormal shutdown database is likely to cause sequence jump
Create sequence Test
[Oracle@localhost ~]$ Sqlplus/as SYSDBA

Sql*plus:release 11.2.0.4.0 Production on Sun APR 5 15:44:23 2015

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


Connected to:
Oracle Database 11g Enterprise Edition release 11.2.0.4.0-64bit Production
With the partitioning, OLAP, Data Mining and real application testing options

Sql> Select Sysdate "Www.111cn.net" from dual;

Www.xifen
---------
05-apr-15

sql> Create sequence Seq_xifenfei
2 MinValue 1
3 MaxValue 100000
4 Start with 1
5 increment by 1
6 Cache 100;

Sequence created.

Sql> select Seq_xifenfei.nextval from dual;

Nextval
----------
1

Sql>/

Nextval
----------
2

Sql>/

Nextval
----------
3

Sql> Select object_id from dba_objects where object_name= ' Seq_xifenfei ';

object_id
----------
87549

Sql> SELECT minvalue,highwater,increment$, Cycle#,order$,cache from seq$ where obj#=87549;

MinValue highwater increment$ cycle# order$ CACHE
---------- ---------- ---------- ---------- ---------- ----------
1 101 1 0 0 100
This creates a sequence named Seq_xifenfei, and Nextval has queried to 3, and the cache value is set to 100,object_id 87549.

Normal shutdown database test sequence.nextval changes
sql> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Sql> Startup
ORACLE instance started.

Total System Global area 1570009088 bytes
Fixed Size 2253584 bytes
Variable Size 469765360 bytes
Database buffers 1090519040 bytes
Redo buffers 7471104 bytes
Database mounted.
Database opened.
Sql> SELECT minvalue,highwater,increment$, Cycle#,order$,cache from seq$ where obj#=87549;

MinValue highwater increment$ cycle# order$ CACHE
---------- ---------- ---------- ---------- ---------- ----------
1 4 1 0 0 100

Sql> select Seq_xifenfei.nextval from dual;

Nextval
----------
4

Sql> SELECT minvalue,highwater,increment$, Cycle#,order$,cache from seq$ where obj#=87549;

MinValue highwater increment$ cycle# order$ CACHE
---------- ---------- ---------- ---------- ---------- ----------
1 104 1 0 0 100

After the normal shutdown of the database started, Sequence.nextval is still on the last increase in the foundation, and for the emergence of jumping phenomenon.

Abnormal shutdown database Test sequence.nextval changes

sql> shutdown abort;
ORACLE instance shut down.
Sql> Startup
ORACLE instance started.

Total System Global area 1570009088 bytes
Fixed Size 2253584 bytes
Variable Size 469765360 bytes
Database buffers 1090519040 bytes
Redo buffers 7471104 bytes
Database mounted.
Database opened.
Sql> SELECT minvalue,highwater,increment$, Cycle#,order$,cache from seq$ where obj#=87549;

MinValue highwater increment$ cycle# order$ CACHE
---------- ---------- ---------- ---------- ---------- ----------
1 104 1 0 0 100

Sql> select Seq_xifenfei.nextval from dual;

Nextval
----------
104

Sql> SELECT minvalue,highwater,increment$, Cycle#,order$,cache from seq$ where obj#=87549;

MinValue highwater increment$ cycle# order$ CACHE
---------- ---------- ---------- ---------- ---------- ----------
1 204 1 0 0 100

Abnormal shutdown After the database was started, Sequence.nextvla jumped from the supposed 5 jump directly to 104.

Track database normal shutdown process

Sql> alter session SET events ' 10046 Trace name Context forever, Level 4 ';

Session altered.

sql> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Sql> Startup
ORACLE instance started.

Total System Global area 1570009088 bytes
Fixed Size 2253584 bytes
Variable Size 469765360 bytes
Database buffers 1090519040 bytes
Redo buffers 7471104 bytes
Database mounted.
Database opened.

Sql> SELECT minvalue,highwater,increment$, Cycle#,order$,cache from seq$ where obj#=87549;

MinValue highwater increment$ cycle# order$ CACHE
---------- ---------- ---------- ---------- ---------- ----------
1 105 1 0 0 100

Sql> select Seq_xifenfei.nextval from dual;

Nextval
----------
105

Sql> SELECT minvalue,highwater,increment$, Cycle#,order$,cache from seq$ where obj#=87549;

MinValue highwater increment$ cycle# order$ CACHE
---------- ---------- ---------- ---------- ---------- ----------
1 205 1 0 0 100
Once again proved that the normal shutdown of the database, Sequence.nextval did not occur jump.

Profiling Trace Files

Parsing in CURSOR #139819144537744 len=129 dep=1 uid=0 oct=6 lid=0 tim=1428220381105111
hv=2635489469 ad= ' bc6e6c30 ' sqlid= ' 4m7m0t6fjcs5x '
Update seq$ set Increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,
Highwater=:8,audit$=:9,flags=:10 where obj#=:1
End of STMT
Binds #139819144537744:
Bind#0
Oacdty=02 mxl=22 () mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=bc41e288 bln=22 avl=02 flg=09
Value=1
Bind#1
Oacdty=02 mxl=22 () mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=bc41e29a bln=22 avl=02 flg=09
Value=1
Bind#2
Oacdty=02 mxl=22 () mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
KXSBBBFP=BC41E2AC bln=22 avl=02 flg=09
value=100000
Bind#3
oacdty=02 mxl=22 mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
Kxsbbbfp=7f2a2edc2da8 bln=22 avl=01 flg=05
Value=0
Bind#4
oacdty=02 mxl=22 mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
KXSBBBFP=7F2A2EDC2DC0 bln=22 avl=01 flg=01
Value=0
Bind#5
Oacdty=02 mxl=22 () mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
Kxsbbbfp=bc41e2be bln=22 avl=02 flg=09
value=100
Bind#6
Oacdty=02 mxl=22 () mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
Kxsbbbfp=bc41e2d0 bln=22 avl=03 flg=09
value=105 <----Seq_xifenfei.nextval value
Bind#7
oacdty=01 mxl=32 mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
Kxsbbbfp=bc41e2e2 bln=32 avl=32 flg=09
Value= "--------------------------------"
Bind#8
oacdty=02 mxl=22 mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
Kxsbbbfp=7f2a2edc2d60 bln=22 avl=01 flg=05
Value=0
Bind#9
oacdty=02 mxl=22 mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7f2a2edc2d78 bln=22 avl=04 flg=01
value=87549 <---This is our sequence (Seq_xifenfei)
EXEC #139819144537744: c=0,e=650,p=0,cr=1,cu=2,mis=0,r=1,dep=1,og=4,plh=1935744642,tim=1428220381108066
Close #139819144537744: c=0,e=2,dep=1,type=3,tim=1428220381108119
Here we find out why the database shuts down properly sequence.nextval in the case of cache, there is no jump: because when the database shuts down normally, an update seq$ action is triggered. Update the current sequence.nextval value to Seq$.highwater, so that sequence in the case of cache, the database normal shutdown does not appear nextval jump (Currval also do not jump) And when the database closes abnormally, the database cannot update the sequence.nextval to Eq$.highwater in time to cause the loss of the value in sequence cache, which may lead to the situation that sequence use cache to cause jumps.