undo 表空間使用案例分析（使用率100%，enq：US-contention,釋放undo）

 Undo參數

SQL> show parameter undo

NAME                                         TYPE                   VALUE

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

undo_management            string AUTO

undo_retention                     integer 900

undo_tablespace                 string UNDOTBS1

UNDO_MANAGEMENT：該初始化參數用于指定UNDO資料的管理方式。如果要使用自動管理模式，必須設定該參數為AUTO，如果使用手工管理模式，必須設定該參數為MANUAL，使用自動管理模式時，Oracle使用UNDO表空間管理undo管理，使用手工管理模式時，Oracle會使用復原段管理undo資料，需要注意，使用自動管理模式時，如果沒有配置初始化參數UNDO_TABLESPACE。Oracle會自動選擇第一個可用的UNDO表空間存放UNDO資料，如果沒有可用的UNDO表空間，Oracle會使用SYSTEM復原段存放UNDO記錄，并在ALTER檔案中記載警告。

UNDO_TABLESPACE：該初始化參數用于指定例程所要使用的UNDO表空間，使用自動UNDO管理模式時，通過配置該參數可以指定例程所要使用UNDO表空間。在RAC(Real

Application Cluster)結構中，因為一個UNDO表空間不能由多個例程同時使用，所有必須為每個例程配置一個獨立的UNDO表空間。

UNDO_RETENTION：該初始化參數用于控制UNDO資料的最大保留時間，其預設值為900秒，從9i開始，通過配置該初始化參數，可以指定undo資料的保留時間，進而确定倒叙查詢特征(Flashback

Query)可以檢視到的最早時間點

UNDO_POOL：這個參數是用來限定每個使用者可以使用的撤銷表空間的配額的，他的預設值是UNLIMITED

，但是你想想，如果一個事務發生了錯誤，占用了幾乎全部的undo表空間，這個執行個體就這麼一個表空間，你讓别的會話的事務情何以堪啊，是以使用UNDO_POOL參數對每個事務進行限制。

相關視圖

使用者可以檢視v$undostat視圖可以監視撤銷表空間的使用情況，他能顯示與撤銷表空間和相關事務有關的統計資訊（如目前執行個體中撤銷表空間的使用量），這樣就可以通過配置v$undostat來提高效率。

V$UNDOSTAT 可以顯示撤銷空間運作的曆史統計資訊。使用者可以查詢撤銷空間使用率（undo consumption rate），事務并發性（transaction concurrency），執行個體中最長的查詢的運作時間等統計資訊。通過這個視圖，使用者可以更好地估計在目前工作負荷下系統所需的撤銷空間（undo

space）容量。

使用者可以使用V$transaction 和 V$rollstat視圖監控事務和撤銷空間的資訊，對于自動撤銷管理（automatic undo management）模式，V$ROLLSTAT視圖能夠展現自動撤銷管理所使用的各個撤銷段（undo segment）的情況。

Oracle 10g 資料庫自動地調整用于控制撤銷資訊儲存周期（undo retention period）的參數。撤銷資訊儲存周期是指，撤銷表空間中舊的撤銷資訊（即已送出事務的撤銷資訊）在被覆寫之前至少需要被儲存的時間。資料庫會收集撤銷資訊的使用情況，并根據統計結果及撤銷表空間（undo tablespace）的大小對撤銷資訊儲存周期進行調整。當資料庫處于自動撤銷管理（automatic

undo management）模式下，其撤銷資訊儲存周期的調整規則如下：

對于存儲參數為 AUTOEXTEND 的撤銷表空間，如果空間情況允許，資料庫将撤銷資訊儲存周期設為比系統中最長的查詢時間稍長。此外，如果空間情況允許，系統設定的撤銷資訊儲存周期不會低于 UNDO_RETENTION 初始化參數。

對于固定容量的撤銷表空間，資料庫将撤銷資訊儲存周期設為撤銷表空間所支援的最大值。這意味着資料庫可以使用接近撤銷表空間極限的容量為使用者提供最長的撤銷資訊儲存周期。The UNDO_RETENTION initialization parameter is ignored unless retention guarantee

