[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1334
  • Last Modified:

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.
0
bencouve2
Asked:
bencouve2
  • 3
  • 2
  • 2
2 Solutions
 
vishal68Commented:
You can use

execute dbms_job.run(jobid)

where jobid is the jobid from dba_jobs view.

HTH
Vishal
0
 
RCorfmanCommented:
Sure. The jobs have a next_date and next_sec column that show when it is scheduled to run. Also a 'broken' column. If broken shows 'Y', then they will never run.

If it is broken, you can use
dbms_Job.broken(jobno,false) to 'fix' it.

If the next_date is in the future, and you want it to run, use
dbms_job.next_date(jobno,sysdate) to set it to run right away.

Oh, after using dbms_job package items, you must 'commit' for them to take effect.

If there is a value in this_date, then the job is actually running right  now.

If the job(s) look like they are ready to run, but not launching, there is a system parameter that sets how many jobs are allowed to be running at any one time.  The default for this is zero I believe so they will never launch.

job_queue_processes is the init parameter. Set it to 2,4, whatever is appropriate. This is used to throttle the number at once. We use jobs on our database extensively... we have AQ with callbacks, they use jobs, I believe streams, and replication schedules do to... Anyway, we have ours set to 36... this just depends on your situation.
0
 
bencouve2Author Commented:
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.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
bencouve2Author Commented:
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.
0
 
vishal68Commented:
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
 
0
 
bencouve2Author Commented:
Thanks for your help guys.
0
 
RCorfmanCommented:
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now