asked on

Starting jobs in Oracle

I have 3 jobs that need to be running in my database but they are not. If I do a

select * from dba_jobs_running;

Nothing is returned. I can see the jobs in dba_jobs. Can I start these jobs by just running the

begin trc.function('pipe_name'); end;

This is what I can see in the dba_jobs table under the WHAT column ?

Any advice would be well received. Thanks in anticipation.

SOLUTION

vishal68

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

RCorfman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bencouve2

ASKER

Thanks for all the input. I have had a few strange things happen. Firstly I did the execute dbms_job.run(jobid) that vishal68 recommended and it seemed to hang for a while. I did a select on dba_jobs_running while it was hanging and it showed that the job was running but when I did the select on dba_jobs it indicated that the job was broken so I killed the execute dbms_job.run(jobid), and ran execute dbms_Job.broken(jobno,false); , checked the BROKEN flag was set to 'N', which it was, and then ran the execute dbms_job.run(jobid) again. But once again it hangs and the dba_jobs_running indicates that the jobs ( I have done this for 3 jobs) are running but the execute dbms_job.run(jobid); sessions are running and I run two from SQLNavigator and one from unix. Is this ok, or should I do something else? Thanks again for all your help. I think I am almost there.

bencouve2

ASKER

Just one other thing,

should I not use dbms_job.submit(..

I am not sure of the syntax for this. Could you advise on this, please.

vishal68

The dbms_job.run is equivalent to running the job manually, i.e. session will continue to be there till the job completes. You can monitor the same using another session and checking dba_jobs_running. Also when you are using dbms_job.run, you don't need to set the broken flag to N, if the job is successfully completed the dbms_job.run will reset the flag to N automatically.

Also I suggest that you consider what RCorfman has suggested to check that your system is properly setup for running the jobs automatically. What I had suggested was just a command to manually run the jobs as asked by you. But if you are seeing the jobs as broken then you need to find the reason for the failed jobs. You can check the alert log of your database for finding the reason for failure. Fix the problem and then check whether the jobs are automatically run or not.

One more thing after fixing the problem, you may need to either set the broken flag as N or run the jobs manually once.

HTH
Vishal

bencouve2

ASKER

Thanks for your help guys.

RCorfman

I know it is closed, but I'm not sure it is all cleared up, and am happy to continue answering some questions relative to dbms_job.
First, I would read the documentation on the package. I cannot emphasize enough that Oracle has some reasonably good documentation on this package. Register on OTN.Oracle.Com if you haven't, this is where their documentation is at. DBMS_JOB hasn't changed much in the last several releases. In 10G, they have a scheduler package that is more robust than dbms_Job, but I haven't heard dbms_job is actually going away.

You may well need to use dbms_job.submit in the future. "Submit" creates a new job, "Run" executes and existing job. vishal68 indicated correctly, whenever a job finishes without failure, it is marked as Broken=N.

Each time a job attempts to execute, but fails, the information on the failure is put out in one of the Dump directories... I'm not a dba, but I'm pretty sure it is UDUMP.... maybe BDUMP, but it is one of them. This will show the ORA-nnnnn failure that occured on the job.

When it failes, the FAILURES is incremented by one, Oracle uses an algorithm to decide when to retry, then it executes the job again, eventually. When it finally gets to 16 Failures, the job goes to 'Broken' state, and it stops trying to execute.

If your job showed as broken, it means that at some point in time, it was failing, failed 16 times, and the flag was set.

Here is a link to the dbms_job package documentation for 9iR2.
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_job.htm#ARPLS019
Here is a link in the administrator's guide. I think this is where you really need to look for more information.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#ADMIN010