Friday, May 25, 2018

ORA-01555: snapshot too old: rollback segment number

There are many reasons for this error. In this post I am providing all the possible scenarios and related Oracle Notes/Links.

Concepts/Definitions

The ORA-1555 errors can happen when a query is unable to access enough undo to build
a copy of the data at the time the query started. Committed "versions" of blocks are
maintained along with newer uncommitted "versions" of those blocks so that queries can
access data as it existed in the database at the time of the query. These are referred to as
"consistent read" blocks and are maintained using Oracle undo management.

See Document 40689.1 - ORA-1555 "Snapshot too old" - Detailed Explanation for more about
these errors.


Diagnosing
Due to space limitations, it is not always feasible to keep undo blocks on hand for the life of the instance. Oracle Automatic Undo Management (AUM) helps to manage the time frame that undo blocks are stored. The time frame is the "retention" time for those blocks.

There are several ways to investigate the ORA-1555 error. In most cases, the error is a legitimate problem with getting to an undo block that has been overwritten due to the undo "retention" period having passed.

AUM will automatically tune up and down the "retention period, but often space limitations or configuration of the undo tablespace will throttle back continuous increases to the "retention" period.
The error message is reported in the user session and often is not captured in the alert log. The user could see a message like

Using rollback segment functionality:

ORA-1555: snapshot too old (rollback segment too small)

or

Using AUM:

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

If the error is captured in the alert.log, you would see something like

Tue May 26 16:16:57 2009

ORA-01555 caused by SQL statement below (SQL ID: 54yn3n36w24ft, Query Duration=922 sec, SCN: 0x0007.8a55f4e3)

Initial Investigation

Rollback Segments:

With Oracle 10g and later versions of Oracle, you can still use a Rollback Segments configuration. ORA-1555 errors in that environment still follow older guidelines as described in


Document 10579.1 - How many Rollback Segments to Have
Document 107085.1 - Tuning Rollback Segments
Document 69464.1 - Rollback Segment Configuration & Tips
Automatic Undo Management:

The database will be self tuning for undo when using Automatic Undo Management. This does not eliminate ORA-1555 completely, but does minimize ORA-1555 as long as there is adequate space in the undo tablespace and workloads tend to follow repeatable patterns. In some cases with periodic changes to workload (large data updates particularly with LOB data) the self tuning of undo can become aggressive and lead to undo issues.

Document 461480.1 - FAQ Automatic Undo Management (AUM) / System Managed Undo (SMU)
Document 135053.1 - How to Create a Database with Automatic Undo Management
Document 268870.1 - How to Shrink the datafile of Undo Tablespace
Document 231776.1 - How to switch a Database from Automatic Undo Management (AUM) back to using Rollback Segments
Document 396863.1 - How to Keep All UNDO Segments from Being Offlined in Oracle 10g - Fast Ramp-Up

LOB Issues:

Out-of-row LOB undo is maintained in the LOB segment. So the UNDO tablespace and undo retention is not associated with most LOB ORA-1555 issues. Instead the LOB column is created using either PCT_VERSION or RETENTION to manage how much space within blocks or time transpires before the LOB undo is overwritten. In environments with high updates, deletes on rows including LOBs, the chances of ORA-1555 on LOB undo is very high.

PCT_VERSION and RETENTION are not auto-tuned. To "tune" those configuration settings, you must change the values for PCT_VERSION or RETENTION. Changes to UNDO_RETENTION does not change LOB retention time frames.


Document 162345.1 - LOBS - Storage, Read-consistency and Rollback
Document 386341.1 - How to determine the actual size of the LOB segments and how to free the deleted/unused space above/below the HWM
Document 563470.1 'Lob retention not changing when undo_retention is changed
Document 422826.1 How to identify LOB Segment Use PCTVERSION or RETENTION from Data Dictionary

Error Tracing

Undo error tracing can be done for normal undo operations using the following events:

NOTE: Normal undo operations will be indicated in the error message in that the error message includes a segment name like

'¦. name "_SYSSMU1$" too small

If the error doesn't show a segment name

'¦ name "" too small

the problem is often related to LOB undo

If using pfile:
event="10442 trace name context forever, level 10"

If using spfile:
Alter system set events '10442 trace name context forever, level 10';

Reproduce the ORA-1555 error and upload the trace file to Oracle Support.

LOB undo error tracing is more difficult. Set additional tracing events as follows:

Start Session 1
Alter session set events '10046 trace name context forever, level 12';
Reproduce the error
Exit Session 1

Start Session 2
Alter session set events '10051 trace name context forever, level 1';
Reproduce the error
Exit Session 2

Start Session
Alter session set events '1555 trace name errorstack forever, level 3';
Reproduce the error
Exit Session 3

Additional resources to review:

Document 846079.1 - LOBs and ORA-1555 troubleshooting
Document 253131.1 - Concurrent Writes May Corrupt LOB Segment When Using Auto Segment Space Management
Document 467872.1 - TROUBLESHOOTING GUIDE (TSG) - ORA-1555
V$UNDOSTAT Analysis

The V$UNDOSTAT view holds undo statistics for 10 minute intervals. This view
represents statistics across instances, thus each begin time, end time, and
statistics value will be a unique interval per instance.

This does not track undo related to LOB
Document 262066.1 - How To Size UNDO Tablespace For Automatic Undo Management

Document 1112363.1 - When Does Undo Used Space Become Available?
Document 240746.1 - 10g NEW FEATURE on AUTOMATIC UNDO RETENTION

Diagnostics Scripts
Refer to Document 746173.1 : Common Diagnostic Scripts for AUM problems
and Document 877613.1 : AUM Common Analysis/Diagnostic Scripts
Common Causes/Solutions
Document 1555.1 - Known defects for ora-1555 error
Using Rollback Segments functionality:

* Problem happening on SYSTEM tablespace that still uses old Rollback Segment functionality even when configured for Automatic Undo Management (AUM).

* There are not enough rollback segments to manage the undo needed for long running queries.

* Rollback Segments are too small and undo is overwritten before long running queries complete.
Reference:

Document 69464.1 - Rollback Segment Configuration & Tips
Document 10630.1 - ORA-1555: 'Snapshot too old' - Overview
Document 862469.1 - ORA-604 & ORA-1555 Rollback Segment 0 with Name "System" Too Small

Using Automatic Undo Management (AUM):

* TUNED_UNDORETENTION in V$UNDOSTAT around the time of the error is lower than the QUERY DURATION indicated in the error message. This is a legitimate ORA-1555 and if queries are going to run for very long time frames, UNDO_RETENTION may need to be larger. Auto-tuned retention may not be able to keep up with the undo workload and staying within space limitations on the UNDO tablespace.

* LOB updates and/or deletes are frequent and a higher PCT_VERSION is required to provide enough space in the LOB Segment to accommodate the LOB undo. RETENTION on LOBs that are updated or deleted frequently can run into problems holding UNDO long enough for queries.

* QUERY DURATION shown in the error message is 30+ years and therefore, no amount of undo will satisfy the consistent read blocks.

Document 750195.1 - ORA-1555 Shows Unrealistic Query Duration (billions of seconds)

* QUERY DURATION shown in the error message is 0. NOTE: This has been filed as a bug on many release levels and has been very difficult to narrow down to a specific problem.

Document 761128.1 - ORA-1555 Error when Query Duration as 0 Seconds

* QUERY DURATION is lower than TUNED_UNDRETENTION. Undo header information can sometimes get overwritten or you could be seeing a bug.

* TUNED_UNDORETENTION stays very high and UNDO tablepsace continues to grow continuously or getting space errors.

Document 1112431.1 - Undo Remains Unexpired When Using Non-autoextensible Datafiles for Undo Tablespace.

* 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 SQL_TEXT 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>';

Thanks
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

31 comments:

  1. Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.

    Devops training in chennai"

    Devops training in marathahalli"

    Devops training in rajajinagar"

    Devops training in BTM Layout"

    ReplyDelete
  2. Really I Appreciate The Effort You Made To Share The Knowledge. This Is Really A Great Stuff For Sharing. Keep It Up . Thanks For Sharing.

    Mobile Apps Training
    Mobile Apps Training in Chennai

    ReplyDelete
  3. Hire Manish Packers and Movers Pvt Ltd in India for hassle-free Household Shifting, Office Relocation, Car Transportation, Loading Unloading, packing Unpacking at affordable Price Quotation. Top Rated Safe and Secure Service Providers who can help you with 24x7 and make sure a Untroubled Relocation Services at Cheapest/Lowest Rate. Visit More :-
    Manish Packers and Movers Pvt Ltd
    Packers and Movers Bangalore
    Packers and Movers Gurgaon
    Packers and Movers Indore
    Packers and Movers Kolkata
    Packers and Movers Mumbai
    Packers and Movers Nagpur
    Packers and Movers Ahmedabad
    Manish Packers in Indore
    Manish Packers and Movers Pvt Ltd Sitemap

    ReplyDelete
  4. Manish Packers and Movers Pvt Ltd as a Services providing company can make all the difference to your Home Relocation experience. Indore based Company which offers versatile solutions, Right team that easily reduce the stress associated with a Household Shifting, Vehicle Transportation. we help things run smoothly and reduce breakages and offer you seamless, Affordable, Reliable Shifting Services, Compare Shifting Charges, Visit :
    Manish Packers and Movers Ptv Ltd @ https://www.manishpackersmoversindore.in/
    Packers and Movers Indore @ https://www.manishpackersmoversindore.in/packers-and-movers-indore.html
    Manish Packers Indore @ https://www.manishpackersmoversindore.in/manish-packers-and-movers-indore.html
    Packers and Movers Gurgaon @ https://www.manishpackersmoversindore.in/packers-movers-gurgaon.html
    Packers and Movers Kolkata @ https://www.manishpackersmoversindore.in/packers-movers-kolkata.html
    Packers and Movers Mumbai @ https://www.manishpackersmoversindore.in/packers-movers-mumbai.html
    Packers and Movers Bhopal @ https://www.manishpackersmoversindore.in/packers-movers-bhopal.html
    Packers and Movers Nagpur @ https://www.manishpackersmoversindore.in/packers-movers-nagpur.html
    Packers and Movers Raipur @ https://www.manishpackersmoversindore.in/packers-movers-raipur.html
    Packers and Movers Ahmedabad @ https://www.manishpackersmoversindore.in/packers-movers-ahmedabad.html

    ReplyDelete
  5. Thank you much for this tutorial; this is an informative and valuable blog. Visit for
    SEO Service in Delhi
    Web Development Company

    ReplyDelete
  6. Howdy, would you mind letting me know which web host you’re utilizing? I’ve loaded your blog in 3 completely different web browsers, and I must say this blog loads a lot quicker than most. Can you suggest a good internet hosting provider at a reasonable price?


    Best AWS Training Institute in BTM Layout Bangalore ,AWS Coursesin BTM


    Best AWS Training in Marathahalli | AWS Training in Marathahalli

    Amazon Web Services Training in Jaya Nagar | Best AWS Training in Jaya Nagar

    ReplyDelete
  7. I would really like to read some personal experiences like the way, you've explained through the above article. I'm glad for your achievements and would probably like to see much more in the near future. Thanks for share.

    Java training in Bangalore |Java training in Rajaji nagar

    Java training in Bangalore | Java training in Kalyan nagar

    Java training in Bangalore | Java training in Kalyan nagar

    Java training in Bangalore | Java training in Jaya nagar

    ReplyDelete
  8. Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here.
    python training in chennai
    python course institute in chennai

    ReplyDelete
  9. The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.
    excel advanced excel training in bangalore | Devops Training in Chennai

    ReplyDelete
  10. Great post! I am actually getting ready to across this information, It’s very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.

    angularjs interview questions and answers

    angularjs Training in bangalore

    angularjs Training in bangalore

    angularjs Training in chennai

    automation anywhere online Training

    ReplyDelete
  11. Hmm, it seems like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog. I as well as an aspiring blog writer, but I’m still new to the whole thing. Do you have any recommendations for newbie blog writers? I’d appreciate it.
    Advanced AWS Interview Questions And Answers, Top 250+AWS Interviews Questions and Answers 2018
    Advanced AWS Interview questions and answers | Top 110 AWS Interview Question and Answers
    Advanced AWS Training in Bangalore | Best Amazon Web Services Training in Bangalore
    Advanced AWS Training in Pune | Best Amazon Web Services Training in Pune
    Advanced AWS Online Training | Best Online AWS Certification Course in india

    ReplyDelete
  12. Nice post! thanks a lot for this informative article.

    hotmail.com sign in

    ReplyDelete
  13. Laminated Doors manufacturer in hubli
    Thanks for giving great kind of information. So useful and practical for me. Thanks for your excellent blog, nice work keep it up thanks for sharing the knowledge.

    ReplyDelete
  14. led lawn lights in delhi
    Thanks for giving great kind of information. So useful and practical for me. Thanks for your excellent blog, nice work keep it up thanks for sharing the knowledge.

    ReplyDelete