Wednesday, 23 July 2014

Identify failed/skipped patch jobs in ebusiness 11i

/* Formatted on 7/23/2014 1:25:25 PM (QP5 v5.115.810.9015) */
create or replace view failed_jobs as
--Collect all sql jobs which are failed in the last 30 days
select ''decode(command,'sqlplus_single','sqlplus','sqlplus','sqlplus','sql',
      'sqlplus')''' apps/aps @/ora/prod/prod10appl/'''PRODUCT'''/11.5.0/'''
      SUBDIRECTORY'''/'''job_name' ' COMMAND,START_TIME
from
ad_task_timing
where
end_time is null
and
START_TIME>SYSDATE-20 and command like'%sql%'
--Collect all fnd loader jobs which are failed in the last 30 days
UNION
select ' FNDLOAD apps/apps 'substr(arguments,10)' ' COMMAND,start_time
from
ad_task_timing
where
end_time is null
and
START_TIME>SYSDATE-20 and (command='bin') and arguments like'%LOAD%'
--Collect all attachment uploads which are failed in the last 30 days
UNION
select ' FNDGFU apps/apps 'substr(arguments,10)' ' COMMAND,start_time
from
ad_task_timing
where
end_time is null
and
START_TIME>SYSDATE-20 and command='bin' and arguments not like'%UPLOAD%'
--collect all odf jobs which are failed in the last 30 days
UNION
select
      'adodfcmp priv_schema=system/manager userid=inv/apps touser=apps/apps change_db=yes odffile=/ora/prod/prod10appl/'''
      PRODUCT'''/11.5.0/'''SUBDIRECTORY'''/'''job_name' 'arguments' ' COMMAND,
      start_time
from
ad_task_timing
where
end_time is null
and
START_TIME>SYSDATE-20 and command='odf';

select command from failed_jobs order by start_time;

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete