error on auto execute of job "SYS"."PURGE_LOG"错误分析

        数据库版本11.2.0.3 for Linux x86-64bit，告警日志有如下报错：

Tue Feb 05 17:00:01 2013
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_1416.trc:
ORA-12012: error on auto execute of job "SYS"."PURGE_LOG"
ORA-04063: package body "SYS.DBMS_AQADM_SYS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"
ORA-06512: at "SYS.DBMS_ISCHED", line 678
ORA-06512: at "SYS.DBMS_SCHEDULER", line 3425

......

Tue Feb 05 17:23:26 2013
Archived Log entry 2543 added for thread 1 sequence 2543 ID 0x4f636503 dest 1:
Tue Feb 05 18:00:00 2013
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter

执行下面的SQL查询PURGE_LOG JOB信息：
SQL> select owner, program_name, program_type, program_action
  2    from dba_scheduler_programs
  3   where (owner, program_name) in
  4         (select owner, program_name
  5            from dba_scheduler_jobs
  6           where job_name = 'PURGE_LOG');

OWNER PROGRAM_NAME                   PROGRAM_TYPE     PROGRAM_ACTION
----- ------------------------------ ---------------- ------------------------------
SYS   PURGE_LOG_PROG                 STORED_PROCEDURE dbms_scheduler.auto_purge

        参考文章：http://www.dba-oracle.com/job_scheduling/job_run_details.htm，dbms_scheduler.auto_purge是根据LOG_HISTORY属性清除过期的scheduler日志信息，窗口期是每天下午的5~6点，默认scheduler日志保留的天数为30天。执行下面的SQL语句查询LOG_HISTORY的属性值：

SQL> select attribute_name, value
  2    from dba_scheduler_global_attribute
  3   where attribute_name = 'LOG_HISTORY';

ATTRIBUTE_NAME                 VALUE
------------------------------ ----------
LOG_HISTORY                    30


该存储过程也可以手动执行：
SQL> exec dbms_scheduler.auto_purge;

PL/SQL 过程已成功完成。


相关METALINK文章：

Resource Manager and Sql Tunning Advisory DEFAULT_MAINTENANCE_PLAN [ID 786346.1]
修改时间:2012-4-24类型:PROBLEM状态:MODERATED优先级:3

In this Document

Symptoms

Cause

Solution

---

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]
Oracle Server - Enterprise Edition - Version 11.1.0.6 to 11.1.0.7 [Release 11.1]
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 11.1.0.6 to 11.1.0.7 and 11.2.0.1 to 11.2.0.3

Symptoms

After upgrade to 11g getting the following messages in the alert.log. 

Setting Resource Manager plan SCHEDULER[0x51B5]:DEFAULT_MAINTENANCE_PLAN via scheduler window  
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter  
Thu Feb 05 22:00:03 2009  
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"  
Thu Feb 05 22:00:39 2009  
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"

Is database resource manager and sql tuning advisor being turned on by default in 11g.

Cause

Summary of changes between 10g and 11g. 

Resource Manager:

Subject	10g	11g
Maintenance Window	2 windows, WEEK and WEEKEND	Each day has its own window
Resource manager	Not enabled per default	Default resource plan specified

 

Sql Tunning Advisory:

Oracle Database 11g, by default, the Automatic Tunning Optimizer runs regularly during the Oracle scheduler Maintenance window, as the new automated maintenance task called the SQL Tunning Advisory task.

So this is a default behavior. in Oracle 11g.

Solution

1]. To disable the resource manager you can use the below steps. 

++ set the current resource manager plan to null (or another plan that is not restrictive):  

alter system set resource_manager_plan='' scope=both

++ change the active windows to use the null resource manager plan (or other nonrestrictive plan) using:  

execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN',''); and  
execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');  

++ Then, for each window_name (WINDOW_NAME from DBA_SCHEDULER_WINDOWS), run:  

SQL> execute dbms_scheduler.set_attribute('','RESOURCE_PLAN','');

 

2]. To disable SQL tunning you can use the below procedure.  

BEGIN  
DBMS_AUTO_TASK_ADMIN.DISABLE(  
client_name => 'sql tuning advisor',  
operation => NULL,  
window_name => NULL);  
END;  
/

相关bug一例：
High "Resmgr:Cpu Quantum" Wait Events In 11g Even When Resource Manager Is Disabled [ID 949033.1]
修改时间:2013-1-25类型:PROBLEM状态:PUBLISHED优先级:3

In this Document

Symptoms

Cause

Solution

References

---

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.
***Checked for relevance on 18-NOV-2011***

Symptoms

Issuing a sqlplus / as sysdba might be hanging and/or high waits on event 'resmgr:cpu quantum' might be noticed even when resource manager is disabled.    

You already have confirmed parameter RESOURCE_MANAGER_PLAN is set to null but still noticing the above wait events.

Top 5 Timed Foreground Events:

Event                    Waits   Time(s)  Avg wait(ms) % DB time Wait Class

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


resmgr:cpu quantum         1,596  346,281       216968          89.19 Scheduler


db file scattered read   171,071   14,778           86           3.81 User I/O


log file sync             28,575   10,810          378           2.78 Commit


db file sequential read  943,457   6,569             7           1.69 User I/O


DB CPU                     2,133   0.55

Cause

This could be due to DEFAULT_MAINTENANCE_PLAN. From 11g onwards every weekday window has a pre-defined Resource Plan called DEFAULT_MAINTENANCE_PLAN, which will become active once the related window opens.

Following entries can also be noted in alert log at the time of issue.

Wed Sep 16 02:00:00 2009
Clearing Resource Manager plan via parameter
:
Wed Sep 16 22:00:00 2009
Setting Resource Manager plan SCHEDULER[0x2C55]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Wed Sep 16 22:00:05 2009
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"

Solution

Please review the following document first to address any known bugs:
Note 392037.1 - Database 'Hangs'. Sessions Wait for 'resmgr:cpu quantum'

It may be better to move the maintenance windows to a time of day when CPU resources might be more available for such tasks to run and complete.

The following solution should only be used as a last resort because it may lead to other issues in the long run if Oracle has inadequate maintenance windows to collect new optimizer stats, find better execution plans for expensive SQL, purge AWR, etc.

To disable the DEFAULT_MAINTENANCE_PLAN you can use the below steps as suggested in Note 786346.1

1. Set the current resource manager plan to null (or another plan that is not restrictive):

alter system set resource_manager_plan='' scope=both;

2. Change the active windows to use the null resource manager plan (or other nonrestrictive plan) using:

execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');  
execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

3. Then, for each window_name (WINDOW_NAME from DBA_SCHEDULER_WINDOWS), run:

execute dbms_scheduler.set_attribute('','RESOURCE_PLAN','');

 

References

NOTE:392037.1 - Database 'Hangs'. Sessions Wait for 'resmgr:cpu quantum'


        参考文章：《11g：调度程序维护任务或 Autotasks [ID 1526120.1]》：https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1526120.1

--end--

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23135684/viewspace-754299/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23135684/viewspace-754299/