oracle 釋放過度使用的Undo資料表空間____oracle

故障現象：UNDO資料表空間越來越大,長此下去最終資料因為磁碟空間不足而崩潰；

問題分析：產生問題的原因主要以下兩點：
1. 有較大的事務量讓Oracle Undo自動擴充，產生過度佔用磁碟空間的情況；
2. 有較大事務沒有收縮或者沒有提交所導制；
說    明：本問題在ORACLE系統管理中屬於比較正常的一現象，日常維護多注意對磁碟空間的監控。
備    份： (如果沒有線上事務，可以不做，關閉監聽)
 
$>exp vas/vas file=/opt/oracle/data_1.dmp,/opt/oracle/data_2.dmp log=/opt/oracle/date.log owner=vas rows=y indexes=y compress=n buffer=65536 feedback=100000 volsize=0 filesize=1000M

解決步驟：
1. 啟動SQLPLUS，並用sys登陸到資料庫。

#su - oracle
$>sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Nov 8 13:45:10 2006
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
SQL> conn sys/qq994238@ddptest as sysdba;
Connected.

2. 尋找資料庫的UNDO資料表空間名

#cat $ORACLE_HOME/dbs/initddptest.ora
……
*.undo_management=’AUTO’
*.undo_retention=10800
*.undo_tablespace=’UNDOTBS2’
……

3. 確認UNDO資料表空間；

SQL> select name from v$tablespace;

NAME
------------------------------
CWMLITE
DRSYS
EXAMPLE
INDX
ODM
SYSTEM
TOOLS
USERS
XDB
TEMP
TESTLIB
UNDOTBS2

4. 檢查資料庫UNDO資料表空間佔用空間情況以及資料檔案存放位置；

SQL>select file_name,bytes/1024/1024 from dba_data_files
  2  where tablespace_name like 'UNDOTBS2';
    
5. 查看復原段的使用方式，哪個使用者正在使用復原段的資源，如果有使用者最好更換時間（特別是生產環境）。

SQL> select s.username, u.name from v$transaction t,v$rollstat r,
  2  v$rollname u,v$session s where s.taddr=t.addr and
  3  t.xidusn=r.usn and r.usn=u.usn order by s.username;

6. 檢查UNDO Segment狀態；

SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
  2  from v$rollstat order by rssize;

7. 建立新的UNDO資料表空間，並設定自動擴充參數；

SQL> create undo tablespace undotbs1 datafile '/oradata/oradata/ddptest/UNDOTBS1.dbf' size 1000m reuse autoextend on next 800m maxsize unlimited;

Tablespace created.

8. 動態更改spfile設定檔；

SQL> alter system set undo_tablespace=undotbs1 scope=both;

System altered.

9. 等待原UNDO資料表空間所有UNDO SEGMENT OFFLINE；

SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
  2 from v$rollstat order by rssize;

10. 再執行看UNDO資料表空間所有UNDO SEGMENT ONLINE；

SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
  2 from v$rollstat order by rssize;

11. 刪除原有的UNDO資料表空間；

SQL> drop tablespace undotbs2 including contents;

Tablespace dropped.

12. 確認刪除是否成功；

SQL> select name from v$tablespace;

NAME
------------------------------
CWMLITE
DRSYS
EXAMPLE
INDX
ODM
SYSTEM
TOOLS
USERS
XDB
TEMP
TESTLIB
UNDOTBS1

12 rows selected.

13. 在做此步驟前，請到$ORACLE_HOME/dbs/init$ORACLE_SID.ora如下內容是否發生變更：

#cat $ORACLE_HOME/dbs/initddptest.ora
……
*.undo_management=’AUTO’
*.undo_retention=10800
*.undo_tablespace=’UNDOTBS2’
……

 如果沒有發生變更請執行如下語句：

SQL> create pfile from spfile;

File created.

14. 冊除原UNDO資料表空間的資料檔案，其檔案名稱為步驟中執行的結果。

#rm $ORACLE_BASE/oradata/$ORACLE_SID/undotbs02.dbf

 

附：UNDO資料表空間介紹

UNDO資料表空間用於存放UNDO資料,當執行DML操作(INSERT,UPDATE和DELETE)時,oracle會將這些操作的舊資料寫入到UNDO段,在oracle9i之前,管理UNDO資料時使用(Rollback Segment)完成的.從oracle9i開始,管理UNDO資料不僅可以使用復原段,還可以使用UNDO資料表空間.因為規劃和管理復原段比較複雜,所有oracle database 10g已經完全丟棄用復原段.並且使用UNDO資料表空間來管理UNDO資料.

