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

59 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. Thank you much for this tutorial; this is an informative and valuable blog. Visit for
    SEO Service in Delhi
    Web Development Company

    ReplyDelete
  3. 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
  4. 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
  5. 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
  6. 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
  7. Nice post! thanks a lot for this informative article.

    hotmail.com sign in

    ReplyDelete
  8. شركة تنظيف منازل بجدة
    شركة تنظيف منازل بمكة
    افضل شركة تنظيف بجدة
    تتميز شركتنا شركة تنظيف خزانات بجدة
    بعدة عوامل هامة تجعلنا أفضل شركة تنظيف خزانات بجدة فتقوم بجميع خدمات التنظيف ومنها تنظيف
    والشوائب الخزانات بجدة وايضا شركة تنظيف خزانات بالمدينة المنورة متخصصة في مجال تنظيف الخزانات وكذلك شركة غسيل خزانات بالمدينة سواء كانت العلوية أو السفلية وتعقيمها تعقيم تام بأفضل أنواع المطهرات
    أيضا لدينا في شركتنا من أهم الخدمات الخاصة بتنظيف خزانات المياه اطلب من شركة تنظيف خزانات بجدة خدمات متنوعة فنحن تقوم بعمل كشف على خزانك ولو وجد خلل بالخزان تقوم بتصليح خزانك ونقوم بعمل اللازم وعمل عزل كامل للخزان من الداخل لمنع تسربات المياه من الخزانات شركة صيانة خزانات بجدة نضمن لك عزيزي العميل بان تكون عملية الصيانة والتنظيف تتم على أكمل وجه فلدينا فريق محترف خاص بعملية عزل الخزانات بجدة واخلاءها من الأتربة والترسبات الموجودة بقاع الخزان وترك الخزان نظيف تماما من جميع الأتربة الموجودة بالقاع .

    ReplyDelete
  9. من افضل الشركات التي توجد في منطقة مكة المكرمة والتي تعمل في مجال نقل العفش مع الفك والتركيب تلك الشركة الجيدة التي تقدم خدمات جيدة وتسمى افضل شركة نقل عفش بجدة ايضا وهي التي تختص بنقل العفش من بيت الى بيت آخر في مدينة جدة وما جاورها من مناطق تابعة لها
    وايضا من افضل شركات نقل العفش مع الفك والتركيب تلك التي توجد في العاصمة المقدسة مكة المكرمة وقد نالت شركة نقل عفش بمكة شهرة واسعة لأنها تعتمد على اساليب حديثة في اعمال نقل العفش مع الفك والتركيب
    وفي مدينة الطائف تطورت خدماتنا واصبحنا نقدم خدمات جيدة في اعمال نقل العفش مع الفك والتركيب ولهذا السبب اصبحت شركتنا
    شركه نقل عفش بالطائف من افضل واحسن واقوى شركات النقل في مدينة الطائف فلو تريد نقل عفش بيتك وانت في الطائف اتصل بنا

    ReplyDelete
  10. Such great information for blogger iam a professional blogger thanks…

    Get SAP S4 HANA Training in Bangalore from Real Time Industry Experts with 100% Placement Assistance in MNC Companies. Book your Free Demo with Softgen Infotech.

    ReplyDelete
  11. I haven’t any word to appreciate this post.....Really i am impressed from this post....the person who create this post it was a great human..thanks for shared this with us. Old World

    ReplyDelete
  12. when we are talking about data, how can we miss the opportunity to show some data related to the profession of a Data Scientist? According to a report published by an online education portal, there has been a dramatic rise noticed in the listing and application for jobs related to Data Science. data science course syllabus

    ReplyDelete
  13. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Data Science Course In Hyderabad

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete
  15. Thanks for sharing this valuable content. In my view, if all webmasters and bloggers made good content as you did, the web will be a lot more useful than ever before. Expert secrets

    ReplyDelete
  16. Very excellent post!!! Thank you so much for your great content. Keep posting.....

    Data Science Training In Bangalore

    ReplyDelete
  17. This is most informative and also this post most user friendly and super navigation to all posts.
    3D Laser Scanning Targets
    Dimensional Control

    ReplyDelete
  18. I absolutely love your blog and find the majority of your post’s to be precisely what I’m looking for. on most of the subjects you write related to Cash For Car Sydney. Again, awesome weblog!

    ReplyDelete
  19. I absolutely love your blog and find the majority of your post’s to be precisely what I’m looking for. on most of the subjects you write related Car Removal Sydney. Again, awesome weblog!

    ReplyDelete
  20. This comment has been removed by the author.

    ReplyDelete
  21. Onroadz, a provider of Self Drive Car Rental Services in Chennai, offers you a wide selection of small to big cars that could be hired per hour, per day, per week or even per month based on your requirements. Ask for your brand new car and you will definitely get it with them. What makes them even more exciting is the fact that their schemes have fixed rates, guaranteed services with unlimited kilometers. Liability is also limited. Moreover, they deliver you the best choice of self driving rental cars at your doorstep and when you need it they make your lives easier and more convenient.

    ReplyDelete
  22. Five Fingers Exports are the leading industrial machinery manufacturing company in offering the high quality PP woven sack making machine , single color, two color, three color and four color offset printing machine manufacturers in India at an affordable cost. You can get high-quality offset printing machine which has been specifically made for quick printing to meet the requirements.
    Five Fingers Exports are leading manufacturer of high quality non woven bag, supreme face mask , paper straw, surgical head cap and pp woven sack making machines which are fast and cost-effective. These machineries also come with a lower total cost of ownership.

    ReplyDelete
  23. I would like to thank you for the efforts you've put in writing this website. I am hoping to see the same high-grade blog posts from you in the future as well. In fact, your creative writing abilities have motivated me to get my own blog now ;)
    Framed glass balustrade



    ReplyDelete
  24. Thank You for the valuable info. Are you looking for cooling tower for your industry? get a high quality cooling tower at an affordable price. We are a ISO 9001:2015 certified company in cooling tower manufacturing company in coimbatore.

    ReplyDelete
  25. OfficeStac is a professional and experienced office interior and office space renovation team in Singapore. We offer complete commercial office interior and renovation services at the most competitive price. A proper renovation plan will help you to bring a total style change or a better space utilization of your office space.

    ReplyDelete
  26. Nice Post. Very informative Message and found a great post. Thank you...

    AWS Training in Hyderabad

    ReplyDelete
  27. Kamsys Techsolutions is a leading Amazon SEO company, offering result-oriented Amazon SEO services that include the industry's most advanced software and innovative product listing optimization strategies to increase your visibility, sales, and revenue.

    We are the #1 Amazon SEO consultant company that offers the best Amazon SEO listing optimization, Amazon PPC Sponsored marketing services & FBA management. Hire Amazon SEO Company in USA, UK, Canada, Australia, India. Generating High ROI by our effective and advanced amazon marketing services.

    ReplyDelete
  28. Thanks for sharing this information with us. It's helpful for me.
    AI Training in Hyderabad

    ReplyDelete
  29. You’re so interesting! I don’t believe I’ve truly read something like this before. So great to find someone with genuine thoughts on this issue. Really.. many thanks for starting this up. This website is something that’s needed on the internet, someone with some originality!

    CBSE Schools In Kangra
    CBSE Schools In Kinnaur
    CBSE Schools In Kullu
    CBSE Schools In Lahaul Spiti
    CBSE Schools In Mandi
    CBSE Schools In Shimla
    CBSE Schools In Sirmaur
    CBSE Schools In Solan
    CBSE Schools In Una
    CBSE Schools In Dharamshala

    ReplyDelete
  30. Excellent post! Are you looking for the best deals for your old or unwanted cars? Let's come - Cash For Cars Sydney. We also provide free scrap car removals service.

    ReplyDelete
  31. Qld Car Removals Brisbane buy all makes & models of cars. We are the ultimate destination to sell your used scrap cars for cash anywhere in Qld. Car Removals Brisbane gives you the best deals on your old cars offering cash for cars in Brisbane for unwanted cars, vans, utes, 4WD, trucks and all type of commercial vehicles.

    ReplyDelete
  32. I read this article. I think You put a great deal of exertion to make this article. I like your work. check this site

    ReplyDelete
  33. Get free scrap car collection in Sydney or around areas. They are good choice for city of Sydney and Penrith New South Wales.

    ReplyDelete
  34. Thanks for your blog post, I am a regular reader of your blog and subscribed your posts. I am also a blogger and social worker so sometimes I help customers about the automotive industry where people can ask to us about old car suggestions. For car disposal services in NSW the one I have found on internet that people can go with. Thanks

    ReplyDelete
  35. I have a similar interest this is my page read everything carefully and let me know what you think. custom back patches

    ReplyDelete
  36. Gives you the best website address I know there alone you'll find how easy it is. check this site

    ReplyDelete
  37. Hmm… I interpret blogs on a analogous issue, however i never visited your blog. I added it to populars also i’ll be your faithful primer. visit this site

    ReplyDelete
  38. Best AWS Training provided by Vepsun in Bangalore for the last 12 years. Our Trainer has more than 20+ Years
    of IT Experience in teaching Virtualization and Cloud topics.. we are very delighted to say that Vepsun is
    the Top AWS cloud training Provider in Bangalore. We provide the best atmosphere for our students to learn.
    Our Trainers have great experience and are highly skilled in IT Professionals. AWS is an evolving cloud
    computing platform provided by Amazon with a combination of IT services. It includes a mixture of
    infrastructure as service and packaged software as service offerings and also automation. We have trained
    more than 10000 students in AWS cloud and our trainer Sameer has been awarded as the best Citrix and Cloud
    trainer in india.

    ReplyDelete
  39. Nagaland Board 10th Model Question Paper 2023 Blueprint 2023 Available Students you can Download in Pdf format form this web page below given links. HSLC Regular and Private Students Prepare their Exam from these Sample Paper 2023, Students have to Register for Appearing in Public Exam 2023. NBSE 10th Class Question Paper 2023 Provide SSLC Latest and Last Year Exam Study Material for Syllabus 2023, Question Paper 2023 etc, Hindi, English Medium Pdf Format. so if you are also among those students who have Registered them self as a Regular or Private Student are Suggested to Download these Nagaland Board HSLC Sample Paper 2023.

    ReplyDelete
  40. The superbly written article, if only all bloggers offered the same content as you, the internet would be a far better place. Thank you for sharing the amazing article.
    Cash for car sunshine coast
    Car Removals Brisbane Northside
    Cash for scrap cars Morayfield
    Cars for cash Toowoomba

    ReplyDelete
  41. PACKERS AND MOVERS

    packers and movers bangalore near me specialize in packing and transporting household and commercial goods from one location to another. They provide a range of services including packing, loading, unloading, and unpacking of goods. The goal of these companies is to make the process of moving as stress-free and efficient as possible for their clients.


    Smooth and Efficient Moving Services in Bangalore

    Professional and experienced movers: The moving company should have a team of experienced and professional movers who are trained to handle the moving process efficiently.

    Comprehensive services: The best packers and movers in bangalore company should offer a range of services such as packing, loading, transportation, and unpacking to ensure a smooth and efficient moving process.

    Reliable and timely delivery: The moving company should ensure that the goods are delivered to the new location on time and in good condition.

    Adequate protection of goods: The moving company should take adequate measures to protect the goods during the moving process, such as using high-quality packing materials and loading the goods carefully into the trucks.

    Affordable pricing: The moving company should offer competitive pricing that is affordable and fits within the budget of the customers.




    Find the Best Local Packers and Movers in Bangalore

    Get the best local best packers and movers in Bangalore for your move. Enjoy reliable and affordable services for a stress-free experience.
    Ask for referrals: If you know someone who has recently moved in Bangalore, ask for recommendations and referrals. Word of mouth is one of the best ways to find reliable movers.

    Check credentials: Make sure that the packers and movers bangalore cost you choose are licensed and insured. Ask for their licenses, certificates, and insurance policies, and verify their authenticity.

    Get quotes: Contact several movers and ask for detailed quotes. Compare the quotes and services offered by different companies to find the best deal.

    Evaluate experience and expertise: Look for top 10 packers and movers in Bangalore who have been in business for several years and have a track record of delivering high-quality services. Check if they have the necessary equipment and expertise to handle your belongings safely.



    Affordable and Reliable Packers and Movers in Bangalore

    Find affordable and reliable packers and movers in Bangalore for your move. Get the best value for your money with high-quality services.


    Bangalore is home to many affordable and reliable packers and movers. These companies offer comprehensive moving services at competitive prices, making it easier for residents to move to their new home or office. With years of experience and expertise, these top 5 packers and movers in Bangalore ensure that your belongings are safely packed and transported to your new location. Whether you're moving locally or long-distance, you can trust these movers to handle your move with care and efficiency. From packing to loading, transportation to unpacking, they offer a range of services to make your move stress-free and seamless.



    Find Trusted and Reliable Packers and Movers in Bangalore



    Get the most trusted and reliable best packers and movers in bangalore for your move. Enjoy peace of mind with high-quality services.


    Finding trusted and reliable best packers and movers in bangalore can be a challenging task. To ensure a smooth and hassle-free moving experience, it is important to find a company that has a good reputation and offers quality services. You can start by researching online, asking for referrals, and checking the credentials of the movers. Get detailed quotes from several companies and compare their services and pricing

    ReplyDelete
  42. Awesome blog! Thank you for sharing this information with us. Packer and movers in Mumbai

    ReplyDelete
  43. Your writing is interesting and educational. Continue your wonderful work. erp customization

    ReplyDelete
  44. Very informative blog! I am glad that I came across your article. Discover the ultimate learning experience with Ziyyara Edutech, the world's largest online education platform offering comprehensive and personalized home tuition classes for Class 12 students.
    Book A Free Demo Today visit Private tuition classes for class 12

    ReplyDelete
  45. Thanks for adding it to my knowledge. You are wonderful writer. Your articles are very helpful and easy to understand. Spoken English Classes in Saudi Arabia: We focus on practical communication skills, enabling you to converse fluently in English.
    For Book a demo now Best english speaking course in Riyadh

    ReplyDelete