Oracle釋放過度使用的Undo表空間

1、Oracle,釋放過度使用的Undo表空間故障現(xiàn)象：UNDO表空間越來越大長此下去最終數(shù)據(jù)因為磁盤空間不足而崩潰；問題分析：產(chǎn)生問題的原因主要以下兩點：1.有較大的事務(wù)量讓OracleUndo自動擴展，產(chǎn)生過度占用磁盤空間的情況；2.有較大事務(wù)沒有收縮或者沒有提交所導(dǎo)制；說明:本問題在ORACLE系統(tǒng)管理中屬于比較正常的一現(xiàn)象，日常維護多注意對磁盤空間的監(jiān)控。備份：(如果沒有在線事務(wù)，可以不做，關(guān)閉監(jiān)聽)$expvas/vasfile=/opt/oracle/data_1.dmp,/opt/oracle/data_2.dmplog=/opt/oracle/date.logwner=vasrow

2、s=yindexes=ycompress=nbuffer=65536feedback=100000volsize=0filesize=1000M解決步驟：1.啟動SQLPLUS,并用sys登陸到數(shù)據(jù)庫。#su-oracle$sqlplus/nologSQL*Plus:Release.0-ProductiononWedNov813:45:102006Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.SQLconnsys/qq994238ddptestassysdba;Connected.2.查找數(shù)據(jù)庫的UNDO表空間

3、名#cat$ORACLE_HOME/dbs/initddptest.ora*.undo_management=AUTO*.undo_retention=10800*.undo_tablespace=UNDOTBS23.確認UNDO表空間；SQLselectnamefromv$tablespace;NAMECWMLITEDRSYSEXAMPLEINDXODMSYSTEMTOOLSUSERSXDBTEMPTESTLIBUNDOTBS24.檢查數(shù)據(jù)庫UNDO表空間占用空間情況以及數(shù)據(jù)文件存放位置；SQLselectfile_name,bytes/1024/1024fromdba_data_files

4、2wheretablespace_namelikeUNDOTBS2;5.查看回滾段的使用情況，哪個用戶正在使用回滾段的資源，如果有用戶最好更換時間（特別是生產(chǎn)環(huán)境）。SQLselects.username,fromv$transactiont,v$rollstatr,2v$rollnameu,v$sessionswheres.taddr=t.addrand3t.xidusn=r.usnandr.usn=u.usnorderbys.username;6.檢查UNDOSegment狀態(tài)；SQLselectusn,xacts,rssize/1024/1024/1024,hwmsize/1

5、024/1024/1024,shrinks2fromv$rollstatorderbyrssize;7.創(chuàng)建新的UNDO表空間，并設(shè)置自動擴展參數(shù)；SQLcreateundotablespaceundotbs1datafile/oradata/oradata/ddptest/UNDOTBS1.dbfsize1000mreuseautoextendonnext800mmaxsizeunlimited;Tablespacecreated.8.動態(tài)更改spfile配置文件；SQLaltersystemsetundo_tablespace=undotbs1scope=both;Systemaltere

6、d.9.等待原UNDO表空間所有UNDOSEGMENTOFFLINE；SQLselectusn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks2fromv$rollstatorderbyrssize;10.再執(zhí)行看UNDO表空間所有UNDOSEGMENTONLINE；SQLselectusn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks2fromv$rollstatorderbyrssize;11.刪除原有的UNDO表空間；S

7、QLdroptablespaceundotbs2includingcontents;Tablespacedropped.12.確認刪除是否成功；SQLselectnamefromv$tablespace;NAMECWMLITEDRSYSEXAMPLEINDXODMSYSTEMTOOLSUSERSXDBTEMPTESTLIBUNDOTBS112rowsselected.13.在做此步驟前，請至U$ORACLE_HOME/dbs/init$ORACLE_SID.ora如下內(nèi)容是否發(fā)生變更：#cat$ORACLE_HOME/dbs/initddptest.ora*.undo_management=A

8、UTO*.undo_retention=10800*.undo_tablespace=UNDOTBS2如果沒有發(fā)生變更請執(zhí)行如下語句：SQLcreatepfilefromspfile;Filecreated.14.冊除原UNDO表空間的數(shù)據(jù)文件，其文件名為步驟中執(zhí)行的結(jié)果。#rm$ORACLE_BASE/oradata/$ORACLE_SID/undotbs02.dbf附：UNDO表空間介紹UNDO表空間用于存放UNDO數(shù)據(jù),當(dāng)執(zhí)行DML操作(INSERT,UPDATE和DELETE)時,oracle會將這些操作的舊數(shù)據(jù)寫入到UNDO段，在oracle9i之前，管理UNDO數(shù)據(jù)時使用(Roll

