How to diagnostic a failed agent in 11G.
You can get the job id from the Job Manager or from the bi_platform schema.
From the bi_platform schema.
set define off;
select * from
(
select
job.job_id as job_id,
(select job_param from s_nq_job_param where script_type = 'iBot' and relative_order = 1 and job_id = job.job_id) as job_path,
'Agent-'|| job.job_id || '-' || last_instance.instance_id || '.*' as log_pattern,
job.name as job_name,
last_instance.instance_id,
last_instance.begin_ts,
last_instance.end_ts,
round((last_instance.end_ts - last_instance.begin_ts)*24*60,2) as instance_duration_min,
last_instance.exit_code
from s_nq_job job left outer join
(select * from (
select row_number() over (partition by job_id order by instance_id desc) as row_number, instance.* from s_nq_instance instance
)
where row_number = 1 ) last_instance on last_instance.job_id = job.job_id
)
where
job_path like '/shared/%'
order by job_path;
The location for agent log files is specified in the instanceconfig.xml file for the Oracle BI Scheduler. Normally, fmw_home/instances/instance1/diagnostics/logs/OracleBISchedulerComponent/coreapplication_obisch1
The log file name has the following format:
Agent-JobID-InstanceID.xxx
where:
SELECT
saw_src_path,
row_count,
ROUND((CAST(acct.end_ts AS DATE)-CAST(acct.start_ts AS DATE))*24,2) AS duration_hour,
acct.*
FROM
s_nq_acct acct
WHERE
saw_src_path LIKE
'/path'
AND start_dt > sysdate - 1
-- AND user_name = 'agent user'
ORDER BY
start_ts DESC;
To get more information, you can: