Oracle DBMS作业未运行 [英] Oracle DBMS Job not running

问题描述

我定义了一个工作，每5分钟从周二运行到周日.从上午9:00到下午22:00

I defined a job to run from Tuesday to Sundays every 5 min. from 9:00 am to 22:00 pm

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'GET_INVOICES_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN LOPES.GET_INVOICES; END;',
repeat_interval =>'FREQ=MINUTELY; INTERVAL=5; BYHOUR=9,22; BYDAY=TUE,WED,THU,FRI,SAT,SUN', 
enabled => TRUE,
comments => 'GET_INVOICES');
END;
/

但是这项工作不会使人感到窒息

But the job does not run cheking

SELECT *
FROM USER_SCHEDULER_JOB_RUN_DETAILS 
ORDER BY LOG_DATE DESC

检查作业似乎没问题:

并手动运行该作业，它会执行该过程，但不会每5分钟执行一次

and running the job manually it execute the procedure but only once not every 5 minutes

推荐答案

这是最常见的Scheduler问题之一. 在这里，我们列出了一些常见问题及其解决方案.

This is one of the most common Scheduler questions asked. Here we list some of the common problems and their solutions.

1)job_queue_processes可能太低(这是最常见的问题) job_queue_processes的值限制了dbms_scheduler的总数 和可以在给定时间运行的dbms_job作业. 要检查是否存在这种情况，请检查的当前值 job_queue_processes与 SQL>从v $ parameter中选择值，其中name ='job_queue_processes'; 然后检查正在运行的作业数 SQL>从dba_scheduler_running_jobs中选择count(); SQL>从dba_jobs_running中选择count();

1) job_queue_processes may be too low (this is the most common problem) The value of job_queue_processes limits the total number of dbms_scheduler and dbms_job jobs that can be running at a given time. To check whether this is the case check the current value of job_queue_processes with SQL> select value from v$parameter where name='job_queue_processes'; Then check the number of running jobs SQL> select count() from dba_scheduler_running_jobs; SQL> select count() from dba_jobs_running;

如果这是问题，您可以使用 SQL>更改系统设置job_queue_processes = 1000;

If this is the problem you can increase the parameter using SQL> alter system set job_queue_processes=1000;

2)max_job_slave_processes可能太低 如果此参数不为NULL，则它将限制可以执行的dbms_scheduler作业数量 一次运行.要检查这是否是问题，请检查当前 值使用 SQL>从dba_scheduler_global_attribute选择值 其中attribute_name ='MAX_JOB_SLAVE_PROCESSES'; 然后检查正在运行的作业数 SQL>从dba_scheduler_running_jobs中选择count(*);

2) max_job_slave_processes may be too low If this parameter is not NULL then it limits how many dbms_scheduler jobs can be running at a time. To check whether this is the problem, check the current value using SQL> select value from dba_scheduler_global_attribute where attribute_name='MAX_JOB_SLAVE_PROCESSES'; Then check the number of running jobs SQL> select count(*) from dba_scheduler_running_jobs;

如果这是问题，您可以使用或增加NULL来增加数量 SQL>执行dbms_scheduler.set_scheduler_attribute('max_job_slave_processes'，null)

If this is the problem you can increase the number or just NULL it out using SQL> exec dbms_scheduler.set_scheduler_attribute('max_job_slave_processes',null)

3)会话次数可能太少 此参数可随时限制会话数.每个调度程序工作 需要2个会话.要检查这是否是问题，请检查当前 重视使用 SQL>从v $ parameter中选择值，其中name ='sessions'; 然后使用检查当前会话数 SQL>从v $ session选择count(*)

3) sessions may be too low This parameter limits the number of sessions at any time. Every Scheduler job requires 2 sessions. To check whether this is the problem, check the current valule using SQL> select value from v$parameter where name='sessions'; Then check the current number of sessions using SQL> select count(*) from v$session ;

如果数字太近，您可以使用 SQL>更改系统设置job_queue_processes = 200;

If the numbers are too close you can increase the maximum using SQL> alter system set job_queue_processes=200;

4)您最近是否应用了时区更新补丁或升级了数据库 到具有更新的时区信息的版本?如果您跳过任何步骤， 更新时区信息时，作业可能无法运行.要检查这是否 是这样的情况下尝试做 SQL>从sys.scheduler $ _job选择*; 和 SQL>从sys.scheduler $ _window中选择*; 并确保它们完成没有错误.

4) Have you recently applied a timezone update patch or upgraded the database to a version with newer timezone information ? If you skipped any steps when updating the timezone information, jobs may not run. To check whether this is the case try doing SQL> select * from sys.scheduler$_job; and SQL> select * from sys.scheduler$_window; and make sure they finish without errors.

