由Oracle Bug引起的AWR Snapshot收集故障

AWR报告是Oracle从10g开始引入的一项重要性能特性。以9i可选组件Statspack为基础，借助自动周期作业，我们可以获得一系列的Oracle性能片段，从而在出现性能问题的时候更加从容。AWR引入的一个结果，就是系统需要根据配置内容将性能数据保存在数据库中。从10g之后，sysaux表空间从system表空间从脱离开来，就提供了这种可能性。我们在实际运维工作中，是可能会遇到AWR元数据引起的故障问题。本篇主要介绍这个案例，留待需要同仁待查。1、问题说明运维人员都有“节日休假恐怖症”，越到节日、休假和外出出差，系统越可能出现问题。笔者在进行一个系统的例行检查时，出现了问题。数据库版本为11gR2，具体版本编号为11.2.0.3。SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE 11.2.0.3.0 ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 – Production问题发现的由头是生成AWR报告的时候，发现近几天都没有正常生成AWR Snapshot。由于是很少用的系统，所以笔者只在每月进行一次跟踪。这种情况肯定不正常，进入10g之后，AWR后台默认每隔一小时，都会自动生成一个Snapshot镜像数据。这种情况，笔者本能想去定位alert log，大部分异常情况，Oracle都会记录在数据库中。果然在其中发现了问题。Wed Sep 30 14:24:15 2015ORA-1653: unable to extend table SYS.SCHEDULER$_EVENT_LOG by 128 in tablespace SYSAUX Errors in file /home/oracle/app/diag/rdbms/xxx/xxxdb/trace/xxxdb_j000_3385.trc:ORA-01653: unable to extend table . by in tablespace ORA-01653: unable to extend table SYS.SCHEDULER$_EVENT_LOG by 128 in tablespace SYSAUXWed Sep 30 15:06:58 2015ORA-1653: unable to extend table SYS.SCHEDULER$_EVENT_LOG by 128 in tablespace SYSAUX Errors in file /home/oracle/app/diag/rdbms/xxxdb/xxxdb/trace/xxxdb_j000_5102.trc:ORA-01653: unable to extend table . by in tablespace ORA-01653: unable to extend table SYS.SCHEDULER$_EVENT_LOG by 128 in tablespace SYSAUX从内容上看，是sysaux表空间满了。默认情况下，Oracle的系统性质表空间都是不支持文件自动拓展的。如果原有大小写满了，同时不支持自动拓展，的确会有报错异常。此时，AWR配置内容是默认方式。SQL> select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL---------- --------------------------------------- --------------------------------------- ----------1778314713 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT默认AWR是每小时保存一个镜像，镜像数据会保存八天。此时，AWR中已经没有对应的数据镜像了。SQL> select snap_id, dbid, startup_time from dba_hist_snapshot; SNAP_ID DBID STARTUP_TIME---------- ---------- --------------------------------------------------------------------------------2、问题缓解一般数据库故障，通常不是一个单独策略可以解决的。笔者认为：问题分轻重缓急，解决方案也分猛药温补。关键的取舍取决于不同的场景优先级别。在这种情况下，恢复AWR工作，增加sysaux表空间存储是首要需求。这种操作比较简单，只要单独定位和允许文件自动拓展即可。SQL> alter database datafile "/data/xxxdb/systs/sysaux01.dbf" autoextend on;Database alteredSQL> select bytes/1024/1024, AUTOEXTENSIBLE from dba_data_files where tablespace_name="SYSAUX";BYTES/1024/1024 AUTOEXTENSIBLE--------------- -------------- 1032 YESAlert log中记录信息。YSAUXWed Sep 30 15:30:13 2015alter database datafile "/data/xxxdb/systs/sysaux01.dbf" autoextend onCompleted: alter database datafile "/data/xxxdb/systs/sysaux01.dbf" autoextend on手工测试生成AWR镜像，判断问题是否解决。SQL> exec dbms_workload_repository.create_snapshot;PL/SQL procedure successfully completedSQL> select snap_id, to_char（BEGIN_INTERVAL_TIME,"yyyy-mm-dd hh24:mi:ss"） from dba_hist_snapshot; SNAP_ID TO_CHAR（BEGIN_INTERVAL_TIME,"Y---------- ------------------------------ 23383 2015-09-30 15:40:16在日志中没有新的报错信息出现。可以认为初步问题解决。下一步就是定位问题：为什么会出现sysaux爆棚的情况。3、深层分析过程AWR和其他一些性能收集，的确是不断的将数据收集到sysaux表空间里面进行记录。笔者一直认为：任何正确的数据架构模式，必要条件之一就是“有进有出”。数据不断积累，一定要有机制（系统内或者系统外）让数据可以脱离系统。从微观角度看，数据表要维持一个稳定的体积容量结构。AWR系统也的确是这样。在不断收集数据的时候，也会依据Retention规则（默认为8天）不断将数据Purge掉。无论如何设置，sysaux正常情况下应该是一个固定稳定的大小规格。笔者当前数据库已经运行一两年的时间，要出问题早就出现问题了。说明系统中，有一些数据在不断的“默默长大”，问题在不断的慢慢积累。尝试检查当前sysaux表空间段结构排名，看有没有与此相关的信息。SQL> select owner, segment_name, segment_type, bytes/1024/1024 from （select * from dba_segments where tablespace_name="SYSAUX" order by bytes desc） where rownum<5;OWNER SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024---------- ------------------------------ ------------------ ---------------SYS WRM$_SNAPSHOT_DETAILS_INDEX INDEX 102SYS WRM$_SNAPSHOT_DETAILS TABLE 88SYS SMON_SCN_TO_TIME_AUX CLUSTER 80XDB SYS_LOB0000057474C00025$$ LOBSEGMENT 53.1875疑问出现了，最大的几个对象中，snapshot赫然出现在其中。当前Snapshot里面没有数据，无论是被自然purge掉，还是认为删掉，都不应该有数据存在。查看基础数据表：SQL> select count（*） from WRM$_SNAPSHOT_DETAILS; COUNT（*）---------- 1723102SQL> select * from WRM$_SNAPSHOT_DETAILS where rownum<5; SNAP_ID DBID INSTANCE_NUMBER TABLE_ID BEGIN_TIME ---------- ---------- --------------- ---------- ------------------------------- 1 1778314713 1 4 05-12？？-12 05.00.08.719 ？？？？ 1 1778314713 1 5 05-12？？-12 05.00.08.771 ？？？？ 1 1778314713 1 6 05-12？？-12 05.00.08.841 ？？？？ 1 1778314713 1 7 05-12？？-12 05.00.08.892 ？？？？其中数据量还是很大的，重点在于snap_id。SQL> select max（snap_id）, min（snap_id） from WRM$_SNAPSHOT_DETAILS;MAX（SNAP_ID） MIN（SNAP_ID）------------ ------------ 23383 1“慢慢变大”的数据找到了，作为AWR基础的数据，从来就没有被删除。手工删除呢？是不是可以删除？SQL> select snap_id from dba_hist_snapshot; SNAP_ID---------- 23383SQL> exec dbms_workload_repository.drop_snapshot_range（1,23383）;PL/SQL procedure successfully completedSQL> select snap_id from dba_hist_snapshot; SNAP_ID----------SQL> select max（snap_id）, min（snap_id） from WRM$_SNAPSHOT_DETAILS;MAX（SNAP_ID） MIN（SNAP_ID）------------ ------------ 23383 1SQL> select max（snap_id）, min（snap_id） from WRM$_SNAPSHOT;MAX（SNAP_ID） MIN（SNAP_ID）------------ ------------删除的数据，没有连带将基础数据表WRM$_SNAPSHOT_DETAILS内容删除。诡异的问题！经过查证MOS，发现该数据表不能删除是一个Oracle Bug，具体描述如下：Bug 9797851 - WRM$_SNAPHOST_DETAILS is never purgedThe 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.The following solutions are available:The Patch 9797851 for unpublished Bug 9797851 is available for some platforms and can be downloaded from My Oracle SupportIf 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.1As 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.其中内容所，如果要解决这个问题，需要进行补丁操作，或者在Oracle Support指导之下手工进行删除。由于是生产环境，经过协调，认为当前开启拓展sysaux策略是可以接受的解决方案。于是笔者打算适可而止，不???响系统正常运行。4、恢复自动AWR收集最后需要恢复AWR收集。注意：在一些资料中，建议最开始有一个snapshot，通过手工创建，之后Oracle才能自动的进行生成。为了快速验证，调整收集周期是10分钟。SQL> exec dbms_workload_repository.create_snapshot;PL/SQL procedure successfully completedSQL> exec dbms_workload_repository.modify_snapshot_settings（interval=>10）;PL/SQL procedure successfully completedSQL> select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL---------- --------------------------------------- --------------------------------------- ----------1778314713 +00000 00:10:00.0 +00008 00:00:00.0 DEFAULT经过一段时间，可以看到数据变化。SQL> select snap_id, to_char（BEGIN_INTERVAL_TIME,"yyyy-mm-dd hh24:mi:ss"） from dba_hist_snapshot; SNAP_ID TO_CHAR（BEGIN_INTERVAL_TIME,"Y---------- ------------------------------ 23384 2015-09-30 16:07:51SQL> select snap_id, to_char（BEGIN_INTERVAL_TIME,"yyyy-mm-dd hh24:mi:ss"） from dba_hist_snapshot; SNAP_ID TO_CHAR（BEGIN_INTERVAL_TIME,"Y---------- ------------------------------ 23384 2015-09-30 16:07:51 23385 2015-09-30 16:39:32SQL> select snap_id, to_char（BEGIN_INTERVAL_TIME,"yyyy-mm-dd hh24:mi:ss"） from dba_hist_snapshot; SNAP_ID TO_CHAR（BEGIN_INTERVAL_TIME,"Y---------- ------------------------------ 23384 2015-09-30 16:07:51 23385 2015-09-30 16:39:32 23386 2015-09-30 16:50:17恢复60分钟收集。SQL> exec dbms_workload_repository.modify_snapshot_settings（interval=>60）;PL/SQL procedure successfully completed问题解决。5、结论经过这个案例，笔者的感受有两条。首先，定期检查alert log，检查系统性能数据情况是非常重要的运维手段。要么依靠自动监控工具，要么依靠运维人员的日常工作。所谓“养病如养虎，虎大必伤人”在运维领域同样存在。其次就是“有可为，有可不为”，发现问题时候，要根据情形进行分析，找到技术、管理、业务的平衡点。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址