undo案列一

這周跟oracle 10g undo耗上了，一周連續遇到兩套資料庫undo表空間使用率過高的情況，表面現象基本一緻，但是實際的情況卻不同，記錄下。。。

案例一：

DB：10.2.0.2.0

OS：AIX

現象：早上一到就被客戶找到說有套庫的UNDO使用率很高（後來才知道前一天還因為undo表空間空間不足，一批處理程式報ORA-30036錯誤，沒執行成功）。

一聽到這個消息，我第一反應是問客戶是否是10g資料庫（10g資料庫因為undo_retention自動優化引起undo表空間使用率過高的情況很常見，之前也遇到過幾個這樣的案例），客戶說是10g的，慣性思維的認為是undo_retention自動優化功能引起的（有時候慣性思維也不好）。

簡單的看了下該資料庫設定的undo_retention設定為

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_in_memory_undo                      boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     43200
           

43200/60/60 = 12個小時，算是一個比較長的時間了。

通過檢視v$undostat來檢視undo_retention優化自動調整的undo_retention時間

BEGIN_TIME          END_TIME             ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
------------------- -------------------  ---------- ------------- ----------- -------------------
2013-01-08 08:05:59 2013-01-08 08:15:59     0       2368       2907672   33808        43200
2013-01-08 08:15:59 2013-01-08 08:25:59     0       2368       2827800   32784        43200
2013-01-08 08:25:59 2013-01-08 08:35:59     0       2368       2829848   31760        43200
2013-01-08 08:35:59 2013-01-08 08:45:59     0       3392       2841368   32528        43200
2013-01-08 08:45:59 2013-01-08 08:55:59     0       3392       2846488   30480        43200
2013-01-08 08:55:59 2013-01-08 09:05:59     0       6464       2853656   26384        43200
2013-01-08 09:05:59 2013-01-08 09:15:59     0       4416       2880280   31504        43200
2013-01-08 09:15:59 2013-01-08 09:25:59     0      11584       2885400   20240        43200
           

檢視undo表空間extent的情況

SQL> select status,sum(bytes)/1024/1024 mb from dba_undo_extents group by status;

STATUS            MB
--------- ----------
EXPIRED    21350.625
UNEXPIRED  1488.1875
ACTIVE             8
           

從上面的情況來看，确實是undo_retention時間比較長引起。

建議客戶通過設定_undo_autotune為false來關閉undo_retention的自動優化功能，設定undo_retention為10800。

設定後讓客戶重新跑程式，結果到中午的時候發現undo 中active狀态的extent持續增加，undo表空間又即将不足，又緊急擴容8G，難道是真的由于undo不足，經熊哥（O(∩_∩)O哈！，大牛老熊）指點，發現些線索。下面是前一天undo運作的情況：

BEGIN_TIME          END_TIME            SSOLDERRCNT NOSPACEERRCNT ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS
------------------- ------------------- ----------- ------------- ---------- ------------- -----------
2013-01-07 13:45:59 2013-01-07 13:55:59           0             0    1646136        142360      131488
2013-01-07 13:55:59 2013-01-07 14:05:59           0             0    1740112        127328       52288
2013-01-07 14:05:59 2013-01-07 14:15:59           0             0    1834240         81264        4352
2013-01-07 14:15:59 2013-01-07 14:25:59           0             0    1909856          9728         400
2013-01-07 14:25:59 2013-01-07 14:35:59           0             0    1910088          8864         344
2013-01-07 14:35:59 2013-01-07 14:45:59           2             4    1909032         10824         128
2013-01-07 14:45:59 2013-01-07 14:55:59           2             1    1908616         11352          16
2013-01-07 14:55:59 2013-01-07 15:05:59           0             1       3424       1916552           8
2013-01-07 15:05:59 2013-01-07 15:15:59           0             0       3656       1915664         664
2013-01-07 15:15:59 2013-01-07 15:25:59           0             0       2752       1915000        2232
2013-01-07 15:25:59 2013-01-07 15:35:59           0             0       3776       1906608        1792
           