is enabled.

撤銷資訊儲存周期的自動調整不适用于 LOB 資料。LOB 列的撤銷資訊儲存周期由UNDO_RETENTION 參數決定。

當一個固定容量的撤銷表空間（undo tablespace）與一個自動擴充的撤銷表空間（存儲參數為 AUTOEXTEND）容量相同時，前者使用的撤銷資訊儲存周期（undo retention period）調整方法通常能夠提供更長的儲存時間。這使回閃（flashback）操作可以追溯的更遠，也使運作時間長的查詢有更多的撤銷資料（undo

data ）可用。

問題描述：

近幾天發現Oracle10.2.0.4資料庫undo tablespace表空間使用率很高（最高時達到100%），報警系統頻繁報障undo tablespace表空間使用率過高。

問題分析

2.1 業務系統影響

undo tablespace表空間使用率達到100%時，沒有業務系統使用者反映系統出現表空間不能擴充的報錯（ORA-30036），但曾經出現過快照過舊報錯（ORA-01555）。

ID: 1h54gg6shbrkh），語句執行時間都很長（在20萬秒以上），最長執行時間達到355659 sec。

Tue May 18 10:51:27 2010

ORA-01555 caused by SQL statement below (SQL ID: 1h54gg6shbrkh, Query Duration=349634 sec, SCN: 0x0a1b.a93246fa):

select decode(temp_kp.mc,null,temp_hdhs.mc,temp_kp.mc) as mc,

decode(temp_kp.jzyje,null, 0,temp_kp.jzyje) as jzyje,

decode(temp_kp.jzyfs,null, 0,temp_kp.jzyfs) as jzyfs,

decode(temp_kp.bdcje,null, 0,temp_kp.bdcje) as bdcje,

decode(temp_kp.bdcfs,null, 0,temp_kp.bdcfs) as bdcfs,

decode(temp_kp.ptje,null, 0,temp_kp.ptje) as ptje,

decode(temp_kp.ptfs,null, 0,temp_kp.ptfs) as ptfs,

decode(temp_kp.dkje,null, 0,temp_kp.dkje) as dkje,

decode(temp_kp.dkfs,null, 0,temp_kp.dkfs) as dkfs,

decode(temp_kp.bgdlyje,null, 0,temp_kp.bgdlyje) as bgdlyje,

decode(temp_kp.bgdlyfs,null, 0,temp_kp.bgdlyfs) as bgdlyfs,

decode(temp_kp.gjhyyje,null, 0,temp_kp.gjhyyje) as gjhyyje,

decode(temp_kp.gjhyyfs,null, 0,temp_kp.gjhyyfs) as gjhyyfs,

decode(temp_kp.gjhyycpdlje,null, 0,temp_kp.gjhyycpdlje) as gjhyycpdlje,

decode(temp_kp.gjhyycpdlfs,null, 0,temp_kp.gjhyycpdlfs) as gjhyycpdlfs,

decode(temp_kp.gjhwysje,null, 0,temp_kp.gjhwysje) as

2.4 Undo tablespace空間使用情況

select file_name,autoextensible,bytes/1048576 MB,increment_by,maxbytes/1048576 Max_MB

from dba_data_files

where tablespace_name = 'UNDOTBS1';

FILE_NAME AUTOEXTENSIBLE MB INCREMENT_BY MAX_MB

/dev/rinstdbUNDO_lv NO 1023 0 0

/dev/rinstdbUNDO2_lv NO 3071 0 0

/dev/rinstdbUNDO3_lv NO 2047 0 0

select tablespace_name,status, sum(bytes)/1024/1024 MB, count(*) from dba_undo_extents

group by tablespace_name,status

TABLESPACE_NAME STATUS MB COUNT(*)

TS_UNDO EXPIRED 2.375 38

UNDOTBS1 UNEXPIRED 4344.75 2687

UNDOTBS1 EXPIRED 1796.0625 3146

UNDOTBS1表空間總空間是6141MB，已配置設定空間達到6140.8125MB，其中UNEXPIRED EXTENT空間占4344.75MB。

