Monday, October 26, 2009

Stop dbconsole job hung in status Stop Pending

Some times a job run from EM Console usually backup when you try to stop it it has a status : "Stop Pending". Until this status changes you can not delete the job and the job may hung on this status. If this happens do the following :

Loging as sysman :

select job_id, job_name, job_owner from mgmt_job where job_name like '%%';

exec mgmt_job_engine.stop_all_executions_with_id('');

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

Stopped status=8 , but you may see other statuses. If so check for EM the job status. If its still Stop Pending then force the stop

exec mgmt_job_engine.stop_all_executions_with_id('',TRUE);

Now check the status either by running the query or using the EM and the delete the job from EM console

5 comments:

  1. In case something has gone really bad and the some executions are stuck in running status or stop pending then you have to do a manual update of the status of the problematic executions:

    (If you login as sys and not sysman add sysman. prefix to all tables and procedures)

    SQL>select job_id, job_name, job_owner from mgmt_job where job_name like '%%';

    SQL> select EXECUTION_ID, status from MGMT_JOB_EXEC_SUMMARY
    where job_id = ''; -- from previous query

    example output:

    EXECUTION_ID STATUS
    -------------------------------- ----------
    BC1B0E66C1937435E040460A29147ED8 5
    BBB679617ADE0507E040460A291452DC 5
    BBCA970D7AA48C18E040460A291413FE 5
    BBF2D2B2A8C2784AE040460A291414F6 5
    BC2F2C4B45626084E040460A29143F48 12
    BBDEB51156E447A1E040460A29145372 5
    BC06F0C99BACD91EE040460A2914555D 5

    7 rows selected.

    SQL> update mgmt_job_exec_summary SET status = 8,
    end_time = (sysdate - 1) WHERE job_id ='' and status = 12;

    1 row updated.

    Then you can delete the entire job with the following:

    SQL> exec mgmt_jobs.DELETE_JOB('','SYS');

    PL/SQL procedure successfully completed.

    ReplyDelete
  2. Thank you very much. It was very helpfull (The job was deleted).

    ReplyDelete
  3. Really nice thing you had posted here. And i hope this strategies will help the people for hiring process. Thank you and also i am expecting much more post from you. So please keep update like this.

    Hr Consultancy in Chennai

    ReplyDelete