大学同学在生产awr时,遇到ORA-04023错误,awr正式内容开始之前出现大量如下信息,并重复多次
WARNING (-4023) ORA-04023: Object SELECT SUM(VALUE), COUNT(INSTANCE_NUMBER), SUM(INSTANCE_NUMBER) FROM DBA_HIST_SYS_TIME_MODEL WHERE DBID = :B4 AND SNAP_ID = :B3 AND INSTANCE_NUMBER MEMBER OF :B2 AND STAT_NAME = :B1 could not be validated or authorized
参考网上部分文章的说法,尝试alter system flush shared_pool;
在清理的shared pool之后,确实得到了解决,但是过一段时间,还可能会出现同样的问题
总不能每次都去flush一下shared pool吧,后来我在metalink上查到,这是一个bug
Bug 9843251 : ORA-04023 ERROR OCURRED WHEN GENERATING AWR REPORT
官方给出的解释为:
Abstract: ORA-4023 ERROR OCURRED WHEN GENERATING AWR REPORT *** 06/23/10 01:38 am *** *** 06/23/10 02:09 am *** *** 06/23/10 02:09 am *** (CHG: Sta->16) *** 07/15/10 07:27 am *** (CHG: Sta->10 Asg->BDUIJNEN SubComp->DICTIONARY) *** 07/15/10 07:27 am *** (ADD: Impact/Symptom->NON-INTERNAL ERROR ) *** 07/15/10 07:27 am *** The ORA-4023 error means that we could not parse the statement even when
trying 100 times. This used to be kksfbc-reparse-infinite-loop.
The typical reason is that timestamps for valid objects are wrong
somewhere.
Please do an alter view compile for:
DBA_HIST_SYS_TIME_MODEL, DBA_HIST_STAT_NAME
And check if the problem is resolved.
Looks to be the same problem as in bug 9863554. *** 08/02/10 10:15 pm *** *** 08/02/10 10:16 pm *** (CHG: Sta->16) *** 08/02/10 11:58 pm *** (CHG: Sta->91) *** 08/02/10 11:58 pm *** Closing as could not reproduce. *** 08/03/10 12:32 am *** *** 03/28/13 10:21 am *** *** 03/02/15 06:31 am ***
Abstract: LIBRARY CACHE: MUTEX X EVENT AND ORA-4023 OCCURED *** 06/29/10 09:40 pm *** *** 06/29/10 09:40 pm *** (ADD: Impact/Symptom->FEATURE UNUSABLE ) *** 06/29/10 09:40 pm ***
BUG TYPE CHOSEN
===============
Supportability
SubComponent: Performance Tools
===============================
DETAILED PROBLEM DESCRIPTION
============================
Customer encountered intermittent performance issue when generating an AWR
report during a schedule job with high waits on 'library cache: mutex X'
event and the AWR report error with ORA-4023.
Below error in the AWR report :-
WARNING (-4023) ORA-4023: Object SELECT SUM(VALUE), COUNT(INSTANCE_NUMBER),
SUM(INSTANCE_NUMBER) FROM DBA_HIST_SYS_TIME_MODEL WHERE DBID = :B4 AND
SNAP_ID = :B3 AND INSTANCE_NUMBER MEMBER OF :B2 AND STAT_NAME = :B1 could
not be validated or authorized WARNING: Since the DB Time is less than one
second, there was minimal foreground activity in the snapshot period. Some
of the percentage values will be invalid.
DIAGNOSTIC ANALYSIS
===================
Expect no issue during AWR generation.
WORKAROUND?
===========
No
TECHNICAL IMPACT
================
Performance issue impacting the DB when generating AWR report via cronjob.
RELATED ISSUES (bugs, forums, RFAs)
===================================
Bug 9843251 and Bug 8992081
HOW OFTEN DOES THE ISSUE REPRODUCE AT CUSTOMER SITE?
====================================================
Intermittent
DOES THE ISSUE REPRODUCE INTERNALLY?
====================================
Never
IS A TESTCASE AVAILABLE?
========================
No
Link to IPS Package:
====================
NA
*** 06/29/10 09:42 pm *** *** 07/22/10 07:54 am *** *** 07/22/10 07:56 am *** *** 07/22/10 07:56 am *** (CHG: Sta->10) *** 08/10/10 01:06 am *** (CHG: Sta->16) *** 08/10/10 01:06 am *** *** 08/10/10 09:42 am *** *** 08/10/10 09:42 am *** (CHG: Sta->10) *** 08/10/10 03:05 pm *** (CHG: Sta->16) *** 08/10/10 03:05 pm *** *** 08/10/10 04:37 pm *** *** 08/10/10 04:37 pm *** (CHG: Sta->10) *** 08/10/10 10:14 pm *** (CHG: Sta->16) *** 08/10/10 10:14 pm *** *** 08/10/10 10:14 pm *** *** 08/11/10 03:28 pm *** *** 08/11/10 03:29 pm *** (CHG: Sta->10) *** 10/08/10 01:58 pm *** (CHG: Sta->33) *** 10/08/10 01:58 pm *** *** 12/14/14 06:15 pm *** *** 12/14/14 06:21 pm ***
我同学没有打补丁,通过重新编译 DBA_HIST_SYS_TIME_MODEL, DBA_HIST_STAT_NAME解决了这个问题
希望遇到同样问题的人,看到了会有所帮助。
WARNING (-4023) ORA-04023: Object SELECT SUM(VALUE), COUNT(INSTANCE_NUMBER), SUM(INSTANCE_NUMBER) FROM DBA_HIST_SYS_TIME_MODEL WHERE DBID = :B4 AND SNAP_ID = :B3 AND INSTANCE_NUMBER MEMBER OF :B2 AND STAT_NAME = :B1 could not be validated or authorized
参考网上部分文章的说法,尝试alter system flush shared_pool;
在清理的shared pool之后,确实得到了解决,但是过一段时间,还可能会出现同样的问题
总不能每次都去flush一下shared pool吧,后来我在metalink上查到,这是一个bug
Bug 9843251 : ORA-04023 ERROR OCURRED WHEN GENERATING AWR REPORT
官方给出的解释为:
bug1、
Hdr: 9843251 11.2.0.1 RDBMS 11.2.0.1 DICTIONARY PRODID-5 PORTID-23Abstract: ORA-4023 ERROR OCURRED WHEN GENERATING AWR REPORT *** 06/23/10 01:38 am *** *** 06/23/10 02:09 am *** *** 06/23/10 02:09 am *** (CHG: Sta->16) *** 07/15/10 07:27 am *** (CHG: Sta->10 Asg->BDUIJNEN SubComp->DICTIONARY) *** 07/15/10 07:27 am *** (ADD: Impact/Symptom->NON-INTERNAL ERROR ) *** 07/15/10 07:27 am *** The ORA-4023 error means that we could not parse the statement even when
trying 100 times. This used to be kksfbc-reparse-infinite-loop.
The typical reason is that timestamps for valid objects are wrong
somewhere.
Please do an alter view compile for:
DBA_HIST_SYS_TIME_MODEL, DBA_HIST_STAT_NAME
And check if the problem is resolved.
Looks to be the same problem as in bug 9863554. *** 08/02/10 10:15 pm *** *** 08/02/10 10:16 pm *** (CHG: Sta->16) *** 08/02/10 11:58 pm *** (CHG: Sta->91) *** 08/02/10 11:58 pm *** Closing as could not reproduce. *** 08/03/10 12:32 am *** *** 03/28/13 10:21 am *** *** 03/02/15 06:31 am ***
bug2、
Hdr: 9863554 11.2.0.1 RDBMS 11.2.0.1 PERF TOOLS PRODID-5 PORTID-226 ORA-4023Abstract: LIBRARY CACHE: MUTEX X EVENT AND ORA-4023 OCCURED *** 06/29/10 09:40 pm *** *** 06/29/10 09:40 pm *** (ADD: Impact/Symptom->FEATURE UNUSABLE ) *** 06/29/10 09:40 pm ***
BUG TYPE CHOSEN
===============
Supportability
SubComponent: Performance Tools
===============================
DETAILED PROBLEM DESCRIPTION
============================
Customer encountered intermittent performance issue when generating an AWR
report during a schedule job with high waits on 'library cache: mutex X'
event and the AWR report error with ORA-4023.
Below error in the AWR report :-
WARNING (-4023) ORA-4023: Object SELECT SUM(VALUE), COUNT(INSTANCE_NUMBER),
SUM(INSTANCE_NUMBER) FROM DBA_HIST_SYS_TIME_MODEL WHERE DBID = :B4 AND
SNAP_ID = :B3 AND INSTANCE_NUMBER MEMBER OF :B2 AND STAT_NAME = :B1 could
not be validated or authorized WARNING: Since the DB Time is less than one
second, there was minimal foreground activity in the snapshot period. Some
of the percentage values will be invalid.
DIAGNOSTIC ANALYSIS
===================
Expect no issue during AWR generation.
WORKAROUND?
===========
No
TECHNICAL IMPACT
================
Performance issue impacting the DB when generating AWR report via cronjob.
RELATED ISSUES (bugs, forums, RFAs)
===================================
Bug 9843251 and Bug 8992081
HOW OFTEN DOES THE ISSUE REPRODUCE AT CUSTOMER SITE?
====================================================
Intermittent
DOES THE ISSUE REPRODUCE INTERNALLY?
====================================
Never
IS A TESTCASE AVAILABLE?
========================
No
Link to IPS Package:
====================
NA
*** 06/29/10 09:42 pm *** *** 07/22/10 07:54 am *** *** 07/22/10 07:56 am *** *** 07/22/10 07:56 am *** (CHG: Sta->10) *** 08/10/10 01:06 am *** (CHG: Sta->16) *** 08/10/10 01:06 am *** *** 08/10/10 09:42 am *** *** 08/10/10 09:42 am *** (CHG: Sta->10) *** 08/10/10 03:05 pm *** (CHG: Sta->16) *** 08/10/10 03:05 pm *** *** 08/10/10 04:37 pm *** *** 08/10/10 04:37 pm *** (CHG: Sta->10) *** 08/10/10 10:14 pm *** (CHG: Sta->16) *** 08/10/10 10:14 pm *** *** 08/10/10 10:14 pm *** *** 08/11/10 03:28 pm *** *** 08/11/10 03:29 pm *** (CHG: Sta->10) *** 10/08/10 01:58 pm *** (CHG: Sta->33) *** 10/08/10 01:58 pm *** *** 12/14/14 06:15 pm *** *** 12/14/14 06:21 pm ***
我同学没有打补丁,通过重新编译 DBA_HIST_SYS_TIME_MODEL, DBA_HIST_STAT_NAME解决了这个问题
希望遇到同样问题的人,看到了会有所帮助。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30202921/viewspace-1695708/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30202921/viewspace-1695708/