Tuesday, August 9, 2016


Remove Old SNAPs From AWR History



Recently we came across the siutation, where our AWR snapshots taking lot of space in SYSAUX. Upon review we figure out that the DB was recently restored to new servers during physical migration. 
Hence the data from old DBID was still pertinent in DB. 

New DBID=3360745648
Old DBID = 926777616 


SYS@NPOFE02P1>select DBID,NAME from v$database;

      DBID NAME
---------- ------------
3360745648 xxxxx

SYS@NPOFE02P1>select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL                  RETENTION                      TOPNSQL
---------- ------------------------------ ------------------------------ ----------
3360745648 +00000 00:15:00.0              +00015 00:00:00.0              DEFAULT
 926777616 +00000 00:15:00.0              +00060 00:00:00.0              DEFAULT

-- We checked how many snaps we do have for old DBID.
SYS@NPOFE02P1>select SNAP_ID, DBID,INSTANCE_NUMBER,SNAP_LEVEL from dba_hist_snapshot where dbid=926777616 order by snap_id, dbid;

-- find out the min and max range to remove from DB

SYS@NPOFE02P1>select min(SNAP_ID), max(SNAP_ID) from dba_hist_snapshot where dbid=926777616;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
      113085       118150

 
SQL> execute dbms_workload_repository.drop_snapshot_range(113085,118150);

PL/SQL procedure successfully completed.

Once done the above step, all the snpas were removed from Database.