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
205 0
|
5月前
|
关系型数据库 分布式数据库 PolarDB
PolarDB操作报错合集之执行drop操作报The consensus follower is not allowed to to do current operation错误,select可以执行,是什么导致的
在使用阿里云的PolarDB(包括PolarDB-X)时,用户可能会遇到各种操作报错。下面汇总了一些常见的报错情况及其可能的原因和解决办法:1.安装PolarDB-X报错、2.PolarDB安装后无法连接、3.PolarDB-X 使用rpm安装启动卡顿、4.PolarDB执行UPDATE/INSERT报错、5.DDL操作提示“Lock conflict”、6.数据集成时联通PolarDB报错、7.编译DN报错(RockyLinux)、8.CheckStorage报错(源数据库实例被删除)、9.嵌套事务错误(TDDL-4604)。
130 1
|
6月前
|
存储 NoSQL 关系型数据库
实时计算 Flink版操作报错之抽取Oracle11g时,报错: "Retrieve schema history failed, the schema records for engine ... has been removed",怎么处理
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
6月前
|
消息中间件 Oracle 关系型数据库
实时计算 Flink版操作报错合集之报错io.debezium.DebeziumException: The db history topic or its content is fully or partially missing. Please check database history topic configuration and re-execute the snapshot. 是什么原因
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
377 0
subclipse同步冲突问题A conflict in the working copy obstructs the current operation
subclipse同步冲突问题A conflict in the working copy obstructs the current operation
101 0
subclipse同步冲突问题A conflict in the working copy obstructs the current operation
|
关系型数据库 MySQL 数据库
mysql中的update(更新)与alter(更改)以及 change和modify的区别
mysql中的update(更新)与alter(更改)以及 change和modify的区别
1468 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