AWR 及STATSPACK的snapshot不能自動生成排查

近期接連遇到AWR 及STATSPACK的snapshot不能自動生成排查。

STATSPACK的snapshot不能自動生成原因是存放快照的基表：ORA-01631: max # extents (505) reached in table PERFSTAT.STATS$FILESTATXS

AWR不能自動生成快照是因為之前系統程式數達到上限，資料庫也HANG住；MMON程式異常；通過os層面kill MMON程式後，資料庫例項自動又啟動MMON，之後恢復正常。

問題與排查如下：

1.STATSPACK的snapshot不能自動生成排查

ALERT日誌中資訊：

Mon Apr  4 15:01:00 2016
 ORA-1631: max # extents 505 reached in table PERFSTAT.STATS$FILESTATXS
Mon Apr  4 15:01:00 2016
Errors in file /oracle/8.1.7/admin/prod/bdump/snp0_150774_prod.trc:
ORA-12012: error on auto execute of job 403
ORA-01631: max # extents (505) reached in table PERFSTAT.STATS$FILESTATXS
ORA-06512: at "PERFSTAT.STATSPACK", line 1167
ORA-06512: at "PERFSTAT.STATSPACK", line 71
ORA-06512: at line 1
Mon Apr  4 17:38:19 2016

檢視快照生成情況：

                           14232 04 Apr 2016 01:00     5
                           14233 04 Apr 2016 02:00     5
                           14234 04 Apr 2016 03:00     5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:

檢視生成快照的JOB的執行情況：
SQL> conn perfstat/perfstat
Connected.
select job,schema_user,to_char(next_date,'yyyy/mm/dd hh24:mi:ss') as next_D,interval,what from user_jobs;
SQL> SQL>
       JOB SCHEMA_USER  NEXT_D             INTERVAL        WHAT
---------- ------------ ------------------ --------------- --------------------
       423 PERFSTAT     <span style="color:#FF0000;">4000/01/01 </span>00:00:0 trunc(SYSDATE+1 statspack.snap;
                        0                  /24,'HH')
----可以看到下次執行是2000年以後。。
 SQL> exec dbms_job.remove(423);

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/spauto.sql  

PL/SQL procedure successfully completed.

Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:
     JOBNO
----------
       443

Job queue process
~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:
NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
job_queue_processes                  integer 1
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

       JOB NEXT_DATE NEXT_SEC
---------- --------- ----------------
       443 29-APR-16 11:00:00

SQL> select job,schema_user,to_char(next_date,'yyyy/mm/dd hh24:mi:ss') as next_D,interval,what from user_jobs;

       JOB SCHEMA_USER  NEXT_D             INTERVAL        WHAT
---------- ------------ ------------------ --------------- --------------------
       443 PERFSTAT     2016/04/29 11:00:0 trunc(SYSDATE+1 statspack.snap;
                        0                  /24,'HH')

這裡沒有使用擴充套件表的EXTENT的方式，而是刪除舊有的快照資訊，刪除方法有如下兩種：
方法1：刪除快照資訊：
Oracle還提供了系統指令碼用於Truncate這些統計資訊表，這個指令碼名字是: sptrunc.sql (8i、9i都相同)
該指令碼主要內容如下，裡面看到的就是statspack相關的所有系統表：

方法2：手動刪除指定範圍的快照：
刪除stats$snapshot資料表中的相應資料，其他表中的資料會相應的級連刪除：
SQL> select max(snap_id) from stats$snapshot;
MAX(SNAP_ID)
------------
12345

delete from stats$snapshot where snap_id < = 100;  ---如果一次刪除的多，建議分批刪除、提交。--我是一次刪除500個快照，可以觀察到生成REDO較多。
commit;
你可以更改snap_id的範圍以保留你需要的資料。
在以上刪除過程中，你可以看到所有相關的表都被鎖定。

2.到AWR 的snapshot不能自動生成排查

參考文件：Troubleshooting: Missing Automatic Workload Repository (AWR) Snapshots and Other Collection Issues (文件 ID 1301503.1)按順序進行排查，主要是檢視MMON/MMNL程式的TRACE資訊，發現其中MMON程式TRACE不存在；手動執行生成快照命令exec dbms_workload_repository.create_snapshot;也無法正常完成，但是未發現相關表是被鎖：結合文件 ID 1301503.1中的排查思路進行排查後，惟一存在異常的是MMON程式，然後在OS層面KILL MMON程式，之後例項自動啟動MMON，之後可以正常生成快照。