标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,671)
- DB2 (22)
- MySQL (73)
- Oracle (1,533)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (21)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (65)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (560)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (78)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- Kylin Linux 安装19c
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
- ORA-600 16703故障再现
- 数据库启动报ORA-27102 OSD-00026 O/S-Error: (OS 1455)
- .[metro777@cock.li].Elbie勒索病毒加密数据库恢复
- 应用连接错误,初始化mysql数据库恢复
- RAC默认服务配置优先节点
- Oracle 19c RAC 替换私网操作
- 监听报TNS-12541 TNS-12560 TNS-00511错误
- drop tablespace xxx including contents恢复
标签归档:sequence
正常关闭数据库sequence cache不为0 sequence不跳跃
在Oracle中sequence使用很频繁,而大部分使用都配合了cache一起使用,那在sequence+cache一起使用的过程中,如果数据库正常关闭sequence.nextval如果变化,如果数据库异常关闭sequence.nextval又如何变化?这里通过试验进行了证明,结论为:在有cache的sequence中,正常关闭数据库sequence不会出现跳跃,异常关闭数据库很可能导致sequence出现跳跃
创建sequence测试
[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.xifenfei.com" 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
这里创建了一个名为SEQ_XIFENFEI的sequence,并且nextval已经查询到3,而且cache值设置为100,object_id为87549.
正常关闭数据库测试sequence.nextval变化
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
正常关闭数据库启动后,sequence.nextval依然在上次基础之上增加,并为出现跳跃现象.
异常关闭数据库测试sequence.nextval变化
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
异常关闭数据库启动后,sequence.nextvla发生跳跃从本该5直接跳跃到了104.
跟踪数据库正常关闭过程
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
再次证明了正常关闭数据库后,sequence.nextval未发生跳跃.
分析trace文件
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(02) 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(02) 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(02) 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(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(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(02) 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(03) 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值 Bind#7 oacdty=01 mxl=32(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(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(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 <---这个就是我们的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
这里我们找出来了为什么数据库正常关闭sequence.nextval在有cache的情况下,未发生跳跃:因为在数据库正常关闭的情况下,会触发一个update seq$的操作,把当前的sequence.nextval的值更新到seq$.highwater中,从而使得sequence在有cache的情况下,数据库正常关闭未出现nextval跳跃(currval也同样不跳跃);而在数据库异常关闭之时,数据库不能及时将sequence.nextval更新到eq$.highwater从而引起sequence cache中的值丢失,从而可能出现了sequence使用cache导致跳跃的情况
关于另外一篇关于sequence cache减小update seq$频率的测试,请见:关于oracle sequence一些小测试