Oracle - 回滾表空間 Undo 的整理

一、Undo的概念
    Oracle 資料庫建立並管理用於回滾或撤銷資料庫更改的資訊。這些資訊包括事務中所有操作的記錄，主要是在它們被提交之前。這些記錄統稱為 Undo。

    Undo 表空間僅用於存放 Undo 資料，不能建立其他的資料庫物件，Undo 表空間只儲存系統的 Undo 資料。當執行 DML(INSERT、UPDATE、DELETE) 操作時，Oracle 會將這些操作的舊資料(事務所修改的資料的舊值)寫入到 Undo 表空間中。在 Oracle9i 之前，管理 Undo 資料時使用回滾段(Rollback Segment)完成的；從 Oracle9i 開始，管理 Undo 資料不僅可以使用回滾段，還可以使用 Undo 表空間；Oracle11g 開始，因為規劃和管理回滾段的複雜性而不推薦用回滾段，並且預設使用 Undo 表空間來管理 Undo 資料。

二、Undo的作用
    事務回退：當執行 DML 操作修改資料時，Undo 資料被存放到 Undo 段，而新資料則被存放到資料段中，如果事務操作存在問題，舊需要回退事務，以取消事務變化。

    讀一致性：使用者檢索資料庫資料時，Oracle 總是使用使用者只能看到被提交過的資料(讀取提交)或特定時間點的資料(SELECT語句時間點)，這樣可以確保資料的一致性。
    
    事務恢復：事務恢復是例程恢復的一部分，它是由Oracle伺服器自動完成的。如果在資料庫執行過程中出現例程失敗(如斷電、記憶體故障、後臺程式故障等)，那麼當重啟Oracle伺服器時，後臺程式 SMON 會自動執行例程恢復，執行例程恢復時，Oracl會重新做所有未應用的記錄，回退未提交事務。

    倒敘查詢：倒敘查詢用於取得特定時間點的資料庫資料，它是9i新增加的特性，假定當前時間為上午11:00，某使用者在上午10:00執行了修改並提交了事務，為了取得10:00之前的資料，使用者可以使用倒敘查詢特徵。

