EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS的删除创建

时间:2021-08-22 20:35:05

在最近的一次优化过程中发现了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)。切记切记。

EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS的删除创建

 

重建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;

EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS的删除创建