oracle JOB 无法运行方法处理汇总

来看看

　　oracle JOB 有时会无法运行，当一个JOB无法运行时，首先通过如下思路去查询，
　　查看last date、next date/failure次数、broken，分析是否因为job执行的存储过程异常或产生了死锁，导致job多次运行失败，最终被自动broken掉，通常数据库开发中大多数问题都是这个原因导致的。通过下面的语句查看job的运行情况:
　　SELECT JOB,WHAT,LOG_USER,TO_CHAR(LAST_DATE,'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(NEXT_DATE,'YYYY-MM-DD HH24:MI:SS'),INTERVAL,FAILURES,broken from user_jobs;
　　如果这里面的failures失败次数变为17，broken变回Y，也就是以后不会再执行了。这时要排查JOB里面的存储过程了，这里通过两种方式来执行，
　　1  exec dbms_job.broken(job numbert,false,sysdate+1/2440);
　　commit;
　　2   SQL> exec dbms_job.run(job number,true);
　　commit
　　如不是因为这个造成的，下面来检查job_queue_processes这个参数设置，这个参数决定着同时运行JOB的数量，通过dba_jobs_running来查正在运行的JOB数量，如超过的话，加大这个值，alter system set job_queue_processes=n scope=spfile;
　　同时也要注意这个SNP进程死了造成JOB不跑，查询语句如下：select * from v$bgprocess where name like 'SNP%' OR NAME LIKE 'CJQ%';
　　查询结果中，如果PADDR=00，则可确定确实是SNP进程死掉了，重起即可，方法如下：alter system set job_queue_processes=0;  alter system set job_quene_processes=20;
　　如还是搞不定的情况，请以下情况依次来解决####来自from metalink docs ： 313102.1
　　下面提供一个checklist用于检查job异常的原因：
　　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;
　　^– Checked!
　　#####有个库重新导入后，导致JOB无法运行，经查询该实例原来登录为RESTRICTED，采用alter system disable restricted session; 查询登录为allow了，可以重新运行该JOB了。
　　2) JOB_QUEUE_PROCESSES=0
　　Make sure that job_queue_processes is > 0
　　show parameter job_queue_processes
　　^– Checked!
　　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’;
　　If _system_trig_enabled=false, then
　　alter system set “_system_trig_enabled”=TRUE scope=both;
　　^– Checked!
　　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.
　　^– Checked! The job is not broken.
　　5) Is the job COMMITted?
　　Make sure a commit is issued after submitting the job:
　　BEGIN
　　SYS.DBMS_JOB.SUBMIT
　　(
　　job => X
　　,what => ‘dbms_utility.analyze_schema
　　(”SCOTT”,”COMPUTE”,NULL,NULL,NULL);’
　　,next_date => to_date(’08/06/2005 09: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.
　　^– Checked! The job is committed after submission.
　　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 unpublished bug 3427424
　　(Jobs may stop running after 497 days uptime) which is fixed in 9206 and A102
　　^– Checked! The server in this case has been up 126 days only
　　7) DBA_JOBS_RUNNING
　　Check dba_jobs_running to see if the job is still running:
　　select * from dba_jobs_running;
　　^– Checked! The job is not running.
　　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) Toggle value for JOB_QUEUE_PROCESSES
　　Stop and restart CJQ process(es)
　　alter system set job_queue_processes=0 ;
　　–
　　alter system set job_queue_processes=4 ;
　　Ref: Bug 2649244 (fixed by: 9015, 9203, 10201)
　　^– Done but did not help
　　11) DBMS_IJOB(Non-documented):
　　Last ditch effort.
　　Either restart the database or try the following:
　　exec dbms_ijob.set_enabled(true);
　　Ref: Bug 3505718 (Closed, Not a Bug)
　　Done but did not help
　　These are the most common causes for this behavior.
　　Solution
　　The solution ended up to be the server (machine) uptime.
　　Even though it was up for only 126 days, after the server was rebooted all jobs were able to execute automatically.
　　To implement the solution, please execute the following steps:
　　1. Shutdown all applications, including databases.
　　2. Shutdown the server (machine)
　　3. Restart all applications, including databases.
　　4. Check that jobs are executing automatically.