三、AUM的機制
    從 Oracle11g 開始，新建的資料庫預設使用自動的機制來管理 Undo 的資訊和空間，這個機制就是 AUM(Automatic Undo Management)。

    當使用 DBCA 建立資料庫的時候，一個自動擴充套件的名為 UNDOTBS1 的 Undo 表空間將會自動建立。例項啟動後，資料庫會自動選用第一個可用的 Undo 表空間，如果沒有可用的 Undo 表空間，例項將會把 Undo 記錄儲存在 system 表空間。如果資料庫包含多個 Undo 表空間，那麼可以選擇在啟動時通過 UNDO_TABLESPACE 引數來決定使用哪個表空間。如果在初始化引數裡指定的 Undo 表空間不存在，那麼例項就會啟動失敗，在 RAC 環境下，每個例項分別對應一個 Undo 表空間。

    資料庫也可以執行在手動管理 Undo 模式，在這種模式下，Undo Space 的管理是通過 Rollback Segments，而不是使用 Undo TableSpace。但是手工管理 Rollback Segment 是很複雜的，Oracle 強烈推薦使用自動管理 Undo 模式。在Oracle11g 和之後的版本里，在初始化引數中如果沒有指定 UNDO_MANAGEMENT 引數，那麼預設使用 AUTO，之前的版本則預設使用手動。

    在自動管理的 Undo 表空間下，資料庫建立時會自動初始化 10 個回滾段，從 V$ROLLNAME 中可以查詢到這些自動建立的回滾段資訊。此外，Oracle 會根據系統的負載情況自動建立以及釋放回滾段。回滾段的大小和數量對於系統至關重要，所有 Online 的回滾段(除了 system 的回滾段)都會被迴圈使用。 而每個回滾段都包含有一些 Extent(擴充套件)，當某個 Extent 寫滿後會自動切換到另一個 Extent 繼續使用。
 
    回滾段儲存的是修改的資料的前映象資料，對於 DML(INSERT、UPDATE、DELETE) 語句來說，回滾段儲存的就是 DML 反向操作的資料，如果是 INSERT 插入一行資料，回滾段儲存的就是刪除該行的記錄，為了節約空間精簡資料，回滾段中只儲存 INSERT 資料的 Rowid，Undo 只需要刪除該 Rowid 的記錄即可；如果是 UPDATE 資料，回滾段只儲存修改欄位的舊值，Undo 只需要把舊值覆蓋即可；如果是 DELETE 資料，回滾段則需要儲存整行的資料，Undo 需要把整行的資料插入，即 DELETE 操作產生的 Undo(回滾段) 資料最多，大批量刪除可以考慮 Truncate 或者分批刪除。

    AUM的主要初始化引數
        UNDO_MANAGEMENT：如果是 AUTO 或者是 NULL，代表啟用了自動 Undo 管理；如果是 MANUAL，代表採用手動管理 Undo；預設值為 AUTO
        UNDO_TABLESPACE：可選的值，只在自動 Undo 管理模式下有效，指定 Undo 表空間的名字

    Undo Retention Period(Undo資料過期時間)
        當事務提交後，Undo 資料將不需要回滾或者事務恢復。然而為了資料庫一致性讀，比如一些時間長的查詢可能還是需要這些舊的 Undo 資料來獲取 Block 的前映象。此外，Flashback 特性也將依靠這些可用的舊的 Undo 資料。因此，儘可能長的儲存這些舊的 Undo 資料將是非常有用的。

        當啟用 AUM 後，就會涉及到 Undo Retention period(撤銷資料過期時間)，它是儲存 Undo 資訊的最小時間。Old Undo 資訊(已經Committed)如果超過了這個Retention Period，那麼其就會被標記為Expiered，對應的空間也可以被其他的事務重寫。Oracle 會根據 Undo 表空間的大小和系統的活動情況自動調整 Undo Retention Period 的時間。我們也可以手動在初始化引數裡修改 UNDO_RETENTION 引數，該引數單位是秒。

        注意一點，自動調整 Undo Retention 不適用於 LOBs。因為 LOBs 的 Undo 資訊儲存在其自己的 Segments 裡，而不是在 Undo 表空間裡。

        對於自動擴充套件的 Undo 表空間，Oracle 會根據 Undo_retention 的值作為保留 Undo 資料的最少時間。如果表空間不足時，不會去重寫沒有過期的 Undo 資料，而是會自動擴充套件 Undo 表空間。如果 Undo 表空間不足且無法自動擴充套件時，Undo_retention 設定也將被忽略，因為為了滿足系統事務需求，一些狀態為 unexpired 的 Undo 資料也有可能被置換重用。

        對於固定大小的 Undo 表空間，Undo_retention 設定的值將被忽略，Oracle 會根據 Undo 表空間大小及 v$Undostat 統計資訊自動計算和調整 Undo Retention Period 為最佳的值，以最大可能的保留 Undo 資料。管理最理想的 Undo 和計算最佳的 Retention 值，不是根據 100% 的 Undo 表空間來計算，而是根據 Undo 表空間的 85% 來計算或者設定的空間警告值來計算，預設情況下空間警告值是 85%。
 
        當一個正在執行的事務需要更多 Undo 空間，而 Undo 表空間不足且無法擴充套件時，Oracle 優先會重用當前回滾段下狀態為 expired 的 Undo extents，如果沒有就去其他回滾段下狀態為 expired Undo extents，如果沒有再回到當前回滾段中查詢狀態為 unexpired 的 Undo extents，如果還沒有再去查詢其他回滾段中狀態為 unexpired 的 Undo extents，如果還沒有就報空間不足的錯誤訊息。
 
        因此，如果 Undo_retention 為 900 秒（預設值），並不一定意味著 Undo 資料在 Undo 表空間中儲存 900 秒，因為如果空間不足就會去覆蓋不管是否已經 expired 的 已提交事務的Undo 資料。 此外，Undo_retention 時間到了之後，並不一定意味這些 Undo 資料在 Undo 表空間中消失，它只是並標示為 expired，只要沒有被其他事務的 Undo 資料覆蓋，它會依然存在。

        Undo 表空間太小，將會出現的兩類錯誤: DML 操作失敗，因為沒有足夠大的空間來容納新事務的 Undo 資料；Long-running 查詢語句查詢失敗並且返回 "snnapshot too old" 的錯誤提示，因為沒有足夠的 Undo 資料來保證一致性讀

        設定 UNDO_RETENTION 的方法

