检查及设置合理的undo表空间

      UNDO是用于实现并发控制以及构建一致性读，也就是在数据变更之前产生前镜像，以保证用户能够回滚或撤销对数据库所作的修改。是Oracle数据库完整性的重要组成部分。因此合理的设计及配置undo以及使用undo都将对数据库有较大的影响。通常情况下，对于大规模数据的删除，更新操作，我们建议使用分批删除分次提交以减少对undo的占用和冲击。那么对于undo的大小到底应该设置多大？是启用自动扩展还是关闭自动扩展？这个问题仁者见仁，智者见智，见下文。

 

1、当前数据库环境及undo配置信息

sys@SYTST> select * from v$version where rownum<2;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.3.0 - 64bit Productionsys@SYTST> show parameter undoNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------undo_management                      string      AUTOundo_retention                       integer     900undo_tablespace                      string      UNDOTBSsys@SYTST> select tablespace_name,file_name,AUTOEXTENSIBLE,bytes/1024/1024 size_mb  2  from dba_data_files where tablespace_name like '%UNDO%';TABLESPACE_NAME                FILE_NAME                                               AUT    SIZE_MB------------------------------ ------------------------------------------------------- --- ----------UNDOTBS                        /u02/database/SYTST/undo/undoSYTST1.dbf                 NO          20--创建演示表tsys@SYTST> CREATE TABLE t  2  AS  3  SELECT rownum AS id,  4         round(5678+dbms_random.normal*1234) AS n1,  5         mod(255+trunc(dbms_random.normal*1000),255) AS n2,  6         dbms_random.string('p',255) AS pad  7  FROM dual  8  CONNECT BY level <= 100000  9  ORDER BY dbms_random.value;Table created.--直接用脚本得到undo的信息及建议值sys@SYTST> @chk_advs_undoSession altered.- Undo Analysis started at : 24/10/2013 14:39:58 ---------------------------------------------------NOTE:The following analysis is based upon the database workload during the period -Begin Time : 17/10/2013 14:39:58End Time   : 24/10/2013 14:39:58Current Undo Configuration--------------------------Current undo tablespace                                 : UNDOTBSCurrent undo tablespace size (datafile size now)        : 20MCurrent undo tablespace size (consider autoextend)      : 20MAUTOEXTEND for undo tablespace is                       : OFFCurrent undo retention                                  : 900UNDO GUARANTEE is set to                                : FALSEUndo Advisor Summary---------------------------Finding 1:The undo tablespace is OK.    --->当前的undo配置合理Undo Space Recommendation-------------------------Allocated undo space is sufficient for the current workload.Retention Recommendation------------------------The best possible retention with current configuration is    : 5996 SecondsThe longest running query ran for                            : 52 SecondsThe undo retention required to avoid errors is               : 52 SecondsPL/SQL procedure successfully completed.

2、模拟undo超出并获得建议值

--先查看当前已产生的undosys@SYTST> @mystat "undo change"sys@SYTST> set echo offNAME                                                                  VALUE---------------------------------------------------------------- ----------undo change vector size                                              363568--我们来更新之前创建表t上的pad列sys@SYTST> update t set pad=dbms_random.string('l',255);update t set pad=dbms_random.string('l',255)       *ERROR at line 1:ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'   --->得到了错误提示，无法扩展undo，因为当前环境undo未启用自动扩展--Author : Leshami--Blog   : http://blog.csdn.net/leshami--看看update语句到底产生了多少undosys@SYTST> @mystat2  sys@SYTST> set echo offNAME                                                                      V DIFF---------------------------------------------------------------- ---------- ----------------undo change vector size                                            18817316       18,453,748--由下可知，上面的update语句已经产生了17M以上的undo,而当前的undo的大小为20M，显然不够，所以我们收到了ORA-30036sys@SYTST> select 18453748/1024/1024 from dual;18453748/1024/1024------------------        17.5988655--再次获得undo建议值  sys@SYTST> @chk_advs_undoSession altered.- Undo Analysis started at : 24/10/2013 14:49:07 ---------------------------------------------------NOTE:The following analysis is based upon the database workload during the period -Begin Time : 17/10/2013 14:49:07End Time   : 24/10/2013 14:49:07Current Undo Configuration--------------------------Current undo tablespace                                 : UNDOTBSCurrent undo tablespace size (datafile size now)        : 20MCurrent undo tablespace size (consider autoextend)      : 20MAUTOEXTEND for undo tablespace is                       : OFFCurrent undo retention                                  : 900UNDO GUARANTEE is set to                                : FALSEUndo Advisor Summary---------------------------Finding 1:Undo Tablespace is under pressure. Recommendation 1:Size undo tablespace to 48 MBUndo Space Recommendation-------------------------Minimum Recommendation           : Size undo tablespace to 48 MBRationale                        : Increase undo tablespace size so that long running queries will not failRecommended Undo Tablespace Size : 48M    --->这里给出了undo的建议值Retention Recommendation------------------------                  --->下面是一些和undo相关的建议，如可能的保留时间等等The best possible retention with current configuration is    : 306 SecondsThe longest running query ran for                            : 52 SecondsThe undo retention required to avoid errors is               : 52 SecondsPL/SQL procedure successfully completed.

3、获得undo信息及建议值脚本 

