Oracle UNDO表空间维护

UNDO表空间通常都是Oracle自动化管理（通过undo_management初始化参数确定）；

UNDO表空间是用于存储DML操作的前镜像数据，它是实例恢复，数据回滚，一致性查询功能的重要组件；

UNDO表空间可能出现以下问题：

1).空间使用率100%，导致DML操作无法进行。

2).告警日志中出现大量的ORA-01555告警错误。

3).实例恢复失败，数据库无法正常打开。

一.对Oracle自动化管理UNDO进行干预。

由于UNDO是自动化管理，可干预的地方非常的少，更多的是监控，通过以下几个地方可对UNDO表空间实施一定的干预：

1).初始化参数

undo_management=AUTO            表示实例自动化管理UNDO表空间。

undo_retention=900              事务提交后，相应的UNDO数据保留的时间，单位：秒。

undo_tablespace=UNDOTBS1        活动的UNDO表空间。

2).UNDO表空间guarantee属性

如果UNDO表空间是noguarantee状态，Oracle不确保提交后的事务对应的UNDO表空间中的数据会保留UNDO_RETENTION指定的时长，如果UNDO表空间不足，其他事务将可能偷盗相应的未过期的空间；将UNDO表空间设置为guarantee能够确保提交后的事务对应UNDO表空间中的数据在任何情况下都将保留UNDO_RETENTION指定的时长。

SQL> SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                                              RETENTION

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

UNDOTBS1                                                     NOGUARANTEE

SQL> alter tablespace undotbs1 retention guarantee;

表空间已更改。

SQL> SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                                              RETENTION

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

UNDOTBS1                                                     GUARANTEE

3).UNDO表空间大小

针对不同类型的业务系统，需要有充足的UNDO表空间，确保系统能够正常的运行。UNDO空间的大小跟业务系统有关系，也跟UNDO_RETENTION和UNDO表空间的GUARANTEE属性有关系，通常我们可以通过V$UNDOSTAT的统计信息估算出需要的UNDO表空间大小。

二.监控UNDO表空间使用情况。

作为管理员来说，针对UNDO表空间更重要的是日常的监控工作，监控常用到以下的视图：

a).DBA_ROLLBACK_SEGS

DBA_ROLLBACK_SEGS describes rollback segments.

b).V$ROLLSTAT

V$ROLLSTAT contains rollback segment statistics.

c).V$TRANSACTION

V$TRANSACTION lists the active transactions in the system.

d).V$UNDOSTAT

V$UNDOSTAT displays a histogram of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. The view returns NULL values if the system is in manual undo management mode.

Each row in the view keeps statistics collected in the instance for a 10-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the (partial) current time period. The view contains a total of 576 rows, spanning a 4 day cycle.

1.UNDO表空间空间使用情况。

1).UNDO表空间总大小。

   UNDO表空间下也以段的形式存储数据，每个事务对应一个段，这种类型的段通常被称为回滚段，或者UNDO段。默认情况下，数据库实例会初始化10个UNDO段，这主要是为了避免新生成的事务对UNDO段的争用。

UNDO表空间的总大小就是UNDO表空间下的所有数据文件大小的总和：

SQL> select tablespace_name,contents from dba_tablespaces where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                                              CONTENTS

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

UNDOTBS1                                                     UNDO

SQL> select tablespace_name,sum(bytes)/1024/1024 mb from dba_data_files where tablespace_name='UNDOTBS1'  group by tablespace_name;

TABLESPACE_NAME                                                      MB

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

UNDOTBS1                                                             90

2).查看UNDO表空间的使用情况。

    该使用情况可以通过两个视图来查看：

SQL> select owner,segment_name,bytes/1024/1024 mb from dba_segments where tablespace_name='UNDOTBS1';

OWNER      SEGMENT_NAME                           MB

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

SYS        _SYSSMU12_2867006942$                .125

SYS        _SYSSMU11_3120896088$                .125

SYS        _SYSSMU10_1735367849$               2.125

SYS        _SYSSMU9_3051513041$                2.125

SYS        _SYSSMU8_2280151962$                2.125

SYS        _SYSSMU7_825858386$                 .9375

