ORA-01555——理解

2011年8月3日
檢查告警日誌資訊發現如下錯誤：
ORA-12012: error on auto execute of job 1925127
ORA-01555: snapshot too old: rollback segment number 19 with name "_SYSSMU19_1272485971$" too small
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 2483
ORA-06512: at "SYS.DBMS_STATS", line 24063

Oracle Support解釋發生ORA-01555的原因如下：
1. Few number of rollback segments with potentially small sizes in a
very active database (e.g., multiple users doing on-line transaction
processing -- OLTP).

2. Corrupted rollback segment prevents a consistent read requested by a
query from being able to read the block in the rollback segment.

3. Fetch across commits within an open cursor (cursors retain the "snapshot"
of the query at cursor open time).

4. Fetch across commits with delayed block cleanout (the data block is not
updated with the last committed image until the next reader accesses the
data block, which requires verification from the transaction table of
the rollback segment).

解決辦法：
回滾段“_SYSSMU19_1272485971$”太小引起ORA-01555錯誤。建議擴大UNDO表空間或者調整undo_retention時間和最佳化SQL語句。

檢視UNDO表空間的使用情況：
SQL> SELECT A.TABLESPACE_NAME TBS_NAME,A.TOTAL_BYTES "TBS_SIZE(M)",A.TOTAL_BYTES-B.FREE_BYTES "TBS_USED(M)",ROUND((A.TOTAL_BYTES-B.FREE_BYTES)/A.TOTAL_BYTES*100,2) "TBS_USE",B.FREE_BYTES "TBS_FREE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/1024/1024,2) TOTAL_BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME)A,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/1024/1024,2) FREE_BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME)B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME='UNDOTBS1';

TBS_NAME TBS_SIZE(M) TBS_USED(M) TBS_USE TBS_FREE(M)
-------------------- ----------- ----------- ---------- -----------
UNDOTBS1 126948 59557.69 46.92 67390.31

檢視檢視建議值：
select begin_time,end_time,undoblks,tuned_undoretention,maxquerylen,maxqueryid from v$undostat order by begin_time;

檢視系統UNDO相關的引數
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer 300
undo_tablespace string UNDOTBS1

修改undo_retention的引數值
SQL> ALTER SYSTEM SET undo_retention=900 SCOPE=BOTH

檢視UNDO表空間的RETENTION的值
SQL> select tablespace_name,retention from dba_tablespaces where tablespace_name='UNDOTBS1';

TABLESPACE_NAME RETENTION
------------------------------ -----------
UNDOTBS1 NOGUARANTEE
如果UNDO表空間的RETENTION設定為GUARANTEE意味著UNDO表空間必須滿足UNDO_RETENTION的時間限制，即UNDO資訊的保留時間必須滿足UNDO_RETENTION設定的值。
1.如果設定為NOGUARANTEE。ORACLE並不能保證能夠將UNDO資訊的保留時間滿足UNDO_RETENTION設定的值，如果undo表空間不足，那麼ORACLE將忽略UNDO_RETENTION的設定，直接覆蓋掉以前的UNDO,這個時候有可能會產生ORA-01555錯誤。如果UNDO表空間空間足夠，那麼undo將會儲存很長一段時間，直到undo表空間達到maxsize，這個時候才會覆蓋undo資訊，而且ORACLE會從儲存時間最長的UNDO資訊開始覆蓋。ORACLE推薦我們將UNDO表空間中的datafile設定MAXSIZE，不要讓它一直自動擴充套件，如果ORACLE獲得了自動擴充套件的能力，那麼舊的UNDO不會被覆蓋，到後來UNDO表空間會越來越大，直到將磁碟空間耗盡。

2.如果設定為GUARANTEE。ORACLE將會保證UNDO資訊能夠儲存到UNDO_RETENTION設定的值之後才允許被覆蓋，如果這個時候同時執行了很多事物，將UNDO表空間耗完了，那麼那個事物會失敗，會報ORA-30036錯誤，所以使用guarantee一定要慎用，如果非要使用guarantee，那麼儘量將undo表空間設大一點。Oracle10g開始，如果設定UNDO_RETENTION為0，那麼Oracle啟用自動調整以滿足最長執行查詢的需要。當然如果空間不足，那麼Oracle滿足最大允許的長時間查詢。而不再需要使用者手工調整。

3.啟用或者禁用UNDO表空間RETENTION
啟用：
SQL> ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;
禁用：
SQL> ALTER TABLESPACE UNDOTBS1 RETENTION NOGUARANTEE;

[@more@]