Troubleshooting ORA-01555 - Snapshot Too Old: Rollback Segment Number "String" With Name "String" Too Small (Doc ID 1580790.1)

https://support.oracle.com/epmos/faces/DocContentDisplay?id=1580790.1 

In this Document

Purpose

Troubleshooting Steps

1) Check Error Messages:

a) Identify segment name:

1. LOB segment corruption:

2. If no LOB corruption found, so Issues with Retention/Pctversion values :

b) Identify Query Duration:

2) Check Undo Datafiles :

3) TUNED_UNDORETENTION :

4) High utilization of ACTIVE/UNEXPIRED extents :

5) UNDO_RETENTION :

6) 1555 error during export :

7) How to find the complete SQL statement caused ORA-1555 :

---

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

PURPOSE

This document provides troubleshooting steps to resolve ORA-01555 / ORA-22924. It also discusses known issue and bugs related ORA-01555 / ORA-22924.

TROUBLESHOOTING STEPS

1) Check Error Messages:

Check alert log (or the log where the error is reported) to determine 1555 error message as we have some different types of 1555 error messages:

a) Identify segment name:

ORA-01555: snapshot too old: rollback segment number  with name "" too small

--> notice that segment name is null "" or/and
ORA-22924: snapshot too old

In this case 1555 error is reported while accessing UNDO data stored inside LOB segment. ORA-01555 for LOB segment is caused by one of the following reasons :

1. LOB segment corruption:

To check the LOB table corruption, review the following documents :

Export Receives The Errors ORA-1555 ORA-22924 ORA-1578 ORA-22922 (Doc ID 787004.1)
Export Fails With ORA-2354 ORA-1555 ORA-22924 and How To Confirm LOB Segment Corruption Using Export Utility? (Doc ID 833635.1)
ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption. (Doc ID 452341.1)

2. If no LOB corruption found, so Issues with Retention/Pctversion values :

You may need to increase Retention/Pctversion. Check following document carefully :

LOBs and ORA-01555 troubleshooting (Doc ID 846079.1)

 - ORA-01555: snapshot too old: rollback segment number 107 with name "_SYSSMU107_1253191395$" too small

notice that segment name is existing "_SYSSMU107_1253191395$" which mean undo data inside UNDO tablespace.

In this case, 1555 error is reported while accessing UNDO data at UNDO tablespace which will discuss how to troubleshoot in this document.

b) Identify Query Duration:

Failed query duration is logged in some 1555 error messages which showed at alert log file or any application log.

ORA-01555 caused by SQL statement below (Query Duration=1974 sec, SCN: 0x0002.bc30bcf7):

- If query duration = 0 or few seconds, check the following document :

ORA-01555 When Max Query Length Is Less Than Undo Retention, small or 0 Seconds (Doc ID 1131474.1)

- If query duration equal to a value closer or larger than undo_retention value proceed for following analysis.

2) Check Undo Datafiles :

select tablespace_name, file_id, sum(bytes)/1024/1024 a,
sum(maxbytes)/1024/1024 b,
autoextensible
from dba_data_files
where tablespace_name in (select tablespace_name from dba_tablespaces
where retention like '%GUARANTEE' )
group by file_id, tablespace_name, autoextensible
order by tablespace_name;

If you are using non-autoextensible UNDO data files, this can lead to high calculation of tuned_undoretntion and hence high allocation of undo space especially with large UNDO data files.

To avoid this make sure that the UNDO data files are autoextensible even you have enough free space .

NOTE: It is strongly not recommended to have both autoextensible and non-autoextensible undo data files in the undo tablespace as will be lead to TUNED_UNDORETENTION mis-calculation.

  

3) TUNED_UNDORETENTION :

SQL> select max(maxquerylen),max(tuned_undoretention) from v$undostat;

SQL> select max(maxquerylen),max(tuned_undoretention) from DBA_HIST_UNDOSTAT;

In case you have large value for TUNED_UNDORETENTION, this can be caused by two reasons :

1. As described in step 2), Using non-autoextensible UNDO data files which can be resolved by one of the following :

- Set _smu_debug_mode=33554432 (recommended)

With this setting, TUNED_UNDORETENTION is not calculated based on a percentage of the fixed size undo tablespace.

Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.

- set autoextensible= YES for all UNDO data files

- set "_undo_autotune" = false and undo_retention=max(maxquerylen)

- set SET "_highthreshold_undoretention"=max(maxquerylen)+1

2.  Long running queries which can raise tuned_undoretention to very high values.

You need to tune these queries to avoid retain UNDO data for more time inside UNDO tablespace.

To identify these long queries, run the following :

select MAXQUERYSQLID, MAXQUERYLEN from DBA_HIST_UNDOSTAT order by MAXQUERYLEN desc;

select MAXQUERYID, MAXQUERYLEN from v$UNDOSTAT order by MAXQUERYLEN desc;

4) High utilization of ACTIVE/UNEXPIRED extents :

SELECT DISTINCT STATUS,TABLESPACE_NAME, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS, TABLESPACE_NAME;

Excessive allocation of ACTIVE/UNEXPIRED extents can be caused by one of following reasons :

- large value of undo_retention or TUNED_UNDORETENTION as described above.

- large UNDO data generation at certain point of time which can be identified by run following query :

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select BEGIN_TIME , UNDOBLKS from DBA_HIST_UNDOSTAT order by UNDOBLKS desc ;

select BEGIN_TIME , UNDOBLKS from v$UNDOSTAT order by UNDOBLKS desc;

- large dead transaction(s) rollback 

- Using flashback data archive

For more details:

How To Check the Usage of Active Undo Segments in AUM (Doc ID 1337335.1)

5) UNDO_RETENTION :

We recommend to set undo_retention to at least the average of maximum queries length as follow and increase it if 1555 error still reported :

select avg(maxquerylen) from DBA_HIST_UNDOSTAT;

select avg(maxquerylen) from v$UNDOSTAT;

6) 1555 error during export :

To diagnose resolve 1555 error during export check following document :

Troubleshooting ORA-01555/ORA-01628/ORA-30036 during export and import (Doc ID 1579437.1)

7) How to find the complete SQL statement caused ORA-1555 :

If the Database was not restarted after the error ORA-1555 , so the Statement can be obtained from :

select SQL_TEXT from V$SQL where SQL_ID='<sql id from the error message>';

If the Database was restarted after the error ORA-1555 and an AWR snapshot was gathered before the restart , so the Statement can be obtained from :

select SQL_TEXT from DBA_HIST_SQLTEXT where SQL_ID='<sql id from the error message>';