Oracle釋放過(guò)度使用的Undo表空間

1、Oracle,釋放過(guò)度使用的Undo表空間故障現(xiàn)象：UNDO表空間越來(lái)越大長(zhǎng)此下去最終數(shù)據(jù)因?yàn)榇疟P(pán)空間不足而崩潰；問(wèn)題分析：產(chǎn)生問(wèn)題的原因主要以下兩點(diǎn)：1.有較大的事務(wù)量讓OracleUndo自動(dòng)擴(kuò)展，產(chǎn)生過(guò)度占用磁盤(pán)空間的情況；2.有較大事務(wù)沒(méi)有收縮或者沒(méi)有提交所導(dǎo)制；說(shuō)明:本問(wèn)題在ORACLE系統(tǒng)管理中屬于比較正常的一現(xiàn)象，日常維護(hù)多注意對(duì)磁盤(pán)空間的監(jiān)控。備份：(如果沒(méi)有在線事務(wù)，可以不做，關(guān)閉監(jiān)聽(tīng))$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.啟動(dòng)SQLPLUS,并用sys登陸到數(shù)據(jù)庫(kù)。#su-oracle$sqlplus/nologSQL*Plus:Release.0-ProductiononWedNov813:45:102006Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.SQLconnsys/qq994238ddptestassysdba;Connected.2.查找數(shù)據(jù)庫(kù)的UNDO表空間

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

4、2wheretablespace_namelikeUNDOTBS2;5.查看回滾段的使用情況，哪個(gè)用戶正在使用回滾段的資源，如果有用戶最好更換時(shí)間（特別是生產(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è)置自動(dòng)擴(kuò)展參數(shù)；SQLcreateundotablespaceundotbs1datafile/oradata/oradata/ddptest/UNDOTBS1.dbfsize1000mreuseautoextendonnext800mmaxsizeunlimited;Tablespacecreated.8.動(dòng)態(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.確認(rèn)刪除是否成功；SQLselectnamefromv$tablespace;NAMECWMLITEDRSYSEXAMPLEINDXODMSYSTEMTOOLSUSERSXDBTEMPTESTLIBUNDOTBS112rowsselected.13.在做此步驟前，請(qǐng)至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如果沒(méi)有發(fā)生變更請(qǐng)執(zhí)行如下語(yǔ)句：SQLcreatepfilefromspfile;Filecreated.14.冊(cè)除原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)時(shí),oracle會(huì)將這些操作的舊數(shù)據(jù)寫(xiě)入到UNDO段，在oracle9i之前，管理UNDO數(shù)據(jù)時(shí)使用(Roll

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

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

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

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

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

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

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

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

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

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

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