Queries Arsenel for Oracle Apps DBA - Part 6

Queries Arsenel for Oracle Apps DBA - Part 5

**********************************************************

APPS HEALTH CHECK

**********************************************************

--Confirm Alert Periodic Scheduler is okay, should see no rows



select 'PROBLEM: No pending Alert Periodic Scheduler request' from dual


where not exists (


select


cp.concurrent_program_name Program,


cr.request_id Request,


phase_code S,


status_code C,


cr.argument1


', '






cr.argument2


', '






cr.argument3


', '






cr.argument4


', '






cr.argument5


', '






cr.argument6


', '






cr.argument7


', '






cr.argument8 Arguments


from


apps.fnd_concurrent_requests cr,


apps.fnd_concurrent_programs cp


where


cr.concurrent_program_id = cp.concurrent_program_id


and cp.application_id >= 0 -- force on index leading edge


and cr.program_application_id = cp.application_id


and cp.concurrent_program_name = 'ALEPPE' -- Alert scheduler con pgm


and cr.phase_code = 'P'


and cr.status_code != 'H' -- not on hold


)










--Look for programs that should usually be disabled based on description



SELECT


cp.concurrent_program_name


, cp.user_concurrent_program_name


, cp.description


FROM


apps.fnd_concurrent_programs_vl cp


WHERE


UPPER(cp.description) like '%USUALLY%DISABLED%'


AND cp.enabled_flag = 'Y' -- should be N to be disabled










--Show any requests by USER_NAME that ended in error in last 5 days


select request_id Request


,fu.user_name User_Name


,to_char(cr.actual_start_date, 'DD HH24:MI') When_start


,to_char(cr.actual_completion_date, 'DD HH24:MI') When_end


,cp.concurrent_program_name Program


,phase_code


,status_code


from


apps.fnd_concurrent_programs cp,


apps.fnd_user fu,


apps.fnd_concurrent_requests cr


where


cr.concurrent_program_id = cp.concurrent_program_id


and cr.program_application_id = cp.application_id


and cr.actual_completion_date is not null


and cr.requested_by = fu.user_id


and fu.user_name = 'user_name'


and cr.status_code = 'E' -- requests that ended in error


and trunc(cr.actual_start_date) >= trunc(sysdate-5) -- started recently


order by cr.actual_start_date desc