How should I implement a database cron job logger?

Question

I use PHP and Oracle, with crontab executing the PHP scripts at scheduled times. My current logging/auditing solution involves simple log files. I'd like to save my cron execution logs to a database instead.

Right now I'm trying to design the process so that when a cron job starts I create a record in an CronExecution table. Then every time I want to log something for that cron I'll put a record in a CronEvent table which will have a foreign key to the CronExecution table.

I plan to log all events using a PRAGMA AUTONOMOUS pl/sql procedure. With this procedure I will be able to log events inside of other pl/sql procedures and also from my PHP code in a consistent manner.

To make it more robust, my plan is to have a fallback to log errors to a file in the event that the database log calls fail.

Has anybody else written something similar? What suggestions do you have based on your experience?

Answer 1

Yep, I've done this several times.

The CronExecution table is a good idea. However, I don't know that you really need to create the CronEvent table. Instead, just give yourself a "status" column and update that column.

You'll find that makes failing over to file much easier. As you build lots of these CronExecutions, you'll probably have less interest in CronEvents and more interest in the full status of the execution.

Wrap all of the update calls in stored procedures. You definitely have that correct.

Including a 'schedule' in your CronExecution will prove handy. It's very easy to have a lot of cron jobs and not be able to connect the dots on "how long did this take?" and "when is this supposed to run". Including a "next scheduled run" on completion of a job will make your life much easier.

Answer 2

You will want to read the documentation on DBMS_SCHEDULER. It's a little bit of work to implement (what isn't?), but it will allow you to control scheduled and one-time jobs from within the database. That includes OS-level jobs.