Oracle daily maintenance-job failures

1.About check_mk metric:ORA_IYOA_DBMS_JOB_Check

This is a custom monitoring metric on check_mk platform,
the threshold is defined in /usr/lib/check_mk_oracle/MAIN/conf/perf_IYOA_job.json

{"target_info":[{
"perf_min":"0",
"chk_interval":5000,
"output_cmd":"echo",
"post_action":"",
"perf_crit":"10",
"chk_cmd":"sh run_job.sh",
"perf_max":"0",
"chk_result_separator":";",
"perf_warn":"2",
"database":"IYOA"
}]}

notice the two threshold value:“perf_warn”:“2”,“perf_crit”:“10”,Represent warning and critical respectively

2.solution:

2.1check whether the failures times are more than threshold value,notice the failures column

SELECT job,log_user,priv_user,schema_user,last_date,last_sec,this_date,this_sec,next_date,next_sec,total_time,broken,interval,failures,what schema_user FROM DBA_JOBS WHERE FAILURES<>0

output :

2.2 check alert log

you can confirm where the alert log located by the following command in sqlplus or other sql tools

SQL> show parameter background_dump_dest

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
background_dump_dest                 string                            /oracle/admin/IYOA/bdump

2.3view the alert_{sid}.log

vi /oracle/admin/IYOA/bdump/alert_IYOA.log
Tue Jan 26 11:45:35 WIT 2021
Errors in file /oracle/admin/IYOA/bdump/iyoa_j003_2950.trc:
ORA-12012: error on auto execute of job 2242
ORA-12170: TNS:Connect timeout occurred
ORA-06512: at "HGCOST.SAP_INBOUND_ISSUE_E_CANCEL", line 6
ORA-06512: at "HGCOST.SAP_INBOUND_ISSUE_E_CANCEL", line 23
ORA-06512: at "HGCOST.SAP_INBOUND_RECIEVE", line 13
ORA-06512: at line 1

2.4　fix the problem

send the information that the previous sql stement outputed to relevant IT person, ask them to check what happened, and fix thier program
of course, the reason that result in job execution failure is very many, but you must have a basic judgement,like this, it’s much possible to be a network connection problem, maybe you can solve by yourself,here how to check network will be another topic

##　2.5 fix failures counts in job$ table
finally you must reset failures column in dba_jobs to zero after the problem is resolved,else check_mk warning or critical alarm will be persist

update job$ set failures=0 where failures<>0
commit