Oracle – Logging Procedure and Package Errors to OS Syslog

logsoraclesolaris

Good day,

Setup: Oracle 11G on Solaris 10 x86

We'd like to capture the errors/failures of our nightly scheduled procedures/ packages in the OS's syslog. That log will then be sent to a remote NMS managed by the NOC who'll alert the on-call personnel. Can and how can this be done?

Thanks.

Best Answer

Of course it can be done, the question is, do you really want this?

Scheduler jobs can raise events (put in a queue). You can capture (dequeue) those events using Advanced Queueing. For example:

Monitoring Job State with Events Raised by the Scheduler

How To Use DBMS_SCHEDULER To Publish Events For Oracle Advanced Queueing (Doc ID 871718.1)

Once you have the events and details, you need to write them to syslog. You can do this by using external procedures, for example:

write to syslog from pl/sql

Or, you could keep it nice and simple, and use the built-in e-mail notification support for Scheduler, for example:

BEGIN
 DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
  job_name   =>  'EOD_JOB',
  recipients =>  'jsmith@example.com, rjones@example.com',
  sender     =>  'do_not_reply@example.com',
  subject    =>  'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%',
  body       =>   '%event_type% occurred at %event_timestamp%. %error_message%',
  events     =>  'JOB_FAILED, JOB_BROKEN, JOB_DISABLED, JOB_SCH_LIM_REACHED');
END;
/

Monitoring Job State with E-mail Notifications

Related Question