11 Replies Latest reply: Mar 20, 2014 9:27 AM by GregV RSS

    Scheduled Chained Steps

    RobbR

      I've been trying to create a Scheduler Chain in Oracle 11.2 to kick off steps based off of a schedule.  Below is an example script I used to attempt this, what I would like is Step1 to execute with the start of the chain, then Step2 to execute on a schedule, then Step3 to execute once Step1 and Step2 are complete.  This is for a nightly process where Step1 would execute at midnight, then Step2 would execute at 1AM.  The step I'm having issues with is defining the start of Step2 is at line 134.

       

      I also couldn't find an example of using DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP using the event_schedule_name overload, so if anyone has come across one please share.

       

      /* CREATE TABLE */
      Create table chain_table
      (
          chain_ts timestamp default current_timestamp,
          chain_step varchar2(100)
      );
      
      
      /* CREATE PROCEDURE */
      create procedure chain_test_proc
      (
          step_name in varchar2
      ) as
      begin
          execute immediate 'insert into chain_table (chain_step) values (:1)'
          using step_name;
          commit;
      end chain_test_proc;
      /
      
      
      /* CREATE SCHEDULE */
      begin
          dbms_scheduler.create_schedule
          (
              repeat_interval  => 'FREQ=MINUTELY;',    
              start_date => to_timestamp_tz('2014-03-17 12:14:00.000000000 AMERICA/CHICAGO','YYYY-MM-DD HH24:MI:SS.FF TZR'),
              comments => 'Runs minutely for testing',
              schedule_name  => '"STEP2_SCHEDULE"');
      end;
      /
      
      
      
      
      
      
      /* CREATE PROGRAMS */
      begin
          dbms_scheduler.create_program
          (
              program_name => 'STEP1_PROGRAM',
              program_action => 'CHAIN_TEST_PROC',
              program_type => 'STORED_PROCEDURE',
              number_of_arguments => 1,
              comments => null,
              enabled => false
          );
          dbms_scheduler.define_program_argument
          (
              program_name => 'STEP1_PROGRAM',
              argument_position => 1,
              argument_name => 'STEP_NAME',
              argument_type => 'VARCHAR2',
              default_value => 'STEP1',
              out_argument => false
          );
      
      
      
      
          dbms_scheduler.create_program
          (
              program_name => 'STEP2_PROGRAM',
              program_action => 'CHAIN_TEST_PROC',
              program_type => 'STORED_PROCEDURE',
              number_of_arguments => 1,
              comments => null,
              enabled => false
          );
          dbms_scheduler.define_program_argument
          (
              program_name => 'STEP2_PROGRAM',
              argument_position => 1,
              argument_name => 'STEP_NAME',
              argument_type => 'VARCHAR2',
              default_value => 'STEP2',
              out_argument => false
          );
      
      
          dbms_scheduler.create_program
          (
              program_name => 'STEP3_PROGRAM',
              program_action => 'CHAIN_TEST_PROC',
              program_type => 'STORED_PROCEDURE',
              number_of_arguments => 1,
              comments => null,
              enabled => false
          );
          dbms_scheduler.define_program_argument
          (
              program_name => 'STEP3_PROGRAM',
              argument_position => 1,
              argument_name => 'STEP_NAME',
              argument_type => 'VARCHAR2',
              default_value => 'STEP3',
              out_argument => false
          );
      
      
          dbms_scheduler.enable ('STEP1_PROGRAM');
          dbms_scheduler.enable ('STEP2_PROGRAM');
          dbms_scheduler.enable ('STEP3_PROGRAM');
      
      
      end;
      /
      
      
      
      
      
      
      begin
      /* CREATE CHAIN */
          sys.dbms_scheduler.create_chain
          (
              chain_name          => 'MY_CHAIN',
              rule_set_name       => null,
              evaluation_interval => null,
              comments            => null
          );
      
      
      /* CREATE CHAIN STEPS */
          sys.dbms_scheduler.define_chain_step
          (
              chain_name          => 'MY_CHAIN',
              step_name           => 'STEP1_PROG',
              program_name        => 'STEP1_PROGRAM'
          );
      
      
          sys.dbms_scheduler.define_chain_step
          (
              chain_name          => 'MY_CHAIN',
              step_name           => 'STEP2_PROG',
              program_name        => 'STEP2_PROGRAM'
          );
      
      
          sys.dbms_scheduler.define_chain_event_step
          (
              chain_name          => 'MY_CHAIN',
              step_name           => 'STEP2_SCHED',
              event_schedule_name => 'STEP2_START_SCHEDULE'
          );
      
      
          sys.dbms_scheduler.define_chain_step
          (
              chain_name          => 'MY_CHAIN',
              step_name           => 'STEP3_PROG',
              program_name        => 'STEP3_PROGRAM'
          ); 
      
      /* CREATE CHAIN RULES */
          -- Start of Chain
          sys.dbms_scheduler.define_chain_rule
          (
              chain_name          => 'MY_CHAIN',
              condition           => 'TRUE',
              action              => 'START STEP1_PROG',
              rule_name           => 'STEP1',
              comments            => null
          );
      
      
          -- Step having issues with
          sys.dbms_scheduler.define_chain_rule
          (
              chain_name          => 'MY_CHAIN',
              condition           => 'STEP2_SCHED COMPLETED',
              action              => 'START STEP2_PROG',
              rule_name           => 'STEP2',
              comments            => 'Runs on a schedule'
          );
      
      
          -- Last step
          sys.dbms_scheduler.define_chain_rule
          (
              chain_name          => 'MY_CHAIN',
              condition           => 'STEP1_PROG COMPLETED AND STEP2_PROG COMPLETED',
              action              => 'START STEP3_PROG',
              rule_name           => 'STEP3',
              comments            => null
          );
      
      
          -- End Chain
          sys.dbms_scheduler.define_chain_rule
          (
              chain_name          => 'MY_CHAIN',
              condition           => 'STEP3_PROG COMPLETED',
              action              => 'END',
              rule_name           => 'END_CHAIN',
              comments            => null
          );
      
      
      /* ENABLE CHAIN */
          dbms_scheduler.enable (name => 'MY_CHAIN');
      
      
      end;
      /
      
      
      /* RUN CHAIN */
      -- exec dbms_scheduler.run_chain (chain_name => 'MY_CHAIN', start_steps => null, job_name => null);
      
      
      
      
      
      
      
      
      /* ROLLBACK
      
      
          -- Drop Chain
          exec dbms_scheduler.drop_chain ('MY_CHAIN');
      
      
          -- Drop Programs
          exec dbms_scheduler.drop_program ('STEP1_PROGRAM, STEP2_PROGRAM, STEP3_PROGRAM');
      
      
          -- Drop Schedule
          exec dbms_scheduler.drop_schedule ('STEP2_SCHEDULE');
         
          -- Drop table
          drop table chain_table;
      
      
          -- Drop procedure
          drop procedure chain_test_proc;
      
      
      */
      
      
        • 1. Re: Scheduled Chained Steps
          GregV

          Hi,

           

          What is the issue you're facing?

          • 2. Re: Scheduled Chained Steps
            RobbR

            The chain isn't behaving as I expected.  I DEFINE_CHAIN_EVENT_STEP, then create a rule to execute a Scheduler Procedure after that event (scheduler schedule).  But every time I execute the chain, it appears to execute Step1 and hang on Step2 indefinitely.  I cannot find any examples on how to create a chain rule to use schedules anywhere and I was hoping someone on this site could provide a working example of where Step1 would execute, then execute Step2 on the scheduled event, then once both are complete execute Step3 because I'm at a loss.

            • 3. Re: Scheduled Chained Steps
              GregV

              Hi,

               

               

              For Step2 you have set the event_schedule_name parameter as STEP2_START_SCHEDULE for the DEFINE_CHAIN_EVENT_STEP procedure:

              event_schedule_name => 'STEP2_START_SCHEDULE'  

               

              But, it seems the schedule you've created for Step2 is called STEP2_SCHEDULE, or you didn't post all the code. So could it be the problem?

              • 4. Re: Scheduled Chained Steps
                RobbR

                Thanks GregV, I didn't catch that originally.

                 

                I've modified the statement (see below), and when I start the chain and check the status after several minutes, Step1 was successful (inserted into CHAIN_TABLE) and CHAIN_START is still running. It seems like it's still waiting for Step2 to complete before continuing with the chain (which it should), but why isn't Step2 being triggered with the Schedule?  When defining the rule for Step2, should I be using a different condition?  Instead of using 'STEP2_SCHED COMPLETED', should I be using something along the lines of ':STEP2_SCHED.state', or some other attribute to define the check for the schedule?  Oracle uses the following example in their DBMS_SCHEDULER document for a chained event:

                ':step1.completed = ''TRUE'' AND :step1.end_date >SYSDATE-1/24'

                --satisfied when step step1 completed less than an hour ago

                 

                But that is for a step in their chain, and doesn't define what Step1 is in this example.

                 

                Modification:

                ...
                sys.dbms_scheduler.define_chain_event_step
                    (
                        chain_name          => 'MY_CHAIN',
                        step_name           => 'STEP2_SCHED',
                        event_schedule_name => 'STEP2_SCHEDULE'
                    );
                ...
                

                 

                Rule defined:

                ...
                    sys.dbms_scheduler.define_chain_rule
                    (
                        chain_name          => 'MY_CHAIN',
                        condition           => 'STEP2_SCHED COMPLETED',
                        action              => 'START STEP2_PROG',
                        rule_name           => 'STEP2',
                        comments            => 'Runs on a schedule'
                    );
                ...
                
                • 5. Re: Scheduled Chained Steps
                  GregV

                  Hi,

                   

                   

                  By using define_chain_event_step for Step2, Step2 will actually complete once it reaches the schedule STEP2_SCHED.

                  If you want to trigger Step2 at a specific time, at 1 am if I understood correctly, I see 2 possibilities:

                   

                   

                   

                  1°) Define the chain event step for Step1:

                   

                   

                  sys.dbms_scheduler.define_chain_event_step 

                      ( 

                          chain_name          => 'MY_CHAIN', 

                          step_name           => 'STEP1_SCHED', 

                          event_schedule_name => 'STEP1_SCHEDULE' 

                      );

                     

                  Step1 will then finish at 1 am.

                  Then, for step2, just start it when step1 completes:

                   

                   

                  sys.dbms_scheduler.define_chain_rule 

                      ( 

                          chain_name          => 'MY_CHAIN', 

                          condition           => 'STEP1 COMPLETED', 

                          action              => 'START STEP2_PROG', 

                          rule_name           => 'STEP2', 

                          comments            => 'Runs when Step1 completes' 

                      );  

                     

                  2°) Use a condition similar to the one you mentioned for starting step2:

                   

                   

                  ':step1.completed = ''TRUE'' AND (TRUNC(SYSDATE) + 1/24) - :step1.end_date = SYSDATE - :step1.end_date'

                   

                   

                  That is, start once sysdate is 1 am. I'm not sure it will work, but it's worth trying.

                  • 6. Re: Scheduled Chained Steps
                    RobbR

                    I was trying to keep Step1 and Step2 separate so if Step1 were to run past 1AM, then Step2 would still kick off at the desired time and Step3 would wait for the first two steps to complete before executing.

                     

                    I could do it as possibility 2, and I could set an interval to run Step2 after HH:MM:SS, but I would like stay away from hard coding times or make the steps run after X time of start of chain.

                     

                    I was hoping I could use a Schedule object to trigger a step within a chain, but I guess at this point it's either not documented fully or not possible.  Oracle has it "documented" on how to use DEFINE_CHAIN_EVENT_STEP (but not really) .... Examples of Creating Chains

                     

                    So for the time being, I'm going to use possibility 2 ... If I can figure anything out with triggering events based on a schedule, I'll post back.

                    • 7. Re: Scheduled Chained Steps
                      GregV

                      Yes I agree with you about the lack of information on some points. Scheduler is really a great and powerful feature, but for some points we could gladly go with more information from the documentation. Like you, I couldn't even find any example for the DEFINE_CHAIN_EVENT_STEP, either from the documentation or from the MOS KB.

                       

                      I guess the idea of chain is that it follows a sequence based on basic rules, even though the possibilities are already pretty flexible.

                      Keep us informed, it's nice to have feedback on this considering the lack of documentation.

                       

                      Thanks

                      • 8. Re: Scheduled Chained Steps
                        spajdy

                        We have similar problem.

                        We created chain with couple of steps. Chain is started at 22:00 and one middle step in chain cann't be runned before midnight. So we created simple step called wait_till_midnight and put this step before step than cann't be started before midnight.

                        This wait_till_midnight step is very simple. It check actual time and if it is after midnight it end immediatelly if not then it count how long it have to wait a then call dbms_lock.sleep.

                        the is code

                        PROCEDURE wait_till_midnight IS
                            v_sysday    NUMBER(6);
                            v_sec2sleep NUMBER(5);
                            c_job_ident CONSTANT VARCHAR2(30) := 'wait_till_midnight';
                            v_id_log supp.pkg_operation_log.type_id_operation_log;
                        BEGIN
                            v_id_log := supp.pkg_operation_log.f_start(c_job_ident);
                            v_sysday := to_number(TO_CHAR(supp.ifc_supp.get_sys_date, 'HH24MISS'));
                            -- are we before midnight
                            IF (v_sysday < 240000)
                               AND (v_sysday > 200000)
                            THEN
                                v_sec2sleep := ((trunc(supp.ifc_supp.get_sys_date + 1) - supp.ifc_supp.get_sys_date) * 86400) + 60;
                                -- wait to one minute after midnight
                                dbms_lock.sleep(seconds => v_sec2sleep);
                            END IF;
                            supp.pkg_operation_log.p_finish(v_id_log, 'FINISHED');
                        EXCEPTION
                            WHEN OTHERS THEN
                                supp.pkg_operation_log.p_output(v_id_log, 'ERROR', c_job_ident || ': ' || substr(SQLERRM, 1, 200));
                                supp.pkg_operation_log.p_finish(v_id_log, 'ERROR');
                        END;
                        

                         

                        Rule for start the setp is now very simple = "wait_till_midnight  COMPLETED"

                        • 9. Re: Scheduled Chained Steps
                          RobbR

                          Thanks spajdy!  I did a similar thing where I had a job start and wait until a batch of jobs completed before doing anything.  It worked, but then I was introduced to chaining, now I'm trying to get the jobs it waited for on a schedule (Step1 and Step2).  I'll definitely consider this option too!

                          • 10. Re: Scheduled Chained Steps
                            spajdy

                            Yes, chains in Scheduler give us a new possibilities. But as I understand Scheduler chain give us following options:

                            • run more step in parallel
                            • run one of X steps based on result of another step
                            • run step when N previous steps (typically runned in parallel) are finished = synchonization

                             

                            As you described you you chain have three steps with following start conditions:

                            step 1 - start with chain start = time midnight

                            step 2 - start time = 1AM

                            step 3 - start when step 1 and step 2 are finished

                             

                            But I think that chain can have only one start point and one end point. For me your chain have two start points (step1 and step2).

                            I now I return to wait till time step.

                            Consider chain with step1, step1a, step2 and step3.

                            Start conditions:

                            step 1 - start with chain start = time midnight

                            step 1a - start with chain start = time midnight

                            step 2 - start when step1a is finished

                            step 3 - start when step 1 and step 2 are finished

                            And step 1a will do nothing anly wait to 1AM and then finish.

                             

                            This is the way I solve your request.

                            • 11. Re: Scheduled Chained Steps
                              GregV

                              Hi,

                              Another possible workaround I thought about: play with the PAUSE attribute of the chain.

                              When your chain starts, alter it inside program1 to pause Step2:

                              DBMS_SCHEDULER.ALTER_CHAIN(chain_name =>'MY_CHAIN',

                                                         step_name  => 'STEP2_PROG',

                                                         attribute  => 'PAUSE',

                                                         value      => TRUE

                                                        );

                              Create an independant scheduler job to alter the PAUSE attribute of the chain:

                               

                              DBMS_SCHEDULER.CREATE_JOB(job_name       => 'ALTER_MY_CHAIN_JOB',

                                                        job_type       => 'PLSQL_BLOCK',

                                                        job_action     => 'BEGIN

                                                                                DBMS_SCHEDULER.ALTER_CHAIN(chain_name =>''MY_CHAIN'',

                                                                                                           step_name  => ''STEP2_PROG'',

                                                                                                           attribute  => ''PAUSE'',

                                                                                                           value      => FALSE

                                                                                                          );

                                                                           END;',

                                                        repeat_interval => 'FREQ=DAILY;BYHOUR=1',

                                                        enabled         => TRUE,

                                                        auto_drop       => FALSE

                                                       );

                               

                              This job will run everyday at 1am to set the PAUSE attribute to FALSE for the chain. This way the chain should restart.   

                              Again, it's a suggestion, needs to be tested!