--脚本来自Oracle. 参考Metalink: Doc ID 1579035.1 --以下脚本适用于Oracle 10g以上版本SET SERVEROUTPUT ONSET LINES 600ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';DECLARE    v_analyse_start_time    DATE := SYSDATE - 7;    v_analyse_end_time      DATE := SYSDATE;    v_cur_dt                DATE;    v_undo_info_ret         BOOLEAN;    v_cur_undo_mb           NUMBER;    v_undo_tbs_name         VARCHAR2(100);    v_undo_tbs_size         NUMBER;    v_undo_autoext          BOOLEAN;    v_undo_retention        NUMBER(6);    v_undo_guarantee        BOOLEAN;    v_instance_number       NUMBER;    v_undo_advisor_advice   VARCHAR2(100);    v_undo_health_ret       NUMBER;    v_problem               VARCHAR2(1000);    v_recommendation        VARCHAR2(1000);    v_rationale             VARCHAR2(1000);    v_retention             NUMBER;    v_utbsize               NUMBER;    v_best_retention        NUMBER;    v_longest_query         NUMBER;    v_required_retention    NUMBER;BEGIN    select sysdate into v_cur_dt from dual;    DBMS_OUTPUT.PUT_LINE(CHR(9));    DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');    DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');    v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);    select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;    DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');    DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);    DBMS_OUTPUT.PUT_LINE('End Time   : ' || v_analyse_end_time);        DBMS_OUTPUT.PUT_LINE(CHR(9));    DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');    DBMS_OUTPUT.PUT_LINE('--------------------------');    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');    IF V_UNDO_AUTOEXT THEN        DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');      ELSE        DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');      END IF;    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);    IF v_undo_guarantee THEN        DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');    ELSE        dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');    END IF;    DBMS_OUTPUT.PUT_LINE(CHR(9));    SELECT instance_number INTO v_instance_number FROM V$INSTANCE;    DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');    DBMS_OUTPUT.PUT_LINE('---------------------------');    v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);    DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);    DBMS_OUTPUT.PUT_LINE(CHR(9));    DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');    DBMS_OUTPUT.PUT_LINE('-------------------------');    v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);    IF v_undo_health_ret > 0 THEN        DBMS_OUTPUT.PUT_LINE('Minimum Recommendation           : ' || v_recommendation);        DBMS_OUTPUT.PUT_LINE('Rationale                        : ' || v_rationale);        DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');    ELSE        DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');    END IF;        SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;    SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;    SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;    DBMS_OUTPUT.PUT_LINE(CHR(9));    DBMS_OUTPUT.PUT_LINE('Retention Recommendation');    DBMS_OUTPUT.PUT_LINE('------------------------');    DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');    DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');    DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');END;/

4、后记
a、undo究竟要多大，推荐的做法是根据你的业务高峰期得到一个峰值，然后适当高于峰值来启用自动扩展。
b、undo保留时间与所需的空间成正比，保留时间越长，空间开销越大，不过出现ORA-01555的几率也就越低。
c、在未启用自动扩展的情形下，对于新的事务，UNDO_RETENTION会回收空闲空间，并重新使用，如果启用自动扩展，则该功能被忽略，而是先扩展。
d、小的undo表空间(数据文件)通常情况下对于数据库及操作系统影响更小，具有更好的性能以及减少备份所需的开销等。

 

    

更多参考

有关Oracle RAC请参考
     使用crs_setperm修改RAC资源的所有者及权限
     使用crs_profile管理RAC资源配置文件
     RAC 数据库的启动与关闭
     再说 Oracle RAC services
     Services in Oracle Database 10g
     Migrate datbase from single instance to Oracle RAC
     Oracle RAC 连接到指定实例
     Oracle RAC 负载均衡测试(结合服务器端与客户端)
     Oracle RAC 服务器端连接负载均衡(Load Balance)
     Oracle RAC 客户端连接负载均衡(Load Balance)
     ORACLE RAC 下非缺省端口监听配置(listener.ora tnsnames.ora) 
     ORACLE RAC 监听配置 (listener.ora tnsnames.ora) 
     配置 RAC 负载均衡与故障转移
     CRS-1006 , CRS-0215 故障一例 
     基于Linux (RHEL 5.5) 安装Oracle 10g RAC 
     使用 runcluvfy 校验Oracle RAC安装环境

有关Oracle 网络配置相关基础以及概念性的问题请参考：
     配置非默认端口的动态服务注册 
     配置sqlnet.ora限制IP访问Oracle 
     Oracle 监听器日志配置与管理 
     设置 Oracle 监听器密码(LISTENER)
     配置ORACLE 客户端连接到数据库

有关基于用户管理的备份和备份恢复的概念请参考
     Oracle 冷备份
     Oracle 热备份
     Oracle 备份恢复概念
     Oracle 实例恢复
     Oracle 基于用户管理恢复的处理
     SYSTEM 表空间管理及备份恢复
     SYSAUX表空间管理及恢复
     Oracle 基于备份控制文件的恢复(unsing backup controlfile)

有关RMAN的备份恢复与管理请参考
     RMAN 概述及其体系结构
     RMAN 配置、监控与管理
     RMAN 备份详解
     RMAN 还原与恢复
     RMAN catalog 的创建和使用
     基于catalog 创建RMAN存储脚本
     基于catalog 的RMAN 备份与恢复
     RMAN 备份路径困惑
     使用RMAN实现异机备份恢复(WIN平台)
     使用RMAN迁移文件系统数据库到ASM
     linux 下RMAN备份shell脚本
     使用RMAN迁移数据库到异机

有关ORACLE体系结构请参考
     Oracle 表空间与数据文件
     Oracle 密码文件
     Oracle 参数文件
     Oracle 联机重做日志文件(ONLINE LOG FILE)
     Oracle 控制文件(CONTROLFILE)
     Oracle 归档日志
     Oracle 回滚(ROLLBACK)和撤销(UNDO)
     Oracle 数据库实例启动关闭过程
     Oracle 10g SGA 的自动化管理
     Oracle 实例和Oracle数据库(Oracle体系结构)