Oracle 11gR2 Database UNDO資料表空間使用率居高不下

Oracle 11gR2 Database UNDO資料表空間使用率居高不下

客戶的資料庫是Oracle Database 11.2.0.3.0 for AIX 6.1 64bit的單機資料庫。客戶查詢DBA_FREE_SPACE發現UNDO資料表空間的使用率高達98%以上。客戶的UNDO資料表空間已經手動擴充到了25GB，且一直在增加，為了UNDO資料表空間能及時的被釋放，UNDO資料表空間對應的所有資料檔案自動擴充都被關閉。查詢DBA_UNDO_EXTENTS發現在UNDO資料表空間中當前沒有ACTIVE的EXTENT存在，UNEXPIRED的佔到總空間的60%，有30%是EXPIRED，但Oracle並沒有及時的釋放這些空間。

  客戶的UNDO資料表空間並沒有設定成GUARANTEE模式，所以根據我們的知識都明白UNDO資料表空間中的EXPIRED和UNEXPIRED都是可能被重用的，但是這麼高的UNDO資料表空間使用率看著讓人不踏實。

  雖然我們在初始化參數中設定了UNDO_RETENTION等參數，但從Oracle 10gR2開始，預設Oracle都開啟了UNDO資料表空間的自動調整功能，尋找V$UNDOSTAT.TUNED_UNDORETENTION發現最近一段時間該值都被自動調整到了3500多分鐘，也就是說UNDO資料表空間中的資料要保留接近3天才會到期，正是因為這麼長的資料未到期時間，且資料表空間又足夠的大，才導致了UNDO資料表空間的空間一致未被釋放，同時也找到了Oracle下面的一段解釋：

Why TUNED_UNDORETENTION is calculated so high making undo space grow fast ?

When non-autoextensible undo space is used, tuned_undoretention is calculated based on a percentage of the undo tablespace size. In some cases especially with large undo tablespace, This will make it to be calculated so large.

To fix this behaviour, Set the following instance parameter:

_smu_debug_mode=33554432

 With this setting, TUNED_UNDORETENTION is not calculated based on a percentage of the fixed size undo tablespace. Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.

    簡單的說，就是當UNDO資料表空間對應的資料檔案非自動擴充，且UNDO資料表空間又比較大的時候，tuned_undoretention的值是根據UNDO資料表空間大小的百分比來計算的，在一些情況下會將tuned_undoretention的值調整得特別大。

  解決辦法，如果設定_smu_debug_mode=33554432，那麼Oracle的UNDO RETENTION自動調整功能依然被開啟，但是計算tuned_undoretention是根據MAXQUERYLEN secs +300來計算，而不是根據UNDO資料表空間大小的百分比來計算，這樣就可以避免TUNED_UNTORETENTION出現特別大的值。

以上內容摘自：《FAQ – Automatic Undo Management (AUM) / System Managed Undo (SMU) (Doc ID 461480.1)》。

同樣我們還參考了另一篇文章：

Automatic Tuning of Undo_retention Causes Space Problems (Doc ID 420525.1)

In this Document

 Symptoms 

 Cause 

 Solution 

 References 

 

Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.4 to 10.2.0.4 [Release 10.2]
Information in this document applies to any platform.
 Oracle Server Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3 -- fixed by patchset 10.2.0.4 and no issues on this at 11g

 *** Checked for currency: 13-SEP-2012 ***

Symptoms

You have verified that Document 413732.1 is not applicable and the problem is not a misunderstanding in the way EXPIRED/UNEXPIRED are used and reused over time.

 Look for:
1. Whether the undo is automatically managed by the database by checking the following instance parameter:

UNDO_MANAGEMENT=AUTO

2. Whether the undo tablespace is fixed in size:

SQL> SELECT autoextensible
      FROM dba_data_files
      WHERE tablespace_name='<UNDO_TABLESPACE_NAME>'

 This returns "NO" for all the undo tablespace datafiles.
3. The undo tablespace is already sized such that it always has more than enough space to store all the undo generated within the undo_retention time, and the in-use undo space never exceeds the undo tablespace warning alert threshold (see below for the query to show the thresholds).
4. The tablespace threshold alerts recommend that the DBA add more space to the undo tablespace:

SQL> SELECT creation_time, metric_value, message_type, reason, suggested_action
      FROM dba_outstanding_alerts
      WHERE object_name='<UNDO_TABLESPACE_NAME>';

 This returns a suggested action of: "Add space to the tablespace".

 Or,

 This recommendation has been reported in the past but the condition has now cleared:

SQL> SELECT creation_time, metric_value, message_type, reason, suggested_action, resolution
      FROM dba_alert_history
      WHERE object_name='<UNDO_TABLESPACE_NAME>';

5. The undo tablespace in-use space exceeded the warning alert threshold at some point in time. To see the warning alert percentage threshold, issue:

