Designing monitoring of jobs on Oracle database

Question

I have several Oracle databases 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 solution:

1. Create a monitoring function and configuration tables only on one database which will check jobs on every database using database links.

   pros: Centralized functionality, easy to maintain.
   cons: I have to do the checks using database links.

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

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

Which solution is better?

Answer 1

I'd go with option #1 - centralized functionality that uses database links.

Database links have an undeserved bad reputation. One of the main reasons is that too many people use public database links, where anyone connecting to the database can use the link. That's obviously a security nightmare, but it's not the default setting and it's easy to avoid that trap.

Some other issues with database links:

1. They don't perform well for huge inserts of millions of rows. On the other hand they're great at many small SELECTs or INSERTs. I frequently have hundreds of links open and fetching data concurrently, on 10 year-old hardware, and it works great.
2. They make execution plans more difficult to troubleshoot.
3. Not all data types are natively supported. This is better in 12.2, but in earlier versions you will need to use an INSERT to move data types like CLOB into tables, and then read from those tables.
4. For DDL you'll need to use DBMS_UTILITY.EXEC_DDL_STATEMENT@LINK_NAME('create ...'); Make sure to only use DDL in there. Other types of commands will silently fail.
5. Links may hang indefinitely in a few rare situations, like if the database has an archiver error or a guaranteed restore point that's full. (This one is really a blessing in disguise - many tools like Oracle Enterprise Manager will not catch those issues. You may want to have a background job checking for database link queries that have been running longer than X minutes.)

6. Links should not be hard-coded or else they could invalidate the package. But this may not matter - you'll probably want to loop through the list of databases and use dynamic SQL anyway. And if the link doesn't exist it's pretty easy to create a new one. Here's an example:

   declare
       v_result varchar2(4000);
   begin
       --Loop through a configuration table of links.
       for links in
       (
           select database_name, db_link
           from dbs_to_monitor
           left join user_db_links
               on dbs_to_monitor.database_name = user_db_links.db_link
           order by database_name
       ) loop
           --Run the query if the link exists.
           if links.db_link is not null then
               begin
                   --Note the user of REPLACE and the alternative quoting mechanism, q'[...]';
                   --This looks a bit silly with this small example, but in a real-life query
                   --it avoids concatenation hell and makes the query much easier to read. 
                   execute immediate replace(q'[
                       select dummy from dual@#DB_LINK#
                   ]',
                   '#DB_LINK#', links.db_link)
                   into v_result;
   
                   dbms_output.put_line('Result: '||v_result);
               --Catch errors if the links are broken or some other error happens.
               exception when others then
                   dbms_output.put_line('Error with '||links.db_link||': '||sqlerrm);
               end;
           --Error if the link was not created.
           --You will have to run:
           --create database link LINK_NAME connect to USERNAME identified by "PASSWORD" using 'TNS_STRING'; 
           else
               dbms_output.put_line('ERROR - '||links.db_link||' does not exist!');
           end if;
       end loop;
   end;
   /
   

Despite all of that, database links are great because you can do everything in PL/SQL, on one database. In a single language you can create an agentless monitoring solution and don't have to worry about installing and fixing agents.

As an example, I built the open source program Method5 to do everything using database links. With that program installed you could gather results from hundreds of databases as simply as running select * from table(m5('select * from dba_jobs'));. That program is probably overkill for your scenario but it shows that database links are all you need for a full monitoring solution.