最近在一个测试环境中建立物化视图远程同步环境但是远程视图一直未按照预期的时间进行刷新以至于一开始让我怀疑ONDEMAND与STARTWITHNEXTBY沖突但再三确认发现是我的测试环境中ora_cjq进程不存在唉!开始怎么不往这方面去想呢修改一下JOB_QUEUE_PROCESSES参数一切恢复正常突然发现ORACLE JOB方面的问题还不少贴一篇METALINK文章历数以前碰到的JOB失效的原因无非以下几种
JobsNotExecutingAutomatically文档ID:注释:
Symptoms
Jobsarenolongerexecutingautomatically
Ifforced(execdbms_jobrun();)theseexecutefine
Cause
Tryingthemostcommonreasonswhyjobsdontexecuteautomaticallyandasscheduled:
)InstanceinRESTRICTEDSESSIONSmode?
Checkiftheinstanceisinrestrictedsessionsmode:
selectinstance_nameloginsfromv$instance;
Iflogins=RESTRICTEDthen:
altersystemdisablerestrictedsession;
^Checked!
)JOB_QUEUE_PROCESSES=
Makesurethatjob_queue_processesis>
showparameterjob_queue_processes
^Checked!
)_SYSTEM_TRIG_ENABLED=FALSE
Checkif_system_enabled_trigger=false
colparameterformata
colvalueformata
selectaksppinmparameterbksppstvlvaluefromx$ksppiax$ksppcvb
Whereaindx=bindxandksppinm=_system_trig_enabled;
^Checked!
)IsthejobBROKEN?
selectjobbrokenfromdba_jobswherejob=;
Ifbrokenthencheckthealertlogandtracefilestodiagnosetheissue
^Checked!Thejobisnotbroken
)IsthejobCOMMITted?
Makesureacommitisissuedaftersubmittingthejob:
DECLAREXNUMBER;
BEGIN
SYSDBMS_JOBSUBMIT
(
job=>X
what=>dbms_utilityanalyze_schema
(SCOTTCOMPUTENULLNULLNULL);
next_date=>to_date(//::dd/mm/yyyyhh:mi:ss)
no_parse=>FALSE
);
COMMIT;
END;
/
Ifthejobexecutesfineifforced(ieexecdbms_jobsrun();)thenlikelyacommit
ismissing
^Checked!Thejobiscommittedaftersubmission
)UPTIME>days
Checkiftheserver(machine)hasbeenupformorethandays:
ForSUNuseuptimeOScommand
Ifuptime>andthejobsdonotexecuteautomaticallythenyouarehittingbug
(Jobsmaystoprunningafterdaysuptime)whichisfixedinandA
^Checked!Theserverinthiscasehasbeenupdaysonly
)DBA_JOBS_RUNNING
Checkdba_jobs_runningtoseeifthejobisstillrunning:
select*fromdba_jobs_running;
^Checked!Thejobisnotrunning
)LAST_DATEandNEXT_DATE
Checkifthelast_dateandnext_dateforthejobareproper:
selectJobNext_dateLast_datefromdba_jobswherejob=;
^NEXT_DATEisporperhoweverLAST_DATEisnullsincethejobneverexecutesautomatically
)NEXT_DATEandINTERVAL
CheckiftheNext_dateischangingproperlyaspertheintervalsetindba_jobs:
selectJobIntervalNext_dateLast_datefromdba_jobswherejob=;
^Thisisnotpossiblesincethejobnevergetsexecutedautomatically
)TogglevalueforJOB_QUEUE_PROCESSES
Setjob_queue_processes=waitsometimeandthenandsetitbacktotheoriginalvalue:
altersystemsetjob_queue_processes=;
altersystemsetjob_queue_processes=;
Ref:Bug(fixedby:A)
^Donebutdidnothelp
)DBMS_IJOB(Nondocumented):
Lastditcheffort
Eitherrestartthedatabaseortrythefollowing:
execdbms_ijobset_enabled(true);
Ref:Bug(ClosedNotaBug)
@Note(UsingDBMS_IJOB)INTERNALNOTE
Donebutdidnothelp
Thesearethemostcommoncausesforthisbehavior
Solution
Thesolutionendeduptobetheserver(machine)uptime
Eventhoughitwasupforonlydaysaftertheserverwasrebootedalljobswereabletoexecuteautomatically
Toimplementthesolutionpleaseexecutethefollowingsteps:
Shutdownallapplicationsincludingdatabases
Shutdowntheserver(machine)
Restartallapplicationsincludingdatabases
Checkthatjobsareexecutingautomatically
