数据库迁移到RAC环境的一个节点后,JOB不能正常执行。其中job中包含db link
问题描述:
job多次failure后,broken
job多次failure后,broken
查找原因:
以what=pr_paylog_today为例,在存储过程pr_paylog_today_test中记录错误日志:
以what=pr_paylog_today为例,在存储过程pr_paylog_today_test中记录错误日志:
create table temperr (errcode number);
CREATE OR REPLACE PROCEDURE PR_PAYLOG_TODAY_test
as
as
.......
exception when others
then
intcode := sqlcode;
insert into temperr values(intcode);
commit;
end;
/
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 /
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
job1
---------
48
SQL> select * from temperr where rownum <3;
ERRCODE
----------
-12154
-12154
----------
-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.
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.
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.
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
--------------- ----------------
1 preboss1
SQL> begin
dbms_job.instance(
job =>49,
instance => 1,
force => false);
end;
/
dbms_job.instance(
job =>49,
instance => 1,
force => false);
end;
/
commit;
现在job能正常调度了。
现在job能正常调度了。
后续:
把PR_PAYLOG_TODAY_test测试的任务停掉:
begin
dbms_job.remove(48);
end;
/
begin
dbms_job.remove(48);
end;
/
commit;
drop procedure PR_PAYLOG_TODAY_test;
drop table temperr;
drop table temperr;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10173379/viewspace-688334/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10173379/viewspace-688334/