DBA Jobs 

Check Job status 

 select JOB,SCHEMA_USER,LAST_DATE,LAST_SEC,NEXT_DATE,NEXT_SEC,BROKEN,INTERVAL,WHAT,failures from dba_jobs where JOB in ('17057','17058');

                    broken--  Job become broken after 16 failures .

                    what  -    procedure being called

                    

 Fixing broken Jobs

  Connect to database as job owner user

    EXEC DBMS_JOB.BROKEN(17058,FALSE);

    Commit ;

                Make sure to put commit .

                Make sure next date and time is updated with latest time stamp

   Sometimes even after fixing the Job failure count remains the same i.e 16 ,To overcome it run the Job manually and Job will get fixed within defined interval time.

   connect to database as job owner user
     EXEC DBMS_JOB.RUN(17058 ) ;

     Commit ;

       

 If A job fails reason can be seen in alert log .It generates trace file along with root 

SET linesize 200 pagesize 200

col RECORD_ID FOR 9999999 head ID

col MESSAGE_TEXT FOR a120 head Message

SELECT ORIGINATING_TIMESTAMP,record_id, message_text

FROM X$DBGALERTEXT

WHERE originating_timestamp > systimestamp - 2 AND regexp_like(message_text, '(ORA-|error)');

--------------------------------------

select * from dba_jobs_running;

BEGIN

DBMS_JOB.NEXT_DATE(17058, sysdate+1/1440);

END;

/

commit;

1/1440  ->1 minutes

10/1440  -> 10 minutes

to remove 16 fail;ures.

Procedure to fix all the broken Jobs

BEGIN

  update company a

  set a.cycling_active = 'Y';

  commit;

  FOR x IN (SELECT broken, job, what FROM dba_jobs WHERE job IN (133, 135, 3619, 12569, 17017, 5999)) LOOP

    IF x.broken = 'Y' THEN

      dbms_job.broken(x.job,FALSE,SYSDATE);

      dbms_output.put_line('Job '||x.job||' has started. '||x.what);

      COMMIT;

    ELSE

      dbms_output.put_line('Job '||x.job||' was already running. '||x.what);

    END IF;

  END LOOP;

END;

/

Query to check jobs from over DB link.

SELECT owner,

       job_name,

       status,

       to_char(log_date,'YYYY-MM-DD HH24:MI:SS') actual_start_date,

       additional_info

  FROM sys.dba_scheduler_job_run_details

WHERE status = 'FAILED'

UNION ALL

SELECT owner,

       job_name,

       status,

       to_char(log_date,'YYYY-MM-DD HH24:MI:SS') actual_start_date,

       additional_info

  FROM sys.dba_scheduler_job_run_details@koegndbl

WHERE status = 'FAILED'

ORDER BY actual_start_date DESC;