2.5 V$UNDOSTAT和DBA_HIST_UNDOSTAT

查詢V$UNDOSTAT視圖和DBA_HIST_UNDOSTAT資料字典可以看到：

1. 從2010-05-19 PM 10:53:38到2010-5-22 04:53:38 PM，MAXQUERYLEN值都保持在20萬秒以上，MAXQUERYLEN最大值是285073s（2010-5-22 04:53:38 PM），

2. TUNED_UNDORETENTION保持在20萬秒以上，TUNED_UNDORETENTION最大值是345742s （2010-5-26 09:13:38 AM），TUNED_UNDORETENTION目前值是255484（2010-5-27 02:43:38 PM）

3. SSOLDERRCNT累計值大于0

4. NOSPACEERRCNT一直都是0

從以上資料可以了解到，從2010-05-19 PM 10:53:38到2010-5-22 04:53:38 PM期間出現了多次異常超長時間查詢，這些長時間查詢導緻TUNED_UNDORETENTION參數值變大，這些查詢曾經導緻ORA-01555報錯，但沒有導緻DML語句出錯。

UNDO Retention特性，即oracle會根據實際情況自動調整undo_retention值，以盡可能避免ORA-01555的報錯。v$undostat視圖中幾個主要列的解釋：

MAXQUERYLEN Identifies the length of the longest query (in seconds) executed in the instance during the period. You can

use this statistic to estimate the proper setting of the UNDO_RETENTION initialization parameter. The length of a query is measured from the cursor open time to the last fetch/execute time of the cursor. Only the length of those cursors that have been fetched/executed

during the period are reflected in the view.

SSOLDERRCNT

Identifies the number of times the error ORA-01555 occurred.

NOSPACEERRCNT

Identifies the number of times space was requested in the undo tablespace and there was no free space available. That is, all of the space in the undo tablespace was in use by active transactions.

The corrective action is to add more space to the undo tablespace.

TUNED_UNDORETENTIONSystem tuned value indicating the period for which undo is being retained

總結及建議

3.1 Undo tablespace使用率100%原因

從oracle10g開始，oracle提供了Automatic UNDO Retention特性，即oracle會根據實際情況自動調整undo_retention值，從2010.5.18至2010.5.22期間産生了多次超長時間查詢，這段期間MAXQUERYLEN值保持在20萬秒以上，為了盡可能避免ORA-01555的報錯，盡可能保留更多的UNEXPIRED EXTENT，ORACLE會首先選擇在undo

tablespace中擴充rollback segment，是以undo tablespace使用率會不斷上升，最終導緻了undo tablespace空間使用率達到100%。

3.2 Undo tablespace空間監控方法

undo tablespace空間使用率達到100%，隻是意味着所有的空間都已經配置設定給rollback segment，但并不一定會影響到業務的select和insert、delete、update操作，因為rollback segment中的空間是可以重用的。在oracle10g，不能通過監控dba_free_space資料庫字典的方法去獲得undo

tablespace的使用情況，可以考慮通過以下方法進行監控：

1. 監控v$undostat視圖，主要包括MAXQUERYLEN、SSOLDERRCNT、NOSPACEERRCNT列值，如：MAXQUERYLEN大于36000秒則報警、SSOLDERRCNT大于0次（或一定次數）則報警、NOSPACEERRCNT大于0次則嚴重警告等；

2. 監控平均每一秒鐘所需的最少復原表空間容量，如果超過一定範圍則報警

SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"

FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),

(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),

(select block_size as DBS from dba_tablespaces where tablespace_name=

(select value from v$parameter where name = 'undo_tablespace'));

     Bytes

----------

 413215736

那麼一個undo表空間的理論大小為：413215736*900

對于oracle10g版本，使用如下指令強制undo儲存時間

alter tablespace undotbs1 retention guarantee;

執行個體二：

