Oracle Enterprise Manager job is Hung

Question

I have a OEM job hung for few days and unfortunately logging is not enabled so i would like to know how to troubleshoot this issues ? or Better approach to create/handle OEM job ?

DECLARE
  CHECK DATE;
BEGIN
  CHECK := NULL;
  PROCESS_MAIL_TOTAL(
    CHECK => CHECK
  );
END;

PROCESS_MAIL_TOTAL is the procedure used in PROCESS_MAIL_JOB OEM job.

create or replace PROCEDURE PROCESS_MAIL_TOTAL
  (
    CHECK IN DATE )
AS
  M_CHECK DATE;
  M_DATE_FORMAT VARCHAR2(20);
BEGIN
  M_DATE_FORMAT := 'YYYY-MM-DD';
  IF ( CHECK IS NULL ) THEN
    M_CHECK    := to_date(TO_CHAR(sysdate - 1, M_DATE_FORMAT), M_DATE_FORMAT);
  ELSE
    M_CHECK := to_date(TO_CHAR(CHECK, M_DATE_FORMAT), M_DATE_FORMAT);
  END IF;
  DELETE
  FROM MAIL_TABLE
  WHERE mail_process_dt = M_CHECK;
  INSERT INTO MAIL_TABLE
  SELECT COUNT(*) total
  FROM MAILBOX
  WHERE MESSAGE LIKE '%Invalid%';
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Its Broke!');
END PROCESS_MAIL_TOTAL;

OEM ERROR:

when i tried running a hung job

java.sql.SQLException: ORA-27492: unable to run job "PROCESS_MAIL_JOB": scheduler unavailable Check the log/trace file for more Details

Answer 1

Try:

select * from v$parameter where upper(name) = 'JOB_QUEUE_PROCESSES';

JOB_QUEUE_PROCESSES "value" might be set to 0. I believe you would need an alter system to change it, for example: alter system set job_queue_processes = 10; From the ORA error code 27492:

A job run with current session set to false can not be issued if the scheduler is not active. Check value of job_queue_processes parameter, issue dbms_ijob.set_enabled(true), database is in upgrade/migrade mode, database is in data guard mode, or scheduler attribute SCHEDULER_DISABLED is set to TRUE

Run the job in the current session or activate the scheduler

Answer 2

    NUM                  NAME   TYPE    VALUE   DISPLAY_VALUE   ISDEFAULT   ISSES_MODIFIABLE    ISSYS_MODIFIABLE    ISINSTANCE_MODIFIABLE   ISMODIFIED  ISADJUSTED  ISDEPRECATED    ISBASIC DESCRIPTION UPDATE_COMMENT  HASH
      1     job_queue_processes 3         0                 0     FALSE              FALSE                  IMMEDIATE       TRUE                    FALSE   FALSE   FALSE   FALSE   maximum number of job queue slave processes     1663833312