監控和管理Oracle UNDO資料表空間的使用

監控和管理Oracle UNDO資料表空間的使用

對Oracle資料庫UNDO資料表空間的監控和管理是我們日常最重要的工作之一，UNDO資料表空間通常都是Oracle自動化管理（通過undo_management初始化參數確定）；UNDO資料表空間是用於儲存DML操作的前鏡像資料，它是執行個體恢複，資料復原，一致性查詢功能的重要組件；我們常常會忽略對它的監控，這會導致UNDO資料表空間可能出現以下問題：
1).空間使用率100%，導致DML操作無法進行。
2).警示日誌中出現大量的ORA-01555警示錯誤。
3).執行個體恢複失敗，資料庫無法正常開啟。

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

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

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

一.對Oracle自動化管理UNDO進行幹預。

  由於UNDO是自動化管理，可幹預的地方非常的少，更多的是監控，通過以下幾個地方可對UNDO資料表空間實施一定的幹預：

1).初始化參數

undo_management=AUTO    表示執行個體自動化管理UNDO資料表空間，從Oracle 9i開始，Oracle引進了AUM（Automatic Undo Management）。
undo_retention=900              事務提交後，相應的UNDO資料保留的時間，單位：秒。
undo_tablespace=UNDOTBS1 活動的UNDO資料表空間。
_smu_debug_mode=33554432
 _undo_autotune=TRUE

2).Automatic UNDO Retention

    Automatic UNDO Retention是10g的新特性，在10g和之後的版本的資料庫，這個特性是預設啟用的。
    在Oracle Database 10g中當自動undo管理被啟用，總是存在一個當前的undo retention，Oracle Database嘗試至少保留舊的undo資訊到該時間。資料庫收集使用方式統計資訊，基於這些統計資訊和UNDO資料表空間大小來調整undo retention的時間。
    Oracle Database基於undo資料表空間大小和系統活動自動調整undo retention，通過設定UNDO_RETENTION初始化參數指定undo retention的最小值。

    查看Oracle自動調整UNDO RETENTION的值可以通過以下查詢獲得：
SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
    TUNED_UNDORETENTION FROM V$UNDOSTAT;

    針對自動擴充的UNDO資料表空間，系統至少保留UNDO到參數指定的時間，自動調整UNDO RETENTION以滿足查詢對UNDO的要求，這可能導致UNDO急劇擴張，可以考慮不設定UNDO RETENTION值。

    針對固定的UNDO資料表空間，系統根據最大可能的undo retention進行自動調整，參考基於UNDO資料表空間大小和使用曆史進行調整，這將忽略UNDO_RETENTION,除非資料表空間啟用了RETENTION GUARANTEE。

自動調整undo retention不支援LOB，因為不能在undo資料表空間中儲存任何有關LOBs事務的UNDO資訊。

 可以通過設定_undo_autotune=FALSE顯示的關閉Automatic UNDO Retention功能。

3).TUNED_UNDORETENTION計算的值很大導致UNDO資料表空間增長很快？

    當使用的UNDO資料表空間非自動成長，tuned_undoretention是基於UNDO資料表空間大小的使用率計算出來的，在一些情況下，特別是較大的UNDO資料表空間時，這將計算出較大的值。

 為瞭解決此行為，設定以下的執行個體參數：
_smu_debug_mode=33554432

設定該參數，TUNED_UNDORETENTION就不基於undo資料表空間大小的使用率計算，代替的是設定(MAXQUERYLEN +300)和UNDO_RETENTION的最大值。

4).UNDO資料表空間資料檔案自動擴充

    如果UNDO資料表空間是一個自動擴充的資料表空間，那麼很有可能UNDO資料表空間狀態為EXPIRED的EXTENT不會被使用（這是為了減少報ORA-01555錯誤的幾率），這將導致UNDO資料表空間變得很大；如果將UNDO資料表空間設定為非自動擴充，那麼狀態為EXPIRED的EXTENT就能被利用，這樣可以一定程度控制UNDO資料表空間的大小，但這樣會增加ORA-01555報錯和UNDO空間不足報錯的風險。合理的非自動擴充的UNDO資料表空間大小，以及合理的UNDO_RETENTION設定可以確保穩定的UNDO空間使用。