UNDO資料也稱為復原(ROLLBACK)資料,它用於確保資料的一致性.當執行DML操作時,事務操作前的資料被稱為UNDO記錄.UNDO段用於儲存事務所修改資料的舊值,其中儲存著被修改資料區塊的位置以及修改前資料,

UNDO資料的作用.

1,回退事務

當執行DML操作修改資料時,UNDO資料被存放到UNDO段,而新資料則被存放到資料區段中,如果事務操作存在問題,舊需要回退事務,以取消事務變化.假定使用者A執行了語句UPDATE emp SET sal=1000 WHERE empno=7788後發現,應該修改僱員7963的工資,而不是僱員7788的工資,那麼通過執行ROLLBACK語句可以取消事務變化.當執行ROLLBACK命令時,oracle會將UNDO段的UNDO資料800寫回的資料區段中.

2,讀一致性

使用者檢索資料庫資料時,oracle總是使用使用者只能看到被提交過的資料(讀取提交)或特定時間點的資料(SELECT語句時間點).這樣可以確保資料的一致性.例如,當使用者A執行語句UPDATE emp SET sal=1000 WHERE empno=7788時,UNDO記錄會被存放到復原段中,而新資料則會存放到EMP段中;假定此時該資料尚未提交,並且使用者B執行SELECT sal FROM emp WHERE empno=7788,此時使用者B將取得UNDO資料800,而該資料正是在UNDO記錄中取得的.

3,事務恢複

事務恢複是常式恢複的一部分,它是由oracle server自動完成的.如果在資料庫運行過程中出現常式失敗(如斷電,記憶體故障,後台進程故障等),那麼當重啟oracle server時,後台進程SMON會自動執行常式恢複,執行常式恢複時,oracl會重新做所有未應用的記錄.回退未提交事務.

4,倒敘查詢(FlashBack Query)

倒敘查詢用於取得特定時間點的資料庫資料,它是9i新增加的特性,假定目前時間為上午11:00,某使用者在上午10:00執行UPDATE emp SET sal=3500 WHERE empno=7788語句,修改並提交了事務(僱員原工資為3000),為了取得10:00之前的僱員工資,使用者可以使用倒敘查詢特徵.

使用UNDO參數

1,UNDO_MANAGEMENT

該初始化參數用於指定UNDO資料的管理方式.如果要使用自動管理員模式,必須設定該參數為AUTO,如果使用手工管理員模式,必須設定該參數為MANUAL,使用自動管理員模式時,oracle會使用undo資料表空間管理undo管理,使用手工管理員模式時,oracle會使用復原段管理undo資料,

需要注意,使用自動管理員模式時,如果沒有配置初始化參數UNDO_TABLESPACE,oracle會自動選擇第一個可用的UNDO資料表空間存放UNDO資料,如果沒有可用的UNDO資料表空間,oracle會使用SYSTEM復原段存放UNDO記錄,並在ALTER檔案中記載警告.

2,UNDO_TABLESPACE

該初始化參數用於指定常式所要使用的UNDO資料表空間,使用自動UNDO管理員模式時,通過配置該參數可以指定常式所要使用的UNDO資料表空間.

在RAC(Real Application Cluster)結構中,因為一個UNDO資料表空間不能由多個常式同時使用,所有必須為每個常式配置一個獨立的UNDO資料表空間.

3,UNDO_RETENTION

該初始化參數用於控制UNDO資料的最大保留時間,其預設值為900秒,從9i開始,通過配置該初始化參數,可以指定undo資料的保留時間,從而確定倒敘查詢特徵(Flashback Query)可以查看到的最早時間點.

建立UNDO資料表空間,

UNDO資料表空間專門用於存放UNDO資料,並且在UNDO資料表空間尚不能建立任何資料對象(表,索引,簇)

1,使用CREATE DATABASE命令建立UNDO資料表空間.

當使用CREATE DATABASE命令建立資料庫時,通過指定UNDO TABLESPACE選項,可以建立UNDO資料表空間.樣本如下:

CREATE DATABASE db01

…

UNDO TABLESPACE undotbs_01

DATAFILE ‘/u01/oracle/rbdb1/undo0101.dbf’ SIZE 30M;

注意:UNDO TABLESPACE 子句不是必須的,如果使用自動UNDO管理員模式,並且沒有指定該子句,那麼建立資料庫時會自動產生名為SYS_UNDOTBS的UNDO資料表空間.

2,使用CREATE UNDO TABLESPACE命令建立UNDO資料表空間.

CREATE UNDO TABLESPACE undotbs3

DATAFILE ‘D:demoundotbs3.dbf’ SIZE 10M;

修改UNDO資料表空間,

使用ALTER TABLESPACE命令修改UNDO資料表空間.

