DROP_SNAPSHOT_RANGE過程不能清理表RM$_SNAPSHOT_DETAILS

    今天在測試、驗證DROP_SNAPSHOT_RANGE不能徹底快照的過程中遇到了DROP_SNAPSHOT_RANGE無法清理WRM$_SNAPSHOT_DETAILS表中數據的情況，測試伺服器版本為10.2.0.5.0，AWR的快照是1小時採集一次數據，快照保留14天，也就是二周。具體情況如下所示：

SQL> select * from v$version;       

BANNER

----------------------------------------------------------------

Oracle Database 10g Release 10.2.0.5.0 - 64bit Production

PL/SQL Release 10.2.0.5.0 - Production

CORE    10.2.0.5.0      Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.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"

其實這個是一個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:

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:

1. Drop a range of snap id's using dbms_workload_repository.drop_snapshot_range()
2. Check the corresponding snap id's in WRM$_SNAPSHOT_DETAILS.
3. 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掉了，請留意自己的版本信息。