9、backSegment)完成的.從oracle9i開始，管理UNDO數(shù)據(jù)不僅可以使用回滾段還可以使用UNDO表空間因為規(guī)劃和管理回滾段比較復(fù)雜，所有oracledatabase10g已經(jīng)完全丟棄用回滾段并且使用UNDO表空間來管理UNDO數(shù)據(jù).UNDO數(shù)據(jù)也稱為回滾(ROLLBACK數(shù)據(jù)，它用于確保數(shù)據(jù)的一致性當(dāng)執(zhí)行DML操作時，事務(wù)操作前的數(shù)據(jù)被稱為UNDO記錄.UNDO段用于保存事務(wù)所修改數(shù)據(jù)的舊值，其中存儲著被修改數(shù)據(jù)塊的位置以及修改前數(shù)據(jù),UNDO數(shù)據(jù)的作用.1,回退事務(wù)當(dāng)執(zhí)行DML操作修改數(shù)據(jù)時,UNDO數(shù)據(jù)被存放到UNDO段，而新數(shù)據(jù)則被存放到數(shù)據(jù)段中如果事務(wù)操作存在問題，舊需要回

10、退事務(wù)以取消事務(wù)變化假定用戶A執(zhí)行了語句UPDATEempSETsal=1000WHEREempno=7788后發(fā)現(xiàn)，應(yīng)該修改雇員7963的工資，而不是雇員7788的工資，那么通過執(zhí)行ROLLBACK語句可以取消事務(wù)變化當(dāng)執(zhí)行ROLLBACK命令時,oracle會將UNDO段的UNDO數(shù)據(jù)800寫回的數(shù)據(jù)段中2,讀一致性用戶檢索數(shù)據(jù)庫數(shù)據(jù)時oracle總是使用用戶只能看到被提交過的數(shù)據(jù)（讀取提交）或特定時間點的數(shù)據(jù)（SELECT語句時間點）這樣可以確保數(shù)據(jù)的一致性例如，當(dāng)用戶A執(zhí)行語句UPDATEempSETsal=1000WHEREempno=7788時,UNDO記錄會被存放到回滾段中,而新

11、數(shù)據(jù)則會存放到EMP段中;假定此時該數(shù)據(jù)尚未提交，并且用戶B執(zhí)行SELECTsalFROMempWHEREempno=7788，此時用戶B將取得UNDO數(shù)據(jù)800,而該數(shù)據(jù)正是在UNDO記錄中取得的.3，事務(wù)恢復(fù)事務(wù)恢復(fù)是例程恢復(fù)的一部分它是由oracleserver自動完成的如果在數(shù)據(jù)庫運行過程中出現(xiàn)例程失敗（如斷電，內(nèi)存故障，后臺進程故障等），那么當(dāng)重啟oracleserver時，后臺進程SMON會自動執(zhí)行例程恢復(fù)，執(zhí)行例程恢復(fù)時，oracl會重新做所有未應(yīng)用的記錄回退未提交事務(wù).4,倒敘查詢（FlashBackQuery）倒敘查詢用于取得特定時間點的數(shù)據(jù)庫數(shù)據(jù)它是9i新增加的特性假定當(dāng)前

12、時間為上午11:00,某用戶在上午10:00執(zhí)行UPDATEempSETsal=3500WHEREempno=7788語句，修改并提交了事務(wù)（雇員原工資為3000），為了取得10:00之前的雇員工資,用戶可以使用倒敘查詢特征使用UNDO參數(shù)1，UNDO_MANAGEMENT該初始化參數(shù)用于指定UNDO數(shù)據(jù)的管理方式如果要使用自動管理模式必須設(shè)置該參數(shù)為AUTO,如果使用手工管理模式，必須設(shè)置該參數(shù)為MANUAL,使用自動管理模式時,oracle會使用undo表空間管理undo管理,使用手工管理模式時,oracle會使用回滾段管理undo數(shù)據(jù)，需要注意，使用自動管理模式時，如果沒有配置初始化參數(shù)

13、UNDO_TABLESPACE，oracle會自動選擇第一個可用的UNDO表空間存放UNDO數(shù)據(jù)，如果沒有可用的UNDO表空間,oracle會使用SYSTEM回滾段存放UNDO記錄，并在ALTER文件中記載警告2，UNDO_TABLESPACE該初始化參數(shù)用于指定例程所要使用的UNDO表空間，使用自動UNDO管理模式時，通過配置該參數(shù)可以指定例程所要使用的UNDO表空間.在RAC（RealApplicationCluster）結(jié)構(gòu)中，因為一個UNDO表空間不能由多個例程同時使用,所有必須為每個例程配置一個獨立的UNDO表空間.3,UNDO_RETENTION該初始化參數(shù)用于控制UNDO數(shù)據(jù)的最

14、大保留時間其默認值為900秒，從9i開始，通過配置該初始化參數(shù)可以指定undo數(shù)據(jù)的保留時間,從而確定倒敘查詢特征(FlashbackQuery)可以查看到的最早時間點建立UNDO表空間，UNDO表空間專門用于存放UNDO數(shù)據(jù),并且在UNDO表空間尚不能建立任何數(shù)據(jù)對象表，索引，簇)1，使用CREATEDATABASE命令建立UNDO表空間.當(dāng)使用CREATEDATABASE命令建立數(shù)據(jù)庫時，通過指定UNDOTABLESPACE選項，可以建立UNDO表空間示例如下：CREATEDATABASEdb01UNDOTABLESPACEundotbs_01DATAFILE/u01/oracle/rbd

