Oracle释放过度使用的Undo表空间

1、Oracle,释放过度使用的Undo表空间故障现象：UNDO表空间越来越大长此下去最终数据因为磁盘空间不足而崩溃；问题分析：产生问题的原因主要以下两点：1.有较大的事务量让OracleUndo自动扩展，产生过度占用磁盘空间的情况；2.有较大事务没有收缩或者没有提交所导制；说明:本问题在ORACLE系统管理中属于比较正常的一现象，日常维护多注意对磁盘空间的监控。备份：(如果没有在线事务，可以不做，关闭监听)$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登陆到数据库。#su-oracle$sqlplus/nologSQL*Plus:Release.0-ProductiononWedNov813:45:102006Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.SQLconnsys/qq994238ddptestassysdba;Connected.2.查找数据库的UNDO表空间

3、名#cat$ORACLE_HOME/dbs/initddptest.ora*.undo_management=AUTO*.undo_retention=10800*.undo_tablespace=UNDOTBS23.确认UNDO表空间；SQLselectnamefromv$tablespace;NAMECWMLITEDRSYSEXAMPLEINDXODMSYSTEMTOOLSUSERSXDBTEMPTESTLIBUNDOTBS24.检查数据库UNDO表空间占用空间情况以及数据文件存放位置；SQLselectfile_name,bytes/1024/1024fromdba_data_files

4、2wheretablespace_namelikeUNDOTBS2;5.查看回滚段的使用情况，哪个用户正在使用回滚段的资源，如果有用户最好更换时间（特别是生产环境）。SQLselects.username,fromv$transactiont,v$rollstatr,2v$rollnameu,v$sessionswheres.taddr=t.addrand3t.xidusn=r.usnandr.usn=u.usnorderbys.username;6.检查UNDOSegment状态；SQLselectusn,xacts,rssize/1024/1024/1024,hwmsize/1

5、024/1024/1024,shrinks2fromv$rollstatorderbyrssize;7.创建新的UNDO表空间，并设置自动扩展参数；SQLcreateundotablespaceundotbs1datafile/oradata/oradata/ddptest/UNDOTBS1.dbfsize1000mreuseautoextendonnext800mmaxsizeunlimited;Tablespacecreated.8.动态更改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.再执行看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如下内容是否发生变更：#cat$ORACLE_HOME/dbs/initddptest.ora*.undo_management=A

8、UTO*.undo_retention=10800*.undo_tablespace=UNDOTBS2如果没有发生变更请执行如下语句：SQLcreatepfilefromspfile;Filecreated.14.册除原UNDO表空间的数据文件，其文件名为步骤中执行的结果。#rm$ORACLE_BASE/oradata/$ORACLE_SID/undotbs02.dbf附：UNDO表空间介绍UNDO表空间用于存放UNDO数据,当执行DML操作(INSERT,UPDATE和DELETE)时,oracle会将这些操作的旧数据写入到UNDO段，在oracle9i之前，管理UNDO数据时使用(Roll

9、backSegment)完成的.从oracle9i开始，管理UNDO数据不仅可以使用回滚段还可以使用UNDO表空间因为规划和管理回滚段比较复杂，所有oracledatabase10g已经完全丢弃用回滚段并且使用UNDO表空间来管理UNDO数据.UNDO数据也称为回滚(ROLLBACK数据，它用于确保数据的一致性当执行DML操作时，事务操作前的数据被称为UNDO记录.UNDO段用于保存事务所修改数据的旧值，其中存储着被修改数据块的位置以及修改前数据,UNDO数据的作用.1,回退事务当执行DML操作修改数据时,UNDO数据被存放到UNDO段，而新数据则被存放到数据段中如果事务操作存在问题，旧需要回

10、退事务以取消事务变化假定用户A执行了语句UPDATEempSETsal=1000WHEREempno=7788后发现，应该修改雇员7963的工资，而不是雇员7788的工资，那么通过执行ROLLBACK语句可以取消事务变化当执行ROLLBACK命令时,oracle会将UNDO段的UNDO数据800写回的数据段中2,读一致性用户检索数据库数据时oracle总是使用用户只能看到被提交过的数据（读取提交）或特定时间点的数据（SELECT语句时间点）这样可以确保数据的一致性例如，当用户A执行语句UPDATEempSETsal=1000WHEREempno=7788时,UNDO记录会被存放到回滚段中,而新

11、数据则会存放到EMP段中;假定此时该数据尚未提交，并且用户B执行SELECTsalFROMempWHEREempno=7788，此时用户B将取得UNDO数据800,而该数据正是在UNDO记录中取得的.3，事务恢复事务恢复是例程恢复的一部分它是由oracleserver自动完成的如果在数据库运行过程中出现例程失败（如断电，内存故障，后台进程故障等），那么当重启oracleserver时，后台进程SMON会自动执行例程恢复，执行例程恢复时，oracl会重新做所有未应用的记录回退未提交事务.4,倒叙查询（FlashBackQuery）倒叙查询用于取得特定时间点的数据库数据它是9i新增加的特性假定当前

12、时间为上午11:00,某用户在上午10:00执行UPDATEempSETsal=3500WHEREempno=7788语句，修改并提交了事务（雇员原工资为3000），为了取得10:00之前的雇员工资,用户可以使用倒叙查询特征使用UNDO参数1，UNDO_MANAGEMENT该初始化参数用于指定UNDO数据的管理方式如果要使用自动管理模式必须设置该参数为AUTO,如果使用手工管理模式，必须设置该参数为MANUAL,使用自动管理模式时,oracle会使用undo表空间管理undo管理,使用手工管理模式时,oracle会使用回滚段管理undo数据，需要注意，使用自动管理模式时，如果没有配置初始化参数

13、UNDO_TABLESPACE，oracle会自动选择第一个可用的UNDO表空间存放UNDO数据，如果没有可用的UNDO表空间,oracle会使用SYSTEM回滚段存放UNDO记录，并在ALTER文件中记载警告2，UNDO_TABLESPACE该初始化参数用于指定例程所要使用的UNDO表空间，使用自动UNDO管理模式时，通过配置该参数可以指定例程所要使用的UNDO表空间.在RAC（RealApplicationCluster）结构中，因为一个UNDO表空间不能由多个例程同时使用,所有必须为每个例程配置一个独立的UNDO表空间.3,UNDO_RETENTION该初始化参数用于控制UNDO数据的最

14、大保留时间其默认值为900秒，从9i开始，通过配置该初始化参数可以指定undo数据的保留时间,从而确定倒叙查询特征(FlashbackQuery)可以查看到的最早时间点建立UNDO表空间，UNDO表空间专门用于存放UNDO数据,并且在UNDO表空间尚不能建立任何数据对象表，索引，簇)1，使用CREATEDATABASE命令建立UNDO表空间.当使用CREATEDATABASE命令建立数据库时，通过指定UNDOTABLESPACE选项，可以建立UNDO表空间示例如下：CREATEDATABASEdb01UNDOTABLESPACEundotbs_01DATAFILE/u01/oracle/rbd

