【搬砖】ORA-01555 ORA-22924: snapshot too old
本文转自dbdream的博客 源地址：http://www.dbdream.org/?p=1520
由博主dbdream发布于2014年7月
昨晚接到一个电话，打电话的是开发人员，说数据库遇到ORA-01555错误，应用无法正常使用，当时很纳闷，ORA-01555错误通常影响的是查询操作，怎么会影响业务无法使用呢？
按照正常流程，让他查了下undo表空间和undo保留时间，发现除了undo保留时间较短外并没有异常。
SQL> select a.NAME,b.FILE_NAME,b.BYTES/1024/1024/1024 as BYTES,b.MAXBYTES/1024/1024/1024 as MAXBYTES from v$tablespace a,dba_data_files b where a.NAME=b.TABLESPACE_NAME AND A.NAME='UNDOTBS'; NAME FILE_NAME BYTES MAXBYTES ---------- ------------------------------------------------ ---------- ---------- UNDOTBS /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf .48828125 31.9999847
SQL> show parameter undo_r NAME TYPE VALUE ------------------------- ----------- --------- undo_retention integer 900
虽然undo的保留时间只有900秒(15分钟)，可是undo表空间只使用了500M，而undo表空间最大可以扩展到32G，说明undo表空间还是足够的，将undo_retention修改为10800秒(3小时)，开发人员说问题依旧，还是报快照过旧，而且SQL刚运行就报错，怀疑是使用了闪回查询，开发人员不懂，发过来SQL语句看了下，只是一条非常简单的SQL，并没有使用闪回查询。
select * from t_restree where c_pid = 'BPM_TREENODE';
难道是遇到了10.2.0.5版本之前的ORA-01555的BUG了？申请远程看下，远程后发现特别奇怪，告警日志并没有记录这个ORA-01555的任何信息，无论是SYSTEM UNDO还是UNDO TABLESPACE导致的ORA-01555错误还是BUG导致的ORA-01555错误，告警日志通常都会记录，而这个却没有记录。查看数据库版本，发现是11.2.0.3.0版本。
由于是查询操作，直接在SQLPLUS里查询，发现的确会遇到ORA-01555错误。
SQL> select * from jxpg_smartbi.t_restree where c_pid = 'BPM_TREENODE'; ERROR: ORA-01555: snapshot too old: rollback segment number with name "" too small ORA-22924: snapshot too old
正常无论是SYSTEM UNDO还是UNDO TABLESPACE导致的ORA-01555错误，在报错提示中通常都可以看到回滚段的number号和名字，而这个报错number和name都是空的，很奇怪，而且ORA-01555错误下面还带着ORA-22924错误，这让我想到了第三种UNDO，就是LOB的回滚段。这里先说一下ORACLE数据库的回滚段，我知道的有三种：
1.system undo：system回滚段，使用system表空间，system undo损坏通常需要借助bbed工具恢复，相对复杂。
2.undo tablespace：这个大家都知道，存一些用户DML操作的数据块的前镜像，保证一致性读，大部分的ORA-01555错误都与undo tablespace有关。
3.LOB undo：这个可能很多人就不知道了，LOB大对象有自己的undo空间，并不使用undo tablespace，而是使用存放LOB本身的数据块，使用的是LOB使用的表空间，而且大小受LOB对象占用的数据块个数与pctversion的影响。
通常system undo出发ORA-01555错误都是在数据库启动的时候，而且数据库起不来，而且业务用户基本不会触发，第一种情况基本可以排除了。第二种情况也不符合，除了BUG和闪回查询，如果是第二种情况基本不会在SQL运行时立马抛出ORA-01555错误。那么，最大的嫌疑就是第三种情况了。
查看表结构，是否存在LOB字段(应该是CLOB，BLOB通常不会在数据库中做DML操作)。
SQL> desc jxpg_smartbi.t_restree Name Null? Type ----------------------------------------------------- -------- ------------------------------------ C_RESID NOT NULL VARCHAR2(255) C_RESNAME VARCHAR2(255) C_RESTYPE VARCHAR2(255) C_PERM CLOB C_RESDESC VARCHAR2(255) C_STATUS VARCHAR2(255) C_LASTMODIFIED DATE C_RESALIAS VARCHAR2(255) C_CREATED DATE C_ORDER NUMBER(38) C_EXTENDED CLOB C_PID VARCHAR2(255)
可见，表中包含两个CLOB字段，和猜测的基本一致，在看看ORA-22924错误，快照过旧不都是ORA-01555吗，怎么又出来个22924，查看22924的错误描述。
[oracle@ora99 ~]$ oerr ora 22924 22924, 00000, "snapshot too old" // *Cause: The version of the LOB value needed for the consistent read was // already overwritten by another writer. // *Action: Use a larger version pool.
原来ORA-22924是LOB专有的快照过旧的错误代号，到这也就证明我的猜测是对的。查看下他们的表是如何建的，发现还是存在很多问题。
SQL> SET LONG 10000
SQL> select dbms_metadata.get_ddl('TABLE','T_RESTREE','JXPG_SMARTBI') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','T_RESTREE','JXPG_SMARTBI')
--------------------------------------------------------------------------------
CREATE TABLE "JXPG_SMARTBI"."T_RESTREE"
( "C_RESID" VARCHAR2(255) NOT NULL ENABLE,
"C_RESNAME" VARCHAR2(255),
"C_RESTYPE" VARCHAR2(255),
"C_PERM" CLOB,
"C_RESDESC" VARCHAR2(255),
"C_STATUS" VARCHAR2(255),
"C_LASTMODIFIED" DATE,
"C_RESALIAS" VARCHAR2(255),
"C_CREATED" DATE,
DBMS_METADATA.GET_DDL('TABLE','T_RESTREE','JXPG_SMARTBI')
--------------------------------------------------------------------------------
"C_ORDER" NUMBER(*,0),
"C_EXTENDED" CLOB,
"C_PID" VARCHAR2(255),
PRIMARY KEY ("C_RESID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 655360 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JYJGODS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
DBMS_METADATA.GET_DDL('TABLE','T_RESTREE','JXPG_SMARTBI')
--------------------------------------------------------------------------------
NOCOMPRESS LOGGING
STORAGE(INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JYJGODS"
LOB ("C_PERM") STORE AS BASICFILE (
TABLESPACE "JYJGODS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
DBMS_METADATA.GET_DDL('TABLE','T_RESTREE','JXPG_SMARTBI')
--------------------------------------------------------------------------------
LOB ("C_EXTENDED") STORE AS BASICFILE (
TABLESPACE "JYJGODS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
这里发现了几处问题：
ENABLE STORAGE IN ROW：含有LOB字段的表，建议不要讲LOB字段放到行内，应该将LOB字段放到行外，并且存储在独立的表空间，也就是此处应该是DISABLE STORAGE IN ROW。原因是LOB对象通常都较大，存在行内可能会导致行链接，影响性能，将LOB存在独立的表空间，IO性能也会有所提高。
STORE AS BASICFILE：数据库当前版本11.2.0.3.0，11g推出了securefiles新特性来存储LOB字段，读写性能都比BASICFILE要高很多，出于对性能的考虑，应该使用SECUREFILES存储模式来存LOB字段。
PCTVERSION 10：这也是导致这个问题发生的根本原因，PCTVERSION也就是version pool，这部分空间就是预留给LOB使用的回滚段，默认是10%，对于BLOB来讲(BLOB通常只是查看，基本没有在数据库中修改的操作)，10%基本足够了，可是对CLOB来讲，10%的回滚段就不是很富裕了，也正是这样，他们才遇到这个问题。
解决方法：
可以增大PCTVERSION，使LOB的回滚段增大。
扩展表的EXTENT，扩展表的EXTENT也会间接增大LOB的回滚段。
建立BLOCK SIZE为16K的表空间，将表MOVE到新表空间。
Comments are currently closed.