Oracle UNDO表空间

* 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