7 Replies Latest reply on Apr 14, 2017 2:18 PM by rp0428

    Oracle JOb not running

    TanmoyMoulik

      Hi All,

       

      I have created a MV

       

      CREATE  MATERIALIZED VIEW "USLAM_MTTR" ("CIRCUIT_ID", "SLA_START", "SLA_END",  "SLA_AVAIL", "SLI_ID", "UPDATE_TIMESTAMP")

      refresh complete on demand as

      select

      SUBSTR(C.SERVICE_ID,1,INSTR(C.SERVICE_ID,'_',1,5)-1),

      --sla_uniq_inx.nextval,

      --SUBSTR(SUBSTR(a.SLA_ID,5),1,length(SUBSTR(a.SLA_ID,5))-4),

      --a.SLA_ID,

      a.REF_PER_START_TIME + (1/24)*5.5 SLA_START,

      a.REF_PER_END_TIME + (1/24)*5.5 SLA_END,

      --nvl(a.ACCUMULATING_INDICATOR,0),

      --NVL(a.CALC_RESULT / 1000,0) as CALC_RESULT,

      a.calc_status,

      --round(CALC_VALUE/60,2) as SLA_AVAIL,

      --round(a.CALC_VALUE * 100/(60*60),0)  as SLA_AVAIL,

      --a.CALC_VALUE * 1000/(60)  as SLA_AVAIL1,

      --nvl(D.value,0) SLA_CONFIG_VALUE,

      --nvl(D.value,0) - nvl( a.CALC_RESULT/1000,0)  REMANING_VALUE_SEC,

      B.SLI_ID,

      sysdate

      from SLI_STATUS a,  REPS_SLA_CLAUSE B, CAL_SLI_INSTANCE C , REPS_SLA_CLAUSE_THRESHOLD D,REPS_SLA E

      where

      --a.SLA_ID = 'INUETHNATHNATW6001ENBESR001'  and

      --a.REF_PER_START_TIME > sysdate - 60  and a.REF_PER_START_TIME < sysdate - 10

      --a.REF_PER_START_TIME+(1/24)*5.5 > = trunc(ADD_MONTHS((LAST_DAY(SYSDATE)+1),-1))  and a.REF_PER_START_TIME+(1/24)*5.5 < =sysdate

      a.REF_PER_START_TIME+(1/24)*5.5 <=sysdate  and  a.REF_PER_END_TIME+(1/24)*5.5>=sysdate

      and a.SLI_ID = C.id and C.SLA_CLAUSE_GID = B.GID and C.SLA_CLAUSE_GID = D.SLA_CLAUSE_GID

      and D.THRESHOLD_TYPE = 60 and B.UNIT <> '%'

      and B.SLI_ID='ILL_MTTR_Service_Affecting_Circuit'

      and E.id=a.SLA_ID

      and e.admin_state=6;

       

       

       

      Ffinally have created Schedule job to run Refresh this MV at scheduled time.

       

      BEGIN

         DBMS_SCHEDULER.CREATE_JOB (

         job_name           =>  'VMV_UCMDBIPMPLS_REFRESH' ,

         job_type           =>  'PLSQL_BLOCK' ,

         job_action         =>  'begin

                       dbms_mview.refresh(''USLAM_MTTR'',''C'');

                    

                       end;',

      START_DATE =>sysdate,

      REPEAT_INTERVAL =>    'FREQ=MINUTELY;INTERVAL=5;BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN',       

      END_DATE => NULL,

      enabled            =>  TRUE );

      END;

       

      The job was running successfully in all env DEV and SIT.However after the same script was deployed in PROD.ITs not running.What could be the issue?How can we resolve the issue?

       

      Thanks,

      Tanmoy

        • 1. Re: Oracle JOb not running
          Gaz in Oz

          Run something like this:

          SELECT owner,
                 job_name,
                 log_date,
                 status,
                 additional_info
          FROM   dba_scheduler_job_log
          WHERE  job_name = 'VMV_UCMDBIPMPLS_REFRESH'
          ORDER BY log_date;
          

          (you might be able to use dba_scheduler_job_log table)

          To see the state of the runs...

           

          and something like this to see the state of the schedule:

          SELECT j.job_name,
                  j.job_action                                                              what,
                  TO_CHAR(j.last_start_date, 'DD-MON-YYYY HH24:MI')                         last_date,
                  TO_CHAR(j.last_start_date - elapsed_time, 'DD-MON-YYYY HH24:MI')          this_date,
                  TO_CHAR(j.next_run_date, 'DD-MON-YYYY HH24:MI')                           next_date,
                  repeat_interval                                                           interval,
                  failure_count                                                             failures,
                  NVL2(r.elapsed_time, 'Running', 'Waiting')                                status,
                  state
          FROM    dba_scheduler_jobs j,
                  dba_scheduler_running_jobs r
          WHERE   j.job_name = r.job_name(+)
          AND     j.job_name = 'VMV_UCMDBIPMPLS_REFRESH';
          

          as a start.

          • 2. Re: Oracle JOb not running
            TanmoyMoulik

            Hi ,

             

            On running this query I am getting this status as "Waiting".What could be the cause?How can we make the job run.

            • 3. Re: Oracle JOb not running
              Gaz in Oz

              How about running BOTH queries and posting the results in a formatted fashion. Use advanced editor                                                                         ^

               

              You should be able to run a scheduled job with

              begin
                 dbms_scheduler.run_job('VMV_UCMDBIPMPLS_REFRESH');
              end;
              

              .

              To run the refrsh standalone then

              begin
                 dbms_mview.refresh(''USLAM_MTTR'',''C'');
              end;
              

              Does that work?

              • 4. Re: Oracle JOb not running
                How can we resolve the issue?

                Start by posting in the CORRECT forum;

                General Database Discussions

                 

                This forum is ONLY for questions/issues about Sql Developer and NOTHING you posted indicates any issue with Sql Dev.

                 

                Please mark the thread ANSWERED and repost it in the forum above.

                • 5. Re: Oracle JOb not running
                  TanmoyMoulik

                  Both the script got successfully executed.Only problem is that job is not running as scheduled.What can be done to resolve the issue.

                   

                  Thanks,

                  Tanmoy

                  • 6. Re: Oracle JOb not running
                    Gaz in Oz

                    Please re-read all of the replies and follow their directives.

                    • 7. Re: Oracle JOb not running

                      WRONG FORUM!

                      What can be done to resolve the issue.

                       

                      Start by posting in the CORRECT forum;