12 Replies Latest reply on Jan 19, 2011 12:13 AM by Rnr-Oracle

    Job action based on an event.

      Hi All,
      Would anyone know a good way using Oracle Scheduler to check when all the jobs have finished in a say daily schedule and then send out a log (job_name, start_time, end_time & run_duration) for all the jobs via email?
      We have setup our jobs to run on daily, weekly and monthly schedules. The schedule is a mix of independent jobs and job chains. So when say the daily schedule job finishes we want to send out the run details for all the jobs that ran that night. So this jobs run will be triggered by an event when all the jobs have completed.

      Any suggestions would be of great help..

      Thanks in advance

        • 1. Re: Job action based on an event.
          Any takers for this issue?
          Upon doing some reading on the web found that oracle AQ methods can be used to queue methods and trigger actions based on an event. Totally new to this concept, if any one could explain it with an example, it would be a great, great help. All we are looking for a job that runs in the end upon the completion of all the other jobs and chains within a daily schedule that would call a package to send out an email with the schedule run details..

          Thanks again in advance..
          • 2. Re: Job action based on an event.

            Here is one way I can think of to do this. Write a monitoring job that has the daily (or weekly or monthly) schedule

            - when it is first run it should set its schedule to be every five minutes

            - on subsequent runs it should query the jobs table for jobs pointing to the daily schedule to see whether any have yet to be run
            - If there are still jobs not run then exit.
            - If there are no remaining jobs to run then e-mail the report and change its own schedule back to the daily (or weekly or monthly) schedule .

            Hope this makes sense. The Scheduler does support generating events for jobs on start or completion and starting a job when a job is generated but I don't see any easy way to do this using that functionality.

            Hope this helps,
            • 3. Re: Job action based on an event.
              Hi Ravi,
              Thanks for the response.
              We had thought about this option, but thought if we could come up with a simpler solution to this requirement.
              We were thinking if we could use the oracle messaging functionality of AQ and have each job & chain queue up message upon its completion and the final job would trigger when the event of all the jobs/chains completion is received and then the job does it thing. The concept looks good on paper.. but don't know how we can implement this.

              Do you think that it can be done using messaging?

              Thanks again

              • 4. Re: Job action based on an event.

                You can definitely have all the jobs raise events when they are finished. The problem is that you can only schedule actions based on the receipt of single events, it is difficult to schedule a job to run when all of 6 specific events are received.

                You could do this using a chain job where each event step waits on the completion event for a single job and the chain ends when all steps are complete but this seems to me more complex.

                In addition, the approach I gave above has the advantage that it will not need to be updated when new jobs are added or removed from particular schedules which means less maintenance work. You could even use the same stored procedure for each of the monitoring jobs by passing in an argument specifying which schedule.

                There is one additional approach I can think of. If your daily jobs run at 1am and you know that none will take longer than an hour then you can schedule the monitoring job to run every day at
                1:05, 1:10,1:15....1:55
                every time it runs it would check whether any daily jobs are left to run, if not it would check whether it has already sent an email and only if it hasn't yet sent an e-mail for the day would it send an e-mail.
                This is simpler because there are no schedule changes but there will be a few extra job runs that don't do anything any if any daily jobs take longer than an hour this will never send the report.

                Hope this helps,
                • 5. Re: Job action based on an event.
                  Great help !!!!
                  Thanks Ravi,
                  Can you give an example of such a monitoring job? Hope we are not asking too much here..but a sample code would help..

                  • 6. Re: Job action based on an event.

                    What it monitors is up to you. I was thinking of a job that did the following

                    select count(*) from dba_scheduler_jobs where schedule = daily and last_run_date is older than 12 hours and state is SCHEDULED or RUNNING

                    if count> 0 then jobs are still to be run so exit
                    if count=0 then all jobs have run
                    check if an email has been sent for the day. If yes then exit, if not then send an e-mail

                    the e-mail should probably contain the last entry of dba_scheduler_job_run_details for every job with schedule=daily.

                    If you need any more detailed help, we will have to start talking about a contract :)

                    Hope this helps,
                    • 7. Re: Job action based on an event.
                      Well, we have the procedure ready with the necessary SQL to extract the status and count..but the confusing part with our small team is how do we change the status of the running job to either stop its run for the day if all the necessary requirements are met or continue to run it if they are not.

                      As for starting to talk about a contract.. we can always discuss that offline.. wink

                      Thanks again for all your support..

                      • 8. Re: Job action based on an event.

                        Not sure what job what running job you want to stop. If it is a regular daily job, stop_job should work. If its the monitoring job then I guess they idea here is not to stop it from running but just to make sure that it only does its work (sends the report e-mail) once and just does nothing on its subsequent runs.

                        • 9. Re: Job action based on an event.
                          We would want to stop the monitoring job. Have 2 questions (could be the last 2) before we can finally close this thread..

                          1. Our daily jobs run from 11:00 PM to 8:00 AM (M-F). The 8:00 AM job takes couple of minutes to run. So we are thinking of creating the monitoring job to run every 5 minutes starting 8:00 AM until 8:30 AM. So it will run first at 8:00 AM, then 8:05 AM and so on.. The monitoring job repeating interval definition will something like this
                          START_DATE => TRUNC(SYSDATE + 1)+ 8/24

                          Is this the correct definition? Are we missing anything here?

                          2. Once the monitoring job finds that all the jobs have run, the procedure called by the monitoring job will stop the job Will the status of the stopped monitoring job set to "STOPPED" or "SCHEDULED" or "COMPLETED" and will it run again the following day as per its 8:00 AM schedule?

                          • 10. Re: Job action based on an event.
                            1) that sounds right, you can use the following code to verify calendar syntax expressions

                            create or replace procedure print_schedule_dates
                            schedule in varchar2,
                            start_date in timestamp with time zone default null,
                            number_of_dates in pls_integer default 10
                            ) authid current_user
                            date_after timestamp with time zone;
                            next_date timestamp with time zone;
                            actual_start_date timestamp with time zone;
                            if start_date is null then
                            $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN
                            actual_start_date := systimestamp;
                            $ELSE -- 10gR2 or higher
                            actual_start_date := dbms_scheduler.stime;
                            actual_start_date := start_date ;
                            end if;

                            date_after := actual_start_date - interval '3' second;

                            for i in 1 .. number_of_dates
                            (schedule, actual_start_date, date_after, next_date);

                            'DY DD-MON-YYYY (DDD-IW) HH24:MI:SS TZH:TZM TZR'));

                            date_after := next_date;
                            end loop;

                            exec print_schedule_dates('freq=weekly', sysdate+4, 5 )

                            2) I still maintain that extra runs are harmless once you check that the report has already been sent and immediately exit. But if you really want to eliminate extra runs you should be able to set the start_date for the current job forward to the next start date. There is no one status since there will be several runs of the monitoring job and each run will be 'SUCCEEDED' (unless something fails).

                            Hope this helps,
                            • 11. Re: Job action based on an event.
                              Thanks Ravi,
                              Your's has been a great help. The print_schedule_dates procedure helped us to evaluate our calendar syntax. The approach to handle the extra run for the job is also doable.

                              Thanks again for all your input and support.

                              • 12. Re: Job action based on an event.
                                No problem. Glad you got it working.