RAC下某节点AWR的snapshot不产生了
现象:10g rac下,准备用脚本收集AWR报告时，2节点上没有AWR快照，1节点正常。--系统:redhat5.5
$ uname -a
Linux gps02.yto.com 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
--数据库版本（为双节点RAC）
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
--快照间隔时间
SQL> select snap_interval,retention from dba_hist_wr_control;
SNAP_INTERVAL RETENTION
-------------------- --------------------
+00000 01:00:00.0 +00007 00:00:00.0
--通过下面的试图查出，节点2在在2014-02-22开始就不产生快照信息了，但是节点1正常
select instance_number,snap_id,
to_char(begin_interval_time, 'YYYY-MM-DD'),
to_char(end_interval_time, 'YYYY-MM-DD')
from dba_hist_snapshot
order by snap_id;
INSTANCE_NUMBER SNAP_ID TO_CHAR(BEGIN_INTERV TO_CHAR(END_INTERVAL
-------------- ---------- -------------------- --------------------
......
2 20543 2014-02-21 2014-02-21
1 20543 2014-02-21 2014-02-21
2 20544 2014-02-21 2014-02-21
1 20544 2014-02-21 2014-02-21
2 20545 2014-02-21 2014-02-21
1 20545 2014-02-21 2014-02-21
2 20546 2014-02-21 2014-02-21
1 20546 2014-02-21 2014-02-21
2 20547 2014-02-21 2014-02-21
1 20547 2014-02-21 2014-02-21
1 20548 2014-02-21 2014-02-21
2 20548 2014-02-21 2014-02-21
2 20549 2014-02-21 2014-02-21
1 20549 2014-02-21 2014-02-21
2 20550 2014-02-21 2014-02-21
1 20550 2014-02-21 2014-02-21
2 20551 2014-02-21 2014-02-21
1 20551 2014-02-21 2014-02-21
1 20552 2014-02-21 2014-02-21
2 20552 2014-02-21 2014-02-21
2 20553 2014-02-21 2014-02-21
1 20553 2014-02-21 2014-02-21
2 20554 2014-02-21 2014-02-22
1 20554 2014-02-21 2014-02-21
1 20555 2014-02-21 2014-02-22--开始node2就不产生快照了
1 20556 2014-02-22 2014-02-22
1 20557 2014-02-22 2014-02-22
1 20558 2014-02-22 2014-02-22
1 20559 2014-02-22 2014-02-22
.......
--aler日志切换正常，前后没有ORA-错误；
--在node 2上的，mmon进程trace文件很大,大约700M，从2-11号开始写，之前没有做event跟踪,平均每1分钟写下面的内容
.....
*** 2014-02-23 07:33:39.756
SGA POLICY: Cache below reserve and cant get memory from any other component
GRANULE SIZE is 16777216
COMPONENT NAME : shared pool, curnum=131, usrsz=128, tgt=131, st=0
Number of granules in inactive list (listid 0) is 0
Number of granules in activate list (listid 1) is 0
Number of granules in inuse list (listid 2) is 131
Number of granules in quiesce list (listid 3) is 0
Number of granules in partially inuse list (listid 4) is 0
......
COMPONENT NAME : ASM Buffer Cache, curnum=0, usrsz=875, tgt=0, st=0
Number of granules in inactive list (listid 0) is 0
Number of granules in activate list (listid 1) is 0
Number of granules in inuse list (listid 2) is 0
Number of granules in quiesce list (listid 3) is 0
Number of granules in partially inuse list (listid 4) is 0
*** 2014-02-23 07:34:09.764
SGA POLICY: Cache below reserve and cant get memory from any other component
..... 手动在 不能生成快照的节点执行：
exec dbms_workload_repository.create_snapshot;
效果如何？ 25号早晨8:30左右手动执行时：
SQL> exec dbms_workload_repository.create_snapshot;
BEGIN dbms_workload_repository.create_snapshot; END;
*
ERROR at line 1:
ORA-13509: error encountered during updates to a AWR table
ORA-04031: unable to allocate ORA-04031: unable to allocate 4192 bytes of
shared memory ("shared pool","unknown object","sga heap(1,0)","modification ")
bytes of shared memory ("","","","")
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 10
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 33
ORA-06512: at line 1
在25号的alert日志报了ORA-04031错误，alert日志和trace文件在附件里 2节点
Mon Feb 24 22:50:11 CST 2014
Errors in file /opt/app/oracle/admin/gpsdb/bdump/gpsdb2_j001_28172.trc:
ORA-12012: error on auto execute of job 1
ORA-04031: unable to allocate 4080 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","library cache")
ORA-06508: PL/SQL: could not find program unit being called: "SYS.STANDARD"
ORA-06512: at "SYSMAN.EMD_MAINTENANCE", line 929
ORA-06512: at line 1
4031错误一直出现，可以认为2节点的 负载或者配置本身存在问题， 那么出现快照生成不了 也很正常。
按照基本的 故障诊断思路去解决即可 已处理：
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
谢谢~ 没列出你的处理过程 ，如果有的话 ML 会给你加分的 。 我的RAC有一个节点可以自动生成awr,另外一个节点不可以,但是手动执行exec dbms_workload_repository.create_snapshot() 没有问题,,,..mmon日志也看了 没看到有什么报错,正在解决此问题... zm_tree 发表于 2014-2-25 13:48 static/image/common/back.gif
已处理：
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed ...
你咋处理的?分享分享
页:
[1]