在1月7日下午14:35到15:05之間NOSPACEERRCNT字段開始出現計數（計數總共6次），該字段表示請求undo空間未成功的次數，在14:35到14:45期間undo表空間使用的空間為active狀态的extent為1908616*8192/1024/1024=14911(MB)、unexpired狀态的extent為11352*8192/1024/1024=89(MB)，合計15G，此次資料庫PSAPUNDO表空間的大小也為15G，已沒有空閑空間可配置設定（詢問客戶，這跟前程式報錯的時間基本一緻），随後的時刻active狀态的extent降為3424*8192/1024/1024=27(MB),unexpired狀态的extent增加為1916552*8192/1024/1024=14973(MB).

随後客戶先擴容了8G，再看看擴容後的情況

BEGIN_TIME          END_TIME            SSOLDERRCNT NOSPACEERRCNT ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS
------------------- ------------------- ----------- ------------- ---------- ------------- -----------
2013-01-07 22:55:59 2013-01-07 23:05:59          0             0    2940528          3320            0
2013-01-07 23:05:59 2013-01-07 23:15:59          0             0    2940520          3296           32
2013-01-07 23:15:59 2013-01-07 23:25:59          0             0    2940520          3296           32
2013-01-07 23:25:59 2013-01-07 23:35:59          0             0       2376       2941288          184
2013-01-07 23:35:59 2013-01-07 23:45:59          0             3       2392       2941448            8
2013-01-07 23:45:59 2013-01-07 23:55:59          0             0       2368       2940888          592
           

在23:15到23:25之間，active狀态的extent為2940520*8192/1024/1024=22973(MB),unexpired狀态的extent為26(MB)，合計23G左右，此時已沒有空閑空間可配置設定，客戶确認在此期間運作的批處理程式确實再次報錯ORA-30036。

可以确定該批量程式需要的undo表空間的使用率确實超過undo自身的空間。

總結下：

出現ORA-30036報錯的情況(目前能想到的)：

1）undo_retention設定時間過大，導緻undo中配置設定的大部分extent長期處于unexpired狀态，影響extent的回收重用。

2）oracle 10g的新特性undo_retention自動優化功能根據undo的使用情況，自動計算出undo_retention的值，導緻大部分extent長期處于unexpired狀态，影響extent的回收重用。

3）undo表空間自身設定得過小，且置于非自動增長模式下，也會導緻該錯誤的發生。

4）有過大的事務，其需要的undo空間大于目前資料庫undo表空間的空間

5）Oracle的BUG。Bug:5387030 - Automatic tuning of undo_retentioncauses unusual extra space allocation [ID 5387030.8]

6） Oracle在undo_retention自動優化管理方面的缺陷。

順帶學習了下Oracle在undo表空間自動管理模式下，請求空間的情況如下：

當一個新事務開始，按以下步驟進行：

1) 在沒有active transaction的undo segment上配置設定一個extent，oracle盡可能将事務分布在所有的undo segment上。

2) 如果沒有找到online的segment，則嘗試去找offline的segment，并online它。

3) 如果沒有找到，則建立一個新的undo segment.

4) 如果空間不允許，在目前的undo segment上嘗試重用expired狀态的extent.

如果一個已經運作的事物，需要更多的undo空間時，執行以下步驟：

1) 如果目前的extent有free block，則使用它們。

2) 如果目前的extent沒有free block，并且下一個exent是expired狀态，則使用下一個extent.

3) 如果下一個extent不是expired狀态，那麼嘗試從undo tablespace擷取空間，如果有空閑空間，則為目前的segment配置設定新的extent.

4) 如果沒有空閑空間，需要從offline segment上偷extent，先從offline segment上deallocate extent，然後加到目前的undo segment上。

5) 如果沒有offline segment，則從online segment上偷extent，同樣從online segment 上deallocate extent，然後加到目前的undo segment上。

6) 嘗試擴充資料檔案。

7) 嘗試重用目前undo segment上unexpired狀态的extent，如果所有的exetent都繁忙（包含未送出的事務)，則下一步。

8) 嘗試從offline的segment上偷取unexpired狀态的extent

9) 嘗試從online的segment上偷取unexpired狀态的extent

10)如果以上步驟都無法擷取空間，則報錯ORA-30036：unable to extend segment by %s inundo tablespace ‘%s’

參考資料：http://www.hellodb.net/2009/02/oracle_undo.html