记一次UNDO表空间超90%的处理

http://blog.csdn.net/ashic/article/details/45009019
今天在为一套库扩表空间时查看到UNDOTBS1/2使用率均超过了90%,监控居然没报警,呵呵,这种花钱买监控还不如Nagios

SQL> select * from v$version;   

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> set linesize 200
SQL> col file_name for a40
SQL> select
  2  a.a1 tabelspace_name,
  3  round(b.b3/1048576,0) table_size_M,
  4  round((b.b3-a.a2)/1048576,0) used_M,
  5  round(a.a2/1048576,0) free_M,
  6  round(substr((b.b3-a.a2)/b.b3*100,1,5),2) used_rate
  7   from
  8  (select  tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,
  9  (select tablespace_name b1,sum(bytes) b3 from dba_data_files group by tablespace_name) b
 10  where a.a1(+)=b.b1
 11  order by 5 desc;

TABELSPACE_NAME                TABLE_SIZE_M     USED_M     FREE_M  USED_RATE
------------------------------ ------------ ---------- ---------- ----------
UNDOTBS1                               5000       1009       3991      97.33
UNDOTBS2                               5000        835       4165      91.59
SYSAUX                                 5000       3738       1262      74.75
MNTDATA                              153600     110900      42700       72.2
SYSTEM                                 5000        286       4714       5.72
USERS                                   500          2        498        .36
MNTINDEX                              30720          1      30719          0

查看unexpired和expired的大小

SQL> select tablespace_name,sum(bytes/1024/1024) from dba_undo_extents where status!='ACTIVE' group by tablespace_name;

TABLESPACE_NAME                count(bytes/1024/1024)
------------------------------ ----------------
UNDOTBS1                                    4866
UNDOTBS2                                    4579

actived一个没有,全是expired和unexpired的

SQL> select tablespace_name,sum(bytes/1024/1024) from dba_undo_extents where status='ACTIVE' group by tablespace_name;

no rows selected

SQL>show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

undo retention为900
UNDO表空间并没有设置成GUARANTEE模式，所以根据我们的知识都明白UNDO表空间中的EXPIRED和UNEXPIRED都是可能被重用的

SQL> select undotsn,activeblks,expiredblks,unexpiredblks,tuned_undoretention from v$undostat;

   UNDOTSN ACTIVEBLKS EXPIREDBLKS UNEXPIREDBLKS TUNED_UNDORETENTION
---------- ---------- ----------- ------------- -------------------
         4        464       84016         22272                2361
         4        464       84016         22272                2300
         4        464       83888         23680                1985
         4        464       85936         20992                2296
         4        464       82992         26240                1694
         4        464      280840         13056                2579
         4        464      281352         11648                2287
         4        464      123384        462216                2574
         4        464      125040        460560              176361
         4        464      125144        460456              176482
         4        464      125120        460480              176583


   UNDOTSN ACTIVEBLKS EXPIREDBLKS UNEXPIREDBLKS TUNED_UNDORETENTION
---------- ---------- ----------- ------------- -------------------
         4        464       70864        456752              226010
         4        464       68688        456880              225899
         4        464       69072        456496              225960
         4        464       66768        458800              225860
         4        464       66768        458800              225608
         4        464       66896        458672              225628
         4        464       65744        459824              226171
         4        464       67920        457648              225325
         4        464       64464        461104              225395
         4        464       64720        460848              225330
         4        464       64720        460848              225049

   UNDOTSN ACTIVEBLKS EXPIREDBLKS UNEXPIREDBLKS TUNED_UNDORETENTION
---------- ---------- ----------- ------------- -------------------
         4        464       66768        459824              225003
         4        464       71888        459952              225888
         4        464       71888        458928              225985
         4        464       71120        459696              226087
         4        464       70096        460720              225874
         4        464       70992        459824              225904
         4        464       69072        461744              225913
         4        464       69072        460720              225672
         4        464       67920        461872              225690
         4        464       65872        463920              225357
         4        464       65872        463920              225166
…

576 rows selected.

TUNED_UNDORETENTION被自动调整了

SQL> select 224997/3600 from dual;

224997/3600
-----------
 62.4991667

也就是说UNDO表空间中的数据要保留接近62小时才会过期，正是因为这么长的数据未过期时间，且表空间又足够的大，才导致了UNDO表空间的空间一致未被释放

正常情况下，如果undo 表空间被设置为固定大小，不自动扩展，>oracle会启用Automatic Tuning of undo retention特性。
启用Automatic Tuning of undo retention时，oracle会忽略undo_retention的设置，根据undo表空间大小、系统负载情况，自动调整undo_retention为一个合适的值。这个值一般会大于“所有事务的最长运行时间”。
10gR2的bug现象为，只要设置了undo表空间自动管理，不管有没开自动扩展，不管undo_retention设置为多少，都会启用 Automatic Tuning of undo retention的新特性。

解决方法：

1).将UNDO表空间对应的数据文件调整为自动扩展，并为其设定一个最大值。

SQL> ALTER DATABASE DATAFILE '<datafile_flename>' AUTOEXTEND ON MAXSIZE <current_size>

正是通过这种方式解决了问题，调整之后空间很快得到释放，V$UNDOSTAT.TUNED_UNDORETENTION值立即变小，这和文章前面的解释是完全吻合的，当UNDO表空间对应的数据文件是自动扩展的，那么V$UNDOSTAT.TUNED_UNDORETENTION值的计算就不再依赖于UNDO表空间的百分比（UNDO表空间本身较大）。

2).设置_smu_debug_mode隐藏参数。
_smu_debug_mode=33554432
前面我们已经对这个参数进行了解释，这里再次验证。

3).设置_undo_autotune隐藏参数。
_undo_autotune = false

前面的两种方法没有关闭Oracle的UNDO自动调整RETENTION的功能，将_undo_autotune设置为false，就彻底关闭了自动调整UNDO RETENTION的功能，那么UNDO的RETENTION时间完全依赖于初始化参数UNDO_RETENTION的值，默认值为900秒。

这里我通过第一种方法
alter database datafile ‘+DGSYS/undotbs101.dbf’ autoextend on maxsize 5000m;
alter database datafile ‘+DGSYS/undotbs201.dbf’ autoextend on maxsize 5000m;

过一段时间后查看

SQL> select
  2  a.a1 tabelspace_name,
  3  round(b.b3/1048576,0) table_size_M,
  4  round((b.b3-a.a2)/1048576,0) used_M,
  5  round(a.a2/1048576,0) free_M,
  6  round(substr((b.b3-a.a2)/b.b3*100,1,5),2) used_rate
  7   from
  8  (select  tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,
  9  (select tablespace_name b1,sum(bytes) b3 from dba_data_files group by tablespace_name) b
 10  where a.a1(+)=b.b1
 11  order by 5 desc;

TABELSPACE_NAME                TABLE_SIZE_M     USED_M     FREE_M  USED_RATE
------------------------------ ------------ ---------- ---------- ----------
SYSAUX                                 5000       3738       1262      74.75
MNTDATA                              153600     110900      42700       72.2
UNDOTBS1                               5000       1009       3991      20.18
UNDOTBS2                               5000        835       4165       16.7
SYSTEM                                 5000        286       4714       5.72
USERS                                   500          2        498        .36
MNTINDEX                              30720          1      30719          0

空间释放了