故障現象UNDO表空間越來越大長此下去最終數據因為磁盤空間不足而崩潰
問題分析產生問題的原因主要以下兩點
有較大的事務量讓Oracle Undo自動擴展產生過度占用磁盤空間的情況
有較大事務沒有收縮或者沒有提交所導制
說 明本問題在ORACLE系統管理中屬於比較正常的一現象日常維護多注意對磁盤空間的監控
備 份 (如果沒有在線事務可以不做關閉監聽)
$>exp vas/vas file=/opt/oracle/data_dmp/opt/oracle/data_dmp log=/opt/oracle/datelog wner=vas rows=y indexes=y compress=n buffer= feedback= volsize= filesize=M
解決步驟
啟動SQLPLUS並用sys登陸到數據庫
#su oracle
$>sqlplus /nolog
SQL*Plus: Release Production on Wed Nov ::
Copyright (c) Oracle Corporation All rights reserved
SQL> conn sys/qq@ddptest as sysdba;
Connected
查找數據庫的UNDO表空間名
#cat $ORACLE_HOME/dbs/initddptestora
……
*undo_management=AUTO
*undo_retention=
*undo_tablespace=UNDOTBS
……
確認UNDO表空間
SQL> select name from v$tablespace;
NAME
CWMLITE
DRSYS
EXAMPLE
INDX
ODM
SYSTEM
TOOLS
USERS
XDB
TEMP
TESTLIB
UNDOTBS
檢查數據庫UNDO表空間占用空間情況以及數據文件存放位置
SQL>select file_namebytes// from dba_data_files
where tablespace_name like UNDOTBS;
查看回滾段的使用情況哪個用戶正在使用回滾段的資源如果有用戶最好更換時間（特別是生產環境）
SQL> select susername uname from v$transaction tv$rollstat r
v$rollname uv$session s where staddr=taddr and
txidusn=rusn and rusn=uusn order by susername;
檢查UNDO Segment狀態
SQL> select usnxactsrssize///hwmsize///shrinks
from v$rollstat order by rssize;
創建新的UNDO表空間並設置自動擴展參數
SQL> create undo tablespace undotbs datafile /oradata/oradata/ddptest/UNDOTBSdbf size m reuse autoextend on next m maxsize unlimited;
Tablespace created
動態更改spfile配置文件
SQL> alter system set undo_tablespace=undotbs scope=both;
System altered
等待原UNDO表空間所有UNDO SEGMENT OFFLINE
SQL> select usnxactsstatusrssize///hwmsize///shrinks
from v$rollstat order by rssize;
再執行看UNDO表空間所有UNDO SEGMENT ONLINE
SQL> select usnxactsstatusrssize///hwmsize///shrinks
from v$rollstat order by rssize;
刪除原有的UNDO表空間
SQL> drop tablespace undotbs including contents;
Tablespace dropped
確認刪除是否成功
SQL> select name from v$tablespace;
NAME
CWMLITE
DRSYS
EXAMPLE
INDX
ODM
SYSTEM
TOOLS
USERS
XDB
TEMP
TESTLIB
UNDOTBS
rows selected
在做此步驟前請到$ORACLE_HOME/dbs/init$ORACLE_SIDora如下內容是否發生變更
#cat $ORACLE_HOME/dbs/initddptestora
……
*undo_management=AUTO
*undo_retention=
*undo_tablespace=UNDOTBS
……
如果沒有發生變更請執行如下語句
SQL> create pfile from spfile;
File created
冊除原UNDO表空間的數據文件其文件名為步驟中執行的結果
#rm $ORACLE_BASE/oradata/$ORACLE_SID/undotbsdbf
附UNDO表空間介紹
UNDO表空間用於存放UNDO數據當執行DML操作(INSERTUPDATE和DELETE)時oracle會將這些操作的舊數據寫入到UNDO段在oraclei之前管理UNDO數據時使用(Rollback Segment)完成的從oraclei開始管理UNDO數據不僅可以使用回滾段還可以使用UNDO表空間因為規劃和管理回滾段比較復雜所有oracle database g已經完全丟棄用回滾段並且使用UNDO表空間來管理UNDO數據
UNDO數據也稱為回滾(ROLLBACK)數據它用於確保數據的一致性當執行DML操作時事務操作前的數據被稱為UNDO記錄UNDO段用於保存事務所修改數據的舊值其中存儲著被修改數據塊的位置以及修改前數據
UNDO數據的作用
回退事務
當執行DML操作修改數據時UNDO數據被存放到UNDO段而新數據則被存放到數據段中如果事務操作存在問題舊需要回退事務以取消事務變化假定用戶A執行了語句UPDATE emp SET sal= WHERE empno=後發現應該修改雇員的工資而不是雇員的工資那麼通過執行ROLLBACK語句可以取消事務變化當執行ROLLBACK命令時oracle會將UNDO段的UNDO數據寫回的數據段中
讀一致性
用戶檢索數據庫數據時oracle總是使用用戶只能看到被提交過的數據(讀取提交)或特定時間點的數據(SELECT語句時間點)這樣可以確保數據的一致性例如當用戶A執行語句UPDATE emp SET sal= WHERE empno=時UNDO記錄會被存放到回滾段中而新數據則會存放到EMP段中;假定此時該數據尚未提交並且用戶B執行SELECT sal FROM emp WHERE empno=此時用戶B將取得UNDO數據而該數據正是在UNDO記錄中取得的
事務恢復
事務恢復是例程恢復的一部分它是由oracle server自動完成的如果在數據庫運行過程中出現例程失敗(如斷電內存故障後台進程故障等)那麼當重啟oracle server時後台進程SMON會自動執行例程恢復執行例程恢復時oracl會重新做所有未應用的記錄回退未提交事務
倒敘查詢(FlashBack Query)
倒敘查詢用於取得特定時間點的數據庫數據它是i新增加的特性假定當前時間為上午:某用戶在上午:執行UPDATE emp SET sal= WHERE empno=語句修改並提交了事務(雇員原工資為)為了取得:之前的雇員工資用戶可以使用倒敘查詢特征
使用UNDO參數
UNDO_MANAGEMENT
該初始化參數用於指定UNDO數據的管理方式如果要使用自動管理模式必須設置該參數為AUTO如果使用手工管理模式必須設置該參數為MANUAL使用自動管理模式時oracle會使用undo表空間管理undo管理使用手工管理模式時oracle會使用回滾段管理undo數據
需要注意使用自動管理模式時如果沒有配置初始化參數UNDO_TABLESPACEoracle會自動選擇第一個可用的UNDO表空間存放UNDO數據如果沒有可用的UNDO表空間oracle會使用SYSTEM回滾段存放UNDO記錄並在ALTER文件中記載警告
UNDO_TABLESPACE
該初始化參數用於指定例程所要使用的UNDO表空間使用自動UNDO管理模式時通過配置該參數可以指定例程所要使用的UNDO表空間
在RAC(Real Application Cluster)結構中因為一個UNDO表空間不能由多個例程同時使用所有必須為每個例程配置一個獨立的UNDO表空間
UNDO_RETENTION
該初始化參數用於控制UNDO數據的最大保留時間其默認值為秒從i開始通過配置該初始化參數可以指定undo數據的保留時間從而確定倒敘查詢特征(Flashback Query)可以查看到的最早時間點
建立UNDO表空間
UNDO表空間專門用於存放UNDO數據並且在UNDO表空間尚不能建立任何數據對象(表索引簇)
使用CREATE DATABASE命令建立UNDO表空間
當使用CREATE DATABASE命令建立數據庫時通過指定UNDO TABLESPACE選項可以建立UNDO表空間示例如下:
CREATE DATABASE db
…
UNDO TABLESPACE undotbs_
DATAFILE /u/oracle/rbdb/undodbf SIZE M;
注意:UNDO TABLESPACE 子句不是必須的如果使用自動UNDO管理模式並且沒有指定該子句那麼建立數據庫時會自動生成名為SYS_UNDOTBS的UNDO表空間
使用CREATE UNDO TABLESPACE命令建立UNDO表空間
CREATE UNDO TABLESPACE undotbs
DATAFILE D:demoundotbsdbf SIZE M;
修改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表空間下面以啟用undotbs表空間為例說明切換UNDO表空間的方法
ALTER SYSTEM SET undo_tablespace=undotbs;
在RAC(Real Application Cluster)機構中不同例程必須使用獨立的UNDO表空間而不能共用同一個UNDO表空間
刪除UNDO表空間
當前例程正在使用的UNDO表空間是不能被刪除的如果確定要刪除當前例程正在使用的UNDO表空間應首先切換UNDO表空間然後刪除相應的UNDO表空間
DROP TABLESPACE undotbs;
確定當前例程正在使用的UNDO表空間
Show parameter undo_tablespace
顯示數據庫的所有UNDO表空間
SELECT tablespace_name FROMdba_tablespaces WHERE contents=UNDO;
顯示UNDO表空間統計信息
使用自動UNDO管理模式時需要合理地設置UNDO表空間的尺寸為例合理規劃UNDO表空間尺寸應在數據庫運行的高峰階段搜集UNDO表空間的統計信息最終根據該統計信息確定UNDO表空間的尺寸通過查詢動態性能視圖V%UNDOSTAT可以搜集UNDO統計信息
SELECT TO_CHAR(BEGIN_TIMEHH:MI:SS) BEGIN_TIME
TO_CHAR(END_TIMEHH:MI:SS) END_TIME
UNDOBLKS
FROM V$UNDOSTAT;
BEGIN_TIME用於標識起始統計時間END_TIME用於標識結束統計時間UNDOBLKS用於標識UNDO數據所占用的數據塊個數oracle每隔分鐘生成一行統計信息
顯示UNDO段統計信息
使用自動UNDO管理模式時oracle會在UNDO表空間上自動建立個UNDO段通過查詢動態信息視圖V$ROLLNAME可以顯示所有聯機UNDO段的名稱通過查詢動態性能視圖V$ROLLLISTAT可以顯示UNDO段的統計信息通過在V$ROLLNAME和V$ROLLLISTAT之間執行連接查詢可以監視特定UNDO段的特定信息
SELECT aname bxacts bwrites bextents
FROM v$rollname a v$rollstat b
WHERE ausn=busn;
Name用於標識UNDO段的名稱xacts用於標識UNDO段所包含的活動事務個數
Writes用於標識在undo段上所寫入的字節數extents用於標識UNDO段的區個數
顯示活動事務信息
當執行DML操作時oracle會將這些操作的舊數據放到UNDO段中動態性能視圖v$session用於顯示會話的詳細信息動態性能視圖v$transaction用於顯示事務的詳細信息動態性能視圖v$rollname用於顯示聯機UNDO段的名稱通過在這個動態性能視圖之間執行連接查詢可以確定正在執行事務操作的會話事務所使用的UNDO段以及事務所占用的UNDO塊個數
Col username format a
Col name format a
SELECT ausername bname cused_ublk
FROM v$session a v$rollname b v$transaction c
WHERE asaddr=cses_addr AND busn=cxidusn
AND ausername=SCOTT;
顯示UNDO區信息
數據字典視圖dba_undo_extents用於顯示UNDO表空間所有區的詳細信息包括UNDO區尺寸和狀態等信息
SELECT extend_id bytes status FROM dba_undo_extents
WHERE segment_name_SYSSMU$;
其中extent_id用於標識區編號bytes用於標識區尺寸status用於標識區狀態(ACTIVE:表示該區處於活動狀態EXPIRED:標識該區未用)
From:http://tw.wingwit.com/Article/program/Oracle/201311/17893.html
