oracle的還原資料表空間UNDO寫滿磁碟空間，解決該問題的具體步驟

產生問題的原因主要以下兩點：
1. 有較大的事務量讓Oracle Undo自動擴充，產生過度佔用磁碟空間的情況；
2. 有較大事務沒有收縮或者沒有提交所導制；
說明：本問題在ORACLE系統管理中屬於比較正常的一現象，日常維護多注意對磁碟空間的監控。

UNDO資料表空間介紹
UNDO資料表空間用於存放UNDO資料,當執行DML操作(INSERT,UPDATE和DELETE)時,oracle會將這些操作的舊資料寫入到UNDO段,在oracle9i之前,管理UNDO資料時使用(Rollback Segment)完成的.從oracle9i開始,管理UNDO資料不僅可以使用復原段,還可以使用UNDO資料表空間.因為規劃和管理復原段比較複雜,所有oracle database 10g已經完全丟棄用復原段.並且使用UNDO資料表空間來管理UNDO資料。

1、查看系統磁碟狀態
AIX系統:/> df -g   (Linux系統:  df -h)
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on
/dev/undolv       30.00      0.00  100%        9     1% /u01/app/u01/app/oracle/undo

2、查看Oracle資料庫資料表空間的佔有率
select a.tablespace_name,
       round((a.maxbytes / 1024 / 1024), 2) "sum MB",
       round((a.bytes / 1024 / 1024), 2) "datafile MB",
       round(((a.bytes - b.bytes) / 1024 / 1024), 2) "used MB",
       round(( (a.maxbytes-a.bytes+b.bytes) / 1024 / 1024), 2) "free MB",
       round(((a.bytes - b.bytes) / a.maxbytes) * 100, 2) "percent_used"
  from (select tablespace_name, sum(bytes) bytes,sum(maxbytes) maxbytes
          from dba_data_files where maxbytes!=0
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.maxbytes) desc
 
 Tablespace_name SumDatafile(MB) Datafile Used   Free   Precent_used
1 UNDOTBS1 32767.98 30000 29968 2799.98 91.46

或者通過如下指令碼檢查資料庫資料表空間佔用空間情況:
select tablespace_name,sum(bytes)/1024/1024/1024 GB
from dba_data_files group by tablespace_name
union all
select tablespace_name,sum(bytes)/1024/1024/1024 GB
from dba_temp_files group by tablespace_name order by GB;

3、找出UNDO資料表空間的路徑及大小
SQL>  select file_name,bytes/1024/1024 from dba_data_files
where tablespace_name like 'UNDOTBS1'
 
/u01/app/oracle/undo/undotbs01.dbf   30000

4、檢查UNDO Segment狀態
SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
from v$rollstat order by rssize;

    USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
1 0 0 0.000358582 0.000358582 0
2 14 0 0.796791077 0.796791077 735
3 13 0 0.800453186 0.800453186 894
4 12 0 0.805213928 0.805213928 728
5 15 0 1.186126709 1.186126709 922
6 1 0 1.723365784 1.963180542 946
7 3 0 1.732704163 1.977462769 1051
8 5 0 1.978370667 2.228370667 654
9 2 0 2.032501221 2.034454346 707
10 4 0 2.065216064 2.318145752 875
11 11 0 2.100006104 2.100006104 1269
12 8 0 2.630340576 2.700653076 897
13 6 0 2.740814209 2.740814209 1030
14 9 0 2.745697021 2.772064209 1037
15 7 0 2.833526611 2.833526611 1033
16 10 0 3.088363647 3.310592651 989

這還原資料表空間中還存在16個復原的對象。

5、建立新的臨時UNDO資料表空間
可以在其它的磁碟空間臨時建立還原資料表空間
SQL>
create undo tablespace undotbs2
datafile '/u01/app/oracle/pub/undotbs02.dbf'
size 10M autoextend on;

Tablespace created.

6、切換UNDO資料表空間為新的UNDO資料表空間

SQL> alter system set undo_tablespace=undotbs2 scope=both;
System altered.

7、驗證當前資料庫的還原資料表空間
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2

8、等待原UNDO資料表空間所有UNDO SEGMENT OFFLINE

select t.segment_name,t.tablespace_name,t.segment_id,t.status from dba_rollback_segs t;
    SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID STATUS
