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及其以上版本)
