熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

OracleJob不自動運行解決辦法

2013-11-13 15:34:15  來源: Oracle 

  ORACLE有一種定時調度機制用dbms_job包來管理

  設置的JOB就是不運行搞得的郁悶

  最好執行了這個才搞定 exec dbms_ijobset_enabled(true);

  下面提供一個checklist用於檢查job異常的原因

  ) Instance in RESTRICTED SESSIONS mode?

  Check if the instance is in restricted sessions mode:

  select instance_namelogins from v$instance;

  If logins=RESTRICTED then:

  alter system disable restricted session;

  ^– Checked!

  ) JOB_QUEUE_PROCESSES=

  Make sure that job_queue_processes is >

  show parameter job_queue_processes

  ^– Checked!

  ) _SYSTEM_TRIG_ENABLED=FALSE

  Check if _system_enabled_trigger=false

  col parameter format a

  col value format a

  select aksppinm parameterbksppstvl value from x$ksppi ax$ksppcv b

  where aindx=bindx and ksppinm=_system_trig_enabled;

  If _system_trig_enabled=false then

  alter system set _system_trig_enabled=TRUE scope=both;

  ^– Checked!

  ) Is the job BROKEN?

  select jobbroken from dba_jobs where job=<job_number>;

  If broken then check the alert log and trace files to diagnose the issue

  ^– Checked! The job is not broken

  ) Is the job COMMITted?

  Make sure a commit is issued after submitting the job:

  DECLARE X NUMBER;

  BEGIN

  SYSDBMS_JOBSUBMIT

  (

  job => X

  what => dbms_utilityanalyze_schema

  (SCOTTCOMPUTENULLNULLNULL);

  next_date => to_date(// ::dd/mm/yyyy hh:mi:ss)

  no_parse => FALSE

  );

  COMMIT;

  END;

  /

  If the job executes fine if forced (ie exec dbms_jobsrun(<job_no>);) then likely a commit

  is missing

  ^– Checked! The job is committed after submission

  ) UPTIME > days

  Check if the server (machine) has been up for more than days:

  For SUN use uptime OS command

  If uptime> and the jobs do not execute automatically then you are hitting unpublished bug

  (Jobs may stop running after days uptime) which is fixed in and A

  ^– Checked! The server in this case has been up days only

  ) 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 JobNext_dateLast_date from dba_jobs where job=<job_number>;

  ^– NEXT_DATE is porper however LAST_DATE is null since the job never executes automatically

  ) NEXT_DATE and INTERVAL

  Check if the Next_date is changing properly as per the interval set in dba_jobs:

  select JobIntervalNext_dateLast_date from dba_jobs where job=<job_number>;

  ^– This is not possible since the job never gets executed automatically

  ) Toggle value for JOB_QUEUE_PROCESSES

  Stop and restart CJQ process(es)

  alter system set job_queue_processes= ;

  –<Wait for some time to ensure CJQ process stopped>

  alter system set job_queue_processes= ;

  Ref: Bug (fixed by: )

  ^– Done but did not help

  ) DBMS_IJOB(Nondocumented):

  Last ditch effort

  Either restart the database or try the following:

  exec dbms_ijobset_enabled(true);

  Ref: Bug (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 days after the server was rebooted all jobs were able to execute automatically

  To implement the solution please execute the following steps:

   Shutdown all applications including databases

   Shutdown the server (machine)

   Restart all applications including databases

   Check that jobs are executing automatically

  from metalink docs


From:http://tw.wingwit.com/Article/program/Oracle/201311/16944.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.