Troubleshooting&nbsp;ORA-1555&amp;nb…

wsql

浏览: 11777770 次
性别:
来自: 深圳

最近访客更多访客>>

GDGZWQZ

IT_way

qq_33632159

mhx1535

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (14573)

社区版块

存档分类

2013-03 ( 19)
2013-02 ( 15)
2013-01 ( 268)
更多存档...

Troubleshooting ORA-1555&nb…

Troubleshooting ORA-1555 (Doc ID 1307334.1) By khrobins on <a href="https://blogs.oracle.com/db/entry/troubleshooting_ora_1555"> May 16, 2011</a> This article is intended to assist in finding tips and techniques to assist with finding solutions to ORA-1555 errors. The document will cover the following topics: Concepts/Definitions Diagnosing Common Causes/Solutions 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 <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=40689.1" title="ORA-1555 "> Note 40689.1</a> - 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 <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=10579.1" title="How many Rollback Segments to Have"> Note 10579.1</a> - How many Rollback Segments to Have <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=107085.1" title="Tuning Rollback Segments">Note 107085.1</a> - Tuning Rollback Segments <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=69464.1" title="Rollback Segment Configuration & Tips">Note 69464.1</a> - 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. <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=461480.1" title="FAQ Automatic Undo Management (AUM) / System Managed Undo (SMU)">Note 461480.1</a> - FAQ Automatic Undo Management (AUM) / System Managed Undo (SMU) <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=135053.1" title="How to Create a Database with Automatic Undo Management">Note 135053.1</a> -How to Create a Database with Automatic Undo Management <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=268870.1" title="How to Shrink the datafile of Undo Tablespace">Note 268870.1</a> - How to Shrink the datafile of Undo Tablespace <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=40689.1" title="How to switch a Database from AUM back to using Rollback Segments">Note 231776.1</a> - How to switch a Database from Automatic Undo Management (AUM) back to using Rollback Segments <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=296863.1" title="How to Keep all UNDO Segments from Being Offlined in Oracle 10g - Fast Ramp-Up">Note 296863.1</a> - 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. <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=162345.1" title="LOBS - Storage, Read Consistency and Rollback">Note 162345.1</a> - LOBS - Storage, Read-consistency and Rollback <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=386341.1" title="How to determine the actual size of the LOB segments and free unused space">Note 386341.1</a> - How to determine the actual size of the LOB segments and how to free the deleted/unused space above/below the HWM <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=563470.1" title="Lob retention not changing when undo_retention is changed">Note 563470.1</a> – Lob retention not changing when undo_retention is changed <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=422826.1" title="How to identify LOB Segment Use PCTVERSION or RETENTION">Note 422826.1</a> – 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: <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=846079.1" title="LOBs and ORA-1555">Note 846079.1</a> – LOBs and ORA-1555 troubleshooting <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=253131.1" title="Concurrent Writes May Corrupt LOB Segment">Note 253131.1</a> –Concurrent Writes May Corrupt LOB Segment When Using Auto Segment Space Management <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=467872.1" title="TROUBLESHOOTING GUIDE (TSG) - ORA-1555">Note 467872.1</a> – 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 <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=262066.1" title="How to Size UNDO Tablespace for AUM">Note 262066.1</a> – How To Size UNDO Tablespace For Automatic Undo Management <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1112363.1" title="When Does UNDO Used Space Become Available?">Note 1112363.1</a> – When Does Undo Used Space Become Available? <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=240746.1" title="10g NEW FEATURE on AUTOMATIC UNDO RETENTION">Note 240746.1</a> – 10g NEW FEATURE on AUTOMATIC UNDO RETENTION Diagnostics Scripts Refer to <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=746173.1" title="Common Diagnostics Scripts"> Note 746173.1</a> : Common Diagnostic Scripts for AUM problems and <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=877613.1" title="AUM Common Analysis/Diagnostic Scripts"> Note 877613.1</a> : AUM Common Analysis/Diagnostic Scripts Common Causes/Solutions 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: <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=69464.1" title="Rollback Segment Configuration & Tips"> Note 69464.1</a> – Rollback Segment Configuration & Tips <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=40689.1" title="ORA-1555: ">Note 10630.1</a> – ORA-1555: “Snapshot too old” – Overview <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=862469.1" title="ORA-604 & ORA-1555 Rollback Segment 0 with Name ">Note 862469.1</a> – 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. <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=750195.1" title="ORA-1555 Shows Unrealistic Query Duration">Note 750195.1</a> – 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. <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=761128.1" title="ORA-1555 Error when Query Duration as 0 Seconds">Note 761128.1</a> – 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. <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1112431.1" title="Undo Remains Unexpired">Note 1112431.1</a> – Undo Remains Unexpired When Using Non-autoextensible Datafiles for Undo Tablespace. Additional Reference: <a href="https://communities.oracle.com/portal/server.pt/community/database_administration/227" title="Database Administration Community"> Database Administration Community</a>

分享到：