error on auto execute of job "SYS"."PURGE_LOG"錯誤分析

尛樣兒發表於2013-02-19
Error

        資料庫版本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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章