Oracle中如何解決ORA-01555錯誤

這篇文章給大家分享的是有關Oracle中如何解決ORA-01555錯誤的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

11.6 ORA-01555: 快照太舊

什么是ORA-01555錯誤?

ORA-01555是Oracle數據庫運行過程中常見的一個錯誤。以下就是Oracle關于ORA-01555錯誤的經典描述：

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

Cause: rollback records needed by a reader for consistent read are

overwritten by other writers

簡單而言，就是為保證一致性讀的回退段數據被其它寫進程所覆蓋了。以下就是一個典型例子：

• 時間點1：會話1對 A表開始進行查詢操作。

• 時間點2：會話2修改 A表的記錄X。

• 時間點3：當會話1的查詢語句查詢到記錄X時，通過SCN號發現記錄X被修改了，而且修改的時間（時間點2）晚于時間點1。這樣，Oracle將通過保存在UNDO中記錄X修改前的數據（簡稱Before Image），來進行一致性讀取。

• 時間點4：會話2修改 A表的記錄Y，并且進行了commit操作。這樣，該事務的slot數據就可以被Oracle覆蓋了。

• 時間點5：會話2修改 A表的記錄Z，并且進行了commit操作。此時，由于UNDO表空間的空間不足，記錄Y的修改前的數據被Oracle覆蓋了。

• 時間點6：當會話1的查詢語句查詢到記錄Y時，通過SCN號發現記錄Y被修改了，而且修改的時間（時間點6）晚于時間點1。于是，Oracle將通過保存在UNDO中記錄Y修改前的數據（簡稱Before Image），來進行一致性讀取。但此時記錄Y修改前的數據已經在時間點5被覆蓋了。因此，系統將報ORA-01555錯誤！

ORA-01555錯誤的原因和解決方案比較復雜，Oracle有關該錯誤處理的文章也比較多。本書我們一方面主要針對9i之后的自動UNDO管理技術（Automatic UNDO Management），另一方面也只針對普通表的ORA-01555錯誤處理，而不關注LOB等特殊對象的ORA-01555錯誤處理。

如何獲取ORA-01555錯誤相關信息？

首先，分別從應用會話窗口和alert.log中分別獲取相關信息。

例如，應用會話窗口顯示錯誤信息：

ORA-01555: snapshot too old: rollback segment number 9 with name “_SYSSMU1$” too small

Alert.log中顯示：

ORA-01555 caused by SQL statement below (Query Duration=9999 sec, SCN:0x000.008a7c2d)

其次，通過alert.log確定QUERY DURATION。上例中為9999秒。

第三，從應用會話信息中確定undo segment名稱。例如：_SYSSMU1$。

最后，確定UNDO表空間的UNDO_RETENTION值。

SQL> show parameter undo_retention

如何解決ORA-01555錯誤？

1. 如果QUERY DURATION > UNDO_RETENTION

此時，Oracle無法保證當提交的事務過期，也就是超過UNDO_RETENTION時間之后，還能確保數據的一致性讀取。

這種情況下，最有效的解決辦法是優化查詢語句，降低語句的QUERY DURATION時間。如果無法優化了，則只能參考QUERY DURATION時間值來擴大UNDO_RETENTION值，確保Oracle保存更長時間的UNDO信息。

擴大UNDO_RETENTION值，意味著需要更多的UNDO表空間，下面還將介紹UNDO表空間的計算方法。

2. 如果QUERY DURATION <= UNDO_RETENTION

在這種情況下，通常而言是UNDO表空間滿了。如何進一步確定UNDO表空間是否滿了呢？執行如下腳本：

set pagesize 25
set linesize 120

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 ORA-1555 query>’,’MM/DD/YYYY HH24:MI:SS’)
and
to_date(‘<time when ORA-1555 occured>’,’MM/DD/YYYY HH24:MI:SS’)
order by inst_id, begin_time;

其中：

• UNXPSTEALCNT字段表示提交的Transaction Slots沒有超出UNDO_RETENTION時間，也就是處于unexpired狀態，但由于UNDO表空間滿了，而被 Oracle覆蓋了的次數。

• <start time of the ORA-1555 query>時間可通過ORA-1555發生時間減去QUERY DURATION而得到。例如：

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

922秒為15分22秒。這樣 ORA-1555開始發生的時間為 May 26 16:01:35 2009（16:01:35 = 16:16:57 – 15:22）。

如何計算UNDO表空間大小？

UNDO表空間的計算公式如下：

UndoSpace = UR * (UPS * DBS)

其中：

• UR = UNDO_RETENTION參數，單位為秒。

• UPS = 每秒產生的UNDO數據塊數量。

• DBS = DB_BLOCK_SIZE參數。

上述UNDO_RETENTION、DB_BLOCK_SIZE可通過初始化參數文件獲取，而UPS則可以通過查詢v$undostat視圖而獲得。Oracle建議查詢業務高峰時段產生的UNDO數據塊數量。為此，執行如下查詢：

SELECT undoblks / ((end_time – begin_time) * 86400) “Peak Undo Block Generation”

FROM v$undostat

WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat);

最終，計算高峰時段所需UNDO表空間大小的語句如下：

SELECT (UR * (UPS * DBS)) AS “Bytes”

FROM (SELECT value AS UR FROM v$parameter WHERE name = ‘undo_retention’),

(SELECT undoblks / ((end_time – begin_time) * 86400) AS UPS

FROM v$undostat

WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),

