View the DBMS jobs log oracle

Question

I have a scheduled a job in DBMS jobs (not DBMS scheduler). I can see the job has failed in weekends. I want to see the log file with failure reason. Where i can i find this?

Any suggestions please?

Thanks in advance.

Tychon

AFAIK, there's no information in the database itself available for jobs using the DBMS_JOB package. You'll have to look at the file system, as suggested by CorradoPiola. That's one of the many, many reasons why you should switch to DBMS_SCHEDULER instead. – Arad

Yeah, Frank is right! DBMS_SCHEDULER is really better... – Idioblast

@Frank...Hi Frank, migration from DBMS jobs to DBMS scheduler has be to approved by the senior management . Unfortuanetly, i cant make a decision on this and so i have to go with the DBMS jobs at this moment.. – Tychon

Answer 1

For DBMS_SCHEDULER (as noted by Frank Schmitt) try this:

SELECT *
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
ORDER BY LOG_DATE DESC;

and then look in your bdump folder, for the trace files.

For DBMS_JOB you can view your alert log file:

SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'background_dump_dest';

or

SELECT VALUE
FROM V$SPPARAMETER
WHERE NAME = 'background_dump_dest';

The alert log file has a name like "alert_orcl.log", if your database name is the default "orcl".

Idioblast

That's for DBMS_SCHEDULER, not for DBMS_JOB. – Arad

Yeah, right. The question was for DBMS_JOB... :( For this package you can view your alert log file... – Idioblast

Hello...thanks for ur reply. But cab u tell how can i go to this alert log file. – Tychon

i updated my answer, to reflect what's noted by Frank Schmitt too... Thanks again, Frank... – Idioblast

Sorry Piola...no luck with this solution – Tychon

Answer 2

For DBMS_JOB you'd see the information about failed job in the database alert log. There you'd also see a name of the tracefile with more information about the failure.

Supper

Answer 3

Late answer, but I think it will help people coming on this page for solution. Before getting into log details to debug, you will need to enable logging. By default, logging is disabled.

Below are options to enable logging :

Logging Level    DBMS_SCHEDULER.LOGGING_OFF
DBMS_SCHEDULER.LOGGING_FAILED_RUNS DBMS_SCHEDULER.LOGGING_RUNS
DBMS_SCHEDULER.LOGGING_FULL

Now, you can set attribute to update logging level :

begin     
DBMS_SCHEDULER.SET_ATTRIBUTE('job_name','logging_level',DBMS_SCHEDULER.LOGGING_FULL);
end;

Prolamine

You are most likely confusing the two different means of running jobs that exist in Oracle Database - DBMS_SCHEDULER and DBMS_JOB. – Grishilda