AWR: Same SNAP ID, Different Dates

Hello everyone,

Recently I ran into an interesting issue. I created a script to automatically generate AWR + ADDM reports by asking only for the start and end timestamps.

Basically the script looks for the minimum and maximum SNAP_IDs between the start and end times in order to get comprehensive information out of the AWR.

Everything worked well until we got the starting time pointing to last year. But we only store 2 weeks of AWR data. So this seemed like a ghost snapshot.

The issue

2 years working perfectly until we got something like this:

SQL> select distinct snap_id, begin_interval_time, end_interval_time
from dba_hist_snapshot
where snap_id=99999
order by 2;

---------- ------------------------- -------------------------
 99999     03-MAY-16 03-MAY-16
 99999     03-MAY-16 03-MAY-16
 99999     03-MAY-16 03-MAY-16
 99999     03-MAY-16 03-MAY-16
 99999     31-JAN-17 31-JAN-17
 99999     31-JAN-17 31-JAN-17
 99999     31-JAN-17 31-JAN-17
 99999     31-JAN-17 31-JAN-17

8 rows selected

So, how can this happen?

Basically, we refreshed this database from another one using RMAN Clone, this means that after the cloning, the databases were identical, including AWR snapshots.

This introduces SNAP_IDs from the source database into the target database, and thus when the target database reaches that same SNAP_ID number, we face this situation.

By issuing a couple more queries, you can find something like this:

SQL> select dbid from v$database

SQL> select dbid, retention from dba_hist_wr_control
----------- -----------
 1357924680 40150 0:0:0
 1234567890 60 0:0:0.0 
 2468013579 60 0:0:0.0

The Solution

The solution for this issue is simple enough.

We can use the following stored procedure to cleanup the mess:

dbms_workload_repository.drop_snapshot_range(min,max, db_id)

By following the next few steps you can find the snap range you want to drop and clear them out.

SQL> select min(snap_id), max(snap_id) from dba_hist_snapshot where dbid = 1357924680;

------------ -------------
       55555         99999

SQL> exec dbms_workload_repository.drop_snapshot_range(55555,99999, 1357924680);

PL/SQL procedure successfully completed.

SQL> select *from dba_hist_snapshot where dbid = 1357924680;

no rows selected

So, this is it for today! I hope this helps some of you fellow DBAs out there!

NOTE: DB_IDs, SNAP_IDs and timestamps have been modified to protect the identity of the protagonists of this post 😉


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s