5).UNDO資料表空間guarantee屬性

    如果UNDO資料表空間是noguarantee狀態，Oracle不確保提交後的事務對應的UNDO資料表空間中的資料會保留UNDO_RETENTION指定的時間長度，如果UNDO資料表空間不足，其他事務將可能偷盜相應的未到期的空間；將UNDO資料表空間設定為guarantee能夠確保提交後的事務對應UNDO資料表空間中的資料在任何情況下都將保留UNDO_RETENTION指定的時間長度。
   
SQL> SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                                              RETENTION
------------------------------------------------------------ ----------------------
UNDOTBS1                                                    NOGUARANTEE

SQL> alter tablespace undotbs1 retention guarantee;

資料表空間已更改。

SQL> SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                                              RETENTION
------------------------------------------------------------ ----------------------
UNDOTBS1                                                    GUARANTEE

6).UNDO資料表空間大小

  針對不同類型的業務系統，需要有充足的UNDO資料表空間，確保系統能夠正常的運行。UNDO空間的大小跟業務系統有關係，也跟UNDO_RETENTION和UNDO資料表空間的GUARANTEE屬性有關係，通常我們可以通過V$UNDOSTAT的統計資訊估算出需要的UNDO資料表空間大小。

二.監控UNDO資料表空間使用方式。

  作為管理員來說，針對UNDO資料表空間更重要的是日常的監控工作，監控常用到以下的視圖：
a).DBA_ROLLBACK_SEGS
DBA_ROLLBACK_SEGS describes rollback segments.

b).V$ROLLSTAT
V$ROLLSTAT contains rollback segment statistics.

c).V$TRANSACTION
V$TRANSACTION lists the active transactions in the system.

d).V$UNDOSTAT
V$UNDOSTAT displays a histogram of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. The view returns NULL values if the system is in manual undo management mode.

Each row in the view keeps statistics collected in the instance for a 10-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the (partial) current time period. The view contains a total of 576 rows, spanning a 4 day cycle.

e).DBA_UNDO_EXTENTS
DBA_UNDO_EXTENTS describes the extents comprising the segments in all undo tablespaces in the database.  This view shows the status and size of each extent in the undo tablespace.

DBA_UNDO_EXTENTS.STATUS有三個值：
ACTIVE      表示未提交事務還在使用的UNDO EXTENT，該值對應的UNDO SEGMENT的DBA_ROLL_SEGMENTS.STATUS一定是ONLINE或PENDING OFFLINE狀態，一旦沒有活動的事務在使用UNDO SEGMENT，那麼對應的UNDO SEGMENT就變成OFFLINE狀態。
EXPIRED    表示已經提交且超過了UNDO_RETENTION指定時間的UNDO EXTENT。
UNEXPIRED 表示已經提交但是還沒有超過UNDO_RETENTION指定時間的UNDO EXTENT。

  Oracle重複使用UNDO EXTENT的原則如下：
1).ACTIVE狀態的EXTENT在任何情況下都不會被佔用。
2).如果是自動擴充的UNDO資料表空間，Oracle會保證EXTENT至少保留UNDO_RETENTION指定的時間。
3).如果自動擴充空間不足或者UNDO資料表空間是非自動擴充，Oracle會嘗試重複使用同一個段下面EXPIRED狀態的EXTENT，如果本段中沒有這樣的EXTENT，就會去偷別的段下面EXPIRED狀態的EXTENT，如果依然沒有這樣的EXTENT，就會使用本段UNEXPIRED的EXTENT，如果還是沒有，那麼會去偷別的段的UNEXPIRED的EXTENT，這個都沒有，就會報錯。

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