希望所有数据库的闪回时间可以保证24小时，也就是说对数据库的任何操作：delete ，update，insert 都可以使用闪回查询，找到最近24内的任何时间段的一致性数据。仔细想想这个需求是绝对无法完全保证的，除非我们的undo表空间非常大，至少要和数据库一样大，这样才能保证，在极端情况下（对数据库所有表delete or update 一次），并且此后不在有任何操作，如果中途还有额外几次操作，就会导致undo 数据被覆盖，flashback table 失败。当然在一般正常业务情况还是可以实现的。
设置步骤：
-----设置undo保留时间为24小时：24*3600=86400 秒
alter system set undo_retention=86400;
----使用 undo advice 来评估需要的undo 空间大小：（）
select dbms_undo_adv.required_undo_size(86400) from dual;
tips：
- Version 1: subprogram is based on historicalinformation in memory or in AWR from start time to end time.
example : select dbms_undo_adv.required_undo_size(retention , start time , endtime ) from dual;
- Version 2: subprogram is based on historical information in memory or in AWRfrom sysdate -7 to sysdate.---- 建议使用这个version，分析结果更正确
example : select dbms_undo_adv.required_undo_size(retention ) from dual;
- Version 3: subprogram is based on historical information in AWR fromBegin/End snapshot id.
example : select dbms_undo_adv.required_undo_size(retention , snap_shot1 ,snap_shot2 ) from dual;
当然有时候我们也需要评估我们系统的健康状态，尽可能避免一些常规问题 如 ora-01555 快照太旧。问题的原因无非就是undo 不够大，无法构建一致性数据。
MOS 提供了一个分析脚本，但是核心还是基于dbms_undo_adv 这个包
报告的分析的结果是根据最近7天的信息来分析
######################
#Oracle 官方提供的一个undo 配置监测脚本
for >=10g
#####################
SET SERVEROUTPUT ON
SET LINES 600
ALTER 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(5);
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;
/
##############################################################################################
运行结果样例1：
- Undo Analysis started at : 12/10/2015 10:49:10 -
--------------------------------------------------
NOTE:The following analysis is based upon the database workload during the period -
Begin Time : 05/10/2015 10:49:10
End Time : 12/10/2015 10:49:10
Current Undo Configuration
--------------------------
Current undo tablespace : UNDOTBS1
Current undo tablespace size (datafile size now) : 26000M
Current undo tablespace size (consider autoextend) : 32768M
AUTOEXTEND for undo tablespace is : ON
Current undo retention : 86400
UNDO GUARANTEE is set to : FALSE
Undo Advisor Summary
---------------------------
Finding 1:The undo tablespace is OK. --------检测结果正常
Undo Space Recommendation
-------------------------
Allocated undo space is sufficient for the current workload. --------检测结果正常，当前undo 空间满足于当前负载。
Retention Recommendation
------------------------
The best possible retention with current configuration is : 35552306 Seconds
The longest running query ran for : 147 Seconds
The undo retention required to avoid errors is : 147 Seconds
PL/SQL procedure successfully completed.
undo配置 不符合 oracle 官方建议的样例结果2：
- Undo Analysis started at : 12/10/2015 14:55:46 -
--------------------------------------------------
NOTE:The following analysis is based upon the database workload during the period -
Begin Time : 05/10/2015 14:55:45
End Time : 12/10/2015 14:55:45
Current Undo Configuration
--------------------------
Current undo tablespace : UNDOTBS2
Current undo tablespace size (datafile size now) : 100M
Current undo tablespace size (consider autoextend) : 100M
AUTOEXTEND for undo tablespace is : OFF
Current undo retention : 0
UNDO GUARANTEE is set to : FALSE
Undo Advisor Summary
---------------------------
Finding 1:Undo Tablespace is under pressure. Recommendation 1:Size undo tablespace to 137 MB -----检测发现undo 空间不足，建议设置137M
Undo Space Recommendation
-------------------------
Minimum Recommendation : Size undo tablespace to 137 MB
Rationale : Increase undo tablespace size so that long running queries will not fail
Recommended Undo Tablespace Size : 137M
Retention Recommendation
------------------------
The best possible retention with current configuration is : 448 Seconds
The longest running query ran for : 910 Seconds
The undo retention required to avoid errors is : 910 Seconds
PL/SQL procedure successfully completed.
99
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