15、b1/undo0101.dbfSIZE30M;注意:UNDOTABLESPACE子句不是必须的,如果使用自动UNDO管理模式,并且没有指定该子句那么建立数据库时会自动生成名为SYS_UNDOTBS的UNDO表空间.2,使用CREATEUNDOTABLESPACE命令建立UNDO表空间.CREATEUNDOTABLESPACEundotbs3DATAFILED:demoundotbs3.dbfSIZE10M;修改UNDO表空间，使用ALTERTABLESPACE命令修改UNDO表空间.当事务用尽了UNDO表空间后，使用ALTERTABLESPACEADDDATAFILE增加数据文件当UNDO表空

16、间所在的磁盘填满是使用ALTERTABLESPACERENAMEDATAFIEL命令移动数据文件到其他磁盘上.使用ALTERDATABASEOFFLINE/ONLINE使表空间脱机/联机.当数据库处于ARCHIVELOG模式时，使用ALTERTABLESPACEBEGINBACKUP/ENDBACKUP命令备份UNDO表空间.切换UNDO表空间.启动例程并打开数据库后，同一时刻特定例程只能使用一个UNDO表空间，切换UNDO表空间是指停止例程当前使用的UNDO表空间，并启动其他UNDO表空间，下面以启用undotbs2表空间为例，说明切换UNDO表空间的方法.ALTERSYSTEMSETund

17、o_tablespace=undotbs02;在RAC(RealApplicationCluster)机构中，不同例程必须使用独立的UNDO表空间，而不能共用同一个UNDO表空间.删除UNDO表空间.当前例程正在使用的UNDO表空间是不能被删除的如果确定要删除当前例程正在使用的UNDO表空间，应首先切换UNDO表空间然后删除相应的UNDO表空间.DROPTABLESPACEundotbs3;1,确定当前例程正在使用的UNDO表空间.Showparameterundo_tablespace2,显示数据库的所有UNDO表空间.SELECTtablespace_nameFROMdba_tablesp

18、acesWHEREcontents=UNDO;3,显示UNDO表空间统计信息使用自动UNDO管理模式时，需要合理地设置UNDO表空间的尺寸，为例合理规划UNDO表空间尺寸，应在数据库运行的高峰阶段搜集UNDO表空间的统计信息最终根据该统计信息确定UNDO表空间的尺寸通过查询动态性能视图V%UNDOSTAT，可以搜集UNDO统计信息.SELECTTO_CHAR(BEGIN_TIME,HH24:MI:SS)BEGIN_TIME,TO_CHAR(END_TIME,HH24:MI:SS)END_TIME,UNDOBLKSFROMV$UNDOSTAT;BEGIN_TIME用于标识起始统计时间,END_T

19、IME用于标识结束统计时间,UNDOBLKS用于标识UNDO数据所占用的数据块个数oracle每隔10分钟生成一行统计信息.4,显示UNDO段统计信息.使用自动UNDO管理模式时,oracle会在UNDO表空间上自动建立10个UNDO段，通过查询动态信息视图V$ROLLNAME,可以显示所有联机UNDO段的名称，通过查询动态性能视图V$ROLLLISTAT，可以显示UNDO段的统计信息通过在V$ROLLNAME和V$ROLLLISTAT之间执行连接查询,可以监视特定UNDO段的特定信息.SELECT,b.xacts,b.writes,b.extentsFROMv$rollnamea,v$rollstatbWHEREa.usn=b.usn;Name用于标识UNDO段的名称,xacts用于标识UNDO段所包含的活动事务个