ITPub博客

首页 > 数据库 > Oracle > Oracle 11.2.0.4 awr过期快照无法自动清理

Oracle 11.2.0.4 awr过期快照无法自动清理

原创 Oracle 作者:dmcatding 时间:2018-11-27 13:28:35 0 删除 编辑

事件:SYSAUX表空间项目存储比重较大



#检查占用空间最大的前20对象

SQL> select * from (select owner,segment_name,partition_name,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 4 desc) where rownum <=20;


#检查策略

SQL> select * from dba_hist_wr_control; 


      DBID SNAP_INTERVAL                                                               RETENTION                                                                   TOPNSQL

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

3560543255 +00000 01:00:00.0                                                           +00008 00:00:00.0                                                           DEFAULT


SQL>  show parameter statistics;


NAME                                 TYPE        VALUE

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

optimizer_use_pending_statistics     boolean     FALSE

statistics_level                     string      TYPICAL

timed_os_statistics                  integer     0

timed_statistics                     boolean     TRUE

SQL> select dbms_stats.get_stats_history_retention from dual;


GET_STATS_HISTORY_RETENTION

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

                         15


#策略检查一切正常,修改统计信息保留10天                  

SQL> exec dbms_stats.alter_stats_history_retention(10);


PL/SQL procedure successfully completed.


SQL> exec dbms_stats.purge_stats(systimestamp -11);


PL/SQL procedure successfully completed.


SQL> select dbms_stats.get_stats_history_retention from dual;


GET_STATS_HISTORY_RETENTION

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

                         10



#创建新的分区

alter session set "_swrf_test_action" = 72; 


#检查分区

SQL>  select table_name,partition_name,high_value from dba_tab_partitions where table_name='WRH$_SYSSTAT';


TABLE_NAME      PARTITION_NAME                 HIGH_VALUE

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

WRH$_SYSSTAT    WRH$_SYSSTAT_MXDB_MXSN         MAXVALUE, MAXVALUE

WRH$_SYSSTAT    WRH$_SYSSTA_3560543255_1322    3560543255, 30532

WRH$_SYSSTAT    WRH$_SYSSTA_3560543255_30532   3560543255, MAXVALUE


#检查快照ID

SQL> select min(snap_id),max(snap_id) from dba_hist_snapshot; 


MIN(SNAP_ID) MAX(SNAP_ID) 

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

30530 30531 


SQL> select min(snap_id),max(snap_id) from wrh$_active_session_history; 


MIN(SNAP_ID) MAX(SNAP_ID) 

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

30530 30531 


SQL> select max(SNAP_ID),min(SNAP_ID) from WRH$_PARAMETER; 


MAX(SNAP_ID) MIN(SNAP_ID) 

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

30531 1322 


SQL> select max(SNAP_ID),min(SNAP_ID) from WRH$_SQLSTAT; 


MAX(SNAP_ID) MIN(SNAP_ID) 

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

30531 1322                   



--检查awr占用情况,发现过期快照无法自动清理Bug 14084247 - ORA-1555 or ORA-12571 Failed AWR purge can lead to continued SYSAUX space use (文档 ID 14084247.8)

@?/rdbms/admin/awrinfo.sql



#统计各个WRH表的最大,最小snap_id

set serveroutput on 

declare 

CURSOR cur_part IS 

SELECT partition_name from dba_tab_partitions 

WHERE table_name = 'WRH$_ACTIVE_SESSION_HISTORY'; 


query1 varchar2(200); 

query2 varchar2(200); 


TYPE partrec IS RECORD (snapid number, dbid number); 

TYPE partlist IS TABLE OF partrec; 


Outlist partlist; 

begin 

dbms_output.put_line('PARTITION NAME SNAP_ID DBID'); 

dbms_output.put_line('--------------------------- ------- ----------'); 


for part in cur_part loop 

query1 := 'select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid'; 

execute immediate query1 bulk collect into OutList; 


if OutList.count > 0 then 

for i in OutList.first..OutList.last loop 

dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid); 

