正德厚生,臻于至善

Oracle UNDO TABLESPACE 100%占用不释放解决办法

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指定时间,该状态可以被覆盖使用。

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;

set lines 200 pages 50000
col name for a40
col value for a10
col describ for a60
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  FROM SYS.x$ksppi x, SYS.x$ksppcv y
 where x.indx = y.indx
   AND x.ksppinm in ('_highthreshold_undoretention','_undo_autotune');

NAME                                     VALUE      DESCRIB
---------------------------------------- ---------- ------------------------------------------------------------
_highthreshold_undoretention             4294967294 high threshold undo_retention in seconds #方法1:限制undoretention最大时间,就是在自动调整的结果上强制限制最大时间(试了效果不大)
_undo_autotune                           TRUE       enable auto tuning of undo_retention     #方法2:直接关闭autotune(这个更干脆)

show parameter undo
set lines 200 pages 50000
col name for a40
col value for a10
col describ for a60
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  FROM SYS.x$ksppi x, SYS.x$ksppcv y
 where x.indx = y.indx
   AND x.ksppinm in ('_highthreshold_undoretention','_undo_autotune');

undo_retention=43200
_highthreshold_undoretention=43200

NAME                                     VALUE      DESCRIB
---------------------------------------- ---------- ------------------------------------------------------------
_highthreshold_undoretention             4294967294 high threshold undo_retention in seconds #方法1:限制undoretention最大时间,就是在自动调整的结果上强制限制最大时间(试了效果不大)
_undo_autotune                           TRUE       enable auto tuning of undo_retention     #方法2:直接关闭autotune(这个更干脆)

--alter system set "_undo_autotune"=false scope=spfile sid='*';
alter system set "_highthreshold_undoretention"=86400 scope=memory sid='*';
alter system set undo_retention=86400 scope=memory sid='*';

alter system set "_highthreshold_undoretention"=86400 scope=both sid='*';
alter system set undo_retention=86400 scope=both sid='*';
########################################APPS_UNDOTS切换成UNDOTBS,生产环境谨慎操作,直接粘贴出事概不负责
create UNDO tablespace UNDOTBS1 datafile '+BZPRODDATA' size 20G;
create UNDO tablespace UNDOTBS2 datafile '+BZPRODDATA' size 20G;

alter tablespace UNDOTBS1 add datafile '+BZPRODDATA' size 20G;
alter tablespace UNDOTBS2 add datafile '+BZPRODDATA' size 20G;

alter system set undo_tablespace=UNDOTBS1 sid='BZPRODDATA1' scope=memory;
alter system set undo_tablespace=UNDOTBS2 sid='BZPRODDATA2' scope=memory;

select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;
select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='APPS_UNDOTS1';
select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='APPS_UNDOTS2';
select tablespace_name, status, count(*) from dba_rollback_segs where tablespace_name='APPS_UNDOTS1' group by tablespace_name, status;
select tablespace_name, status, count(*) from dba_rollback_segs where tablespace_name='APPS_UNDOTS2' group by tablespace_name, status;

drop tablespace APPS_UNDOTS1 including contents and datafiles;
drop tablespace APPS_UNDOTS2 including contents and datafiles;

########################################UNDOTBS切换成APPS_UNDOTS,生产环境谨慎操作,直接粘贴出事概不负责
CREATE UNDO TABLESPACE "APPS_UNDOTS1"
BZPRODDATAFILE
        '+BZPRODDATA' SIZE 20G ,
        '+BZPRODDATA' SIZE 20G ;

CREATE UNDO TABLESPACE "APPS_UNDOTS2"
BZPRODDATAFILE
        '+BZPRODDATA' SIZE 20G ,
        '+BZPRODDATA' SIZE 20G ;

alter system set undo_tablespace=APPS_UNDOTS1 sid='BZPRODDATA1' scope=memory;
alter system set undo_tablespace=APPS_UNDOTS2 sid='BZPRODDATA2' scope=memory;

select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;
select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS1';
select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS2';
select tablespace_name, status, count(*) from dba_rollback_segs where tablespace_name='UNDOTBS1' group by tablespace_name, status;
select tablespace_name, status, count(*) from dba_rollback_segs where tablespace_name='UNDOTBS2' group by tablespace_name, status;

drop tablespace UNDOTBS1 including contents and datafiles;
drop tablespace UNDOTBS2 including contents and datafiles;
########################################How To Set Specific Undo Tablespace For Each PDB Instance On RAC Node
On node-1:
alter session set container=BZPROD;
alter system set undo_tablespace=APPS_UNDOTS1 container=current sid='BZCDB1' scope=both;             >>>> sid=First Instance
On Node-2
alter session set container=BZPROD;
alter system set undo_tablespace=APPS_UNDOTS2 container=current sid='BZCDB2' scope=both;             >>>> sid=Second Instance
#################################################################################

--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;
--select property_name, property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';
--select dr.tablespace_name, dr.segment_name, vr.status from dba_rollback_segs dr, v$rollstat vr where dr.segment_id=vr.usn;

Primary Note: High Undo Space Usage (Doc ID 1578639.1)
Automatic Tuning of Undo Retention Common Issues (Doc ID 1579779.1)
With Local Undo Enabled and RAC Environment,The UNDO Tablespace of a PDB is UNDO_2 (Doc ID 2971554.1)
With Local Undo enabled, How To Set Specific Undo Tablespace For Each PDB Instance On RAC Node (Doc ID 2673826.1)
赞(0) 打赏
未经允许不得转载:徐万新之路 » Oracle UNDO TABLESPACE 100%占用不释放解决办法

评论 抢沙发

联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫

微信扫一扫

登录

找回密码

注册