Oracle实用小技巧：UNDO 100%占用不释放解决办法

最新推荐文章于 2025-04-17 10:08:57 发布

徐sir（徐慧阳）

最新推荐文章于 2025-04-17 10:08:57 发布

阅读量5.1k

点赞数 18

分类专栏： Oracle数据库记录文章标签： oracle linux 数据库运维

本文链接：https://blog.youkuaiyun.com/xxddxhyz/article/details/135700812

版权

Oracle数据库记录专栏收录该内容

52 篇文章

订阅专栏

表现是UNDO 100%占用不释放，其实原因是因为本地磁盘没有空间了，没办法让UNDO继续自动扩展了，同时现有的UNDO也有点大了，想要清理一下。

1、问题现像

起因是客户存储没空间了，目前RAC1节点UNDOTBS1占用200多G，想要把UNDO释放一下。

我这边undo_retention设置的是900，按说不应该增长到很大才对，以前确实没有注意，只是加过几次undo数据文件。

数据库的版本为12CR2，操作系统为solaris 11。

查看发现有大量unexpired

Oracle Undo段中区3种状态（DBA_UNDO_EXTENTS的STATUS列）：ACTIVE、EXPIRED和UNEXPIRED：

ACTIVE即未提交的Undo信息（活动）：表示事物还在活动，该值对应的Undo段的DBA_ROLLBACK_SEGS.STATUS一定是ONLINE状态，一旦没有活动的事务在使用Undo段，那么对应的Undo段就变成OFFLINE状态。ACTIVE状态的Undo区不会被覆盖。
EXPIRED即过期的Undo信息（过期）：表示事务已经提交且超过了UNDO_RETENTION指定时间，该状态可以被覆盖使用。
UNEXPIRED即提交的Undo信息（未过期）：表示事务已经提交但是还没有超过UNDO_RETENTION指定时间，该状态可以被覆盖使用。

2、解决UNDO过大的问题

2.1、修改隐藏参数关闭autotune

查看隐藏参数_undo_autotune默认状态为true：

SELECT ksppinm, ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND ksppinm = '_undo_autotune';

修改隐藏参数来限制undo这里有2个方法

方法1:限制undoretention最大时间，就是在自动调整的结果上强制限制最大时间（试了效果不大）。

alter system set "_highthreshold_undoretention"=50000 scope=spfile;

方法2：直接关闭autotune（这个更干脆）

关于autotune可以查看官方文档Automatic Tuning of Undo Retention Common Issues (Doc ID 1579779.1)

alter system set "_undo_autotune"=false;

创建PDB参数文件

改完效果

再检查undo情况

隔了一天再检查

再检查状态基本都为expired状态

select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status;

2.2、切换UNDO方法

我这边的数据库版本是12.2版本。默认使用LOCAL UNDO

先切换到PDB

SQL> alter session set container=pdb;
Session altered.

动之前再确认是否开启本地UNDO

SQL> select property_name, property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE
----------------------- ---------------
LOCAL_UNDO_ENABLED TRUE

创建undo_01表空间

CREATE UNDO TABLESPACE UNDO_1 datafile '+DATA' size 10G autoextend on;

PDB修改下原来11G基本一样，也要指定下SID（这里不是写数据库的实例名）

alter system set undo_tablespace=UNDO_01 sid='orcl1' scope=both;

然后使用如下SQL查看，回滚段是否己经切换到undo_01里。

select dr.tablespace_name, dr.segment_name, vr.status from dba_rollback_segs dr, v$rollstat vr where dr.segment_id=vr.usn;

确实无误后执行删除原有UNDOTBS1。

drop tablespace UNDOTBS1 including contents and datafiles;

再次查看。

SELECT * FROM DBA_DATA_FILES;

3、关于为啥node2上的undo表空间不叫undotbs2而叫undo_2

第2个节点的名称挺奇怪，原来11g的时候都是undotbs1和undotbs2

在PDB里显示为1节点是undotbs1，2节点是undo_2。这个在12CR2的版本里是正常现像，RAC环境创建PDB的第2个节点就是默认叫这个名，可以参照官方文档说明。

With Local Undo Enabled and RAC Environment,The UNDO Tablespace of a PDB is UNDO_2 (Doc ID 2971554.1)

如果你觉得别扭，可以手动创建个undotbs2进行切换，具体方法参照官方文档，这里我贴出来

 If you need to change the UNDO tablespace of the PDB on node 2 from UNDO_2, do the following in the target PDB on node2:

Check the current UNDO tablespace. -- Connect to and execute the PDB on the target node.
alter session set container=;

show con_name

-- Check the current UNDO tablespace.

show parameter undo_tablespace 

If there is no UNDO tablespace to change to, create a new UNDO tablespace.
CREATE UNDO TABLESPACE SIZE AUTOEXTEND ON ;

example:

CREATE UNDO TABLESPACE UNDOTBS2 datafile SIZE 10M AUTOEXTEND ON ;

Verify that the undo tablespace that you want to change has been created.

SELECT * FROM DBA_DATA_FILES;

 Change the UNDO tablespace.
alter system set undo_tablespace= container=current  sid='' scope=spfile ;example:

alter system set undo_tablespace=UNDOTBS2 container=current  sid='orcl2' scope=spfile ;

Restart the PDB and confirm that you have switched to the new tablespace.
Restart the PDB:shutdown

startupPlease confirm that you have switched to the new tablespace.

See if the segment for the old undo tablespace (UNDO_2) disappears.

select dr.tablespace_name, dr.segment_name, vr.status from dba_rollback_segs dr, v$rollstat vr where dr.segment_id=vr.usn;

 Drop the old UNDO tablespace.
drop tablespace UNDO_2 including contents and datafiles;

Verify that the original undo tablespace has been dropped.

SELECT * FROM   DBA_DATA_FILES;