Emd_maintenance.execute_em_dbms_job_procsdelete and create dbmssql.exe cute

Emd_maintenance.execute_em_dbms_job_procsdelete and create dbmssql.exe cute

In the last optimization process, we found that EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS was executed quite frequently in a 10 Gb ORACLE job. In fact, we have seen it before, but we didn't know much about it or pay close attention to it. This task may cause some performance problems in some versions or situations. In fact, EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS is a job that collects relevant data for Database Control. If you do not use Database Control, you can delete it. The following are official materials

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 shoshould be running every minute for running Ming all the above operations.

As shown in the following figure, it is executed frequently and once every minute.

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> 

Remove EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS

To remove this task, follow these steps:

1: First, check whether the sysman account is locked. If the account is locked, unlock the account. If not, skip this step.

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: View and set the parameter job_queue_processes to 0 (when this value is set to 0, jobs created in any way will not run)

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. log on to sysman and execute the following script to remove the job.

SQL> exec sysman.emd_maintenance.remove_em_dbms_jobs;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> 

Of course, you can also execute the following script to remove the task.

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

4: query the DBA_JOBS view, check whether the task is removed, and reset the value of 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.

Note: If the EM job is submitted by sys or another sysdba, you must log on with the sys account to remove the job. The script executed with sysman logon cannot be removed. You can pay attention to the LOG_USER field when querying a job. (If the value of LOG_USER is sysman, it is submitted by sysman; otherwise, it is another sysdba ). Remember.

EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS Reconstruction

1: Log On As A sysman user and confirm that the parameter job_queue_processes is not 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: execute the following script

SQL>  exec emd_maintenance.submit_em_dbms_jobs; 

PL/SQL procedure successfully completed.

Or

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

admin_submit_dbms_jobs.sql; 

3: Invalid object re-Compilation

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;