联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
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及其以上版本)
加我QQ
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;