oracle sysaux扩大,sysaux表空间非常大，怎么处理 - Oracle数据库管理 - Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀...

WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged (Doc ID 387914.1)

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 is deciding 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. For the large AWR tables, we store the snapshot data into partitions. Purging data from these tables means that a partition gets dropped. 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.

Based on the above clarifications we can say that if anything is going wrong with the partition split operation then we will end up by having the AWR tables not to be purged.

The root cause for the problems can be diagnosed by triggering the same operations which are executed each time the purge job is fired.

Solution

1) Check how many partitions do exist for the offending table :

select table_name,partition_name

from dba_tab_partitions

where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';

2) Try to force the execution of the purge operations :

alter session set "_swrf_test_action" = 72;

This step can finish successfully :

Statement processed

or it can fail:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-13509: error encountered during updates to a AWR table

ORA-14327: Some index [sub]partitions could not be rebuilt

3) If the operation on step 2 is failing the generated (udump) trace file must be analyzed. In the trace file a partition split statement can be found :

*** KEWROCISTMTEXEC - encountered error: (ORA-14327: Some index [sub]partitions could not be rebuilt

ORA-20001: Component "WRH$_SQLSTA_2446360981_11691" does not exist on index "WRH$_SQLSTAT_INDEX" in schema "SYS".

ORA-06512: at "SYS.DBMS_I_INDEX_UTL", line 748

ORA-06512: at "SYS.DBMS_INDEX_UTL", line 397

ORA-06512: at "SYS.DBMS_INDEX_UTL", line 556

ORA-06512: at line 1

)

*** SQLSTR: total-len=228, dump-len=228,

STR={alter table WRH$_SQLSTAT split partition

WRH$_SQLSTA_2446360981_11691 at (2446360981,12774) into (partition

WRH$_SQLSTA_2446

360981_11691 tablespace SYSAUX, partition WRH$_SQLSTA_2446360981_12774

tablespace SYSAUX) update indexes}

The split statement must be executed manually :

alter table WRH$_SQLSTAT split partition WRH$_SQLSTA_2446360981_11691 at (2446360981,12774)

into (partition WRH$_SQLSTA_2446 360981_11691 tablespace SYSAUX, partition WRH$_SQLSTA_2446360981_12774 tablespace SYSAUX)

update indexes;

4) Steps 2 and 3 must be executed until setting the event is successful.

5) Check how many partitions do exist for the offending table after all the above actions :

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;

/

We should have more partitions now and it is expected that the old partitions will get automatically removed when the retention period will expire.

Once you have identified, and split the partitions, you may want to drop a partition range, by dropping a snapshot range:

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(

low_snap_id IN NUMBER,

high_snap_id IN NUMBER

dbid IN NUMBER DEFAULT NULL);