Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]


My Cloud Certifications:

AWS Certified Solutions Architect Associate

Oracle Cloud Infrastructure 2018 Certified Architect Associate.

Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.

Oracle Database Cloud Administrator Certified Professional.

Oracle Database Cloud Service Operations Certified Associate.

Search This Blog

Wednesday, May 4, 2011

SHRINK SYSAUX AWR SPACE

SELECT occupant_name, schema_name, space_usage_kbytes/1024/1024 FROM v$sysaux_occupants;

OCCUPANT_NAME SCHEMA SIZE_IN_GB


SM/ADVISOR SYS 0.821045
SM/OPTSTAT SYS 1.971924
SM/AWR SYS 46.2876


Here clearly it shows AWR is occupying more than 46g. How can I reclaim this space back?

SQL> select * from DBA_HIST_WR_CONTROL;

DBID snap_interval retention topnsql
3861913925 +00 00:15:00.000000 +14 00:00:00.000000 DEFAULT

 shows retention as 14 days @ 15 mins time interval . Looks normal here so check further

If you ever want to change the retention then use below:

SQL> execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 5760);

Here 4320 is 4*24*60 ( Convert the days to minutes). For 3 days, it 5760 minutes.



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

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
4907 83193  Huge snapshot difference that means we have long history of snapshots which needs to be purged.

SQL> show parameter statistics

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL  should be typical.
timed_os_statistics integer 0
timed_statistics boolean TRUE


Thus at this time we can call this as the huge history of snapshots are stored in the database:

Use the below to purge the old snapshots and reclaim the space:

SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id,high_snap_id);


To my surprise the drop snapshot is not dropping the snapshots:


SQL> exec dbms_workload_repository.drop_snapshot_range( 4907 , 9000);

PL/SQL procedure successfully completed.

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

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
4907 83197


SQL> select instance_number from v$instance;

INSTANCE_NUMBER
---------------
3

SQL> exec dbms_workload_repository.drop_snapshot_range( 4907 , 9000);


PL/SQL procedure successfully completed.


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

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
4907 83193


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

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
4907 83195

SQL> select instance_number from v$instance;

INSTANCE_NUMBER
---------------
2


SQL> exec dbms_workload_repository.drop_snapshot_range( 4907 , 9000);

PL/SQL procedure successfully completed.

SQL> select instance_number from v$instance;

INSTANCE_NUMBER
---------------
1


After running the drop snapshot I still see the low snap as the same:


select * from dba_hist_snapshot order by 1<-- indeed show 4907 was from 2008.
4907 3861913925 1 9/16/2008 2:40:41.000 AM 2/1/2009 10:00:49.693 PM 2/1/2009 11:00:02.760 PM +00 00:00:10.200000 1 0
4908 3861913925 1 9/16/2008 2:40:41.000 AM 2/1/2009 11:00:02.760 PM 2/2/2009 12:00:16.084 AM +00 00:00:09.900000 1 0
4909 3861913925 1 9/16/2008 2:40:41.000 AM 2/2/2009 12:00:16.084 AM 2/2/2009 1:00:28.861 AM +00 00:00:10.100000 1 0
4910 3861913925 1 9/16/2008 2:40:41.000 AM 2/2/2009 1:00:28.861 AM 2/2/2009 2:00:42.319 AM +00 00:00:09.700000 1 0

So started doing some research and found the below:



DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE Is Not Removing All Snapshots Data [ID 1218543.1]
Cause
This is not a bug but expected behavior, as oracle does not drop snapshots data associated with the baselines .

To overcome this:

Drop the baseline to clear the data for these snapshots.


SQL> select BASELINE_ID,START_SNAP_ID,END_SNAP_ID from WRM$_BASELINE
2 ;

BASELINE_ID START_SNAP_ID END_SNAP_ID
----------- ------------- -----------
2 4907 5192
3 4932 4956
11 6524 6548
12 6620 6645
1 4908 4932
4 4956 4980
5 4980 5004
6 5004 5028
7 5028 5052
8 5052 5076
9 5076 5139
10 5139 5215
14 5924 6867


SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE ( baseline_name => 'AWR_1234290129964',cascade=>true,dbid=>3861913925);

PL/SQL procedure successfully completed.


WOW,Now the min snapshot has been changed.

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

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
4908 83215


So I went ahead and deleted all the old baselines to purge the old AWR historic info and reclaimed all the space.

Good luck to you all:

1 comment:

Tahir Hussain said...

nice document! and i also appreciate that you put hadith :)

regards,
Tahir