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;

  SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME 
---------- ------------------------- -------------------------
 99999     03-MAY-16 03.30.24.785000 03-MAY-16 03.45.30.203000
 99999     03-MAY-16 03.30.24.810000 03-MAY-16 03.45.30.226000
 99999     03-MAY-16 03.30.24.824000 03-MAY-16 03.45.30.252000
 99999     03-MAY-16 03.30.24.828000 03-MAY-16 03.45.30.227000
 99999     31-JAN-17 12.00.31.899000 31-JAN-17 12.15.37.672000
 99999     31-JAN-17 12.00.31.920000 31-JAN-17 12.15.37.693000
 99999     31-JAN-17 12.00.31.921000 31-JAN-17 12.15.37.697000
 99999     31-JAN-17 12.00.31.921000 31-JAN-17 12.15.37.694000

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
 DBID
-----------
 1234567890

SQL> select dbid, retention from dba_hist_wr_control
 DBID       RETENTION
----------- -----------
 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;

MIN(SNAP_ID)  MAX(SNAP_ID)
------------ -------------
       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 😉

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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