Wednesday, May 26, 2021

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

When we come across “snapshot too old” error we need to look into all possibilities

1. Determine if UNDO_MANAGEMENT is MANUAL or AUTO – Make sure you are using Auto, this will take care of auto management and will help to tune

2. In the ora-01555, if you see segment number with name that means it is caused by UNDO segment, if not LOG segment due to read consistency

3. Find out which client sessions or programs causing the issue and QUERY DURATION.

The possible solution will be set UNDO_MANAGEMENT to AUTO then make sure we have set correct value for UNDO_RETENTION.

Run below SQL query to identify weather undo tablespace was too small to maintain UNDO_RETENTION

select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,
UNXPSTEALCNT "# Unexpired|Stolen", EXPSTEALCNT "# Expired|Reused",
SSOLDERRCNT "ORA-1555|Error", NOSPACEERRCNT "Out-Of-space|Error",
MAXQUERYLEN "Max Query|Length" from gv$undostat
where begin_time between
to_date(‘Start time of the query','MM/DD/YYYY HH24:MI:SS')
and
to_date('End time of the query','MM/DD/YYYY HH24:MI:SS')
order by inst_id, begin_time;


Find out the current retention period by querying the tuned_undoretention column of v$undostat. The database tunes the undo retention period to be longer than the long running query. The v$undostat view contains one row for each 10-minute stats collection interval over the last 4 days. The Data beyond 4 days can query the dba_hist_undostat view.

The below query will display the tuned_undoretention value in seconds:

select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
to_char(end_time, 'DD-MON-RR HH24:MI') end_time,
tuned_undoretention from v$undostat
order by end_time;


Refer Oracle notes
Note 563470.1 Lob retention not changing when undo_retention is changed
Note 800386.1 ORA-1555 - UNDO_RETENTION is silently ignored if the LOB
Note 422826.1 How To Identify LOB Segment Use PCTVERSION Or RETENTION
Bug:3200789 Abstract: VISIBILITY OF LOB SEGMENT USAGE FOR UNDO


Thanks & Regards,
http://oracleracexpert.com, Oracle ACE

8 comments:

  1. Packers and Movers Chennai Give Safe and Reliable ***Household Shifting Services in Chennai with Reasonable ###Packers and Movers Price Quotation. We Provide Household Shifting, Office Relocation, ✔✔✔ Local and Domestic Transportation Services, Affordable and Reliable Shifting Service Charges @ Packers And Movers Chennai

    ReplyDelete
  2. The secret law of attraction dates back 5,000 years ago and it a natural law. It is not magic, but like magic and prays it is placing an intention to God or Universe to let them know what you want or need. It is a very powerful tool to change your life. You can bring yourself into perfect alignment with what you want or need. With the power of affirmations, visualization, and prays you to improve your life. Law of Attraction

    ReplyDelete
  3. Packers and Movers Hyderabad Give Certified and Verified Service Providers, Cheap and Best ###Office Relocation Charges, ***Home Shifting, ✔✔✔Goods Insurance worth Rs. 10,000, Assurance for Local and Domestic House Shifting. Safe and Reliable Household Shifting Services in Hyderabad with Reasonable Packers and Movers Price Quotation @ Packers And Movers Hyderabad

    ReplyDelete
  4. I Would like to thank you for this article. From this article I got more and more useful information. This is so helpful to me. Keep updating more articles.
    Ubs accounting
    Myob Singapore
    Best Accounting software Singapore

    `

    ReplyDelete
  5. https://www.omgab.com 우리카지노사이트
    https://www.omgab.com/theking 더킹카지노
    https://www.omgab.com/sands 샌즈카지노
    https://www.omgab.com/first 퍼스트카지노
    https://www.omgab.com/yes 예스카지노
    https://www.omgab.com/super 슈퍼카지노
    https://www.omgab.com/gatsby 개츠비카지노
    https://www.omgab.com/33 33카지노
    https://www.omgab.com/world 월드카지노
    https://www.omgab.com/merit 메리트카지노

    ReplyDelete
  6. https://www.omgka.com 우리카지노사이트
    https://www.omgka.com/theking 더킹카지노
    https://www.omgka.com/sands 샌즈카지노
    https://www.omgka.com/first 퍼스트카지노
    https://www.omgka.com/yes 예스카지노
    https://www.omgka.com/super 슈퍼카지노
    https://www.omgka.com/gatsby 개츠비카지노
    https://www.omgka.com/33 33카지노
    https://www.omgka.com/world 월드카지노
    https://www.omgka.com/merit 메리트카지노

    ReplyDelete
  7. https://www.oobbg.com 우리카지노사이트
    https://www.oobbg.com/theking 더킹카지노
    https://www.oobbg.com/sands 샌즈카지노
    https://www.oobbg.com/first 퍼스트카지노
    https://www.oobbg.com/yes 예스카지노
    https://www.oobbg.com/coin 코인카지노
    https://www.oobbg.com/33 33카지노
    https://www.oobbg.com/world 월드카지노
    https://www.oobbg.com/merit 메리트카지노
    https://www.oobbg.com/gatsby 개츠비카지노

    ReplyDelete
  8. https://www.bbdd66.com 우리카지노사이트
    https://www.bbdd66.com/theking 더킹카지노
    https://www.bbdd66.com/sands 샌즈카지노
    https://www.bbdd66.com/first 퍼스트카지노
    https://www.bbdd66.com/yes 예스카지노
    https://www.bbdd66.com/super 슈퍼카지노
    https://www.bbdd66.com/gatsby 개츠비카지노
    https://www.bbdd66.com/33 33카지노
    https://www.bbdd66.com/world 월드카지노
    https://www.bbdd66.com/merit 메리트카지노

    ReplyDelete