What's the most auditable way to organize database maintenance scripting/scheduling?

Question

I'm using PHP, Oracle and crontab. Crontab is invoking a php script, which is going to synchronize some data between a local and remote database.

First I thought about putting this all in one large, anonymous inline PL/SQL block and executing it in PHP. The problem is that without creating a table to audit the procedure it's really opaque to my PHP code until it returns. And then when it does return, the best I can do is receive a string in an out parameter which I've concatenated together as an audit log.

Then I considered breaking it up into several SQL statements and have PHP do some intermediate auditing/logging and manipulation of the data between each of them.

I'm not really satisfied with either of these. How do you organize maintenance code that is called in a cronjob?

Answer 1

I assume you use a manual commit mode for this type of scripts. Most of the maintenance script I use just do the job with quite small requests, (not that smaller is better, but smaller is more simple) and throw exceptions when something goes wrong, if the exceptions in not recoverable, then I rollback modifications and use error_log() and for critical task mail() to output the problem. If all work well, no output is made at all, changes are just commited. If I need a flag or something, I may use apc_store/apc_fetch() or a simple file.

Answer 2

If both the local and the remote databases are Oracle, then you might want to consider Oracle Replication as a more resilient alternative to a PHP script

Answer 3

When working with Oracle, this type of thing is usually done in the database, using Oracle's scheduler (or jobs, before 9i). However, even if you were using that, the process would be opaque to your PHP code while it was running.

It sounds like what you really want is for the PHP code to be able to know how the large anonymous block is progressing as it runs. The best way to handle that without writing to a table is to use contexts. Basically, you'll create a global context in the database, then add lines to the anonymous block to update the context as it progresses. Since global contexts are visible across all sessions, you can have a separate PHP thread running to monitor the context and write the changes to your log.

However, when you create a context, you have to indicate which package is allowed to administer it, so you can't update it directly from the autonomous block. You can accomplish this by moving the entirety of the autonomous block into a package or by creating a small package that does nothing but update the context, then calling that procedure from the block.