Life became very easy after Oracle’s invention of OEM Grid Control. That is what Oracle promised us when they invented it. A couple of months ago one of my colleagues asked me to schedule backup jobs. In the past I made very nice OS scripts in order to make a backup. But now with OEM Grid Control being available for quite some time, I thought let’s try making backups using OEM Grid Control.
And yes it works fine. Grid Control makes quite interesting RMAN scripts. You can schedule these RMAN scripts. At one glimpse you can see all your backup jobs and the status of these backup jobs in the job activity list. Also you can see if these backup jobs have successfully run. For script kiddies OEM Grid Control is bad news, because it makes scripts for you. But if you like to be wizard kiddy, you feel to be in heaven.
But after some while
, life with OEM Grid Control turned out not so nice. After some proper backup runs, I found a backup job which remains status running. The next day a new backup job for the same database was automatically started. But this job got immediately status “1 problemsâ€. It turned out that the job was not started because Grid Control says: “An execution in one of the previous runs of the job was still running.â€
So I thought: let’s stop the running job. So I did. Then Grid Control told me: "The job execution was stopped successfully. Currently running steps will not be stopped." So I thought: life is easy again. But it turned out that this was not true. The running job got status “Stop Pending†and remained this status.
So I thought: let’s kill the running step. So I did. But then Grid Control says: "The step was not killed because it has already completed." But the job remains in status running.
So I thought: let’s delete the job. (what else should you do if OEM Grid Control refuses to listen). So I did, but then OEM Grid Control says: "The specified job, job run or execution is still active. It must finish running, or be stopped before it can be deleted. Filter on status ‘Active’ to see active executions."
Doom scenarios as: I will never be able to make a backup anymore of this database, came in my mind.†But finally Oracle Support send me this script:
DECLARE
jguid RAW(16);
BEGIN
SELECT job_id
INTO jguid
FROM mgmt_job
WHERE job_name = ‘<name of your job>’
AND job_owner = ‘<owner of your job>’
;
mgmt_job_engine.stop_all_executions_with_id(jguid,TRUE);
COMMIT;
END;
You have to run this script under user sysman on the OEM Grid Control repository database. You can find the name and owner of the job in the job activity list. Using this script I was able to solve my problem. The running job was deleted.
However I had to schedule a new backup job for this database again. This script deletes all runs of this job and so also the next occurrences of this job.
So if you ever run in a similar kind of problem then you can solve it by running this script and schedule a new job again.
Now life is easy again using OEM Grid Control, but not that easy as they promised us at first.
It is not clear to me why sometimes a job remains in status running.
Hi Herman, thank you for the script.
To the sysman connect problem – you can connect as user SYS as well, only need to use prefixes
sysman.mgmt_job and sysman.mgmt_job_engine.stop_all_executions_with_id(…
I had to run also
SQL> exec mgmt_job_engine.delete_all_executions(‘ARCH_BACKUP_NETL10′,’SYSMAN’);
to delete my job.
SQL> exec mgmt_job_engine.stop_all_executions_with_id(‘623F9047B6596AD6E0’,TRUE);
didn’t delete it only stopped it.
Thanks for posting this document. I had the same issue and was able to resolve it following the steps above. However, in my case, I had auditing enabled in Grid Control, so I had to disable it and bounce the OMS as follow:
Turn off audit in OEM
exec mgmt_audit_admin.set_audit(0,null,2);
COMMIT;
Bounce OMS.
/opmn/bin/opmnctl stopall
/opmn/bin/opmnctl startall
kill the job as outlined in you document above
Turn on audit in OEM
exec mgmt_audit_admin.set_audit(0,null,2);
COMMIT;
Once you exec the above script in sysman, you need to give the commit; (don’t forgot)
Shans
I have same problem in my production server (windows). I used the above valuable comment to solve the problem.
Thanks Shans
Thanks! You help me!
Hi i have login through sysman and excuted the script still the job was not stopped.
jobowner is sys what i have to do….?
please guide me and tell me the reason y it happens?
Hi have the same problem hw to login as sysman
Thanks so much for this. My google search only got me 3 hits and you we the first.
It worked when I change jguid RAW; to jguid RAW (50); otherwise it got the error PLS-00215 abouth absence of length og variable
Thank you very much for this command
Very handy, thanks
and in return, you can also use this to delete alerts that won’t go away
select ‘exec sysman.EM_SEVERITY.delete_current_severity(”’||TARGET_GUID||”’,”’||METRIC_GUID||”’,”’||KEY_VALUE||”’)’
from MGMT_CURRENT_SEVERITY
select the alert and zap it
Hi Jonathan, Just logon using for example sqlplus with: connect sysman/password@databasename_of_the_gridcontrol_repository
Hi, the script posted here was very useful. Thanks, Estifan
How do I log in as “sysman on the OEM Grid Control repository database”. Please explain
he-RMAN. 😎