Troubleshooting Issues with Oracle Database Jobs

Some of the more basic conditions to check whenever encountering any difficulty processing Oracle database jobs are provided below.

Event monitors do not work correctly

Verify that all of the required audit trail views are enabled.

There are no database jobs in the Job Name field

If you are running Oracle 10g and no jobs display in the Job Name field, check the status of the connection to the Oracle database. A valid connection is required to retrieve and display the list of predefined jobs from the Oracle database. Even if there is no connection though, the name of the job can be typed into the text field and the job definition will run when the connection is reestablished (as long as the job name is correctly spelled).

No Oracle jobs will run

Verify that there is a good connection to the Oracle database.

  • If there is a red status light next to the Oracle database connection in the Connections pane, verify that the connection information in the connection definition is accurate.

  • If the Oracle connection’s status light is yellow, check if the connection is in a planned outage. In the Job Activity pane, the Oracle jobs will show Agent Outage as their status.

  • If the status light is green, verify that your user profile is on a runtime user list of a user account authorized to run Oracle database jobs.

The PL/SQL job completed successfully but there is no job output

Verify that the Save Output Option on the Options tab of the job definition is not set to discard the job output.

Verify that the Run as DBMS Job option on the Oracle DB tab of the job definition is not selected. Jobs that run as DBMS jobs run within Oracle where TA cannot retrieve the job output. Clear the option if selected and rerun the job to retrieve the job output.

The job output is difficult to read because all of the values run together.

On the Oracle DB tab of the job definition, click an output format other than “Raw.” Raw format has no format so the values run together. If you prefer not to format the job output, enter a character in the Delimiter field to be used to separate the job output values.

Oracle 9i has job execution errors

Whenever a job fails, information about the failure is recorded in a trace file and in the alert log. Oracle writes message number ORA-12012 and includes the job number of the failed job.

If a job returns an error as Oracle attempts to execute it, Oracle automatically tries to execute it again.The first attempt to rerun the job occurs after one minute, the second attempt to rerun the job occurs after two minutes, the third attempt after four minutes and so on, doubling the time intervals between each attempt. If the job still fails to complete after 16 attempts, Oracle quits resubmitting the job and marks the job as “broken.” Between the subsequent attempts to run the job, the operator has the opertunity to diagnose and correct the condition causing the job to fail.

A job needs to be canceled

A job running in Oracle 10g can be canceled by the Oracle DBA using the STOP_JOB procedure call. This procedure call will set the state to stopped in Oracle.

The syntax for the STOP_JOB procedure call is:

DBMS_SCHEDULER.STOP_JOB(
job_name IN VARCKHAR2
force IN BOOLEAN DEFAULT FALSE);

Replace the job_name with the name of the job or job class. This can be a comma-delimited list. For a job class, the SYS schema should be specified.

If the name of the job class is specified, the jobs that belong to that job class are stopped. The job class is not affected by this call. Replace force with either TRUE or FALSE.

  • If FALSE, TA tries to gracefully stop the job using an interupt mechanism. This method gives control back to the slave process, which updates the status of the job in the job queue to STOPPED. If this process fails, an error is returned.

  • If TRUE, TA immediately terminates the job slave. Oracle recommends using the TRUE parameter only after an attempt to use the FALSE parameter fails.

Note: The force option requires the MANAGE SCHEDULER system privilege.