Remove / Delete / Clean up Orphan Jobs from OEM Repository Database

One of the database has been deleted from 11g OEM. Once the databases are deleted from OEM, the jobs associated with the database will also get deleted.

But sometimes there are chances that Jobs which were scheduled for that targets left orphan without being dropped.

Here we have a database named "ORALIN" which was dropped earlier but the job exist in mgmt_job. When we search for a job in Jobs tab, it doesnt report any jobs.

=====================================================================================================================
Step 1 : Job Information
=====================================================================================================================

set lines 200
col job_name for a35
col job_owner for a25
select job_id, job_name, job_owner,job_status from mgmt_job where job_name ='FULL_DATABASE_ORALIN_EXADATA_WEEKLY';

JOB_ID                           JOB_NAME                            JOB_OWNER                 JOB_STATUS
-------------------------------- ----------------------------------- ------------------------- ----------
C2DF0310F7ED51EWR1E04014AC35966266 FULL_DATABASE_ORALIN_EXADATA_WEEKLY  KASI                               2

Note : Job_status = 2 ( Job is stopped )

#### Job Execution Summary

select EXECUTION_ID, status from MGMT_JOB_EXEC_SUMMARY where job_id ='C2DF0310F7ED51EWR1E04014AC35966266' ; 

EXECUTION_ID                         STATUS
-------------------------------- ----------
D175BEBBEF4B378FEQ04014QAC33961A19         19

=====================================================================================================================
Step 2 : Stop the Job
=====================================================================================================================

As the jobs are not showing in OEM, we cant delete via oem, so we need to delete from OEM Repository Database.

#### Stop the Job Forcefully. ( Some times jobs might show as running instead of scheduled state, in this case its not applicable )

exec mgmt_job_engine.stop_all_executions_with_id('C2DF0310F7ED51EWR1E04014AC35966266');

SQL> exec mgmt_job_engine.stop_all_executions_with_id('C2DF0310F7ED51EWR1E04014AC35966266');
PL/SQL procedure successfully completed.

Where,

C2DF0310F7ED51EWR1E04014AC35966266 - is the job_id from mgmt_job taken from Step 1

#### Job Execution Summary

select EXECUTION_ID, status from MGMT_JOB_EXEC_SUMMARY where job_id ='C2DF0310F7ED51EWR1E04014AC35966266' ; 

EXECUTION_ID                         STATUS
-------------------------------- ----------
D175BEBBEF4B378FEQ04014QAC33961A19         19

=====================================================================================================================
Step 3 : Remove Orphan Jobs
=====================================================================================================================

Below queries should be run as sysman user.

exec mgmt_job_engine.delete_job('C2DF0310F7ED51EWR1E04014AC35966266');

SQL> exec mgmt_job_engine.delete_job('C2DF0310F7ED51EWR1E04014AC35966266');
PL/SQL procedure successfully completed.
SQL>

#### Job Information

set lines 200
col job_name for a35
col job_owner for a25
select job_id, job_name, job_owner,job_status from mgmt_job where job_name ='FULL_DATABASE_ORALIN_EXADATA_WEEKLY';

no rows selected

SQL>

Now we have successfully removed the orphan job from 11g OEM Repository.

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ END @@@@@@@@@@@@@@@@@@@