(SELECT block_size AS DBS

FROM dba_tablespaces

WHERE tablespace_name =

(SELECT UPPER(value)

FROM v$parameter

WHERE name = ‘undo_tablespace’));

11.7 ORA-30036: UNDO表空間無法擴展

什么叫ORA-30036錯誤?

ORA-30036也是Oracle數據庫運行過程中常見的一個錯誤。以下就是Oracle關于ORA-30036錯誤的經典描述：

Error: ORA-30036 (ORA-30036)

Text: unable to extend segment by %s in undo tablespace ‘%s’

—————————————————————————

Cause: the specified undo tablespace has no more space available.

Action: Add more space to the undo tablespace before retrying the operation. An alternative is to wait until active transactions to commit.

該錯誤表示就是UNDO表空間不夠了，簡單解決辦法就是對UNDO表空間進行擴容。但如同Oracle其它空間不夠的類似錯誤一樣，擴容并非唯一解決辦法。

UNDO表空間分配算法

欲深入了解ORA-30036錯誤原因和解決辦法，其實應從深入了解UNDO表空間分配算法開始。以下就是該算法主要思路：

1. 如果當前的UNDO extent還有空間，則從中分配新的數據塊。

2. 否則，假設下一個extent過期（expired）了，則跳到（wrap）下一個extent，并且返回其第一個數據塊。

3. 假設下一個extent為非過期（unexpired）的，則嘗試從UNDO表空間分配新的空間。假設 UNDO表空間足夠，則分配新的extent給Undo segment，并且返回新extent的第一個數據塊。

4. 如果UNDO表空間不夠了，則從offline狀態的Undo Segment中去偷取過期（expired）的extent，分配給Undo segment，并且返回該extent的第一個數據塊。

5. 如果offline狀態的Undo Segment中沒有過期（expired）的extent，則從Online狀態的Undo Segment中偷取過期（expired）的extent，分配給Undo segment，并且返回該extent的第一個數據塊。

6. 如果Undo表空間的數據文件是可擴展的，則擴展Undo表空間的數據文件，并且從中分配新的extent給Undo segment，以及返回該extent的第一個數據塊。

7. 降低Undo保存期限參數（undo_retention）10%，并從釋放的空間中偷取extent。

8. 從offline狀態的Undo Segment中偷取非過期（unexpired）的extent。

9. 重復使用現有Undo Segment中非過期（unexpired）的extent。如果所有extent都處于忙碌狀態，即都包含了未提交的信息，則跳到第10步。否則，跳到(wrap)下一個extent。

10. 從online狀態的Undo Segment中偷取非過期（unexpired）的extent。

11. 如果上述所有嘗試都失敗了，則Oracle報錯：ORA-30036！

診斷和解決辦法

1. 首先，查詢UNDO表空間的空間使用狀況：

select sum(bytes) from dba_free_space where tablespace_name=’UNDOTBS1′;

select sum(bytes) from dba_data_files where tablespace_name=’UNDOTBS1′;

2. 確定UNDO表空間的數據文件是否為可擴展的：

select autoextensible from dba_data_files where tablespace_name=’UNDOTBS1′;

3. 按狀態統計Undo Extents：

SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

如果沒有過期（expired）而只有非過期（unexpired）的Undo Extent，以及Active Extents，則Undo表空間的確太小，需要對Undo表空間大小進行重新規劃并擴容。關于Undo表空間大小的計算方法，請見本章前述內容。在10g中還可以通過OEM中的Undo Advisor特性來進行Undo表空間的規劃。

假設Undo表空間不夠，則Oracle會嘗試偷取非過期（unexpired）的Undo Extent，此時可能會導致ORA-1555錯誤。如果也沒有非過期（unexpired）的Undo Extent，則的確需要對Undo表空間進行擴容。

10g中可以為Undo表空間指定Guaranteed Undo Retention特性。例如：

create undo tablespace undotbs1 datafile ‘undotbs1.dbf’size 1000M autoextend on

retention guarantee;

這樣，Oracle就不會重復使用非過期（unexpired）的Undo Extent。因此，此時只能對Undo表空間擴容了。

關于Bug 5442919

如果有過期（expired）的Undo Extent，意味著這些extent是可以被重用的。但系統卻報出ORA-30036，則很有可能是撞上Oracle Bug 5442919了。以下就是滿足該Bug的所有條件：

1. undo_management=auto。

2. Undo表空間包含的數據文件均不能自動擴展。

3. DML操作失敗并報ORA-30036錯誤，同時被寫入log日志文件。而且alert.log中重復顯示”Failure to extend rollback segment <us#>”，其中<us#>為相同值。

4. 實例運行時間達到1小時以上。

5. 系統存在大量offline的Undo Segment，例如1000個以上。

select count(*) from dba_rollback_segs where status=’OFFLINE’;

6. Undo表空間滿了。

7. 存在大量過期（expired）或者非過期（unexpired）的Undo Extent。

select sum(bytes) “UNEXPIRED BYTES” from dba_undo_extents where tablespace_name=’UNDOTBS1’and status=’UNEXPIRED’;

select sum(bytes) “EXPIRED BYTES” from dba_undo_extents where tablespace_name=’UNDOTBS1’and status=’EXPIRED’;

該Bug在10.2.0.4以及11g中就已經修復了。在之前的版本，例如9i和10.2.0.1/2/3中，在某些平臺可以向Oracle服務部門申請補丁回退（Backport），但這些版本早已經過了Oracle產品服務期，估計已經很難得到Oracle服務部門支持了。

感謝各位的閱讀！關于“Oracle中如何解決ORA-01555錯誤”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！