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 時間.。
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
關于Oracle 10g UNDO表空間過大導致磁盤空間不足的解決方法就介紹到這里了，希望本次的介紹能夠對您有所收獲！
【編輯推薦】
