Oracle undo表空间爆满的处理方法

Oracle undo表空间爆满的处理方法

今天另一部门同事打电话反馈现场运行的一数据库中出现了UNDO表空间使用率过高，且无法释放问题，其实就该问题一般处理方法如下：

1、扩展UNDO表空间；

2、新建一UNDO表空间，切换默认UNDO表空间到新建表空间中，删除旧的UNDO表空间

（如果仍然想用原来的UNDO表空间名，可以再操作一次）；

3、通过查询DBA_UNDO_EXTENTS视图确认UNDO的真实使用情况；

背景知识：

首先了解什么是UNDO，可以说UNDO是ORACLE中一个非常实用的功能

UNDO数据也称为回滚(ROLLBACK)数据,它用于确保数据的一致性.当执行DML操作时,事务操作前的数据被称为UNDO记录.UNDO段用于保存事务所修改数据的旧值,其中存储着被修改数据块的位置以及修改前数据

UNDO数据的作用：

1.回退事务

当执行DML操作修改数据时,UNDO数据被存放到UNDO段,而新数据则被存放到数据段中,如果事务操作存在问题,旧需要回退事务,以取消事务变化.假定用户A执行了语句UPDATE emp SET sal=1000 WHERE empno=7788后发现,应该修改雇员7963的工资,而不是雇员7788的工资,那么通过执行ROLLBACK语句可以取消事务变化.当执行ROLLBACK命令时,oracle会将UNDO段的UNDO数据800写回的数据段中.

2.读一致性

用户检索数据库数据时,oracle总是使用用户只能看到被提交过的数据(读取提交)或特定时间点的数据(SELECT语句时间点).这样可以确保数据的一致性.例如,当用户A执行语句UPDATE emp SET sal=1000 WHERE empno=7788时,UNDO记录会被存放到回滚段中,而新数据则会存放到EMP段中;假定此时该数据尚未提交,并且用户B执行SELECT sal FROM emp WHERE empno=7788,此时用户B将取得UNDO数据800,而该数据正是在UNDO记录中取得的.

3.事务恢复

事务恢复是例程恢复的一部分,它是由oracle server自动完成的.如果在数据库运行过程中出现例程失败(如断电,内存故障,后台进程故障等),那么当重启oracle server时,后台进程SMON会自动执行例程恢复,执行例程恢复时,oracl会重新做所有未应用的记录.回退未提交事务.

4.倒叙查询(FlashBack Query)

倒叙查询用于取得特定时间点的数据库数据,它是9i新增加的特性,假定当前时间为上午11:00,某用户在上午10:00执行UPDATE emp SET sal=3500 WHERE empno=7788语句,修改并提交了事务(雇员原工资为3000),为了取得10:00之前的雇员工资,用户可以使用倒叙查询特征.

本例以第二种方式为例，具体操作如下：

1. 启动SQLPLUS，并用sys登陆到数据库。

#su - oracle

$>sqlplus as sysdba

2. 查找数据库的UNDO表空间名,确定当前例程正在使用的UNDO表空间：

Show parameter undo_tablespace。

3. 确认UNDO表空间；

SQL> select name from v$tablespace;

NAME

------------------------------

UNDOTBS1

4. 检查数据库UNDO表空间占用空间情况以及数据文件存放位置；

SQL>select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS%';

5. 查看回滚段的使用情况，哪个用户正在使用回滚段的资源，如果有用户最好更换时间（特别是生产环境）。

SQL> select s.username, u.name from v$transaction t,v$rollstat r, v$rollname u,v$session s

where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username;

6. 检查UNDO Segment状态；

SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks

from v$rollstat order by rssize;

USN  XACTS  RSSIZE/1024/1024/1024  HWMSIZE/1024/1024/1024  SHRINKS

1    0    0     0.000358582             0.000358582               0

2    14   0     0.796791077             0.796791077               735

3    44   1     0.00920867919921875     3.99295806884766          996

这还原表空间中还存在3个回滚的对象。

7. 创建新的UNDO表空间，并设置自动扩展参数；

SQL> create undo tablespace undotbs2 datafile '/oracle/oradata/orcl/UNDOTBS2.dbf' size 1000m;

Tablespace created.

8. 切换UNDO表空间为新的UNDO表空间 ， 动态更改spfile配置文件；

SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.

9.验证当前数据库的 UNDO表空间

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS2

9. 等待原UNDO表空间所有UNDO SEGMENT OFFLINE；

select usn,xacts,status,rssize/1024/1024,hwmsize/1024/1024, shrinks from v$rollstat order by rssize;

select t.segment_name,t.tablespace_name,t.segment_id,t.status from dba_rollback_segs t;

SEGMENT_NAME      TABLESPACE_NAME SEGMENT_ID   STATUS

1     SYSTEM             SYSTEM          0           ONLINE

2     _SYSSMU1$          UNDOTBS1        1           OFFLINE

3     _SYSSMU2$          UNDOTBS1        2           OFFLINE

4     _SYSSMU47$         UNDOTBS1        47          OFFLINE

上面对应的UNDOTBS1还原表空间所对应的回滚段均为OFFLINE

10.到$ORACLE_HOME/dbs/init$ORACLE_SID.ora如下内容是否发生变更：

#cat $ORACLE_HOME/dbs/initorcl.ora

……

*.undo_management=’AUTO’

*.undo_retention=10800

*.undo_tablespace=’UNDOTBS2’

……

如果没有发生变更请执行如下语句：

SQL> create pfile from spfile;

File created.

11. 删除原有的UNDO表空间；

SQL> drop tablespace undotbs1 including contents;

最后需要在重启数据库或者重启计算机后到存储数据文件的路径下删除数据文件（为什么要手动删除呢：以上步骤只是删除了ORACLE中undo表空间的逻辑关系，即删除了数据文件在数据字典中的关联，不会自动删除项关联的数据文件）。