DROP_SNAPSHOT_RANGE过程不能清理表RM$_SNAPSHOT_DETAILS

简介: 今天在测试、验证DROP_SNAPSHOT_RANGE不能彻底快照的过程中遇到了DROP_SNAPSHOT_RANGE无法清理WRM$_SNAPSHOT_DETAILS表中数据的情况,测试服务器版本为10.2.0.4.0,AWR的快照是1小时采集一次数据,快照保留14天,也就是二周。

    今天在测试、验证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> 

clip_image001

SQLSELECT 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.

 
SQLselect 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
 
SQLexec 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.
 
SQLSELECT 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:


 

CAUSE

This issue is caused by an unpublished bug:

Document 9797851.8  Bug 9797851 - WRM$_SNAPHOST_DETAILS is never purged

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掉了,请留意自己的版本信息。

 

image

相关文章
|
SQL Oracle 关系型数据库
【MySQL异常】1093 - You can‘t specify target table ‘daily_job‘ for update in FROM clause
【MySQL异常】1093 - You can‘t specify target table ‘daily_job‘ for update in FROM clause
163 0
|
6月前
|
存储
Build desc failed:Fetch table group shards failed on meta proxy:Loading cached shard 1ocation value for table group[dwhg_scm.dwhg_prd_tg_default] failed
Build desc failed:Fetch table group shards failed on meta proxy:Loading cached shard 1ocation value for table group[dwhg_scm.dwhg_prd_tg_default] failed
187 2
|
11月前
|
关系型数据库 数据库
Harbor断电重启postgres报错 could not locate a valid checkpoint record
Harbor断电重启postgres报错 could not locate a valid checkpoint record
419 0
|
存储
Oracle-AWR管理包DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
Oracle-AWR管理包DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
133 0
|
SQL 关系型数据库 MySQL
mysql操作中 出现You can‘t specify target table for update in FROM clause错误的解决方法
这个错误实际上也不能称之为咱们sql语句写的不行,实际上是我们在一些细节上没有遵循mysql的语法规范。 问题所在:我们一个sql语句中先select这个表,然后再update这个表的内容。 错误示范: UPDATE StuCose SET Grade=60 WHERE Sno IN( SELECT Sno FROM stucose WHERE Grade<=ALL( SELECT MIN(Grade) FROM stucos
410 0
mysql操作中 出现You can‘t specify target table for update in FROM clause错误的解决方法
SAP QM 执行事务代码QA11 报错- Selected set code does not exist, or data entered is incomplete-
SAP QM 执行事务代码QA11 报错- Selected set code does not exist, or data entered is incomplete-
SAP QM 执行事务代码QA11 报错- Selected set code does not exist, or data entered is incomplete-
|
存储 关系型数据库 Go
PostgreSQL 11 内核优化 - 降低vacuum cleanup阶段index scan概率 ( vacuum_cleanup_index_scale_factor , skip index vacuum cleanup stage)
PostgreSQL 11 内核优化 - 降低vacuum cleanup阶段index scan概率 ( vacuum_cleanup_index_scale_factor , skip index vacuum cleanup stage)
1264 0
[20171218]修改AWR snapshot 设置.txt
[20171218]Modifying AWR snapshot settings.txt SYS@book> select * from dba_hist_wr_control;       DBID SNAP_INTERVAL     RETENTION   ...
940 0