今天在测试、验证DROP_SNAPSHOT_RANGE不能彻底快照的过程中遇到了DROP_SNAPSHOT_RANGE无法清理WRM$_SNAPSHOT_DETAILS表中数据的情况，测试服务器版本为10.2.0.4.0，AWR的快照是1小时采集一次数据，快照保留14天，也就是二周。具体情况如下所示：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> COL SNAP_INTERVAL FOR A20;
SQL> COL TETENTION FOR A26;
SQL> SELECT * FROM dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
3990839260 +00000 01:00:00.0 +00014 00:00:00.0 DEFAULT
SQL> SELECT MIN(SNAP_ID), MAX(SNAP_ID) FROM dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
7417 59195
SQL>
SQL> SELECT MIN(SNAP_ID), MAX(SNAP_ID) FROM dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
7417 59196
SQL> select dbid, status, count(*)
2 from wrm$_snapshot
3 group by dbid, status;
DBID STATUS COUNT(*)
---------- ---------- ----------
3990839260 0 1250
SQL> select min(snap_id), max(snap_id), dbid from wrm$_snapshot
2 group by dbid;
MIN(SNAP_ID) MAX(SNAP_ID) DBID
------------ ------------ ----------
7417 59196 3990839260
SQL> exec dbms_workload_repository.drop_snapshot_range(7417,59196,3990839260);
PL/SQL procedure successfully completed.
SQL> select min(snap_id), max(snap_id), dbid from wrm$_snapshot
2 group by dbid;
MIN(SNAP_ID) MAX(SNAP_ID) DBID
------------ ------------ ----------
7417 59197 3990839260
SQL> select min(snap_id), max(snap_id), dbid from wrm$_snapshot
2 group by dbid;
MIN(SNAP_ID) MAX(SNAP_ID) DBID
------------ ------------ ----------
7417 59197 3990839260
SQL> SELECT MIN(SNAP_ID), MAX(SNAP_ID) FROM dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
7417 59197
如上实验所示，DROP_SNAPSHOT_RANGE不能清理WRM$_SNAPSHOT_DETAILS中的数据，当然对于的空间就不会释放，另外，有些版本中Oracle仅仅修改了对应SNAPSHOT的状态，而并没有删除快照。PS:有些人可能被上面又是DBA_HIST_SNAPSHOT，又是WRM$_SNAPSHOT_DETAILS弄得有点晕，其实DBA_HIST_SNAPSHOT是视图,它的数据来源于表WRM$_SNAPSHOT_DETAILS，使用下面SQL就能查看具体定义
SELECT OWNER, VIEW_NAME, TEXT FROM DBA_VIEWS WHERE VIEW_NAME='DBA_HIST_SNAPSHOT';
"select snap_id, dbid, instance_number, startup_time,
begin_interval_time, end_interval_time,
flush_elapsed, snap_level, error_count
from WRM$_SNAPSHOT
where status = 0"
-------------------------------------------------------------分割线------------------------------------------------------
本来这篇文章写了好几天了，后面讨论发现其实有时候AWR快照不能删除，并不一定就是bug，也有可能是设置了AWR的基线，下面我来演示一下
SQL> select baseline_name, start_snap_id, end_snap_id
2 from dba_hist_baseline
3 order by 1;
BASELINE_NAME START_SNAP_ID END_SNAP_ID
------------------------ ------------- -----------
20100526 7455 7464
20100602 7624 7632
20100609 7791 7800
20100616 7959 7968
20100623 8126 8135
20100630 8294 8303
20100707 8453 8477
20100714 8621 8645
20100721 8789 8813
20100728 8957 8981
20100804 9125 9149
BASELINE_NAME START_SNAP_ID END_SNAP_ID
------------------------ ------------- -----------
20100811 9293 9317
20100818 9461 9485
20100825 9620 9644
20100901 9788 9812
20100908 9957 9980
20100915 10124 10148
20100922 10292 10316
20100929 10460 10484
20101006 10628 10652
20101013 10796 10820
20101020 10964 10988
BASELINE_NAME START_SNAP_ID END_SNAP_ID
------------------------ ------------- -----------
20101027 11132 11156
20101103 11300 11324
20101110 11468 11492
20101117 11636 11660
20101124 11804 11828
20101201 11972 11996
20101208 12140 12164
20101215 12308 12332
20101222 12476 12500
20101229 12644 12668
20110105 12812 12836
BASELINE_NAME START_SNAP_ID END_SNAP_ID
------------------------ ------------- -----------
20110112 12980 13004
20110119 13148 13172
20110126 13316 13340
20110202 13484 13508
SQL> SELECT MIN(SNAP_ID), MAX(SNAP_ID) FROM dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
7100 61252
SQL> exec dbms_workload_repository.drop_snapshot_range(7100,7108,2179993557);
PL/SQL procedure successfully completed.
SQL> SELECT MIN(SNAP_ID), MAX(SNAP_ID) FROM dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
7287 61252
SQL> exec dbms_workload_repository.drop_snapshot_range(7455,7458,2179993557);
PL/SQL procedure successfully completed.
SQL> SELECT SNAP_ID,STARTUP_TIME FROM dba_hist_snapshot
2 WHERE SNAP_ID BETWEEN 7455 AND 7458;
SNAP_ID STARTUP_TIME
---------- ---------------------------------------------------------------------------
7455 16-JAN-10 12.18.46.000 PM
7456 16-JAN-10 12.18.46.000 PM
7457 16-JAN-10 12.18.46.000 PM
7458 16-JAN-10 12.18.46.000 PM
SQL>
AWR快照SNAP_ID从7455 到7458 删除不掉，其实是因为这个段的快照设置成了基线，如下截图所示，所以，如果你发现快照删除不了的话，最好先检查这个SNAP_ID段是否设置成了基线。
另外还有就是有可能一个Bug引起的，这个只出现在特定版本中，官方文档WRM$_SNAPSHOT_DETAILS Table is Not Purged (文档 ID 1489801.1) 和文档 Document 9797851.8 Bug 9797851 - WRM$_SNAPHOST_DETAILS is never purged 都有描述这个Bug
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to 12.1]
Information in this document applies to any platform.
SYMPTOMS
The following symptoms are observed:
- AWR purge code is not automatically purging WRM$_SNAPSHOT_DETAILS, as expected
- Even after dropping a range of snap id's using dbms_workload_repository.drop_snapshot_range(), the table is not purged.
- Table WRM$_SNAPSHOT_DETAILS grows indefinitely.
- There are many orphaned entries in the table WRM$_SNAPSHOT_DETAILS.
The number of orphaned rows for the table WRM$_SNAPSHOT_DETAILS can be found by running the following sql:
SQL> SELECT MIN(snap_id),
max(snap_id) ,
cast(min(begin_time) as date) "Min Begin Time",
CAST(MAX(begin_time) AS DATE) "Max Begin Time",
COUNT(*)
FROM sys.wrm$_snapshot_details a
WHERE NOT EXISTS
(SELECT *
FROM sys.wrm$_snapshot b
WHERE b.snap_id = a.snap_id
AND a.dbid = b.dbid
and a.instance_number = b.instance_number
) MIN(SNAP_ID) MAX(SNAP_ID) Min Begin Time Max Begin Time COUNT(*)
------------ ------------ -------------------- -------------------- ----------
1 6993 29-nov-2011 21:00:01 24-sep-2012 22:00:17 577574
CAUSE
This issue is caused by an unpublished bug:
The verification criteria for the bug are:
- Drop a range of snap id's using dbms_workload_repository.drop_snapshot_range()
- Check the corresponding snap id's in WRM$_SNAPSHOT_DETAILS.
- If snap id's from the range that you chose to drop are still present, then you are hitting this bug.
SOLUTION
The following solutions are available:
- The Patch 9797851 for unpublished Bug 9797851 is available for some platforms and can be downloaded from My Oracle Support
- If the patch is not available on your platform on a supported version, please contact Oracle Support.
- This issue will be fixed from release Oracle 12.1
As a workaround, it is possible to manually purge the range of snap id's from the table WRM$_SNAPSHOT_DETAILS using appropriate delete statments under the guidance of Oracle Support.
Note:
在下面版本中，这些bug才fix掉了，请留意自己的版本信息。
