Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question

migrated from serverfault.com Nov 5 '11 at 13:58

This question came from our site for professional system and network administrators.

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Hi @Erik and welcome to the site. It looks like you have answered your own question - is that right? If so would you mind fleshing out the answer with your solution (rather than relying on the link to OTN)? And of course marking your answer as accepted once you are happy it is the way to go :) –  Jack Douglas Nov 7 '11 at 8:05
    
Hi Jack, thanks! Yes, I'll update the answer with more details. As for Marking it as accepted, is it OK if I wait a couple of days for someone else to tune in? I guess someone around here knows a whole lot more about this subject than me :) –  Erik I Nov 7 '11 at 9:13
    
Yes, that would be good form and thanks for the update. Out of curiosity, why was EM picking older jobs? Does it store a separate history? –  Jack Douglas Nov 7 '11 at 10:08
    
It seems it is tapping into the generic job history. Even though the link says "Backup Jobs" it seems the list is a generic "Job Activity" overview rather than an overview of backup jobs. This seems consistent with what I can see from v$sqlarea. –  Erik I Nov 10 '11 at 7:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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