IT貓撲網(wǎng):您身邊最放心的安全下載站! 最新更新|軟件分類|軟件專題|手機(jī)版|論壇轉(zhuǎn)貼|軟件發(fā)布

您當(dāng)前所在位置: 首頁數(shù)據(jù)庫Oracle → Oracle Job不自動運行解決辦法

Oracle Job不自動運行解決辦法

時間:2015-06-28 00:00:00 來源:IT貓撲網(wǎng) 作者:網(wǎng)管聯(lián)盟 我要評論(0)

  ORACLE有一種定時調(diào)度機(jī)制,用dbms_job包來管理。

  設(shè)置的JOB就是不運行,搞得的郁悶,

  最好執(zhí)行了這個才搞定 exec dbms_ijob.set_enabled(true);

  下面提供一個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!

  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:

  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/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.

  from metalink docs : 313102.1

關(guān)鍵詞標(biāo)簽:Oracle,Job,解決辦法

相關(guān)閱讀

文章評論
發(fā)表評論

熱門文章 Oracle中使用alter table來增加,刪除,修改列的語法 Oracle中使用alter table來增加,刪除,修改列的語法 oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例 oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例 誤刪Oracle數(shù)據(jù)庫實例的控制文件 誤刪Oracle數(shù)據(jù)庫實例的控制文件 為UNIX服務(wù)器設(shè)置Oracle全文檢索 為UNIX服務(wù)器設(shè)置Oracle全文檢索

相關(guān)下載

    人氣排行 oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例 Oracle中使用alter table來增加,刪除,修改列的語法 ORACLE SQL 判斷字符串是否為數(shù)字的語句 ORACLE和SQL語法區(qū)別歸納(1) oracle grant 授權(quán)語句 ORACLE修改IP地址后如何能夠使用 如何加速Oracle大批量數(shù)據(jù)處理 Oracle刪除表的幾種方法