数据库迁移到RAC环境的一个节点后，JOB不能正常执行。其中job中包含db link

问题描述：
job多次failure后，broken

查找原因：
以what=pr_paylog_today为例，在存储过程pr_paylog_today_test中记录错误日志：

create table temperr (errcode number);

CREATE OR REPLACE PROCEDURE PR_PAYLOG_TODAY_test
as

.......
exception when others
then
intcode := sqlcode;
insert into temperr values(intcode);
commit;
end;
/

SQL> variable job1 number;
SQL> begin
  2  dbms_job.submit(:job1, 'PR_PAYLOG_TODAY_test;',sysdate,'sysdate+2/1440');
  3  end;
  4  /

PL/SQL procedure successfully completed
job1
---------
48

SQL> select * from temperr where rownum <3;

   ERRCODE
----------
    -12154
    -12154

[oracle@ebs ~]$ oerr ora 12154
12154, 00000, "TNS:could not resolve service name"
// *Cause:  The service name specified is not defined correctly in the
// TNSNAMES.ORA file.

解决问题：
新的环境是rac 的一个节点，需要确定是哪个instance来执行这个job
/*
To assign a particular instance to execute a job, use the following syntax:

DBMS_JOB.INSTANCE(  JOB IN BINARY_INTEGER,
     instance                IN BINARY_INTEGER,
     force                   IN BOOLEAN DEFAULT FALSE);
The FORCE parameter in this example defaults to FALSE. If the instance value is 0 (zero),
job affinity is altered and any available instance can execute the job despite the value of force.
If the INSTANCE value is positive and the FORCE parameter is FALSE, job affinity is altered only if the specified instance is running,
or Oracle displays error ORA-23428.

If the force parameter is TRUE, any positive integer is acceptable as the job instance and the job affinity is altered.
Oracle displays error ORA-23319 if the instance value is negative or NULL.

*/

SQL> select instance_number, instance_name from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              1                           preboss1

SQL> begin
    dbms_job.instance(
    job =>49,
    instance => 1,
    force => false);
    end;
    /

commit;
现在job能正常调度了。

 

后续：

把PR_PAYLOG_TODAY_test测试的任务停掉：
begin
dbms_job.remove(48);
end;
/

commit;

drop procedure PR_PAYLOG_TODAY_test;
drop table temperr;