|
最近发现两个数据库每天晚上23：55分都有警告日志的报错信息：
Wed Sep 24 23:55:04 2008 Errors in file /oracle/app/oracle/admin/gdimall/bdump/gdimall_j001_4043.trc: ORA-12012: error on auto execute of job 3 ORA-12005: may not schedule automatic refresh for times in the past Wed Sep 24 23:57:09 2008 Errors in file /oracle/app/oracle/admin/gdimall/bdump/gdimall_j001_4043.trc: ORA-12012: error on auto execute of job 3 ORA-12005: may not schedule automatic refresh for times in the past 查看trace文件： gd_idb01:[/oracle/app/oracle/admin/gdimall/bdump$]cat /oracle/app/oracle/admin/gdimall/bdump/gdimall_j001_4043.trc /oracle/app/oracle/admin/gdimall/bdump/gdimall_j001_4043.trc Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production ORACLE_HOME = /oracle/app/oracle/product/9.2.0 System name: HP-UX Node name: gd_idb01 Release: B.11.11 Version: U Machine: 9000/800 Instance name: gdimall Redo thread mounted by this instance: 1 Oracle process number: 147 Unix process pid: 4043, image: oracle@gd_idb01 (J001)
*** SESSION ID:(782.45392) 2008-09-24 23:55:04.144 *** 2008-09-24 23:55:04.144 ORA-12012: error on auto execute of job 3 ORA-12005: may not schedule automatic refresh for times in the past *** SESSION ID:(782.45400) 2008-09-24 23:57:09.407 *** 2008-09-24 23:57:09.407 ORA-12012: error on auto execute of job 3 ORA-12005: may not schedule automatic refresh for times in the past 查看相关的job： SQL>select job,log_user,schema_user,what,LAST_DATE,LAST_SEC,THIS_DATE,THIS_SEC,NEXT_DATE,NEXT_SEC,INTERVAL from dba_jobs where job=3;
JOB LOG_USER SCHEMA_USER ---------- ------------------------------ ------------------------------ WHAT -------------------------------------------------------------------------------- LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC --------- ---------------- --------- ---------------- --------- ---------------- INTERVAL -------------------------------------------------------------------------------- 3 COMP_EAF COMP_EAF P_LOGIN_STATISTICS(); 25-SEP-08 00:01:14 25-SEP-08 23:55:00 trunc(sysdate)+1435/1440
发现interval是trunc(sysdate)+1435/1440。在视图all_jobs中，以下列的含义为： （a）LAST_DATE：Date on which this job last successfully executed——job成功执行的完毕时间 （b）LAST_SEC：Same as LAST_DATE. This is when the last successful execution started.－－上次成功执行的开始时间 （c）THIS_DATE：Date that this job started executing (usually null if not executing)——没有job在执行的时候，该字段为空。若有job正在运行，这个时间是job的开始执行时间。 （d）THIS_SEC：Same as THIS_DATE. This is when the last successful execution started. （e）NEXT_DATE：Date that this job will next be executed——job下次执行时间。 （f）NEXT_SEC：Same as NEXT_DATE. This is when the last successful execution started。 （g）INTERVAL：A date function, evaluated at the start of execution, becomes next NEXT_DATE
对于该INTERVAL参数如上例：job 3执行时间为9月24日23：55，那么如果interval是trunc(sysdate)+1435/1440，因此，next date就是把job的开始时间23：55，代入到interval中的sysdate，得到的结果就仍然是9月24日的23：55。而next date的更新，是一次job完成时更新的，比如job在9月24日的1：14完成，于是在更新next date的时候，就发现next date的时间晚于当前的时间，于是就报错may not schedule automatic refresh for times in the past。而事实上，job应该是成功执行了的。为了解决这个报错，interval改成trunc(sysdate+1)+1435/1440。 conn COMP_EAF/COMP_EAF begin sys.dbms_job.change(job => 3, what => 'P_LOGIN_STATISTICS();', next_date => to_date('2008-09-25 23:55:00', 'yyyy-mm-dd hh24:mi:ss'), interval => 'trunc(sysdate+1)+1435/1440'); commit; end; /
说明，执行的时候，如果以sys执行可以会报以下错误，以all_jobs中shema_user连接执行即可。 ERROR at line 1: ORA-23421: job number 64 is not a job in the job queue ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.DBMS_IJOB", line 529 ORA-06512: at "SYS.DBMS_JOB", line 204 ORA-06512: at "SYS.DBMS_JOB", line 185 ORA-06512: at line 2 |
|
|
来自： guolijiegg > 《oracle》
