DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: dbms_job scheduler not working

  1. #1
    Join Date
    Jul 2002
    Posts
    335

    dbms_job scheduler not working

    Ok,

    New 9206 database on a tru 64 server.

    - Scheduled a job, committed it.

    - Its viewable in the dba_jobs view (not broken)

    - It never kicks in.

    - If I run exec dbms_job.run(x); the job runs. The last_date and next_date are updated accordingly

    - job_queue_processes is set to two.
    (changing this to 0, waiting a few minutes and then back to 2 makes no difference)

    - not running in restricted session mode.

    Any ideas?!? Its got to be something simple!!

    Bazza

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    The fact you next and last dates aer being updated implies that the job is actually running normally. Have you checked the code the job is calling? Perhaps an exception handler is masking a problem, making the job look like it's not being called properly. I'm thinking of this type of thing:

    Code:
    CREATE OR REPLACE PROCEDURE my_job AS
    BEGIN
      -- Error happens. We'll fake one.
      RAISE_APPLICATION_ERROR(-20000, 'Whoops');
      
      -- Do some work.
      UPDATE my_table
      SET    work_done = 'TRUE';
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
    /
    The exeption handler is hiding the fact that there is a problem!

    Just a thought

    Update:
    Whoops. Just re-read your post. I think I missed the point. It sounds like you have hit a bug listed in Metalink Note:228059.1, althought that should have been fixed in your version. The workaround is:

    ALTER SYSTEM SET job_queue_processes=0;
    EXEC DBMS_LOCK.sleep(120);
    ALTER SYSTEM SET job_queue_processes=2;

    Basically, stop the scheduler, wait a couple of minutes and start it again.

    Good luck!

    Cheers

    Tim...
    Last edited by TimHall; 02-28-2006 at 10:28 AM. Reason: Re-read your post!
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  3. #3
    Join Date
    Jul 2002
    Posts
    335
    Hi Tim,

    Thanks for the input. The thing is the last_date and next_date are only updated if I manually do a:

    SQL> exec dbms_job.run(x);

    The 'next_date' comes and goes and the job never runs automatically. Also, I can verify the procedure is working when I run it manually as described.

    This is really bugging me, I can't find any issues related to 9.2.0.6 either.

    Bazza

  4. #4
    Join Date
    Jul 2002
    Posts
    335
    [QUOTE=TimHall]Update:
    Whoops. Just re-read your post. I think I missed the point. It sounds like you have hit a bug listed in Metalink Note:228059.1, althought that should have been fixed in your version. The workaround is:

    ALTER SYSTEM SET job_queue_processes=0;
    EXEC DBMS_LOCK.sleep(120);
    ALTER SYSTEM SET job_queue_processes=2;

    [QUOTE]

    Looks like our updates crossed!

    Yeah, I've seen this, the note says its fixed in 9203, and this is 9206. But I tried it anyway (see my first post). Question though, not seen the EXEC DBMS_LOCK.sleep(120); command, its not in the metalink note either...where did you get that from?

    Bazza

  5. #5
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    The note says wait a couple of minutes, so I put a sleep in, rather than using a stop-watch

    It's not big thing, it just saves me forgetting to reset the value when I get distracted by a phone call

    I assume that didn't work anyway...

    In that case, I all I can think of his raising an SR with support...

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Does it happen with all JOBs?

  7. #7
    Join Date
    Jul 2002
    Posts
    335
    Quote Originally Posted by pando
    Does it happen with all JOBs?
    Yes, all jobs, and I've tried different users too.

    Weird thing is, there is also a 9205 database on the same server, no problems with dbms_jobs.

    Thanks for your input Tim, I agree, SR time.

    Bazza

  8. #8
    Join Date
    Aug 2005
    Posts
    69
    this is very strange ... I am running 9.2.0.7 (just upgraded last weekend) and have exactly the same problem as you ... if I kick it off manually it works fine and updates the next and last dates. If I leave it to run by itself it never does.
    Wanted a job to gather stats (daily to start off with).
    There isn't some agent that needs to be running or some utility we need to run from the admin directory or something is there.

    If you are raising a SR could you please let me know what happens email is davidk@sanctuary-housing.co.uk

    Would appreciate that, cheers

  9. #9
    Join Date
    Jul 2002
    Posts
    335
    Quote Originally Posted by davo
    this is very strange ... I am running 9.2.0.7 (just upgraded last weekend) and have exactly the same problem as you ... if I kick it off manually it works fine and updates the next and last dates. If I leave it to run by itself it never does.
    Wanted a job to gather stats (daily to start off with).
    There isn't some agent that needs to be running or some utility we need to run from the admin directory or something is there.

    If you are raising a SR could you please let me know what happens email is davidk@sanctuary-housing.co.uk

    Would appreciate that, cheers
    Davo, try working through metalink note 313102.1, it does go through the common causes of this issue, worth trying if only to rule it out.

    I haven't opened the SR yet, waiting to hear feedback from a thrird party first, but when I do I will keep this thread updated.

    Cheers,

    Bazza

  10. #10
    Join Date
    Jul 2002
    Posts
    335
    Ok, got the bloody thing working.
    The last thing note 313102.1 suggests is to restart the database. Had to wait as a third party is involved, but oddly enough, bouncing the database has cured the issue.

    Davo, try bouncing your instance if you can, I haven't raised an SR.

    Thanks for everybodys' input. If anyone can offer an explanation, I'd be grateful.

    Cheers,

    Bazza

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width