SQL> SELECT object_type, object_name, warning_value, critical_value
 FROM dba_thresholds
 WHERE object_type='TABLESPACE';

 To see the (current) undo tablespace percent of space in use:

SQL> SELECT
          ((SELECT (NVL(SUM(bytes),0))
            FROM dba_undo_extents
            WHERE tablespace_name='<UNDO_TABLESPACE_NAME>'
            AND status IN ('ACTIVE','UNEXPIRED')) * 100)/       
          (SELECT SUM(bytes)
          FROM dba_data_files
          WHERE tablespace_name='<UNDO_TABLESPACE_NAME>')
          "PCT_INUSE"
      FROM dual;

Cause

The cause of this problem has been identified in:
Bug:5387030 - AUTOMATIC TUNING OF UNDO_RETENTION CAUSING SPACE PROBLEMS

 It is caused by a wrong calculation of the tuned undo retention value.

 Bug:5387030 is fixed in RDBMS 11.1.

Solution

To implement a solution for Bug:5387030, please execute any of the below alternative solutions:
• Upgrade to 11.1 in which Bug:5387030 is fixed

 OR
• Apply patchset release 10.2.0.4 or higher in which Bug:5387030 is fixed.

 OR
• Download and apply interim Patch:5387030, if available for your platform and RDBMS release. To check for conflicting patches, please use the MOS Patch Planner Tool. If no patch is available, file a Service Request through My Oracle Support for your specific Oracle version and platform.

 OR
• Use any of the following workarounds:
1. Set the AUTOEXTEND and MAXSIZE attributes of each datafile of the undo tablespace in such a way that they are autoextensible and the MAXSIZE is equal to the current size (so the undo tablespace now has the AUTOEXTEND attribute but does not autoextend):

SQL> ALTER DATABASE DATAFILE '<datafile_flename>' AUTOEXTEND ON MAXSIZE <current_size>

 With this setting, V$UNDOSTAT.TUNED_UNDORETENTION is not calculated based on a percentage of the undo tablespace size. Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.
2. Set the following instance parameter:

_smu_debug_mode=33554432

 With this setting, V$UNDOSTAT.TUNED_UNDORETENTION is not calculated based on a percentage of the fixed size undo tablespace. Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.
3. Set the following instance parameter:

_undo_autotune = false

 With this setting, V$UNDOSTAT (and therefore V$UNDOSTAT.TUNED_UNDORETENTION) is not maintained and the undo retention used is based on the UNDO_RETENTION instance parameter.

NOTE: This means you loose all advantages in having automatic undo management and is not an ideal long term fix.

 

NOTE: Even with the patch fix installed, the autotuned retention can still grow under certain circumstances. The fix attempts to throttle back how aggressive that autotuning will be. Options 2 and 3 may be needed to get around this aggressive growth in some environments.

References
BUG:5387030 - AUTOMATIC TUNING OF UNDO_RETENTION CAUSING SPACE PROBLEMS
NOTE:413732.1 - Full UNDO Tablespace In 10gR2 and above

    這篇文章本來是用來解決10gR2中的bug，但是在11gR2中同樣適用，說明在11gR2中同樣存在該bug。在文章提供了3種解決方案：

1).將UNDO資料表空間對應的資料檔案調整為自動擴充，並為其設定一個最大值。
SQL> ALTER DATABASE DATAFILE '<datafile_flename>' AUTOEXTEND ON MAXSIZE <current_size>

    客戶正是通過這種方式解決了問題，調整之後空間很快得到釋放，V$UNDOSTAT.TUNED_UNDORETENTION值立即變小，這和文章前面的解釋是完全吻合的，當UNDO資料表空間對應的資料檔案是自動擴充的，那麼V$UNDOSTAT.TUNED_UNDORETENTION值的計算就不再依賴於UNDO資料表空間的百分比（UNDO資料表空間本身較大）。

2).設定_smu_debug_mode隱藏參數。
_smu_debug_mode=33554432
    前面我們已經對這個參數進行瞭解釋，這裡再次驗證。

3).設定_undo_autotune隱藏參數。
_undo_autotune = false
    前面的兩種方法沒有關閉Oracle的UNDO自動調整RETENTION的功能，將_undo_autotune設定為false，就徹底關閉了自動調整UNDO RETENTION的功能，那麼UNDO的RETENTION時間完全依賴於初始化參數UNDO_RETENTION的值，預設值為900秒。

  以上三種方法的任意一種方法都可以解決客戶面臨的該問題。

    這篇文章是對我另一篇文章有益的補充：《監控和管理Oracle UNDO資料表空間的使用》：

    我們學習的很多知識點過一段時間都可能忘記，但通過具體的案例我們更容易的記住。
   
--end--

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

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

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

Oracle undo的一些理解

Oracle undo 鏡像資料探究

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

Linux-6-64下安裝Oracle 12C筆記

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

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