Query Duration=0与ORA-01555

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:Query Duration=0与ORA-01555

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

1.ALERT日志错误
奇怪之处:Query Duration=0 sec,竟然出现了ORA-01555

Tue Feb  7 02:41:34 2012
ORA-01555 caused by SQL statement below (Query Duration=0 sec, SCN: 0x0b2e.efcd78a9):
Tue Feb  7 02:41:34 2012
SELECT "ID_NO","CUST_ID" FROM "DBACCADM"."DCUSTMSG" "C" WHERE "ID_NO"=:1

2.ORA-01555解释
超过了undo_retention时间,undo被覆盖导致ORA-01555

[zwq_acc1:/home/oraeye/check]oerr ora 1555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
//         overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
//          setting. Otherwise, use larger rollback segments

3.数据库版本

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

4.undo基本信息
从这里可以发现,两个节点的undo表空间还有很多剩余,缺发生了undo被覆盖从而出现了ORA-01555

SQL> col name for a20
SQL> col value for a15
SQL> SELECT INST_ID, NAME, VALUE
  2    FROM GV$PARAMETER
  3   WHERE UPPER (Name) LIKE '%UNDO%';

   INST_ID NAME                 VALUE
---------- -------------------- ---------------
         1 undo_management      AUTO
         1 undo_tablespace      UNDOTBS1
         1 undo_suppress_errors FALSE
         1 undo_retention       1800
         2 undo_management      AUTO
         2 undo_tablespace      UNDOTBS2
         2 undo_suppress_errors FALSE
         2 undo_retention       1800

8 rows selected.


TABLESPACE_NAME                CURRENT_TOTAL(MB)   USED(MB)   FREE(MB)      FREE% AUT MAX_TOTAL(MB)
------------------------------ ----------------- ---------- ---------- ---------- --- -------------
UNDOTBS1                                   40950    1587.94 39362.0625      96.12 NO          40950
UNDOTBS2                                   57330    1926.31 55403.6875      96.64 NO          57330

SQL> SELECT DISTINCT STATUS ,
  2                  COUNT(*) "EXTENT_NUM",
  3                  SUM(BYTES) / 1024 / 1024 / 1024 "UNDO(G)"
  4    FROM DBA_UNDO_EXTENTS
  5   GROUP BY STATUS;

STATUS    EXTENT_NUM    UNDO(G)
--------- ---------- ----------
ACTIVE           208 .273658752
EXPIRED         7651 2.42865753
UNEXPIRED        941 .752548218

查询MOS[ID 761128.1],发现可能是Oracle bug导致(BUG:6799685 – ORA-1555 ERROR WITH QUERY DURATION=0 AND UNDO_RETENTION=1800和BUG:5475085 – V$UNDOSTAT.EXPBLKREUCNT IS NEVER INCREMENTED)

5.解决方法
Increase the size of the UNDO tablespace and increase the UNDO_RETENTION parameter value to try to prevent required undo expiring too quickly.
基于本库,因为undo空间还有很大剩余,直接设置UNDO_RETENTION=3600即可(可以从一定程度上缓解整个问题,但是要从根本上解决整个问题,需要升级到10.2.0.4及其以上版本)

此条目发表在 ORA-xxxxx 分类目录,贴了 标签。将固定链接加入收藏夹。

