Oracle UNDO表空间

末蓝、 2022-05-09 03:10 207阅读 0赞

\* Oracle UNDO表空间

UNDO表空间用来暂时存储DML操作的数据，主要的作用是用于：事务回滚、实例恢复、读一致性、闪回。

UNDO表空间通常都是Oracle自动化管理（通过undo\_management初始化参数确定）；  
UNDO表空间是用于存储DML操作的前镜像数据，它是实例恢复，数据回滚，一致性查询功能的重要组件。  
UNDO表空间的相关问题：  
1.空间使用率100%，导致DML操作无法进行。  
2.告警日志中出现大量的ORA-01555告警错误。  
3.实例恢复失败，数据库无法正常打开。

\* 初始化参数

undo\_management=AUTO      //实例自动管理UNDO表空间。从Oracle 9i开始支持AUM（Automatic Undo Management）  
undo\_retention=900        //事务提交后，相应的UNDO数据保留的时间（单位：秒）  
undo\_tablespace=UNDOTBS1  //活动的UNDO表空间  
\_smu\_debug\_mode=33554432   
\_undo\_autotune=TRUE

\* Automatic UNDO Retention  
Automatic UNDO Retention是Oracle 10g的新特性，在10g和之后的版本的数据库，这个特性是默认启用的。

在Oracle 10g中，当自动undo管理被启用，总是存在一个当前的undo retention，Oracle尝试至少保留旧的undo信息到该时间。  
数据库会自动收集使用情况，统计信息，基于这些统计信息和UNDO表空间大小，数据库会自动来调整undo retention的时间。  
Oracle基于undo表空间大小和系统活动自动调整undo retention，通过设置undo\_retention初始化参数指定undo retention的最小值。  
      
查看Oracle自动调整undo\_retention的值。  
查询命令：select to\_char(BEGIN\_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN\_TIME, TUNED\_UNDORETENTION FROM V$UNDOSTAT;  
针对自动扩展的UNDO表空间，系统至少保留UNDO到参数指定的时间，自动调整UNDO RETENTION以满足查询对UNDO的要求，这可能导致UNDO急剧扩张，可以考虑不设置UNDO RETENTION值。

\* 相关命令

查看Oracle自动调整undo\_retention的值  
SQL> select to\_char(BEGIN\_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN\_TIME, TUNED\_UNDORETENTION FROM V$UNDOSTAT;  
说明：  
BEGIN\_TIME 表示每条记录UNDO事务开始的时间  
END\_TIMEE 表示每条记录UNDO事务结束的时间

查看某个实例都有哪些表空间  
SQL> select tablespace\_name, contents from dba\_tablespaces where contents = 'UNDO';

TABLESPACE\_NAME     CONTENTS  
\------------------------------------------------------  
UNDOTBS1            UNDO

仅查看当前默认UNDO表空间  
SQL> show parameter undo\_tablespace;

NAME                   TYPE           VALUE  
\------------------------------------------------------  
undo\_tablespace        string         UNDOTBS1

查看表空间及其对应的数据文件  
SQL> select substr(file\_name,1,60) UNDO\_FILES from dba\_data\_files where tablespace\_name = 'UNDOTBS1' order by 1;

UNDO\_FILES  
\--------------------------------------------------------------------------------  
/opt/oracle/oradata/orcl/undotbs01.dbf

查看UNDO表空间  
select \* from (  
  select a.tablespace\_name,   
  sum(a.bytes)/(1024\*1024) total\_space\_MB,   
  round(b.free,2) Free\_space\_MB,    
  round(b.free/(sum(a.bytes)/(1024\*1024))\* 100,2) percent\_free    
    from dba\_data\_files a,  
        (select tablespace\_name,sum(bytes)/(1024\*1024) free  from dba\_free\_space group by tablespace\_name) b   
    where a.tablespace\_name = b.tablespace\_name(+)   
    group by a.tablespace\_name,b.free)   
  where tablespace\_name = 'UNDOTBS1';

创建UNDO表空间  
create undo tablespace undotbs2 datafile '/data1/oradata/undotbs02\_1.dbf' size 1024M autoextend on next 100M maxsize 10240M;

给指定的UNDO表空间增加数据文件  
alter tablespace undotbs2 add datafile '/opt/oracle/oradata/orcl/undotbs02\_2.dbf' size 1024M autoextend on next 100M maxsize 10240M;

切换默认UNDO表空间  
alter system set undo\_tablespace = UNDOTBS3;

查看UNDO表空间的状态（可通过查询dba\_undo\_extents）  
select status,sum(bytes/1024/1024) from dba\_undo\_extents where tablespace\_name='UNDOTBS1' group by status;

其中STATUS的状态有三个：ACTIVE, UNEXPIRED, EXPIRED  
ACTIVE：正在使用的UNDO表空间区域。是正在执行的没有commit的DML涉及的数据所占用的区域。状态为ACTIVE的区域不可以被新数据覆盖。  
EXPIRED：过期数据区域。这个状态说明该区域的数据可以被新数据覆盖。该区域虽然占用了UNDO表空间，但可以被覆盖使用，因此也可以理解为空闲区域。  
UNEXPIRED：是介于ACTIVER和EXPIRED两者的中间状态，如果undo表空间的空闲区域和EXPIRED区域都已经消耗光了，则Oracle会将新数据覆盖到该区域。但是有可能引起某些SQL错误（例如快照过旧）。UNEXPIRED是由ACTIVE状态的数据转换过来的，至于UNEXPIRED状态何时转换成EXPIRED，这个由当前系统的undo retention决定。  
当我们计算UNDO的可使用的剩余空间的时候，其实应该计算两者之和：UNDO表空间的FREE空间 + EXPIRED空间。

更多UNDO信息查询  
select to\_char(BEGIN\_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN\_TIME,   
       to\_char(END\_TIME, 'MM/DD/YYYY HH24:MI:SS') ND\_TIME,   
       UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON"   
       from v$UNDOSTAT where rownum <= 100;

BEGIN\_TIME：表示每条记录UNDO事务开始的时间  
END\_TIME：表示每条记录UNDO事务结束的时间  
UNDOTSN：在这段时间undo事务的数量  
UNDOBLKS：在这段时间占用的undo块的数量  
TXNCOUNT：事务的总数量  
MAXCON：这些UNDO事务过程中的最大数据库连接数

手动修改调整UNDO RETENTION  
alter system set UNDO\_RETENTION = 100; //降低该值可以加快过期的速度

删除不用的UNDO表空间  
SQL> drop tablespace undotbs2;  
\# rm -rf /opt/oracle/oradata/orcl/undotbs02\_2.dbf