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>sysmanadminemdrepsqlcorelatestadminadmin_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

 

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>sysmanadminemdrepsqlcorelatestadmin
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;