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 Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 – Production

問題發現的由頭是生成AWR報告的時候，發現近幾天都沒有正常生成AWR Snapshot。由於是很少用的系統，所以筆者只在每月進行一次跟蹤。這種情況肯定不正常，進入10g之後，AWR後臺預設每隔一小時，都會自動生成一個Snapshot映象資料。

這種情況，筆者本能想去定位alert log，大部分異常情況，Oracle都會記錄在資料庫中。果然在其中發現了問題。

Wed Sep 30 14:24:15 2015

ORA-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 SYSAUX

Wed Sep 30 15:06:58 2015

ORA-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 altered

SQL> select bytes/1024/1024, AUTOEXTENSIBLE from dba_data_files where tablespace_name='SYSAUX';

BYTES/1024/1024 AUTOEXTENSIBLE

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

1032 YES

Alert log中記錄資訊。

YSAUX

Wed Sep 30 15:30:13 2015

alter database datafile '/data/xxxdb/systs/sysaux01.dbf' autoextend on

Completed: alter database datafile '/data/xxxdb/systs/sysaux01.dbf' autoextend on

手工測試生成AWR映象，判斷問題是否解決。

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed

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

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

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 102

SYS WRM$_SNAPSHOT_DETAILS TABLE 88

SYS SMON_SCN_TO_TIME_AUX CLUSTER 80

XDB SYS_LOB0000057474C00025$$ LOBSEGMENT 53.1875

疑問出現了，最大的幾個物件中，snapshot赫然出現在其中。當前Snapshot裡面沒有資料，無論是被自然purge掉，還是認為刪掉，都不應該有資料存在。檢視基礎資料表：

SQL> select count(*) from WRM$_SNAPSHOT_DETAILS;

COUNT(*)

----------

1723102

SQL> 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

----------

23383

SQL> exec dbms_workload_repository.drop_snapshot_range(1,23383);

PL/SQL procedure successfully completed

SQL> 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 1

SQL> 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 purged

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.

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.

其中內容所，如果要解決這個問題，需要進行補丁操作，或者在Oracle Support指導之下手工進行刪除。

由於是生產環境，經過協調，認為當前開啟擴充sysaux策略是可以接受的解決方案。於是筆者打算適可而止，不影響系統正常執行。

4、恢復自動AWR收集

最後需要恢復AWR收集。注意：在一些資料中，建議最開始有一個snapshot，透過手工建立，之後Oracle才能自動的進行生成。

為了快速驗證，調整收集週期是10分鐘。

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>10);

PL/SQL procedure successfully completed

SQL> 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:51

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:51

23385 2015-09-30 16:39:32

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: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 Bug引起的AWR Snapshot收集故障

相關文章