rac中的job

author：skate
time：2011/08/19

 

rac中的job

 

一个JOB在何级别运行是可以定制的。

对于集群数据库，如果把job定义在db级，job可以运行在任何活动的instance上，并遵循job的调度机制，
JOB的调度通过JOB协调进程来完成的，通过JQ队列锁来避免竞争和重复执行，每个JOB分配一个唯一的JQ锁；
如果把job定义在instance级别上，job将运行在指定的实例上，如因某种异常导致创建job的实例当机，那
job将运行在存活的实例上。

 

参考Oracle文档：
you can create a job at the cluster database level and the job will run on any active instance of the target Oracle RAC database. Or you can create a job at the instance level and the job will only run on the specific instance for which you created it. In the event of a failure, recurring jobs can run on a surviving instance.

Oracle文档说：
Each RAC instance has its own job coordinator. The database monitoring checks that determine whether or not to start the job coordinator do take the service affinity of jobs into account. For example, if there is only one job scheduled in the near future and the job class to which this job belongs has service affinity for only two out of the four RAC instances, only the job coordinators for those two instances will be started.

 

例子：

rac创建job（job默认是创建在‘0’实例上）

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    (
      job        => X
     ,what       => 'declare
v_a nvarchar2(10);
begin
select '' a'' into v_a from dual;
end;'
     ,next_date  => to_date('08/19/2011 11:26:43','mm/dd/yyyy hh24:mi:ss')
     ,interval   => 'SYSDATE+30/1440 '
     ,no_parse   => FALSE
    );
:JobNumber := to_char(X);
END;

在指定实例上创建job

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    (
      job        => X
     ,what       => 'declare
v_a nvarchar2(10);
begin
select '' a'' into v_a from dual;
end;'
     ,next_date  => to_date('08/19/2011 11:26:43','mm/dd/yyyy hh24:mi:ss')
     ,interval   => 'SYSDATE+30/1440 '
     ,no_parse   => FALSE
     ,instance  => 1
     ,force     => TRUE
    );
:JobNumber := to_char(X);
END;

现在查看下现有的job运行在哪个实例上

SQL> select job,instance,what from dba_jobs j where J.JOB in (41,42);

       JOB   INSTANCE WHAT
---------- ---------- --------------------------------------------------------------------------------
        41          0 declare
                      v_a nvarchar2(10);
                      begin
                      select ' a' into v_a from dual;
                      end;

        42          1 declare
                      v_a nvarchar2(10);
                      begin
                      select ' a' into v_a from dual;
                      end;

SQL>
SQL>

 

把job 41 更改到实例1上

SQL> exec dbms_job.instance(41,1);

PL/SQL procedure successfully completed

SQL> select job,instance,what from dba_jobs j where J.JOB in (41,42);

       JOB   INSTANCE WHAT
---------- ---------- --------------------------------------------------------------------------------
        41          1 declare
                      v_a nvarchar2(10);
                      begin
                      select ' a' into v_a from dual;
                      end;

        42          1 declare
                      v_a nvarchar2(10);
                      begin
                      select ' a' into v_a from dual;
                      end;

把job 42 更改到实例2上

SQL> exec dbms_job.instance(42,2);

PL/SQL procedure successfully completed

SQL> select job,instance,what from dba_jobs j where J.JOB in (41,42);

       JOB   INSTANCE WHAT
---------- ---------- --------------------------------------------------------------------------------
        41          1 declare
                      v_a nvarchar2(10);
                      begin
                      select ' a' into v_a from dual;
                      end;

        42          2 declare
                      v_a nvarchar2(10);
                      begin
                      select ' a' into v_a from dual;
                      end;

SQL>

 

 

 

--------------------end-----------------------