Eason

咸鱼咯

【搬砖】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.