Oracle job not running, locating issues

I. Operating frequency settings for the job

1. Daily fixed time operation, such as morning 8:10 minutes: Trunc (sysdate+1) + (8*60+10)/24*60

Available in 2.Toad:

Daily: Trunc (sysdate+1)

Weekly: Trunc (sysdate+7)

Per month: trunc (sysdate+30)

Each Sunday: Next_day (trunc (sysdate), ' SUNDAY ')

6 points per day: trunc (sysdate+1) +6/24

Half an hour: sysdate+30/1440

3. Run the 15th minute of each hour, for example: 8:15,9:15,10:15...:trunc (sysdate, ' hh ') +75/1440. Originally I set is trunc (sysdate, ' hh ') +15/1440, found incredibly not.

Two. Why is the job not running?

1. First to understand the job parameter description: Job-related parameters One is job_queue_processes, this is the number of processes running the job, of course, the system inside the job is greater than this value, there will be queued, the minimum is 0, indicating that the job is not running, The maximum value is 36, on the OS corresponding to the process snpn,9i after the OS management job is called CJQN. You can use the following SQL to determine how many snp/cjq are currently running.

SELECT * from V$bgprocess, this paddr non-empty SNP/CJQ process is the currently idle process, and some indicates a working process.

The other is job_queue_interval, in the range between 1--3600, the unit is the second, this is the wake-up job process, because every time the SNP run he rested, need to wake him regularly, this value can not be too small, too small can affect the performance of the database.

2. Diagnosis: First determine whether the above two parameters are set correctly, especially the first parameter, set to 0, all jobs will not run, confirm the error, we continue down.

3. Use the following SQL to view the job's broken,last_date and Next_date,last_date is the end time of the most recent job run, Next_date is the next execution time based on the frequency of the setup, According to this information can determine whether the job last normal, but also to determine the next time is right, SQL is as follows:

SELECT * FROM Dba_jobs

Sometimes we find his next_date is January 1, 4000, indicating that the job is either in the running, or the state is break (broken=y), if the broken value of the job is found to be Y, to find the user to understand, Determine whether the job can be broken, if not broken, then change the broken value to N, modify and then use the above SQL to see his last_date has changed, the job can run normally, modify the broken status of SQL as follows:

Declare

BEGIN

Dbms_job. Broken (<job_id>,false);

END;

4. Use the following SQL query to see if the job is still running

SELECT * FROM Dba_jobs_running

If you find that the job has run for a long time and is not over, check the reason. General job running lock related resources, you can look at the v$access and v$locked_object two view, if other processes are found to lock the job-related object, including pkg/function/ Procedure/table and other resources, then it is necessary to remove the other processes, the need to remove the job process, and then rerun to see the results.

5. What if the above is normal, but the job is not run? Then we have to consider restarting the job process to prevent the SNP process from dying and causing the job not to run, the instructions are as follows:

Alter system set job_queue_processes=0--shuts down the job process and waits for 5--10 seconds

Alter system set JOB_QUENE_PROCESSES=5--Restores the original value

Bug in 6.Oracle

Oracle9i There is a bug, when the counter to 497 days, just reach its maximum, then the count will become-1, continue to count becomes 0, and then the counter will no longer run. If this is the case, we have to restart the database, we have a production version of the database is 9205, there has been such a problem, and then the user about the time after the restart is no problem. However, other Oracle7345 and oracle8i databases did not find this problem.

7. The database check basically this much, if the job run still have a problem, that need to cooperate with the user to see whether the program itself is a problem, such as the amount of data processing, or slow network speed, resulting in the operation is too long, it needs specific circumstances specific analysis. We can do the job manually through the following SQL to see:

Declare

Begin

Dbms_job.run (<job>_id)

End

If you find that job execution is not normal, you need to combine the program to analyze the specific.

Oracle job not running, locating issues