end loop; 

end if; 


query2 := 'select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid'; 

execute immediate query2 bulk collect into OutList; 


if OutList.count > 0 then 

for i in OutList.first..OutList.last loop 

dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid); 

dbms_output.put_line('---'); 

end loop; 

end if; 


end loop; 

end; 

/


PARTITION NAME SNAP_ID DBID

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

WRH$_ACTIVE_3560543255_1322 Min 30530 3560543255

WRH$_ACTIVE_3560543255_1322 Max 30531 3560543255

---

WRH$_ACTIVE_3560543255_30532 Min 30532 3560543255

WRH$_ACTIVE_3560543255_30532 Max 30548 3560543255

---


PL/SQL procedure successfully completed.





#再次清理历史分区的快照

SQL> begin

dbms_workload_repository.drop_snapshot_range(

low_snap_id =>30530,

high_snap_id =>30531,

dbid =>3560543255);

end;

/


PL/SQL procedure successfully completed.


#重启一下MMON刷新:

SQL> alter system set "_swrf_mmon_flush"=false;

System altered.


SQL> alter system set "_swrf_mmon_flush"=true;

System altered.     


#过半小时左右,再次检查过期快照已清理完毕

SQL> select * from (select owner,segment_name,partition_name,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 4 desc) where rownum <=20;



SQL>  select max(SNAP_ID),min(SNAP_ID) from WRH$_PARAMETER; 


MAX(SNAP_ID) MIN(SNAP_ID)

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

       30549        30532


SQL>  select max(SNAP_ID),min(SNAP_ID) from WRH$_SQLSTAT; 


MAX(SNAP_ID) MIN(SNAP_ID)

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

       30549        30532


SQL> select min(snap_id),max(snap_id) from dba_hist_snapshot; 


MIN(SNAP_ID) MAX(SNAP_ID)

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

       30532        30549



SQL> select min(snap_id),max(snap_id) from wrh$_active_session_history; 


MIN(SNAP_ID) MAX(SNAP_ID)

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

       30532        30549


#检查分区已清理 

SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name='WRH$_SYSSTAT';



TABLE_NAME      PARTITION_NAME                 HIGH_VALUE

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

WRH$_SYSSTAT    WRH$_SYSSTAT_MXDB_MXSN         MAXVALUE, MAXVALUE

WRH$_SYSSTAT    WRH$_SYSSTA_3560543255_30532   3560543255, MAXVALUE      


#检查表空间使用率


注释:

1.关于_swrf_test_action参数,由于此参数属于隐含参数,没有明确的文档介绍此参数,只能确认此参数的部分的取值的作用,比如: 
_swrf_test_action = 72;------------>强制分割AWR分区,以用于删除原有分区 
_swrf_test_action = 82;------------->手工重新设置最后一次的AWR清理时间,从而触发MMON进程再次运行 
_swrf_test_action=53;------------->手工刷新dba_feature_usage_statistics信息 
-------------------------------- 
_swrf_test_action = 28;------------->启用MMON的trace 
_swrf_test_action = 10; ------------->启用快照刷新 trace 
---------------------------------- 
_swrf_test_action = 11; ------------->关闭快照刷新 trace 
_swrf_test_action = 29;------------->关闭MMON的trace 
-------------------------------- 
2.

dba_hist_snapshot,

wrh$_active_session_history,

WRH$_PARAMETER,

WRH$_SQLSTAT: 


dba_hist_snapsho视图:记录的是AWR快照的信息 
wrh$_active_session_history表记录的是基于v$active_session_history的信息创建的表 
WRH$_PARAMETER表记录的是基于v$parameter的信息创建的表 
WRH$_SQLSTAT表记录的是v$sql和v$sqltext信息创建的表 
通过分析以上视图和表的DDL语句,他们之间并没有关联,他们都有snap_id列,如果其中任何一个表中的信息清理失败,都会造成snap_id不一致。 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30327022/viewspace-2221760/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-07-04

  • 博文量
    64
  • 访问量
    45949