這幾天遇到一個錯誤，我也不知道算不算錯誤吧，因為沒有報錯，隻是在那突然的短短2分鐘内表的操作突然降低了，導緻了該軟體重新啟動。檢視alert日志沒有報錯，而是在ASH裡找到了TOP SQL框有一個這樣的錯誤，使我百思不得其解。檢視該SQL語句隻是簡單的一個更新，并不需要優化。最後再百度、google的幫助下終于找到了錯誤原因，原來與UNDO的設定有關。首先來介紹下undo_retention參數，該參數是撤銷段的最短保留時間，而在預設情況下Oracle将根據表空間的大小和曆史使用情況，自動調整undo資訊儲存時間，同時忽略

undo_retention的值,除非undo_retention的guarantee 特性被啟用.也就是執行以下指令：

ALTER TABLESPACE UNDOTBS RETENTION GUARANTEE;

在自動調整啟用的情況下，實際的撤銷資訊最短保留時間可以通過查詢V$UNDOSTAT視圖上的TUNED_UNDORETENTION列獲得。往往最短儲存時間遠遠大于設定的UNDO_RETENTION。UNDO自動優化功能能夠最大限度的使用undo表空間,滿足大部分的sql執行,但是也帶來一個問題：很多事務執行完畢之後，發現UNDO表空間會在很長時間都一直保持着使用率是接近100%的狀态，active

狀态的很少。這種接近狀态還無法手工的收縮，甚至于重新開機資料庫執行個體也無法緩解，而此時常常會收到undo表空間的監控報警。

再來說說enq: US - contention問題

這是oracle10g中開始出現的bug(在11.1.0.7中仍有這個BUG)，當因為系統activity增加或者降低的時候，oracle SMON程序會自動ONLINE或者OFFLINE rollback segments。這樣導緻某些與undo segments相關的latch或者enqueue被hold住太長時間，導緻系統很多活躍session都開始等待enq:

US - contention。可以同時使用以下解決方法:

1. 設定event讓SMON不自動OFFLINE復原段。

alter system set events '10511 trace name context forever, level 1';

2. 設定參數_rollback_segment_count ：表示有多少rollback segment要處于online的狀态；可以将該數值設定為資料庫最繁忙的時候的復原段數目。

alter system set "_rollback_segment_count"=;

這裡以‘_’開頭的為隐藏參數，通過show parameter 是看不到的，可以通過以下語句：

select a.ksppinm name, b.ksppstvl value, a.ksppdesc description

from x$ksppi a, x$ksppcv b

where a.indx = b.indx

and a.ksppinm like '%_rollback_segment_count%';

3.  undo autotune bug多多。最好disable。

alter system set "_undo_autotune"= false;

這種方法就是關閉了UNDO的自動調整功能，同僚也能解決掉UNDO表空間會在很長時間都一直保持着使用率是接近100%的問題。

4.  有一個patch: A fix to bug 7291739 is to set a new hidden parameter, _highthreshold_undoretention to set a high threshold for undo retention completely distinct from maxquerylen.

alter system set "_highthreshold_undoretention"=;

案例三：

一.概述:

使用IMPDP工具導入大表(166G)資料時,報undo表空間不能擴充,導入工作失敗.手工停止了impdp後,undo表空間存在無法自動釋放的故障.本文主要描述如何通過重建undo表空間來手工釋放undo表空間.

資料庫環境的描述:

OS: AIX 6.1+HACMP 5.3

DB: ORACLE 10.2.0.5 RAC

二.問題的描述

impdp 導入資料時,報ora-30036錯誤

$impdp user/passwd directory=imp_dir dumpfile=big_table_%U.dmp parallel=10 logfile=imp_big_table.log

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

檢查資料庫的歸檔日志檔案,也發現了這個報警

