Troubleshooting ORA-1555&nb…
来源:互联网 发布:算法入门经典 编辑:程序博客网 时间:2024/05/01 05:08
Troubleshooting ORA-1555 (Doc ID1307334.1)
By khrobins on May 16, 2011
This article is intended to assist in finding tips andtechniques to assist with finding solutions to ORA-1555 errors. Thedocument will cover the following topics:
Concepts/Definitions
Diagnosing
Common Causes/Solutions
Concepts/Definitions
The ORA-1555 errors can happen when a query is unableto 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 blocksso 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 undomanagement.
See Note 40689.1 - ORA-1555 "Snapshot too old" - Detailed Explanationfor more about
these errors.
Diagnosing
Due to space limitations, it is not always feasible tokeep undo blocks on hand for the life of the instance. OracleAutomatic Undo Management (AUM) helps to manage the time frame thatundo blocks are stored. The time frame is the “retention” time forthose blocks.
There are several ways to investigate the ORA-1555 error. In mostcases, the error is a legitimate problem with getting to an undoblock that has been overwritten due to the undo “retention” periodhaving passed.
AUM will automatically tune up and down the “retention” period, butoften space limitations or configuration of the undo tablespacewill throttle back continuous increases to the “retention”period.
The error message is reported in the user session and often is notcaptured 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 somethinglike
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 canstill use a Rollback Segments configuration. ORA-1555 errors inthat environment still follow older guidelines as describedin
Note 10579.1 - How many Rollback Segments toHave
Note107085.1 - Tuning Rollback Segments
Note69464.1 - Rollback Segment Configuration & Tips
Automatic Undo Management:
The database will be self tuning for undo when usingAutomatic Undo Management. This does not eliminate ORA-1555completely, but does minimize ORA-1555 as long as there is adequatespace in the undo tablespace and workloads tend to followrepeatable patterns. In some cases with periodic changes toworkload (large data updates particularly with LOB data) the selftuning of undo can become aggressive and lead to undo issues.
Note461480.1 - FAQ Automatic Undo Management (AUM) / System ManagedUndo (SMU)
Note135053.1 -How to Create a Database with Automatic UndoManagement
Note268870.1 - How to Shrink the datafile of UndoTablespace
Note231776.1 - How to switch a Database from Automatic UndoManagement (AUM) back to using RollbackSegments
Note296863.1 - How to Keep All UNDO Segments from Being Offlined inOracle 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 withmost LOB ORA-1555 issues. Instead the LOB column is created usingeither PCT_VERSION or RETENTION to manage how much space withinblocks or time transpires before the LOB undo is overwritten. Inenvironments with high updates, deletes on rows including LOBs, thechances of ORA-1555 on LOB undo is very high.
PCT_VERSION and RETENTION are not auto-tuned. To “tune” thoseconfiguration settings, you must change the values for PCT_VERSIONor RETENTION. Changes to UNDO_RETENTION does not change LOBretention time frames.
Note162345.1 - LOBS - Storage, Read-consistency andRollback
Note386341.1 - How to determine the actual size of the LOB segmentsand how to free the deleted/unused space above/below theHWM
Note563470.1 – Lob retention not changing when undo_retention ischanged
Note422826.1 – How to identify LOB Segment Use PCTVERSION orRETENTION from Data Dictionary
Error Tracing
Undo error tracing can be done for normal undooperations using the following events:
NOTE: Normal undo operations will be indicated in the error messagein 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, level10';
Reproduce the ORA-1555 error and upload the trace file to OracleSupport.
LOB undo error tracing is more difficult. Set additional tracingevents as follows:
Start Session 1
Alter session set events '10046 trace name context forever, level12';
Reproduce the error
Exit Session 1
Start Session 2
Alter session set events '10051 trace name context forever, level1';
Reproduce the error
Exit Session 2
Start Session
Alter session set events '1555 trace name errorstack forever, level3';
Reproduce the error
Exit Session 3
Additional resources to review:
Note846079.1 – LOBs and ORA-1555troubleshooting
Note253131.1 –Concurrent Writes May Corrupt LOB Segment When UsingAuto Segment Space Management
Note467872.1 – TROUBLESHOOTING GUIDE (TSG) – ORA-1555
V$UNDOSTAT Analysis
The V$UNDOSTAT view holds undo statistics for 10minute intervals. This view
represents statistics across instances, thus each begin time, endtime, and
statistics value will be a unique interval per instance.
This does not track undo related to LOB
Note262066.1 – How To Size UNDO Tablespace For Automatic UndoManagement
Note1112363.1 – When Does Undo Used Space BecomeAvailable?
Note240746.1 – 10g NEW FEATURE on AUTOMATIC UNDORETENTION
Diagnostics Scripts
Refer to Note 746173.1 : Common Diagnostic Scripts for AUM problems
and Note 877613.1 : AUM Common Analysis/Diagnostic Scripts
Common Causes/Solutions
Using Rollback Segments functionality:
* Problem happening on SYSTEM tablespace that stilluses old Rollback Segment functionality even when configured forAutomatic Undo Management (AUM).
* There are not enough rollback segments to manage the undo neededfor long running queries.
* Rollback Segments are too small and undo is overwritten beforelong running queries complete.
Reference:
Note 69464.1 – Rollback Segment Configuration &Tips
Note10630.1 – ORA-1555: “Snapshot too old” –Overview
Note862469.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 ofthe error is lower than the QUERY DURATION indicated in the errormessage. This is a legitimate ORA-1555 and if queries are going torun for very long time frames, UNDO_RETENTION may need to belarger. Auto-tuned retention may not be able to keep up with theundo workload and staying within space limitations on the UNDOtablespace.
* LOB updates and/or deletes are frequent and a higher PCT_VERSIONis required to provide enough space in the LOB Segment toaccommodate the LOB undo. RETENTION on LOBs that are updated ordeleted frequently can run into problems holding UNDO long enoughfor queries.
* QUERY DURATION shown in the error message is 30+ years andtherefore, no amount of undo will satisfy the consistent readblocks.
Note750195.1 – ORA-1555 Shows Unrealistic Query Duration (billionsof seconds)
* QUERY DURATION shown in the error message is 0. NOTE: This hasbeen filed as a bug on many release levels and has been verydifficult to narrow down to a specific problem.
Note761128.1 – ORA-1555 Error when Query Duration as 0Seconds
* QUERY DURATION is lower than TUNED_UNDRETENTION. Undo headerinformation can sometimes get overwritten or you could be seeing abug.
* TUNED_UNDORETENTION stays very high and UNDO tablepsace continuesto grow continuously or getting space errors.
Note1112431.1 – Undo Remains Unexpired When Using Non-autoextensibleDatafiles for Undo Tablespace.
Additional Reference:
Database Administration Community
- Troubleshooting ORA-1555&nb…
- ORA-00845: MEMORY_TARGET&nb…
- ORA-1652: Unable To&nb…
- ORA-4031 and Shared&nb…
- ORA-31626: job does&nb…
- ORA-28056:Writing audit&nb…
- 【转】Attachment support&nb…
- 【原】Android DHCP&nb…
- 【原】Android DHCP&nb…
- AccessWebElements(jsp by&nb…
- [js]Uncaught RangeError:&nb…
- 【转载】Spring RMI&nb…
- 【原创】 MySQLdb.cursors&nb…
- csapeditorctrl getobject&nb…
- StringUtils中 isNotEmpty&nb…
- Lesson 39 Am I&nb…
- What do I need&nb…
- What do I need&nb…
- oracle validate 所做的…
- VIP 原理和特点
- 28. 在Lotus Notes中利用公式使配置数据更灵活
- AUDIT_TRAIL参数
- 一则测试安装小计
- Troubleshooting ORA-1555&nb…
- What is RowID/ROW_ID,&…
- Oracle'sRedoLog
- oracle bfile
- 解决sqlplus 删除键,方向键扥乱码
- Running Health Checks&…
- access control list (ACL)
- SecureFile LOBs and&nb…
- hot patching