15、b1/undo0101.dbfSIZE30M;注意:UNDOTABLESPACE子句不是必須的,如果使用自動UNDO管理模式,并且沒有指定該子句那么建立數(shù)據(jù)庫時會自動生成名為SYS_UNDOTBS的UNDO表空間.2,使用CREATEUNDOTABLESPACE命令建立UNDO表空間.CREATEUNDOTABLESPACEundotbs3DATAFILED:demoundotbs3.dbfSIZE10M;修改UNDO表空間，使用ALTERTABLESPACE命令修改UNDO表空間.當(dāng)事務(wù)用盡了UNDO表空間后，使用ALTERTABLESPACEADDDATAFILE增加數(shù)據(jù)文件當(dāng)UNDO表空

16、間所在的磁盤填滿是使用ALTERTABLESPACERENAMEDATAFIEL命令移動數(shù)據(jù)文件到其他磁盤上.使用ALTERDATABASEOFFLINE/ONLINE使表空間脫機/聯(lián)機.當(dāng)數(shù)據(jù)庫處于ARCHIVELOG模式時，使用ALTERTABLESPACEBEGINBACKUP/ENDBACKUP命令備份UNDO表空間.切換UNDO表空間.啟動例程并打開數(shù)據(jù)庫后，同一時刻特定例程只能使用一個UNDO表空間，切換UNDO表空間是指停止例程當(dāng)前使用的UNDO表空間，并啟動其他UNDO表空間，下面以啟用undotbs2表空間為例，說明切換UNDO表空間的方法.ALTERSYSTEMSETund

17、o_tablespace=undotbs02;在RAC(RealApplicationCluster)機構(gòu)中，不同例程必須使用獨立的UNDO表空間，而不能共用同一個UNDO表空間.刪除UNDO表空間.當(dāng)前例程正在使用的UNDO表空間是不能被刪除的如果確定要刪除當(dāng)前例程正在使用的UNDO表空間，應(yīng)首先切換UNDO表空間然后刪除相應(yīng)的UNDO表空間.DROPTABLESPACEundotbs3;1,確定當(dāng)前例程正在使用的UNDO表空間.Showparameterundo_tablespace2,顯示數(shù)據(jù)庫的所有UNDO表空間.SELECTtablespace_nameFROMdba_tablesp

18、acesWHEREcontents=UNDO;3,顯示UNDO表空間統(tǒng)計信息使用自動UNDO管理模式時，需要合理地設(shè)置UNDO表空間的尺寸，為例合理規(guī)劃UNDO表空間尺寸，應(yīng)在數(shù)據(jù)庫運行的高峰階段搜集UNDO表空間的統(tǒng)計信息最終根據(jù)該統(tǒng)計信息確定UNDO表空間的尺寸通過查詢動態(tài)性能視圖V%UNDOSTAT，可以搜集UNDO統(tǒng)計信息.SELECTTO_CHAR(BEGIN_TIME,HH24:MI:SS)BEGIN_TIME,TO_CHAR(END_TIME,HH24:MI:SS)END_TIME,UNDOBLKSFROMV$UNDOSTAT;BEGIN_TIME用于標(biāo)識起始統(tǒng)計時間,END_T

19、IME用于標(biāo)識結(jié)束統(tǒng)計時間,UNDOBLKS用于標(biāo)識UNDO數(shù)據(jù)所占用的數(shù)據(jù)塊個數(shù)oracle每隔10分鐘生成一行統(tǒng)計信息.4,顯示UNDO段統(tǒng)計信息.使用自動UNDO管理模式時,oracle會在UNDO表空間上自動建立10個UNDO段，通過查詢動態(tài)信息視圖V$ROLLNAME,可以顯示所有聯(lián)機UNDO段的名稱，通過查詢動態(tài)性能視圖V$ROLLLISTAT，可以顯示UNDO段的統(tǒng)計信息通過在V$ROLLNAME和V$ROLLLISTAT之間執(zhí)行連接查詢,可以監(jiān)視特定UNDO段的特定信息.SELECT,b.xacts,b.writes,b.extentsFROMv$rollnamea,v$rollstatbWHEREa.usn=b.usn;Name用于標(biāo)識UNDO段的名稱,xacts用于標(biāo)識UNDO段所包含的活動事務(wù)個