Today noticed SYSAUX tablespace crossed 80% usage limit. First few things came in mind was
1] SYSAUX is used by some DBA collegues.
2] New database features are installed
3] AWR retention might be too big
4] Snapshot frequency is altered/increased
On examiniation none of above was true. In fact, AWR retention was just 8 days.
Found link to metalink document
WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged [ID 387914.1]
Folowed this document but setting event as per doc never errored out and it was always successful. So this document scenerio is different from the one I am facing
SNAP_INTERVAL RETENTION MOST_RECENT_PURGE_TIME
-------------------- -------------------- ---------------------------------
+00000 00:30:00.0 +00008 00:00:00.0 23-JUL-12 10.06.25.744 PM
select INSTANCE_NUMBER,SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME
from DBA_HIST_SNAPSHOT
where SNAP_ID=(select min(SNAP_ID) from DBA_HIST_SNAPSHOT);
INSTANCE_NUMBER SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
--------------- ---------- ------------------------- -------------------------
1 25385 03-MAY-12 01.45.05.264 PM 03-MAY-12 02.00.00.499 PM
2 25385 03-MAY-12 01.45.05.144 PM 03-MAY-12 02.00.00.653 PM
3 25385 03-MAY-12 01.45.05.268 PM 03-MAY-12 02.00.00.863 PM
4 25385 03-MAY-12 01.45.05.262 PM 03-MAY-12 02.00.00.630 PM
It took very long to delete this snapshot but along with it, it also started purging old data.
SQL>select INSTANCE_NUMBER,min(SAMPLE_TIME) from sys.WRH$_ACTIVE_SESSION_HISTORY
2 group by INSTANCE_NUMBER
3 order by 1;
INSTANCE_NUMBER MIN(SAMPLE_TIME)
--------------- --------------------------------
1 10-APR-12 03.30.07.620 PM
2 10-APR-12 03.30.13.158 PM
3 10-APR-12 03.15.01.995 PM
4 10-APR-12 03.30.07.117 PM
SQL>/
INSTANCE_NUMBER MIN(SAMPLE_TIME)
--------------- --------------------------------
1 11-APR-12 04.59.56.218 PM
2 13-APR-12 05.30.30.874 AM
3 13-APR-12 06.59.07.517 AM
4 11-APR-12 04.54.07.145 PM
SQL>/
INSTANCE_NUMBER MIN(SAMPLE_TIME)
--------------- --------------------------------
1 13-APR-12 06.31.06.346 PM
2 13-APR-12 06.44.59.087 PM
3 13-APR-12 07.01.01.946 PM
4 13-APR-12 09.16.29.905 PM
SQL>/
INSTANCE_NUMBER MIN(SAMPLE_TIME)
--------------- --------------------------------
1 14-APR-12 07.14.42.125 AM
2 14-APR-12 06.14.46.841 AM
3 14-APR-12 06.00.13.102 AM
4 14-APR-12 06.15.20.638 AM
SQL>/
INSTANCE_NUMBER MIN(SAMPLE_TIME)
--------------- --------------------------------
1 03-MAY-12 01.44.56.561 PM
2 03-MAY-12 01.45.01.572 PM
3 03-MAY-12 01.44.59.825 PM
4 03-MAY-12 01.45.03.027 PM
1] SYSAUX is used by some DBA collegues.
2] New database features are installed
3] AWR retention might be too big
4] Snapshot frequency is altered/increased
On examiniation none of above was true. In fact, AWR retention was just 8 days.
Found link to metalink document
WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged [ID 387914.1]
Folowed this document but setting event as per doc never errored out and it was always successful. So this document scenerio is different from the one I am facing
AWR Retention
select snap_interval, retention, most_recent_purge_time from sys.wrm$_wr_control;SNAP_INTERVAL RETENTION MOST_RECENT_PURGE_TIME
-------------------- -------------------- ---------------------------------
+00000 00:30:00.0 +00008 00:00:00.0 23-JUL-12 10.06.25.744 PM
select INSTANCE_NUMBER,SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME
from DBA_HIST_SNAPSHOT
where SNAP_ID=(select min(SNAP_ID) from DBA_HIST_SNAPSHOT);
INSTANCE_NUMBER SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
--------------- ---------- ------------------------- -------------------------
1 25385 03-MAY-12 01.45.05.264 PM 03-MAY-12 02.00.00.499 PM
2 25385 03-MAY-12 01.45.05.144 PM 03-MAY-12 02.00.00.653 PM
3 25385 03-MAY-12 01.45.05.268 PM 03-MAY-12 02.00.00.863 PM
4 25385 03-MAY-12 01.45.05.262 PM 03-MAY-12 02.00.00.630 PM
Strange observation
As per DBA_HIST_SNAPSHOT, DBA_HIST_ASH_SNAPSHOT oldest snapshot was of 03-MAY-12 but as per below query oldest retained data was of 07-APR-12
select INSTANCE_NUMBER,min(snap_id),min(SAMPLE_TIME) from sys.WRH$_ACTIVE_SESSION_HISTORY group by INSTANCE_NUMBER;
INSTANCE_NUMBER MIN(SNAP_ID) MIN(SAMPLE_TIME)
--------------- ------------ -------------------------------------------------
3 22913 07-APR-12 07.59.59.976 PM
2 22913 07-APR-12 08.00.05.535 PM
1 22913 07-APR-12 08.00.04.182 PM
4 22913 07-APR-12 08.00.00.830 PM
--------------- ------------ -------------------------------------------------
3 22913 07-APR-12 07.59.59.976 PM
2 22913 07-APR-12 08.00.05.535 PM
1 22913 07-APR-12 08.00.04.182 PM
4 22913 07-APR-12 08.00.00.830 PM
Action Taken
exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(22913,22913);It took very long to delete this snapshot but along with it, it also started purging old data.
SQL>select INSTANCE_NUMBER,min(SAMPLE_TIME) from sys.WRH$_ACTIVE_SESSION_HISTORY
2 group by INSTANCE_NUMBER
3 order by 1;
INSTANCE_NUMBER MIN(SAMPLE_TIME)
--------------- --------------------------------
1 10-APR-12 03.30.07.620 PM
2 10-APR-12 03.30.13.158 PM
3 10-APR-12 03.15.01.995 PM
4 10-APR-12 03.30.07.117 PM
SQL>/
INSTANCE_NUMBER MIN(SAMPLE_TIME)
--------------- --------------------------------
1 11-APR-12 04.59.56.218 PM
2 13-APR-12 05.30.30.874 AM
3 13-APR-12 06.59.07.517 AM
4 11-APR-12 04.54.07.145 PM
SQL>/
INSTANCE_NUMBER MIN(SAMPLE_TIME)
--------------- --------------------------------
1 13-APR-12 06.31.06.346 PM
2 13-APR-12 06.44.59.087 PM
3 13-APR-12 07.01.01.946 PM
4 13-APR-12 09.16.29.905 PM
SQL>/
INSTANCE_NUMBER MIN(SAMPLE_TIME)
--------------- --------------------------------
1 14-APR-12 07.14.42.125 AM
2 14-APR-12 06.14.46.841 AM
3 14-APR-12 06.00.13.102 AM
4 14-APR-12 06.15.20.638 AM
SQL>/
INSTANCE_NUMBER MIN(SAMPLE_TIME)
--------------- --------------------------------
1 03-MAY-12 01.44.56.561 PM
2 03-MAY-12 01.45.01.572 PM
3 03-MAY-12 01.44.59.825 PM
4 03-MAY-12 01.45.03.027 PM