Query Duration=0与ORA-01555》有 2 条评论

  1. 惜分飞 说:

    Primary Key Index Corruption Generates ORA-01555 With Small Query Duration or with Query Duration as 0 Seconds (Doc ID 977902.1)

    Applies to:
    
    Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.3 - Release: 10.2 to 11.2
    Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.3   [Release: 10.2 to 11.2]
    Information in this document applies to any platform.
    Symptoms
    
     When running a select statement an incorrect ORA-01555 with query_duration=0 is reported 
    as a side effect of an PK index corruption under the following conditions:
    
    * The error ORA-01555 is reported even if the database has just been restarted
    
    * The error is always reproducible when running the select statement
    
    * The "analyze validate structure cascade" command returns the same error:
    
    SQL> analyze table MIPI_OWNER.MIPI_PUBL_OBJ_DATA validate structure cascade;
    analyze table MIPI_OWNER.MIPI_PUBL_OBJ_DATA validate structure cascade
    *
    ERROR at line 1:
    ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12$" too small
    
    * Without cascade option the analyze command doesn't report the error:
    SQL> analyze table MIPI_OWNER.MIPI_PUBL_OBJ_DATA validate structure;
    Table analyzed
    
    * The "analyze validate structure" command executed for the PK index generates the same ORA-01555
    
    * The trace file generated as a result of ORA-01555 with the following events set: 
    SQL>alter system set events '10442 trace name context forever, level 1';
    SQL>alter system set events '1555 trace name errorstack level 10';
    
    contains the following information:
    
    *** SERVICE NAME:(SYS$USERS) 2009-12-04 04:21:45.777
    *** SESSION ID:(537.68) 2009-12-04 04:21:45.776
    ORA-01555 occurred - Dump of diagnostics information
    --- This does not indicate any errors
    SSOLD: SQL ID: at4t8hum4xctr, Statement:
    SELECT * FROM MIPI_PUBL_OBJ_DATA WHERE PUBLICATION_OBJECT_ID 
    IN ('PUBOB19','PUBOB20') AND APPLICABLE_FOR=TRUNC(SYSDATE) ORDER BY 1 DESC
    SSOLD: Query Length = 0 (sttm=1259900505, fchtm=1259900505, curtime=1259900505) 
    SSOLD: Undo Retention = 10854
    SSOLD: Scan Time Dump
    env: (scn: 0x0219.2e46e8a8 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 
    statement num=0 parent xid: xid: 0x0000.000.00000000 
    scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
    ................
    *** 2009-12-04 04:21:51.140
    ksedmp: internal or fatal error
    Current SQL statement for this session:
    SELECT * FROM MIPI_PUBL_OBJ_DATA WHERE PUBLICATION_OBJECT_ID IN
     ('PUBOB19','PUBOB20') AND APPLICABLE_FOR=TRUNC(SYSDATE) ORDER BY 1 DESC
    ----- Call Stack Trace -----
    calling call entry argument values in hex 
    location type point (? means dubious value) 
    -------------------- -------- -------------------- ----------------------------
    ksedmp ktussto kturbk ktrgcm ktrget kdifxs1 qerixtFetch qertbFetchByRowID qerilFetch 
    opifch2 kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv sou2o opimai_real main start
    
    Cause
    
    The Primary Key Index is corrupted and ORA-01555 in this case is a false error.
    Solution
    
    Disable and enable the PK constraint in order to recreate the PK index associated with 
    the primary key constraint and to solve the primary key index corruption.
    Example :
    SQL> alter table MIPI_OWNER.MIPI_PUBL_OBJ_DATA disable primary key;
    SQL> alter table MIPI_OWNER.MIPI_PUBL_OBJ_DATA enable primary key;
    
  2. 惜 分飞 说:
    Applies to:
    Oracle Server - Enterprise Edition - Version: 9.2.0.8
    This problem can occur on any platform.
    
    Symptoms
    ORA-1555 error reported intermittently with query duration as 0 seconds.
    
    Changes
    This may be impacted by bug:5475085 currently being worked by development which identified that not all undo statistics are being updated when they should be, such as V$UNDOSTAT.EXPBLKREUCNT.
    Cause
    This is currently being investigated by development in bug:6799685.
    
    Solution
    Increase the size of the UNDO tablespace and increase the UNDO_RETENTION parameter value to try to prevent required undo expiring too quickly until a solution to bug:6799685 can be identified.
    
    References
    BUG:6799685 - ORA-1555 ERROR WITH QUERY DURATION=0 AND UNDO_RETENTION=1800
    BUG:5475085 - V$UNDOSTAT.EXPBLKREUCNT IS NEVER INCREMENTED