more /oracle/admin/*/bdump/alert_{SID}.log|grep undo

強行終止了impdp的操作,impdp的程序在操作中已不見

# ps -ef|grep impdp

root 8650752 8061396 0 15:02:08 pts/1 0:00 grep impdp

解除安裝資料檔案所在的檔案系統時,發現無法下載下傳

SQL>select * from dba_directories

OWNER DIRECTORY_NAME DIRECTORY_PATH

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

SYS imp_dir /imp_data

#umount /imp_data

umount: 0506-349 Cannot unmount /dev/imp_data: The requested resource is busy.

檢查UNDO 表空間的使用情況,如下,存在一個兩個比較大的EXPIRED的undo segment.

SQL> select owner,segment_name,sum(bytes/1024/1024) from dba_undo_extents group by owner,segment_name order by 3

OWN SEGMENT_NAME SUM(BYTES/1024/1024)

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

.........

SYS _SYSSMU12$ 26.125

SYS _SYSSMU9$ 27.125

SYS _SYSSMU7$ 45.125

SYS _SYSSMU18$ 72.125

SYS _SYSSMU20$ 72.125

SYS _SYSSMU10$ 72.125

SYS _SYSSMU1$ 83.125

SYS _SYSSMU6$ 3563.1875

SYS _SYSSMU8$ 9524.4375

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

TABLESPACE_NAME STATUS SUM(BYTES/1024/1024)

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

UNDOTBS1 ACTIVE 47.0625

UNDOTBS2 EXPIRED 291.25

UNDOTBS1 UNEXPIRED 285.875

UNDOTBS1 EXPIRED 13063.6875

在metalink上,查找相關資訊,看到有一個bug,說的是：undo表空間不足的時候，不會overwrite expired的undo segment。這個bug是10.2.0.3，9.2.0.8版本上發生，目前資料庫的版本為10.2.0.5，不應該是這個bug引起的。

三.問題的分析

資料庫的環境是10.2.0.5 RAC，UNDO表空間不足發生在node1的undotbs1上，為了不影響資料庫的運作，首先為該表空間增加了空間。

SQL>alter tablespace undotbs1 add datafile '/dev/rora_data_03' size 11518m;

嘗試通過重新啟動資料庫，來釋放undo表空間上的内容，結果失敗，undotbs1仍然存在大量的expried的segment不能釋放（這個資料庫的undo 是自動管理的）

因為umount 導入檔案系統也失敗，考慮是不是因為手工強行停止了impdp的操作，導緻了作業系統中還有相關程序沒有完全停掉。是以采用了重新啟動資料庫伺服器的方式來釋放相關的檔案系統的鎖。

四.問題的解決

重新啟動資料庫伺服器後，嘗試umount 檔案系統(/imp_data),結果成功.

現在剩下的問題是,如何shrink undo segment的問題,總不能讓那麼多的undo表空間就象太空垃圾一樣,存在在資料庫中.

在metalink上找到相關的文檔:How to Shrink the datafile of Undo Tablespace [ID 268870.1]

按照文檔介紹的方法,進行了下面的操作

SQL>create undo tablespace undotbs3 datafile '/dev/rora_data_02' size 11518m extent management local;

SQL>alter system set undo_tablespace='UNDOTBS3' scope=both sid='JLZDH1';

SQL>drop tablespace undotbs1 including contents;

ORA-30013 : undo tablespace undotbs1 is currently in use

在删除undo表空間undotbs1時,報表空間在使用,無法删除的錯誤.

重新啟動資料庫,再次檢視undo表空間的情況

SQL> show parameter undo

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 450

undo_tablespace string UNDOTBS3

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

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

UNDOTBS3 UNEXPIRED 6.25

UNDOTBS1 EXPIRED 7152.1875

UNDOTBS3 EXPIRED 13.0625

再次嘗試删除undotbs1,成功了.

SQL> drop tablespace undotbs1 including contents;

Tablespace dropped.

UNDOTBS3 UNEXPIRED 2.3125

UNDOTBS3 EXPIRED 17

五.總結

impdp/expdp是oracle提供的資料庫資料導入/導出的工具,速度比舊工具imp/exp要快很多,但也存在很多的bug,由于加入了job方式的管理,異常出現的機會比較多.曾經遇到過,手工終止impdp操作,導緻了system表空間被大量占用的問題.本次又遇到undo表空間不能釋放的問題.雖然資料泵有很多問題,但使用了parallel并行處理後,速度提升不是一般的多,是以日常工作中,還是首選資料泵工具.

undo表空間不能釋放時,最好的解決辦法就是:

1.重建立立一個新的undo表空間.

2.設定資料庫的undo表空間為新的undo表空間

3.删除舊的undo表空間及其内容