5 Replies Latest reply: Aug 14, 2014 2:39 AM by GregV RSS

    Running Chain Job in Restricted Session

    770691


      I have my normal jobs running is Restricted Session

      DBMS_SCHEDULER.SET_ATTRIBUTE('job_name', 'ALLOW_RUNS_IN_RESTRICTED_MODE', TRUE);

       

       

      I did the same for my chain job. The chain job starts but the first step doesn't start until out of restricted session.

      I get this if I try to set_attribute on a program

      ORA-27469: ALLOW_RUNS_IN_RESTRICTED_MODE is not a valid program attribute
      ORA-06512: at "SYS.DBMS_ISCHED", line 4436
      ORA-06512: at "SYS.DBMS_SCHEDULER", line 2925


      Is there some way to run the steps of a chain in restricted session?

      11gR2

        • 1. Re: Running Chain Job in Restricted Session
          GregV

          Hi,

           

          Can you show your chain settings?

          • 2. Re: Running Chain Job in Restricted Session
            770691

            Greg Here is an example I created for you. It is very close to what I am doing and I was able to duplicate the problem with it.

             

            -- replace sch_util.send_mail()   with your own PL SQL code.

            DECLARE
            v_job_name VARCHAR2(32) := 'example_job';
            v_chain_name VARCHAR2(32) := 'example_chain';
            BEGIN

            --drop chain and job
            BEGIN
              DBMS_SCHEDULER.DROP_CHAIN(v_chain_name,TRUE);
            EXCEPTION WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE('Error DROP_CHAIN ' || v_chain_name);
            END;

            BEGIN
              DBMS_SCHEDULER.DROP_JOB(v_job_name, TRUE);
            EXCEPTION WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE('Error DROP_JOB ' || v_job_name);
            END;

            --create all the programs for chain
            DECLARE

              v_pl_sql VARCHAR2(4000) := 'BEGIN sch_util.send_email(''example chain step 1'', ''Hellow world 1''); END;';
              v_prog_name VARCHAR2(32) := 'example_1_prog';
             
            BEGIN
             
              --drop program
              BEGIN
               DBMS_SCHEDULER.DROP_PROGRAM(v_prog_name);
              EXCEPTION WHEN OTHERS THEN
               DBMS_OUTPUT.PUT_LINE('Error DROP_PROGRAM ' || v_prog_name);
              END;
             
              --create program
              DBMS_SCHEDULER.CREATE_PROGRAM (
                 program_name           => v_prog_name,
                 program_action         => v_pl_sql,
                 program_type           => 'PLSQL_BLOCK',  
                 enabled                => TRUE,
                 number_of_arguments    => 0,
                 comments               => 'example chain step 1');
              
              
              DBMS_SCHEDULER.SET_ATTRIBUTE (
                 name                   => v_prog_name,
                 attribute              => 'max_run_duration',
                 value                  => interval '240' minute );
            END;

            DECLARE
              v_pl_sql VARCHAR2(4000) := 'BEGIN sch_util.send_email(''example chain step 2'', ''Hello world 2''); END;';
              v_prog_name VARCHAR2(32) := 'example_2_prog';
             
            BEGIN
             
              --drop program
              BEGIN
               DBMS_SCHEDULER.DROP_PROGRAM(v_prog_name);
              EXCEPTION WHEN OTHERS THEN
               DBMS_OUTPUT.PUT_LINE('Error DROP_PROGRAM ' || v_prog_name);
              END;
             
              --create program
              DBMS_SCHEDULER.CREATE_PROGRAM (
                 program_name           => v_prog_name,
                 program_action         => v_pl_sql,
                 program_type           => 'PLSQL_BLOCK',  
                 enabled                => TRUE,
                 number_of_arguments    => 0,
                 comments               => 'example chain step 1');
              
              
              DBMS_SCHEDULER.SET_ATTRIBUTE (
                 name                   => v_prog_name,
                 attribute              => 'max_run_duration',
                 value                  => interval '240' minute );
            END;


            -----------------------------Start Chain Definition -----------------------------------

            DBMS_SCHEDULER.CREATE_CHAIN (
                 chain_name            =>  v_chain_name,
                 comments              =>  'Example chain!');
                
            --- define steps for this chain.
            DBMS_SCHEDULER.DEFINE_CHAIN_STEP(v_chain_name, 'example_step1', 'example_1_prog');
            DBMS_SCHEDULER.DEFINE_CHAIN_STEP(v_chain_name, 'example_step2', 'example_2_prog');

            -- define corresponding rules for the chain.
            DBMS_SCHEDULER.DEFINE_CHAIN_RULE(v_chain_name, 'TRUE', 'START example_step1');
            DBMS_SCHEDULER.DEFINE_CHAIN_RULE(v_chain_name, 'example_step1 SUCCEEDED', 'START example_step2');
            DBMS_SCHEDULER.DEFINE_CHAIN_RULE(v_chain_name, 'example_step2 COMPLETED', 'END');

            -- enable the chain
            DBMS_SCHEDULER.ENABLE(v_chain_name);

            --- create a chain job to start the chain 
            DBMS_SCHEDULER.CREATE_JOB (
                 job_name        => v_job_name,
                 job_type        => 'CHAIN',
                 job_action      => v_chain_name,
                 start_date    => '6-AUG-2014 9.35.00 AM AMERICA/CHICAGO',
                 enabled         => FALSE);
              
               DBMS_SCHEDULER.SET_ATTRIBUTE(v_job_name , 'max_run_duration' , interval '4' hour);
               DBMS_SCHEDULER.SET_ATTRIBUTE(v_job_name, 'raise_events',DBMS_SCHEDULER.JOB_FAILED);
               DBMS_SCHEDULER.SET_ATTRIBUTE(v_job_name, 'ALLOW_RUNS_IN_RESTRICTED_MODE', TRUE);
             
               DBMS_SCHEDULER.ENABLE(v_job_name);

            END;

            • 3. Re: Running Chain Job in Restricted Session
              GregV

              Hi,

               

              Thanks for providing the test case. I can't try now though since I'm out of the office for some days.

              Anyway, what is your exact DB version?

              I found a bug that you may be hitting:

              Bug 9205416  Scheduler job does not run when RESTRICTED SESSION is enabled

               

              It's supposed to be fixed from 11.2.0.2 onwards.

              • 4. Re: Running Chain Job in Restricted Session
                770691

                It seems it is Bug 19426695

                • 5. Re: Running Chain Job in Restricted Session
                  GregV

                  Hi,

                   

                  This bug was reported on 12 aug, did you file it?

                   

                  If yes, keep us informed about the outcome,

                   

                  Thanks