oracle sysaux清理,【原】11g 数据库清理SYSAUX表空间中 清理过期的快照

11g 数据库清理SYSAUX表空间中 清理过期的快照

In this Document

This document is

being delivered to you via Oracle Support's Rapid Visibility (RaV) process

and therefore has not been subject to an independent technical review.

APPLIES TO:

Oracle

Database - Enterprise Edition - Version 10.2.0.1 and laterInformation in this document applies to any

platform.

***Checked for relevance on 25-Apr-2014***

SYMPTOMS

AWR tables are not

being purged according to settings in sys.wrm$_wr_control. Because of this the

tables are accumulating more and more rows and the segments associated with

these tables become very large.

CAUSE

Oracle decides

what rows need to be purged based on the retention policy. There is a special

mechanism which is used in the case of the large AWR tables where we store the

snapshot data in partitions. One method of purging data from these tables is by

removing partitions that only contain rows that have exceeded the retention

criteria. During the nightly purge task, we only drop the partition if all the

data in the partition has expired. If the partition contains at least one row

which, according to the retention policy shouldn't be removed,  then

the partition won't be dropped and as such the table will contain old data.

If partition

splits do not occur (for whatever reason), then we can end up with a situation

where we have to wait for the latest entries to expire before the partition

that they sit in can be removed. This can mean that some of the older entries

can be retained significantly past their expiry date. The result of this is

that the data is not purged as expected.

SOLUTION

A potential

solution to this issue is to manually split the partitions of the partitioned

AWR objects such that there is more chance of the split partition being

purged.You will still have to wait for all the rows in the new partitions to

reach their retention time but with split partitions there is more chance of

this happening. you can manually split the partitions using the following

undocumented command:

alter session set

"_swrf_test_action" = 72;

to perform a

single split of all the AWR partitions.

Check the partition details for the

offending table before the split:检查分区之前的分区详情

SELECT

owner,

segment_name,

partition_name,

segment_type,

bytes/1024/1024/1024 Size_GB

FROM dba_segments

WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';

Split the partitions so that there is more chance

of the smaller partition being purged:分裂分区以致于有更多的机会使更小的分区保持Purged；

alter session set

"_swrf_test_action" = 72;

Note that this command will split partitions for ALL partitioned AWR

objects. It also initiates a single split; it does not need to be disabled and

will need to be repeated if multiple splits are required.提示：该命令将会分裂AWR对象的所有分区。它会启动一个单分区；如果需要多个分裂的话它不应该被禁止并且需要被重复。

Check the partition details for the

offending table after the split:检查分区之后的分区详情

SELECT owner,

segment_name,

partition_name,

segment_type,

bytes/1024/1024/1024 Size_GB

FROM dba_segments

WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';

With smaller partitions it is expected that some will be automatically removed

when the retention period of all the rows within each partition is reached.(对于那些在查找到保留时间之前的分区中所有行中的一些更小的分区将自动移除)

As an alternative, you could purge data based upon a snapshot range. Depending

on the snapshots chosen, this may remove data that has not yet reached the

retention limit so this may not be suitable for all cases. The following output

shows the min and max snapshot_id in each partition.(另一种替代方法，你可以基于一个快照范围删除数据。依靠快照的选择，该方法可能移除尚未达到保留限制的数据，因此不适合所有的cases。下列会列出所有的在每个分区中最小和最大的快照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;

/

Once you have split the partitions and identified a partition with a range of

snap ids that can be deleted, you can free up the memory by dropping a snapshot

range than matches the high and low snap_ids for the partition:(一旦你已分裂和分区并且使用snap ids范围标记一个可删除的分区，你可以通过删除一个快照范围通过最高和最低的snap_ids确定的分区匹配来释放内存。)

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(

low_snap_id IN NUMBER,

high_snap_id IN NUMBER

dbid IN NUMBER DEFAULT NULL);

2016-1-13   于北京邮储

王慧  tyger.wang