Clearing a stuck job in OEM OPS Center

I have recently been involved with a project to upgrade a customers OEM OPS Center from 11g to 12c. Whilst the upgrade went smoothly I did notice during the cleanup process that a number of jobs were hanging around.

After further investigation, I noticed that the three jobs (1187, 1192, 1193) all had a status=stopping and had been in this state for the past 14 days which coincided with the OEM OPS Center upgrade I performed over that particular weekend. The hiccup here is that these legacy jobs are preventing new jobs from starting or completing successfully for these assets.

If I attempt to delete any of the legacy jobs via cli we get the message:

localhost/jobs > delete --id opsmon1.1187
    delete of job failed: Exception in Deleting Job(opscent1.759) - Job is in progress. Cannot delete this job. Please stop the job and then delete.. Refer EC cacao logs for more details.

So we attempt to force stop the stale job which also fails.

After scratching my head I head over to MOS and find that to resolve the issue we have to mark the job as completed within the database (this is easier said than done). Here are the steps required.

In this example the jobid = 1187

# ecadm sqplus
SQL > select * from scn_job_run where Job_id=1187;
---------- ----------
---------- -----
        1187         0
10-JUN-16 AM
24-JUN-16 PM

In the example the job run id is 0, we need to change the job status to completed (status=12)

# ecadm sqlplus

SQL> update scn_job_run set status=12 where Job_id=1187 and run_id=0;
SQL> commit;

The above was success for jobs 1187 and 1193, but 1192 also failed so we had to change the status to 16

# ecadm sqlplus

SQL> update scn_job_run set status=16 where Job_id=1192 and run_id=1;
SQL> commit;

Once the changes were committed to the database, we simply refresh the OPS Center UI and the legacy jobs have cleared and been removed from the list.