首页 » ORACLE » Troubleshoot oracle job is not run automatically(不自动执行原因)

Troubleshoot oracle job is not run automatically(不自动执行原因)

A:
A friends ask the  job creation is not executed properly , the goal is executed once every minute .

Q:

How to create a dbms_job?( 10g and later version   had a new job dbms_scheduler_job.)

SQL> variable job1 number;
SQL> begin
dbms_job.submit(:job1,’p_test;’,sysdate,’sysdate+1/24/60′);
end;

Now the other possible causes of the collection to share , as follows:

1) Instance in RESTRICTED SESSIONS mode?
Check if the instance is in restricted sessions mode:
select instance_name,logins from v$instance;
If logins=RESTRICTED, then:
alter system disable restricted session;

2) JOB_QUEUE_PROCESSES=0
Make sure that job_queue_processes is > 0
show parameter job_queue_processes

3) _SYSTEM_TRIG_ENABLED=FALSE
Check if _system_enabled_trigger=false
col parameter format a25
col value format a15
select a.ksppinm parameter,b.ksppstvl value from x$ksppi a,x$ksppcv b
Where a.indx=b.indx and ksppinm=’_system_trig_enabled’;

4) Is the job BROKEN?
select job,broken from dba_jobs where job=;
If broken, then check the alert log and trace files to diagnose the issue.

5) Is the job COMMITted?
Make sure a commit is issued after submitting the job:
DECLARE X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
(
job => X
,what => ‘dbms_utility.analyze_schema
(”SCOTT”,”COMPUTE”,NULL,NULL,NULL);’
,next_date => to_date(’08/06/200509:35:00′,’dd/mm/yyyy hh24:mi:ss’)
,no_parse => FALSE
);
COMMIT;
END;
/
If the job executes fine if forced (i.e., exec dbms_jobs.run();), then likely a commit
is missing.

6) UPTIME > 497 days
Check if the server (machine) has been up for more than 497 days:
For SUN , use ‘uptime’ OS command.
If uptime>497 and the jobs do not execute automatically, then you are hitting bug 3427424
(Jobs may stop running after 497 days uptime) which is fixed in 9206 and A102

7) DBA_JOBS_RUNNING
Check dba_jobs_running to see if the job is still running:
select * from dba_jobs_running;

8) LAST_DATE and NEXT_DATE
Check if the last_date and next_date for the job are proper:
select Job,Next_date,Last_date from dba_jobs where job=;
^– NEXT_DATE is porper, however LAST_DATE is null since the job never executes automatically.

9) NEXT_DATE and INTERVAL
Check if the Next_date is changing properly as per the interval set in dba_jobs:
select Job,Interval,Next_date,Last_date from dba_jobs where job=;
^– This is not possible since the job never gets executed automatically.

10)  oracle background process ora_j000_<sid> not   exists.  e.g. ps -ef|grep ora_j000;

11) if  ora_j000 not exists, mack sure the aq_tm_processes parameter value is >0;

 

The CJQ0 process dynamically spawns job queue slave processes (J000…J999) to run the jobs.

12) max_job_slave_process  parameter (if the version have) is not enough;

13) processes or sessions parameter is not enough;

 

14) update timezone..

打赏

目前这篇文章有2条评论(Rss)评论关闭。

  1. August Carnahan | #1
    2011-12-21 at 03:38

    Please let me know if you’re looking for a writer for your weblog. You have some really good posts and I think I would be a good asset. If you ever want to take some of the load off, I’d love to write some articles for your blog in exchange for a link back to mine. Please blast me an e-mail if interested. Thank you!

  2. Kaylyn | #2
    2011-11-12 at 01:34

    What an awesome way to eplxain this-now I know everything!