SYS        _SYSSMU6_2597279618$                3.125

SYS        _SYSSMU5_247215464$                 3.125

SYS        _SYSSMU4_437228663$                 2.125

SYS        _SYSSMU3_3104504842$                5.125

SYS        _SYSSMU2_2464850095$                2.125

SYS        _SYSSMU1_2523538120$                3.125

已选择12行。

select segment_name, v.rssize/1024/1024 mb From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+)

order by segment_name ;

SEGMENT_NAME                           MB

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

SYSTEM                           .3671875

_SYSSMU10_1735367849$           2.1171875

_SYSSMU11_3120896088$

_SYSSMU12_2867006942$

_SYSSMU1_2523538120$            3.1171875

_SYSSMU2_2464850095$            2.1171875

_SYSSMU3_3104504842$            5.1171875

_SYSSMU4_437228663$             2.1171875

_SYSSMU5_247215464$             3.1171875

_SYSSMU6_2597279618$            3.1171875

_SYSSMU7_825858386$              .9296875

_SYSSMU8_2280151962$            2.1171875

_SYSSMU9_3051513041$            2.1171875

已选择13行。

    通过上面的两个查询可以看出，两个视图查询的值几乎一致，通常在巡检的时候，我们习惯查询dba_segments视图来确定UNDO表空间的使用情况，但查询V$ROLLSTAT数据更加准确。

3).查询事务使用的UNDO段及大小。

    很多客户想知道，我的UNDO表空间超过了90%，是哪些会话的事务占用了这些空间：

SQL> 

select s.sid,s.serial#,s.sql_id,v.usn,segment_name,r.status, v.rssize/1024/1024 mb

From dba_rollback_segs r, v$rollstat v,v$transaction t,v$session s

Where r.segment_id = v.usn and v.usn=t.xidusn and t.addr=s.taddr

order by segment_name ;

SID    SERIAL# SQL_ID                    USN SEGMENT_NAME                                       STATUS                        MB

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

8        163                                     5 _SYSSMU5_247215464$                                  ONLINE                       3.1171875

    通过这个SQL语句可以查询到会话对应的活动事务使用的UNDO段名称，以及该段占用的UNDO空间大小，对于非活动事务占用了UNDO空间是由Oracle实例根据参数配置自动化管理的。

三.释放UNDO表空间。

    UNDO表空间被撑得过大，有些时候我们需要释放这些空间，通常的做法是新建一个UNDO，然后设置使用新建的UNDO表空间，最后DROP原有UNDO表空间。下面通过一个例子来演示这个过程：

SQL> col segment_name format a30

SQL> col tablespace_name format a30

SQL>

select segment_name, tablespace_name, r.status,

