2

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?

  • Is there anything wrong with database links in your particular environment? – JeffO Feb 7 '18 at 17:30
  • #1 is a sort of "server looking for clients" architecture. Seems an inefficient server-client architecture to me. And unnatural. The idea would look better if each database does report to the main one. Somewhat what monitoring solutions often do with agents and servers. – Laiv Nov 29 '18 at 7:26
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.