你的位置:首页 > 数据库

[数据库]EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS的删除创建


    在最近的一次优化过程中发现了ORACLE 10g中一个作业EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS执行相当频繁,其实以前也看到过,只是没有做过多的了解和关注。这个任务在某些版本或某些情况会引起一些性能问题。其实EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS这个作业是为Database Control收集相关数据的一个作业,如果没有使用Database Control,完全可以删除。下面是官方介绍资料

 

The EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS job performs all the necessary maintenance tasks for the database control repository. These tasks include :

+ Agent Ping Verification (EM_PING.MARK_NODE_STATUS)

+ Job Purge (MGMT_JOB_ENGINE.APPLY_PURGE_POLICIES)

+ Metric Rollup (EMD_LOADER.ROLLUP)

+ Purge Policies (EM_PURGE.APPLY_PURGE_POLICIES)

+ Repository Metric Severity Calculation (EM_SEVERITY_REPOS.EXECUTE_REPOS_SEVERITY_EVAL)

+ Repository Side Collections (EMD_COLLECTION.RUN_COLLECTIONS)

+ Send Notifications

This job should be running every minute for performing all the above operations.

 

如下所示,它执行的频繁相当频繁,一分钟执行一次

SQL> SELECT SCHEMA_USER, WHAT, INTERVAL FROM DBA_JOBS 
  2  WHERE WHAT='EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();';
 
SCHEMA_USER          WHAT                                        INTERVAL
----------- -------------------------------------------- -------------------------
SYSMAN       EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();   sysdate + 1 / (24 * 60)
 
SQL> 


移除EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS

 

如何移除这个任务呢,一般情况下使用要用sysman用户登录操作,具体步骤如下所示:

 

1:首先检查用sysman账号是否锁定了,如果锁定了需要解锁,如果没有的话,直接跳过这一步

SQL> show user;
USER is "SYS"
SQL> select username,account_status from dba_users where username='SYSMAN';
 
USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SYSMAN                         EXPIRED & LOCKED
 
SQL> alter user sysman account unlock;
 
User altered.
 
SQL> alter user sysman identified by newpassword;
 
User altered.


 

2:查看并设置参数job_queue_processes为0(当设定该值为0的时候则任意方式创建的job都不会运行)

SQL> show parameter job_queue_processes;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10
SQL> alter system set job_queue_processes=0;
 
System altered.
 
SQL> select * from dba_jobs_running;
 
no rows selected
 
SQL> select * from dba_jobs_running;
 
no rows selected
 
SQL> select * from dba_jobs_running;
 
no rows selected


 

3. 以sysman登录执行下面脚本,移除该作业

SQL> exec sysman.emd_maintenance.remove_em_dbms_jobs;
 
PL/SQL procedure successfully completed.
 
SQL> commit;
 
Commit complete.
 
SQL> 


当然也可以执行下面脚本来移除任务

SQL> @<ORACLE_HOME>\sysman\admin\emdrep\sql\core\latest\admin\admin_remove_dbms_jobs.sql;

 

4:查询DBA_JOBS视图,确认任务是否移除,重设参数job_queue_processes值

If the EM jobs were submitted as SYS (or another SYSDBA account), the removal must be done as SYS (or that specific) account.

注意:如果EM的作业是以sys或者其他sysdba提交的,则必须使用sys账号登录才能移除,上面以sysman登录执行的脚本并不能移除该任务。具体可以在查询作业的时候留意LOG_USER字段(LOG_USER的值为sysman的才是sysman提交的,否则为其它sysdba)。切记切记。

clip_image001

 

重建EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS

 

1:以sysman用户登录,确认参数job_queue_processes不为0

SQL> show user;
USER is "SYSMAN"
SQL>  show parameter job_queue_processes
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0
SQL> alter system set job_queue_processes=10;
 
System altered.


 

2:执行下面脚本

SQL>  exec emd_maintenance.submit_em_dbms_jobs; 
 
PL/SQL procedure successfully completed.
 
SQL>@<ORACLE_HOME>\sysman\admin\emdrep\sql\core\latest\admin\ 
admin_submit_dbms_jobs.sql; 


 

3:重编译无效对象

PL/SQL procedure successfully completed.
 
SQL> exec emd_maintenance.recompile_invalid_objects;
 
PL/SQL procedure successfully completed.
 
SQL> 
 
For 11.1.0.7.0 and above databases:
SQL> exec emd_maint_util.recompile_invalid_objects;


clip_image002