-- 通過在初始化引數檔案中設定
UNDO_RETENTION = 1800

-- 通過 SQL 語句設定
SQL> ALTER SYSTEM SET UNDO_RETENTION = 2400;

        在 V$UNDOSTAT 檢視中的 TUNED_UNDORETENTION 欄位可以檢視 Retention Period 變化的歷史記錄。V$UNDOSTAT 這個檢視包含最近四天每隔10分鐘自動採集的資料。TUNED_UNDORETENTION 的單位為秒。

SELECT 
    TO_CHAR(BEGIN_TIME, 'DD-MON-RR HH24:MI') BEGIN_TIME, TO_CHAR(END_TIME, 'DD-MON-RRHH24:MI') END_TIME, TUNED_UNDORETENTION 
FROM 
    V$UNDOSTAT 
ORDER BY
    END_TIME;

    Retention Guarantee(保證過期時間有效)
        為了保證 Long-running 的查詢語句以及 Flashback 操作能夠成功執行，可以啟用 Retention Guarantee。 如果啟用了 Retention Guarantee，指定的 UNDO_RETENTION(最小的 Undo 資料過期的時間) 將會得到保證，如果 Undo 表空間大小不足，資料庫寧可返回錯誤也不會去覆蓋 Undo 表空間中沒有過期的 Undo 資料。 如果沒有啟用 Retention Guarantee，當 Undo 表空間大小不足時資料庫就會去覆蓋即使沒有過期的 Undo 資料，因而間接就縮短了 UNDO_RETENTION 資料過期時間的大小。

        資料庫預設選項為不啟用 Retention Guarantee。注意，啟用 Retention Guarantee 可能導致多個 DML 操作失敗。可以在 CREATE DATABASE 或者 CREATE UNDO TABLESPACE 語句中增加 Retention Grarantee 語句指定啟用 Retention Guarantee，也可以使用 ALTER TABLESPACE 中指定啟用還是禁用

        設定Retention Guarantee的方法

-- 啟用 Retention Guarantee
SQL> ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;

-- 禁用 Retention Guarantee
SQL> ALTER TABLESPACE UNDOTBS1 RETENTION NOGUARANTEE;

        在 DBA_TABLESPACES 中 RETENTION 欄位可以查詢到 Undo 表空間 Retention Guarantee 的設定，RETENTION 取值為 GUARANTEE / NOGUARANTEE / NOT APPLY，其中如果不是 Undo 表空間的表空間都是 NOT APPLY。

SQL> SELECT TABLESPACE_NAME，RETENTION FROM DBA_TABLESPACES;

四、Undo相關操作
    建立 Undo 表空間
        如果在建立資料庫期間，將Undo_management引數設定為auto，當例項在將在啟動時自動建立一個自動擴充套件的名稱為 UNDOTBS1 的 Undo 表空間；可以建立多個 Undo 表空間，但是在同一時間內只有一個表空間是 Active 狀態的

        建立 Undo 表空間有兩種方式:

-- 使用 CREATE DATABASE 語句的子句
SQL> CREATE DATABASE MyOrclDb
    ……
UNDO TABLESPACE UNDOTBS2 DATAFILE '/usr/oracle/oradata/MyOrclDb/UNDOTBS201.DBF' SIZE 10M REUSE AUTOEXTEND ON;

-- 使用 CREATE UNDO TABLESPACE 語句
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/usr/oracle/oradata/MyOrclDb/UNDOTBS201.DBF' SIZE 10M REUSE AUTOEXTEND ON;

    修改 Undo 表空間

-- 增加資料檔案
SQL> ALTER TABLESPACE UNDOTBS2 ADD DATAFILE '/usr/oracle/oradata/MyOrclDb/UNDOTBS202.DBF' SIZE 10M;

-- 修改資料檔案大小
SQL> ALTER DATABASE DATAFILE '/usr/oracle/oradata/MyOrclDb/UNDOTBS201.DBF' RESIZE 30M;

-- 重新命名資料檔案
SQL> ALTER TABLESPACE UNDOTBS2 RENAME DATAFILE '/usr/oracle/oradata/MyOrclDb/UNDOTBS201.DBF' TO '/usr/oracle/oradata/MyOrclDb/UNDOTBS204.DBF';

-- 使資料檔案ONLINE或者OFFLINE
SQL> ALTER TABLESPACE UNDOTBS2 ONLINE|OFFLINE;