當事務用盡了UNDO資料表空間後,使用ALTER TABLESPACE … ADD DATAFILE增加資料檔案

當UNDO資料表空間所在的磁碟填滿是,使用ALTER TABLESPACE … RENAME DATAFIEL 命令移動資料檔案到其他磁碟上.

使用ALTER DATABASE … OFFLINE/ONLINE使資料表空間離線/聯機.

當資料庫處於ARCHIVELOG模式時,使用ALTER TABLESPACE …BEGIN BACKUP/END BACKUP命令備份UNDO資料表空間.

切換UNDO資料表空間.

啟動常式並開啟資料庫後,同一時刻特定常式只能使用一個UNDO資料表空間,切換UNDO資料表空間是指停止常式當前使用的UNDO資料表空間,並啟動其他UNDO資料表空間,下面以啟用undotbs2資料表空間為例,說明切換UNDO資料表空間的方法.

ALTER SYSTEM SET undo_tablespace=undotbs02;

在RAC(Real Application Cluster)機構中,不同常式必須使用獨立的UNDO資料表空間,而不能共用同一個UNDO資料表空間.

刪除UNDO資料表空間.

當前常式正在使用的UNDO資料表空間是不能被刪除的,如果確定要刪除當前常式正在使用的UNDO資料表空間,應首先切換UNDO資料表空間.然後刪除相應的UNDO資料表空間.

DROP TABLESPACE undotbs3;

1,確定當前常式正在使用的UNDO資料表空間.

Show parameter undo_tablespace

2,顯示資料庫的所有UNDO資料表空間.

SELECT tablespace_name FROMdba_tablespaces WHERE contents=’UNDO’;

3,顯示UNDO資料表空間統計資訊.

使用自動UNDO管理員模式時,需要合理地設定UNDO資料表空間的尺寸,為例合理規劃UNDO資料表空間尺寸,應在資料庫啟動並執行高峰階段搜集UNDO資料表空間的統計資訊.最終根據該統計資訊確定UNDO資料表空間的尺寸.通過查詢動態效能檢視V%UNDOSTAT,可以搜集UNDO統計資訊.

SELECT TO_CHAR(BEGIN_TIME,’HH24:MI:SS’) BEGIN_TIME,

TO_CHAR(END_TIME,’HH24:MI:SS’) END_TIME,

UNDOBLKS

FROM V$UNDOSTAT;

BEGIN_TIME用於標識起始統計時間,END_TIME用於標識結束統計時間,UNDOBLKS用於標識UNDO資料所佔用的資料區塊個數.oracle每隔10分鐘產生一行統計資訊.

4,顯示UNDO段統計資訊.

使用自動UNDO管理員模式時,oracle會在UNDO資料表空間上自動建立10個UNDO段,通過查詢動態資訊視圖V$ROLLNAME,可以顯示所有聯機UNDO段的名稱,通過查詢動態效能檢視V$ROLLLISTAT,可以顯示UNDO段的統計資訊.通過在V$ROLLNAME和V$ROLLLISTAT之間執行串連查詢,可以監視特定UNDO段的特定資訊.

SELECT a.name, b.xacts, b.writes, b.extents

FROM v$rollname a, v$rollstat b

WHERE a.usn=b.usn;

Name用於標識UNDO段的名稱,xacts用於標識UNDO段所包含的活動事務個數,

Writes用於標識在undo段上所寫入的位元組數,extents用於標識UNDO段的區個數.

5,顯示活動事務資訊.

當執行DML操作時,oracle會將這些操作的舊資料放到UNDO段中,動態效能檢視v$session用於顯示會話的詳細資料,動態效能檢視v$transaction用於顯示事務的詳細資料,動態效能檢視v$rollname用於顯示聯機UNDO段的名稱.通過在這3個動態效能檢視之間執行串連查詢,可以確定正在執行事務操作的會話,事務所使用的UNDO段,以及事務所佔用的UNDO塊個數.

Col username format a10

Col name format a10

SELECT a.username, b.name, c.used_ublk

FROM v$session a, v$rollname b, v$transaction c

WHERE a.saddr=c.ses_addr AND b.usn=c.xidusn

AND a.username=’SCOTT’;

6,顯示UNDO區資訊

資料字典視圖dba_undo_extents用於顯示UNDO資料表空間所有區的詳細資料.包括UNDO區尺寸和狀態等資訊.

SELECT extend_id, bytes, status FROM dba_undo_extents

WHERE segment_name’_SYSSMU5$’;

其中,extent_id用於標識區編號,bytes用於標識區尺寸,status用於標識區狀態(ACTIVE:表示該區處於活動狀態,EXPIRED:標識該區未用).