1 SYSTEM SYSTEM 0 ONLINE
2 _SYSSMU1$ UNDOTBS1 1 OFFLINE
3 _SYSSMU2$ UNDOTBS1 2 OFFLINE
48 _SYSSMU47$ UNDOTBS1 47 OFFLINE
49 _SYSSMU48$ UNDOTBS1 48 OFFLINE
50 _SYSSMU49$ UNDOTBS1 49 OFFLINE
51 _SYSSMU50$ UNDOTBS1 50 OFFLINE
52 _SYSSMU51$ UNDOTBS1 51 OFFLINE
53 _SYSSMU52$ UNDOTBS1 52 OFFLINE
54 _SYSSMU53$ UNDOTBS1 53 OFFLINE
55 _SYSSMU54$ UNDOTBS1 54 OFFLINE
56 _SYSSMU55$ UNDOTBS1 55 OFFLINE
57 _SYSSMU56$ UNDOTBS1 56 OFFLINE
58 _SYSSMU57$ UNDOTBS1 57 OFFLINE
59 _SYSSMU58$ UNDOTBS1 58 OFFLINE
60 _SYSSMU59$ UNDOTBS1 59 OFFLINE
61 _SYSSMU60$ UNDOTBS1 60 OFFLINE
62 _SYSSMU61$ UNDOTBS1 61 OFFLINE
63 _SYSSMU62$ UNDOTBS2 62 ONLINE
64 _SYSSMU63$ UNDOTBS2 63 ONLINE
65 _SYSSMU64$ UNDOTBS2 64 ONLINE
66 _SYSSMU65$ UNDOTBS2 65 ONLINE
67 _SYSSMU66$ UNDOTBS2 66 ONLINE
68 _SYSSMU67$ UNDOTBS2 67 ONLINE
69 _SYSSMU68$ UNDOTBS2 68 ONLINE

上面對應的UNDOTBS1還原資料表空間所對應的復原段均為OFFLINE

9、刪除原UNDO資料表空間

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

10、可以再次查看系統磁碟空間：
AIX系統:/> df -g   (Linux系統:  df -h)

如果需要規範資料庫的資料表空間和路徑，還原資料表空間名稱undotbs1和路徑不能改變，
可以安裝剛才的步驟進行切換回來。
1、建立新的原來的UNDO資料表空間
可以在其它的磁碟空間臨時建立還原資料表空間
SQL>
create undo tablespace undotbs1
datafile '/u01/app/oracle/undo/undotbs01.dbf'
size 10M autoextend on maxsize 15G;

剛開始為10M，設定自動擴充，最大為15GB

Tablespace created.

2、切換UNDO資料表空間為新的UNDO資料表空間

SQL> alter system set undo_tablespace=undotbs1 scope=both;
System altered.

3、驗證當前資料庫的還原資料表空間
SQL> show parameter undo

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

4、等待原UNDO資料表空間所有UNDO SEGMENT OFFLINE

select t.segment_name,t.tablespace_name,t.segment_id,t.status from dba_rollback_segs t;
    SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID STATUS

 

上面對應的UNDOTBS2還原資料表空間所對應的復原段均為OFFLINE

5、刪除UNDO2資料表空間

SQL> drop tablespace undotbs2 including contents and datafiles;

Tablespace dropped.

6、可以再次查看系統磁碟空間：
AIX系統:/> df -g   (Linux系統:  df -h)

 

undo_retention:指定事物commit後undo 將要儲存的時間(秒)，在ORACLE10g中預設的是900秒。

GUARANTEE ： 保證undo_retention參數所設定的時間有效，這個是10g的新功能。

SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

SQL> ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

在沒有guarantee的保證下，ORACLE並不能保證能夠將undo資訊儲存900秒，如果undo資料表空間不足，那麼ORACLE將忽略undo_retention的設定，直接覆蓋掉以前的undo,這個時候有可能會產生ORA-01555錯誤。如果undo資料表空間空間足夠，那麼undo將會儲存很長一段時間，直到undo資料表空間達到maxsize，這個時候才會覆蓋undo資訊，而且ORACLE會從最古老的undo資訊開始覆蓋。

ORACLE推薦我們將undo 資料表空間中的datafile 設定MAXSIZE ，不要讓它一直自動擴充，如果ORACLE獲得了自動擴充的能力，那麼舊的undo不會被覆蓋，到後來undo資料表空間會越來越大，越來越大，直到將磁碟空間耗盡。

在有guarantee的保證下，ORACLE將會保證undo資訊能夠儲存到undo_retention設定的值之後才被覆蓋，如果這個時候同時執行了很多事物，將undo資料表空間耗完了，那麼那個事物會失敗，會報ORA-30036 錯誤，所以使用guarantee一定要慎用，如果非要使用guarantee，那麼盡量將undo 資料表空間設大 一點。

Oracle10g開始，如果你設定UNDO_RETENTION為0，那麼Oracle啟用自動調整以滿足最長執行查詢的需要。當然如果空間不足，那麼Oracle滿足最大允許的長時間查詢，而不再需要使用者手工調整。

 

FROM：http://space.itpub.net/12778571/viewspace-689354