ITPub博客

首页 > Linux操作系统 > Linux操作系统 > rac 环境job调度

rac 环境job调度

原创 Linux操作系统 作者:zhaohf_1106 时间:2011-03-03 14:23:52 0 删除 编辑
数据库迁移到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;

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10173379/viewspace-688334/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-10-30

  • 博文量
    44
  • 访问量
    100345