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

最新推荐文章于 2022-11-20 18:54:03 发布

Yushan Bai

最新推荐文章于 2022-11-20 18:54:03 发布

阅读量3.4k

点赞数

分类专栏： ORACLE 故障排查

本文链接：https://blog.csdn.net/haibusuanyun/article/details/51282623

版权

ORACLE 故障排查专栏收录该内容

195 篇文章 15 订阅

订阅专栏

近期接连遇到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，之后可以正常生成快照。