-
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
-
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!
-
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
-
[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
-
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...
-
Does it happen with all JOBs?
-
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
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|