oracle的dbms_job怎么填写,Oracle 如何指定作业(dbms_job, dbms_scheduler) 在指定的实例运行...

在RAC环境有时需要指定JOB 只运行在某个节点，如DBMS_schduler job调用OS shell的情况等对本地节点有依赖时，这里只是简单的记录一下方法

1， dbms_job

DBMS_JOB支持作业的多实例执行。默认情况下，作业可以在任何实例上执行，但是只有一个实例可以执行该作业。另外，您可以通过将作业绑定到特定实例来强制实例绑定。通过为实例相似性参数指定实例编号来实现实例绑定。

DBMS_JOB.SUBMIT

To submit a job to the job queue, use the following syntax:

DBMS_JOB.SUBMIT(

job OUT BINARY_INTEGER,

what IN VARCHAR2, NEXT_DATE IN DATE DEFAULTSYSDATE,

interval IN VARCHAR2 DEFAULT 'NULL',

no_parse IN BOOLEAN DEFAULT FALSE,

instance IN BINARY_INTEGER DEFAULT ANY_INSTANCE,

force IN BOOLEAN DEFAULT FALSE);

Use the parameters instance and force to control job and instance affinity. The default value of instance is 0 (zero) to indicate that any instance can execute the job. To run the job on a certain instance, specify the instance value.

2,  dbms_scheduler

在RAC环境中也是默认是负载最小节点运行，同可以指节点

instance_id

Valid only in an Oracle Real Application Clusters environment. Indicates the instance on which the job is to be run.

You could set instance ID to where job have to run using, 11g and above

begin

dbms_scheduler.set_attribute(name => '.' ,attribute=>'INSTANCE_ID', value=> );

end;

replace symbolic names in <> by your real values.

When no instance_id is set then RAC load balance choose on node to run the job.

Doc for 10g say about instance_stickiness this:

instance_stickiness

This attribute should only be used for a database running in RAC mode. By default, it is set to TRUE. If you set instance_stickiness to TRUE, jobs start running on the instance with the lightest load and the Scheduler thereafter attempts to run on the instance that it last ran on. If that instance is either down or so overloaded that it will not start new jobs for a significant period of time, another instance will run the job. If the interval between runs is large, instance_stickiness will be ignored an the job will be handled as if it were a non-sticky job.If instance_stickiness is set to FALSE, each instance of the job runs on the first instance available.

For non-RAC environments, this attribute is not useful because there is only one instance.

So check you job repeat_interval.

打赏

微信扫一扫，打赏作者吧～