How to monitor backup jobs on Oracle 11g?

Question

Background:

I'm trying to monitor that backups run as expected on several databases. Zenoss is used to monitor most other stuff.

A couple of KPIs are already monitored using Zenoss in combination with script that runs sqlplus on database host.

What I've found so far:

Views in database:

• V$RMAN_STATUS
• V$RMAN_BACKUP_JOB_DETAILS;
• V$RMAN_BACKUP_SUBJOB_DETAILS;

Stop gap solution:

Sending alerts from Enterprise Manager:

• Setup > Monitoring Templates > Create > Target : some Database > Metric Threshold > Failed Job Count

or

• Preferences > Rules > Create > Tab "General" - Target Type: Database Instance, Tab "Jobs" - Specific Jobs > Add Specific Jobs: Select the job and Job Status: "Problem"

What I'd like to know:

• Does this seem reasonable or is there another preferred way to integrate monitoring of Oracle into a third party nms? (Polling is preferred to traps in the current setting. )
• Pitfalls to be aware of.
• There seems to be significant differences between the database views I mentioned and what I see in EM backup history. Where should I be looking for the status of past backups?

Answer 1

I might have found a couple of answers on my own. Posting them here in case others need to know:

• Google (or my google skills) seems to be getting increasingly less reliable.

• Searching the forums at https://forums.oracle.com/ I found this thread: https://forums.oracle.com/forums/thread.jspa?messageID=9956411, which references http://www.morganslibrary.org/reference/dba_best_practices.html. Here is the code mentioned:

  • SELECT start_time, end_time, input_type, input_type, status FROM v$rman_backup_job_details ORDER BY 1;

    and

  • SELECT vbd.file#, vrbjd.start_time, vrbjd.end_time, vbd.incremental_level, vrbjd.input_type, vrbjd.status FROM v$rman_backup_job_details vrbjd, v$backup_datafile vbd WHERE vbd.completion_time BETWEEN vrbjd.start_time AND vrbjd.end_time AND vrbjd.input_type <> 'ARCHIVELOG' ORDER BY 2,1;

• As for why the query in Enterprise Manager picked older jobs I learned a nice trick from someone at work: You can read recent queries from the view v$sqlarea. Something like select sql_text from v$sqlarea order by last_active_time desc should do the trick.

Answer 2

Using a job status from a scheduler is not reliable at all, often the backup scheduling has problems so it is important to also notice that backups are not started at all. For this you can find a zabbix Oracle module at github that does the backup monitoring based on v$rman_status. Zabbix does a real good job for this kind of monitoring.