OBIEE 11G - Agent Diagnostic (log)

Get the job Id

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;

Get the logs of failed agents

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:

• Agent is the prefix for all agent log files.
• JobID is the Oracle BI Scheduler job identifier for the agent.
• InstanceID is the Oracle BI Scheduler instance identifier for the agent.
• xxx is the file extension:
  • .err for agent error log files.
  • .log for debug log files.

Verify the run of the analytics

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;