-- 開始或者結束一個聯機備份
SQL> ALTER TABLESPACE UNDOTBS2 BEGIN|END BACKUP;

    刪除Undo 表空間
        Undo 表空間只能在當前沒有被例項使用的情況下才能被刪除。如果Undo表空間正在使用(尚有回滾段處於 Online 狀態)，那麼drop表空間命令將失敗。強制刪除資料檔案，有引發可能 ora-01555 的錯誤

SQL> DROP TABLESPACE UNDOTBS2;

    切換Undo 表空間
因為 Undo_tablespace 初始化引數是一個動態引數，可以隨時指定一個新的 Undo 表空間以實現切換

SQL>  ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;

        切換 Undo 表空間並不影響使用者事務的執行，當切換操作成功後，Switch 操作之後所有事務產生的 Undo 資料將會儲存在新的 Undo 表空間
        切換表空間操作並不會等待舊的 Undo 空間上所有的事務都提交。如果在舊的 Undo 表空間上還有事務在執行，那麼舊的 Undo 表空間狀態變為 Pending Offline。 在這個模式下，已經存在的事務繼續執行，但是產生的 Undo 資料不能儲存在這個 Undo 表空間中。
        即使切換操作執行完成，舊的 Undo 表空間(狀態為 Pending Offline) 不能被其他例項使用也不能刪除。 最後，當舊的 Undo 表空間上所有的事務都提交後，它的狀態從 Pending Offline 變為 Offline。 這時，舊的 Undo 表空間可以被其他例項使用(RAC環境)

五、Undo相關資料字典
    V$UNDOSTAT
        顯示最近一段時間內關於 Undo 表空間使用情況的統計資訊，可以用這個 View 來優化系統，每隔 10 分鐘自動增加一條資料，總共只儲存四天的資料，如果要查詢超過四天的資料就要使用 DBA_HIST_UNDOSTAT，這個 view 僅對於自動 Undo 管理模式有效

SELECT 
    TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME, UNDOTSN，UNDOBLKS，TXNCOUNT，MAXCONCURRENCY，MAXQUERYLEN，MAXQUERYID，
    UNXPSTEALCNT，UNXPBLKRELCNT，UNXPBLKREUCNT，EXPSTEALCNT，EXPBLKRELCNT，EXPBLKREUCNT，SSOLDERRCNT，NOSPACEERRCNT，TUNED_UNDORETENTION 
FROM 
    V$UNDOSTAT 
ORDER BY 
    END_TIME;

        幾個重要的欄位說明：

           BEGIN_TIME            DATE          -- 時間間隔開始時間
           UNDOTSN               NUMBER        -- 最近活動的 Undo 表空間的 Id  
           UNDOBLKS              NUMBER        -- 當前時間段內消耗的 Undo Blocks 總數 
           TXNCOUNT              NUMBER        -- 當前時間段內執行的事務總數          
           MAXCONCURRENCY        NUMBER        -- 當前時間段內並行執行的最大事務數
           MAXQUERYLEN           NUMBER        -- 當前時間段內執行單個查詢花費的最長時間，單位為秒 
           MAXQUERYID            NUMBER        -- 當前時間段內執行單個查詢花費的最長時間的 Sql id  
           UNXPSTEALCNT          NUMBER        -- 從其他事務嘗試盜用未過期 Undo 回滾段的次數  
           UNXPBLKRELCNT         NUMBER        -- 從其他事務已經盜用未過期 Undo 回滾段的數量 
           UNXPBLKREUCNT         NUMBER        -- 未過期的 Undo 回滾段重用的數量 
           EXPSTEALCNT           NUMBER        -- 嘗試從其他 Undo 段盜用已經過期回滾段的次數 
           EXPBLKRELCNT          NUMBER        -- 從其他 Undo 段盜用過期回滾段的數量 
           EXPBLKREUCNT          NUMBER        -- 已經過期回滾段重用的數量 
           SSOLDERRCNT           NUMBER        -- 返回 ora-01555 錯誤的次數，如果大於 0，說明 UNDO_RETENTION 需要增加大小 
           NOSPACEERRCNT         NUMBER        -- 空間不足申請新的 Undo 表空間的次數 
           TUNED_UNDORETENTION   NUMBER        -- 已提交資料的Undo 資料過期時間，即Retention Period 變化的歷史，單位為秒

    DBA_HIST_UNDOSTAT
        顯示所有的 V$UNDOSTAT 的歷史記錄。

    DBA_UNDO_EXTENTS
        顯示資料庫所有 UNDO 表空間的回滾段情況，其中 STATUS 的取值為 EXPIRED / UNEXPIRED / ACTIVE：
            ACTIVE:    活動狀態，說明當前這個回滾段被某個事務正在使用，不會被其他事務使用；
            UNEXPIRED: 沒有過期，在UNDO_RETENTION時間內，當空間不足時有可能被其他事務使用；
            EXPIRED:   已經過期，隨時可以被其他事務使用。

    V$ROLLNAME
        只有兩個欄位(USN，NAME)，就是一個回滾段 ID 和 NAME，通常和 V$ROLLSTAT 一起使用。

    V$ROLLSTAT
        統計回滾段表的使用情況。XACTS 表示當前回滾段上存在的活動事務的數量，STATUS 的取值為 ONLINE / PENDING OFFLINE / OFFLINE / FULL。

SELECT 
    A.USN, A.NAME, B.RSSIZE, B.WRITES, B.XACTS, B.WAITS, B.EXTENDS, B.STATUS
FROM 
    V$ROLLNAME A, V$ROLLSTAT B
WHERE  
    A.USN = B.USN;

    V$DBA_ROLLBACK_SEGS
        顯示所有回滾段(包括 SYS 和 PUBLIC)的空間分配情況及當前狀態。其中 STATUS 的取值為 OFFLINE / ONLINE / NEEDS RECOVERY / PARTLY AVAILABLE / UNDEFINED。

SELECT 
    OWNER，SEGMENT_NAME，TABLESPACE_NAME，INITIAL_EXTENT，NEXT_EXTENTS，STATUS 
FROM 
    DBA_ROLLBACK_SEGS;

六、Undo 表空間管理
    檢視當前 Undo 表空間配置

SQL> show parameter undo

NAME                            TYPE        VALUE
------------------------------- ----------- -----------
_undo_autotune                  boolean     FALSE
undo_management                 string      AUTO
undo_retention                  integer     28800
undo_tablespace                 string      UNDOTBS2

    檢視當前 Undo 表空間磁碟大小(佔用磁碟的大小)

SQL> SELECT SUM(BYTES)/1024/1024/1024 "UNDO_SIZE(G)" FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='UNDOTBS2';

UNDO_SIZE(G)
------------
  91.9999847

    檢視當前 Undo 表空間使用情況

-- 統計Undo表空間使用的總大小
SQL> SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 "USED_SIZE(G)" FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='UNDOTBS2' GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME                USED_SIZE(G)
------------------------------ ------------
UNDOTBS2                         1.84082031

-- 統計回滾段各狀態下的總大小
SQL> SELECT TABLESPACE_NAME，STATUS，SUM(BYTES)/1024/1024/1024 "USED_SIZE(G)" FROM DBA_UNDO_EXTENTS WHERE TABLESPACE_NAME='UNDOTBS2' GROUP BY TABLESPACE_NAME，STATUS;

TABLESPACE_NAME                STATUS    USED_SIZE(G)
------------------------------ --------- ------------
UNDOTBS2                       EXPIRED     1.08789063
UNDOTBS2                       UNEXPIRED   .752929688

    檢視當前誰佔用了 Undo 表空間

SELECT 
    R.NAME "TABLESPACE", S.SID, S.SERIAL# "SERIAL", S.USERNAME "USERNAME",
    RSSIZE / 1024 / 1024 / 1024 "RSSIZE(G)", HWMSIZE / 1024 / 1024 / 1024 "HWMSIZE(G)",
    S.STATUS, S.SQL_HASH_VALUE, S.SQL_ADDRESS, S.MACHINE, S.MODULE,
    SUBSTR(S.PROGRAM,1,15) "PROGRAM", R.USN, T.START_TIME, SHRINKS, XACTS
FROM 
    SYS.V_$SESSION S, SYS.V_$TRANSACTION T, SYS.V_$ROLLNAME R, V$ROLLSTAT RS
WHERE 
    T.ADDR = S.TADDR AND T.XIDUSN = R.USN AND R.USN = RS.USN
ORDER BY 
    S.SID DESC;