Oracle 10g UNDO資料表空間過大的解決方案

在Oracle 10g資料庫的應用中，出現了UNDO資料表空間過大導致磁碟空間不足而崩潰的現象。對此問題進行分析後，總結了出現該問題的原因主要有以下兩點：

1. 有較大的事務量讓Oracle Undo自動擴充，產生過度佔用磁碟空間的情況；

2. 有較大事務沒有收縮或者沒有提交所導制；

說明：本問題在Oracle系統管理中屬於比較正常的一現象，日常維護多注意對磁碟空間的監控。

Oracle 10g 有自動Automatic Undo Retention Tuning 這個特性。設定的 undo_retention 參數只是一個指導值，預設值900秒,，Oracle 會自動調整 Undo (會跨過 undo_retention 設定的時間) 來保證不會出現 Ora-1555 錯誤.。通過查詢V$UNDOSTAT（該視圖記錄4天以內的UNDO資料表空間使用方式，超過4天可以查詢DBA_HIST_UNDOSTAT視圖）的tuned_undoretention （該欄位在10G版本才有，9I是沒有的）欄位可以得到Oracle 根據事務量（如果是檔案不可擴充，則會考慮剩餘空間）採樣後的自動計算出最佳的 retenton 時間。

RMAN備份與恢複之undo資料表空間丟失

關於Oracle 釋放過度使用的undo資料表空間

Oracle undo的一些理解

Oracle undo 鏡像資料探究

Oracle 復原(ROLLBACK)和撤銷(undo)

1)查詢retention值

show parameter undo_retention

查詢自動計算出最佳的retenton 時間

select tuned_undoretention, maxquerylen, maxqueryid from v$undostat;

2）更改retention值

ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH;

這樣對於一個事務量分布不均勻的資料庫來說,，就會引發潛在的問題--在批處理的時候可能 Undo 會用光， 而且這個狀態將一直持續， 不會釋放。

如何取消10g的auto UNDO Retention Tuning，有如下三種方法：

(1)10.2.0.2/10.2.0.3有相應的patch，這個bug在10.2.0.4中已經修複，建議找時間停機打patch.

(2)設定隱含參數_smu_debug_mode=33554432，將tuned_undoretention取值演算法修正為max(maxquerylen secs + 300,undo_retention )，不建議使用SQL> Alter system set "_smu_debug_mode" = 33554432;

(3)設定隱含參數_undo_autotune=false，關閉自動undo retention調整特性，不建議使用SQL> Alter system set "_undo_autotune" = false;from metalink 420525.1： Automatic Tuning of Undo_retention Causes Space Problems.

解決步驟：

1. 啟動SQLPLUS，並用sys登陸到資料庫。

#su - oracle

$>sqlplus / as sysdba

2. 尋找資料庫的UNDO資料表空間名,確定當前常式正在使用的UNDO資料表空間：Show parameter undo_tablespace。

3. 確認UNDO資料表空間；

SQL> select name from v$tablespace;

NAME

------------------------------

.......

UNDOTBS1

4. 檢查資料庫UNDO資料表空間佔用空間情況以及資料檔案存放位置；

SQL>select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS%';

5. 查看復原段的使用方式，哪個使用者正在使用復原段的資源，如果有使用者最好更換時間（特別是生產環境）。

SQL> select s.username, u.name from v$transaction t,v$rollstat r, v$rollname u,v$session s

where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username;

6. 檢查UNDO Segment狀態；

SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;

7. 建立新的UNDO資料表空間，並設定自動擴充參數；

SQL> create undo tablespace undotbs2 datafile '/opt/oracle/oradata/ge01/UNDOTBS2.dbf' size 100m reuse autoextend on next 50m maxsize 5000m;

Tablespace created.

8. 動態更改spfile設定檔；

SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.

9. 等待原UNDO資料表空間所有UNDO SEGMENT OFFLINE；

select usn,xacts,status,rssize/1024/1024,hwmsize/1024/1024, shrinks from  v$rollstat order by rssize;

10. 再執行看UNDO資料表空間所有UNDO SEGMENT ONLINE；

select usn,xacts,status,rssize/1024/1024,hwmsize/1024/1024, shrinks from v$rollstat order by rssize;

11. 刪除原有的UNDO資料表空間；

SQL> drop tablespace undotbs1 including contents;

Tablespace dropped.

12. 確認刪除是否成功；

SQL> select name from v$tablespace;

NAME

------------------------------

.......

UNDOTBS2

12 rows selected.

13. 更新pfile

SQL> create pfile from spfile;

File created.

14. 冊除原UNDO資料表空間的資料檔案，其檔案名稱為步驟中執行的結果。

#rm $ORACLE_BASE/oradata/$ORACLE_SID/undotbs01.dbf