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