(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,

max_extents, v.curext CurExtent

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn(+)

order by segment_name ;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS                           INITIALEXTENT NEXTEXTENT MAX_EXTENTS  CUREXTENT

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

SYSTEM                         SYSTEM                         ONLINE                                  112            56       32765          4

_SYSSMU10_1735367849$          UNDOTBS1                       ONLINE                                  128            64       32765          2

_SYSSMU11_3120896088$          UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU12_2867006942$          UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU1_2523538120$           UNDOTBS1                       ONLINE                                  128            64       32765          2

_SYSSMU2_2464850095$           UNDOTBS1                       ONLINE                                  128            64       32765          2

_SYSSMU3_3104504842$           UNDOTBS1                       ONLINE                                  128            64       32765          2

_SYSSMU4_437228663$            UNDOTBS1                       ONLINE                                  128            64       32765          2

_SYSSMU5_247215464$            UNDOTBS1                       ONLINE                                  128            64       32765          3

_SYSSMU6_2597279618$           UNDOTBS1                       ONLINE                                  128            64       32765          3

_SYSSMU7_825858386$            UNDOTBS1                       ONLINE                                  128            64       32765          9

_SYSSMU8_2280151962$           UNDOTBS1                       ONLINE                                  128            64       32765          3

_SYSSMU9_3051513041$           UNDOTBS1                       ONLINE                                  128            64       32765          2

已选择13行。

当前所有的回滚段在属于UNDOTBS1表空间。

SQL> create undo tablespace undotbs2 datafile 'E:\APP\ORADATA\ORCL3\undotbs02.dbf' size 20m autoextend on next 100m;

表空间已创建。

SQL> show parameter undo

NAME                                 TYPE                   VALUE

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

undo_management                      string                 AUTO

undo_retention                       integer                900

undo_tablespace                      string                 UNDOTBS1

SQL> alter system set undo_tablespace='UNDOTBS2';

系统已更改。

SQL> show parameter undo

NAME                                 TYPE                   VALUE

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

undo_management                      string                 AUTO

undo_retention                       integer                900

undo_tablespace                      string                 UNDOTBS2

select segment_name, tablespace_name, r.status,

(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,

max_extents, v.curext CurExtent

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn(+)

order by segment_name ;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS                           INITIALEXTENT NEXTEXTENT MAX_EXTENTS  CUREXTENT

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

SYSTEM                         SYSTEM                         ONLINE                                  112            56       32765          5

_SYSSMU10_1735367849$          UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU11_3120896088$          UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU12_2867006942$          UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU13_3398750080$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU14_3208386744$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU15_2082453576$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU16_2746861185$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU17_3752120760$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU18_3475721077$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU19_1407063349$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU1_2523538120$           UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU20_910603223$           UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU21_1261247597$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU22_1117177365$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU2_2464850095$           UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU3_3104504842$           UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU4_437228663$            UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU5_247215464$            UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU6_2597279618$           UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU7_825858386$            UNDOTBS1                       ONLINE                                  128            64       32765          9

_SYSSMU8_2280151962$           UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU9_3051513041$           UNDOTBS1                       OFFLINE                                 128            64       32765

已选择23行。

    虽然将数据库实例使用的UNDO表空间指向了新表空间，但是依然有过去的事务在使用UNDOTBS1表空间下面的段，这个时候不能直接DROP UNDOTBS1，必须等待UNDOTBS1表空间下的所有段状态变成OFFLINE才能DROP。

alter tablespace undotbs1 offline;

SQL>

select segment_name, tablespace_name, r.status,

(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,

max_extents, v.curext CurExtent

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn(+)

order by segment_name

SEGMENT_NAME                   TABLESPACE_NAME                STATUS                           INITIALEXTENT NEXTEXTENT MAX_EXTENTS  CUREXTENT

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

SYSTEM                         SYSTEM                         ONLINE                                  112            56       32765          5

_SYSSMU10_1735367849$          UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU11_3120896088$          UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU12_2867006942$          UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU13_3398750080$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU14_3208386744$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU15_2082453576$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU16_2746861185$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU17_3752120760$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU18_3475721077$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU19_1407063349$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU1_2523538120$           UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU20_910603223$           UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU21_1261247597$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU22_1117177365$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU2_2464850095$           UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU3_3104504842$           UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU4_437228663$            UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU5_247215464$            UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU6_2597279618$           UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU7_825858386$            UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU8_2280151962$           UNDOTBS1                       OFFLINE                                 128            64       32765

_SYSSMU9_3051513041$           UNDOTBS1                       OFFLINE                                 128            64       32765

已选择23行。

    UNDOTBS1表空间下的所有段状态都变成了OFFLINE，这个时候可以DROP UNDOTBS1来释放空间。

SQL> drop tablespace undotbs1 including contents and datafiles;

表空间已删除。

select segment_name, tablespace_name, r.status,

(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,

max_extents, v.curext CurExtent

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn(+)

order by segment_name ;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS                           INITIALEXTENT NEXTEXTENT MAX_EXTENTS  CUREXTENT

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

SYSTEM                         SYSTEM                         ONLINE                                  112            56       32765          5

_SYSSMU13_3398750080$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU14_3208386744$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU15_2082453576$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU16_2746861185$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU17_3752120760$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU18_3475721077$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU19_1407063349$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU20_910603223$           UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU21_1261247597$          UNDOTBS2                       ONLINE                                  128            64       32765          0

_SYSSMU22_1117177365$          UNDOTBS2                       ONLINE                                  128            64       32765          0

已选择11行。

--end--