如果引发时区警告，请重新应用升级或 时区补丁程序，请确保遵循所有步骤.

If it throws a timezone warning, reapply the upgrade or timezone patch making sure to follow all the steps.

5)数据库是否以受限模式运行? 如果数据库以受限模式运行，则不会运行任何作业(除非 您正在使用11g，并使用ALLOW_RUNS_IN_RESTRICTED_MODE属性). 要检查此用途 SQL>从v $ instance选择登录名；

5) Is the database running in restricted mode ? If the database is running in restricted mode then no jobs will run (unless you are using 11g and use the ALLOW_RUNS_IN_RESTRICTED_MODE attribute). To check this use SQL> select logins from v$instance ;

如果登录受到限制，则可以使用以下命令禁用受限模式 SQL>更改系统禁用受限会话；

If logins is restricted you can disable the restricted mode using SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

6)作业是否计划在发生故障的实例上运行?

6) Is the job scheduled to run on an instance which is down ?

您可以通过查看是否为作业设置了instance_id(检查dba_scheduler_jobs视图)来进行检查，如果是，则应检查该实例是否启动.

You can check this by seeing whether instance_id is set for the job (check the dba_scheduler_jobs view), and if so you should check whether that instance is up.

7)作业是否计划在尚未在任何实例上启动的服务上运行?

7) Is the job scheduled to run on a service which has not been started on any instances ?

您可以通过检查作业指向哪个job_class，然后检查该类是否指向服务来进行检查.如果是这样，请确保已在至少一个正在运行的实例上启动该服务.您可以使用dbms_service.start_service在实例上启动服务.

You can check this by checking what job_class a job points to and then checking whether that class points to a service. If it does, make sure the service has been started on at least one running instance. You can start a service on an instance using dbms_service.start_service.

8)资源管理器是否具有限制性的资源计划?

8) Is the Resource Manager in effect with a restrictive resource plan ?

如果限制性资源计划正在生效，则调度程序作业可能没有分配足够的资源，因此它们可能无法运行.您可以通过执行以下操作来查看有效的资源计划

If a restrictive resource plan is in effect, scheduler jobs may not have sufficient resources allocated so they may not run. You can check what resource plan is in effect by doing

SQL>从V $ RSRC_PLAN中选择名称；

SQL> select name from V$RSRC_PLAN ;

如果没有有效的计划或有效的计划是INTERNAL_PLAN，则资源管理器无效.如果资源管理器有效，您可以通过执行以下操作将其禁用

If no plan is in effect or the plan in effect is INTERNAL_PLAN then the resource manager is not in effect. If the resource manager is in effect you can disable it by doing

SQL>更改系统集resource_manager_plan ='';

SQL>alter system set resource_manager_plan = '';

9)调度程序是否已禁用?这不是受支持的操作 但总有可能有人这样做.要检查这个 SQL>从dba_scheduler_global_attribute中选择值，其中attribute_name ='SCHEDULER_DISABLED'

9) Has the Scheduler been disabled ? This is not a supported action but it is possible that someone has done it anyway. To check this do SQL> select value from dba_scheduler_global_attribute where attribute_name='SCHEDULER_DISABLED'

如果此查询返回TRUE，则可以使用 SQL>执行dbms_scheduler.set_scheduler_attribute('scheduler_disabled'，'false');

If this query returns TRUE then you can fix this using SQL> exec dbms_scheduler.set_scheduler_attribute('scheduler_disabled','false');

导致工作迟到的原因

1)首先要检查的是作业预定的时区 SQL>从dba_scheduler_jobs中选择所有者，工作名称，下一个运行日期；

1) The first thing to check is the timezone that the job is scheduled with SQL> select owner, job_name, next_run_date from dba_scheduler_jobs ;

如果作业在错误的时区中，则它们可能无法按预期运行 时间.如果next_run_date使用绝对时区偏移量(例如 +08:00)，而不是指定的时区(例如US/PACIFIC)，则作业可能不会 如果实行夏令时，则按预期运行-可能会运行一个小时 早或晚.

If the jobs are in the wrong timezone they may not run at the expected time. If the next_run_date is using an absolute timezone offset (like +08:00) instead of a named timezone (like US/PACIFIC) then the jobs may not run as expected if daylight savings is in effect - they may run an hour early or late.

2)可能是在预定作业运行时， 可能已暂时达到上述限制，导致作业延迟. 检查上述限制是否足够高，如果可能，请在检查期间检查它们 工作被延迟的时间.

2) It may be that at the time the job was scheduled to run, one of the several limits above may have been temporarily reached causing the job to be delayed. Check if the limits above are high enough and if possible check them during the time that the job is being delayed.

3)可能达到上述限制之一的可能原因是: 维护窗口可能已生效.维护窗口是Oracle 属于名为的窗口组的调度程序窗口 MAINTENANCE_WINDOW_GROUP.在预定的维护时段内，有几个 维护任务是使用作业运行的.这可能会导致列出的限制之一 被击中，用户工作被延迟.有关更多信息，请参见管理指南 关于这一点(第24章).

3) One possible reason that one of the above limits may be hit is that a maintenance window may have come into effect. Maintenance windows are Oracle Scheduler windows that belong to the window group named MAINTENANCE_WINDOW_GROUP. During a scheduled maintenance window, several maintenance tasks are run using jobs. This may cause one of the limits listed above to be hit and user jobs to be delayed. See the admin guide for more info about this (chapter 24).

要获取维护窗口的列表，请使用 SQL>从dba_scheduler_wingroup_members中选择*；

To get a list of maintenance windows use SQL> select * from dba_scheduler_wingroup_members;

要查看Windows运行的时间，请使用 SQL>从dba_scheduler_windows中选择*；

To see when the windows run use SQL> select * from dba_scheduler_windows;

要解决此问题，您可以增加限制或重新安排维护时间 Windows在更方便的时间运行.

To fix this you can either increase the limits or reschedule the maintenance windows to run at more convenient times.

诊断其他问题

如果这些都不起作用，则可以采取以下一些进一步的步骤来尝试 弄清楚发生了什么.

If none of this works, here are some further steps you can take to try to figure out what is going on.

1)检查警报日志中是否有任何错误.如果数据库是 分配内存时遇到问题或磁盘空间不足或其他任何问题 发生了灾难性错误，您应该首先解决这些错误.你可以 通过使用查找警报日志的位置 SQL>从v $ parameter中选择值，其中name ='background_dump_dest'; 警报日志将在此目录中，名称以"alert"开头.

1) Check whether there are any errors in the alert log. If the database is having trouble allocating memory or has run out of disk space or any other catastrophic errors have occurred, you should resolve those first. You can find the location of the alert log by using SQL> select value from v$parameter where name = 'background_dump_dest'; The alert log will be in this directory with a name starting with "alert".

2)检查作业协调器跟踪文件是否存在，是否存在，请检查是否存在 包含任何错误.如果存在，它将位于 您可以在上面找到"background_dump_dest"目录，该目录看起来像 类似于SID-cjq0_nnnn.trc.如果这里有任何错误，他们可能 提示为什么作业没有运行.

2) Check whether if a job coordinator trace file and if it does, check if it contains any errors. If this exists, it will be located in the 'background_dump_dest' directory which you can find as above and will look something like SID-cjq0_nnnn.trc . If there are any errors here they may hint at why jobs are not running.

3)如果以上任何一项表明SYSAUX表空间(调度程序存储其日志记录表的位置)已满，则可以使用dbms_scheduler.purge_log过程清除旧的日志条目.

3) If either of the above indicates that the SYSAUX tablespace (where the scheduler stores its logging tables) is full, you can use the dbms_scheduler.purge_log procedure to clear out old log entries.

4)查看当前是否有打开的窗口.如果有，您可以尝试将其关闭以查看是否有帮助.

4) See if there is a window currently open. If there is, you can try closing it to see if that helps .

SQL> select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE where 
attribute_name='CURRENT_OPEN_WINDOW';
SQL> exec DBMS_SCHEDULER.close_window ('WEEKNIGHT_WINDOW');

5)尝试运行一个简单的一次运行的作业，看看它是否运行

5)try running a simple run-once job and see if it runs

SQL>begin
dbms_scheduler.create_job (
job_name => 'test_job',
job_type => 'plsql_block',
job_action => 'null;',
enabled => true);
end;
/
SQL> -- wait a while
SQL> select * from user_scheduler_job_run_details where job_name='TEST_JOB';

6)如果简单的一次运行作业没有运行，则可以尝试按以下方式重新启动调度程序.

6) If a simple run-once job doesn't run, you can try restarting the scheduler as follows.

SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
SQL> alter system set job_queue_processes=0;
SQL> exec dbms_ijob.set_enabled(FALSE);
SQL> 
SQL> alter system flush shared_pool;
SQL> alter system flush shared_pool;
SQL>
SQL> exec dbms_ijob.set_enabled(TRUE);
SQL> alter system set job_queue_processes=99;
SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');

这篇关于Oracle DBMS作业未运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！