Designing database job monitoring

Question

I have several Oracle database where my in-house applications are running. Those applications use both dba_jobs and dba_scheduler_jobs.

I want to write monitoring function: check_my_jobs which will be called periodically by Nagios to check if everything is OK with my jobs. (Are they running? Is it Broken? Is next_run_date delayed? and so on)

Solutions:

Due to the fact that I have to monitor jobs on different databases, there is two way of implementing the solution:

1. Create monitoring function and configuration tables only on one database which will check jobs on every database using the Database link.

   pros: Centralized functionality, easy to maintain.
   cons: I have to make a check using the Database link.

2. Create monitoring function and configuration tables in every database where I want to check jobs.

   pros: I don't have to use DB link
   cons: Duplicated monitoring code on every database

Which solution is better?

Answer 1

I don't think there's one right answer here - but there are a few considerations you don't mention above:

• Reliability - Your DB link solution creates a single point of failure (or more accurately an additional one). Since the point of this is monitoring, that doesn't seem ideal.
• Credential storage - You'll need to store credentials for the connection. In the DB link case these are stored in the DB link (which is pretty secure if you create it from a privileged account). You should make sure you have a secure credential storage solution for the other case. You'll also want to evaluate overhead when the credentials are rotated.
• Performance - DB links tend to be a bit chatty - not ideal for complex interactions over wide area networks
• Encapsulation - From a security point of view, you should think about least privileges, and about encapsulating the function as encapsulated PL/SQL functions. In 12c you can assign roles to those PL/SQL functions that aren't generally assigned to the user. That encapsulation / least privs model would require having the code on each database.

The reliability argument would make me lean towards the distributed model

Answer 2

I would recommend the latter.
Monitor each database separately, with CheckMK acting as the coordinating "hub".

If you centralise the monitoring through one database, what happens when that one database goes down?
Answer: It takes all of your monitoring with it!

Sadly, we do not live in an Ideal World, where everything is "always on".
Stuff happens and, when it does, a distributed monitoring scheme will cope far better than one that relies on any one part of the system always being available.

Answer 3

Why not just check with simple SQL query if specific results come back?

Like:

Select data from table @ db1, and see if results come back, if not Nagios check will fail.