故障现象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:标识该区未用)
