troubleshooting Question

Running a job on a particular instance within the RAC

Avatar of joebednarz
joebednarzFlag for United States of America asked on
Oracle Database
3 Comments1 Solution5633 ViewsLast Modified:
Have only asked one question in the past, but now I need help so here goes -- as clearly as I can...

I have a job that needs to be run on a regular basis.  The job takes some text files (payroll data) that is downloaded from a mainframe and imports it into our newly designed system written in .net and Oracle (read, NOT mainframe).  The job is an Oracle Scheduler Job and my database is Oracle 10g RAC.

From what I understand of DBMS_SCHEDULER, I can specify a "service" under which to run the job, and this supposedly will get the job to run only on the instance I specify.  This is what I WANT to do since the files are copied into the same machine every night, therefore, run the job from the instance on that machine.  My problem is that I cannot find documentation on how to set this up completely right.

My script for setting up the job is below... what am I missing?

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'TLB_CORE_JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2007/10/25 02:00:00.000000 -05:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'FREQ=DAILY;BYHOUR=2'
      ,end_date        => NULL
      ,job_class       => 'AUTO_TASKS_JOB_CLASS'
      ,job_type        => 'STORED_PROCEDURE'
      ,job_action      => 'TLB_CORE_PKG.MAIN'
      ,comments        => 'This job will run the core import processes, including updating people, their addresses, and the Labor Distribution table.'
    );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'TLB_CORE_JOB'
     ,attribute => 'RESTARTABLE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'TLB_CORE_JOB'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_RUNS);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'TLB_CORE_JOB'
     ,attribute => 'MAX_FAILURES'
     ,value     => 1);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'TLB_CORE_JOB'
     ,attribute => 'MAX_RUNS');
  BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( name      => 'TLB_CORE_JOB'
       ,attribute => 'STOP_ON_WINDOW_CLOSE'
       ,value     => FALSE);
  EXCEPTION
    -- could fail if program is of type EXECUTABLE...
    WHEN OTHERS THEN
      NULL;
  END;
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'TLB_CORE_JOB'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'TLB_CORE_JOB'
     ,attribute => 'SCHEDULE_LIMIT'
     ,value     => '+001 00:00:00');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'TLB_CORE_JOB'
     ,attribute => 'AUTO_DROP'
     ,value     => FALSE);

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'TLB_CORE_JOB');
END;
/
ASKER CERTIFIED SOLUTION
MikeOM_DBA

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros