4 Replies Latest reply: Oct 6, 2012 1:47 PM by spajdy RSS

    DBMS scheduler jobs running twice

    892489
      Hi,

      I have 4 DBMS scheduler jobs , which checks for a specific job status in DB and sends an email , when i started the schedule for the first week the jobs executed fine from next week I am getting two emails from each job , when i check the logs USER_SCHEDULER_JOB_RUN_DETAILS I see only one run , which seems weird to me so i disabled one job and left the three jobs in schedule , next time i got two emails from 3 jobs and one from disabled job . After checking logs i see that there is no entry of the disabled job execution . I am not sure where is the problem i can't find any log from where the disabled job executing. Please help me

      Job schedule is to run every Saturday

      Interval setup :

      start_date => trunc(SYSDATE)+ 8.5/24,
      repeat_interval => 'TRUNC(LEAST(NEXT_DAY(SYSDATE,''SATURDAY'') )) + 8.5/24'

      Suresh
        • 1. Re: DBMS scheduler jobs running twice
          892489
          Hi,

          I tried to schedule the same jobs using DBMS_JOB but i still get the same problem , I created the procedure with all code in and scheduled it using dbms job , first day it run once second day it run twice ( sending two emails) Inow i am not sure if issue is with my code or scheduler

          Procedure

          Declare
          v_count number;
          v_Recipient VARCHAR2(400) := 'sender@company.com';
          v_Subject VARCHAR2(80) := 'TEST_Email';
          v_Mail_Host VARCHAR2(30) := 'localhost';
          v_Mail_Conn utl_smtp.Connection;
          crlf VARCHAR2(2) := chr(13)||chr(10);
          BEGIN
          select count(*) into v_count from TEC_CODERETURN@RPRD where interface like 'FOR002B' and trunc(rundate) =trunc(sysdate);
          if v_count = 0
          then
          v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
          utl_smtp.Rcpt(v_Mail_Conn, 'receiver@company.com');
          UTL_SMTP.OPEN_DATA(v_Mail_Conn);
          utl_smtp.WRITE_RAW_DATA(v_Mail_Conn, UTL_RAW.CAST_TO_RAW(
          'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || UTL_TCP.CRLF ||
          'From: ' || 'sender@company.com' || UTL_TCP.CRLF ||
          'Subject: '|| v_Subject || UTL_TCP.CRLF ||
          'To: ' || v_Recipient || UTL_TCP.CRLF ||
          'This is a test Alert'|| UTL_TCP.CRLF
          ));
          UTL_SMTP.CLOSE_DATA(v_mail_conn);
          utl_smtp.Quit(v_mail_conn);
          end if;
          EXCEPTION
          WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
          raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
          END;
          /

          DBMS job creation

          DECLARE
          jobno NUMBER;
          BEGIN
          DBMS_JOB.submit
          (job => jobno,
          what => 'TEST_ALERT;',
          next_date => trunc(sysdate)+0.1/24,
          interval => 'SYSDATE + 1',
          no_parse => TRUE );

          DBMS_OUTPUT.put_line ('Created Job - the job number is:' || TO_CHAR (jobno));
          COMMIT;
          END;
          /

          Suresh
          • 2. Re: DBMS scheduler jobs running twice
            spajdy
            It's really ver curious.
            When you run you code (procedure) by hand how many email do you receive ?

            Are you sure that you have this job defined only once ?
            Run job sucessfuly (dba_jobs.failures=0 for this job)?
            • 3. Re: DBMS scheduler jobs running twice
              892489
              Hi,

              I tested these jobs with minutes interval close to 30 40 times , it runs fine with one email. once the day passes it starts sending second email. one time i disabled the job for a day but i still got email form the job and i don't see any log of the job run. Seems Oracle bug.

              We opened SR but no reply yet so I had to switch to UNIX using Cron and sendmail .

              Suresh
              • 4. Re: DBMS scheduler jobs running twice
                spajdy
                Are you sure that you don't have two instances of the job ?