|
Category :
Database
Resources -> Database Administration-> Troubleshooting & Solutions
DB Version
:
Oracle 8i /
Oracle 9i
OS Details :
Sun Solaris9
What
are the steps that one needs to follow in order to troubleshoot the
issue with oracle jobs not getting picked up for processing?
(1) Query the dba_jobs
view, do the values for last_date and next_date look okay?
(2) The "BROKEN" column for the job when you query the dba_jobs
view should have a value of N and the "failures" column should have a
value of 0. Otherwise the job's broken due to an earlier failure.
(3) Login to the database and issue this:
SQL> show parameter job_queue_processes
This should return a value
greater than 0. If not, set the value for this parameter to be higher
than 0 for the job queue processes to be started.
(4) Make sure the job queue co-ordinator process is running for
your instance. On unix you can check this by doing :
$ ps -eaf|grep ora_cjq*
The result of the above command should show something like this:
ora_cjq0_<dbname>. If it doesn't show up then it is a problem with the
job_queue_processes parameter not being set correctly.
(5) If the job queue processes are running then you can try to
run the job manually by doing this: Get the value for job and log_user
columns from the dba_jobs view and login to the user as returned by
log_user column and do this:
SQL> exec dbms_job.run(<Job
value that you retrieved above>)
(6) You can also check the background trace directories to see
if there were trace files created due to job failures. The trace file
names on unix usually will be something like : <dbname>_j*.trc. These
files and the alert log file for the database will list the actual
errors if there was a problem with running the job.
|
