Oracle Enterprise Manager notify when Recovery Area low

I seem to be on the Oracle Enterprise manager kick lately, so why not one more post on it. Recently I hit the 85% mark on the Fast Recovery Area due to archive log bloat and did not see any alerts triggered in OEM. Well as I discovered, part of the reason for this is that according to MOS,

"If using Fast Recovery Area (FRA) for the archive destination and the destination is set to USE_DB_RECOVERY_FILE_DEST, then the Archive Area % Used metric will not trigger anymore."
Well that is annoying. However, I did find that the alert does trigger, but it is buried and doesn't show up on any main pages. To see it, go to the target database home page. Then navigate to Oracle Database->Monitoring->All Metrics. In the left pane you can scroll down to “Recovery Area->Recovery Area Free Space”.  There you will see your alert if active. When viewing the All Metrics page, click on the alerts icon to see active alerts.


If you want to see it triggered just temporarily shrink your db_recovery_file_dest_size. i.e.
alter system set db_recovery_file_dest_size=2g scope=both;

To view the status directly in the database use this query.
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

Now, you probably want some type of notification when this happens. Just create an incident rule. By default, all Oracle databases will trigger the alert log at when the FRA is 85% and 97% full. To have OEM notify you when this happens, create a custom rule set.
1. Navigate to Setup->Incidents->Incident Rules
2. Click Create Rule Set
3. Give the rule a name like “Notify On Recovery Area”.
4. For Targets, choose “all targets of types database instance”
5. Within Rules, click Create
6. Choose “Incoming events and updates to events” and click continue
7. Choose Type: “Metric Alert” and “Specific events to type Metric Alert”
8. Set Target Type: Database Instance, in the metric name box type: “recovery area free”
9. Check off the “Recovery Area Free Space (%)” Metric
10. Select the drop down and choose “All”. Click OK.
11. Click Next in the top right.
12. For Add Actions, Click Add, pick “E-mail To” and select a user to send emails to.
13. Click Continue in the top right and then next.
14. Give the Rule a Name like “Notify On Recovery Area Email Rule”.
15. Click next and save.

Now whenever the FRA hits 85% or more, you will be notified. Alternatively, you can approach this another way by using a custom metric extension. See: https://blogs.oracle.com/oem/entry/fast_recovery_area_for_archive


Comments

Popular posts from this blog

Active Directory Authentication with an Oracle Database

Create a custom backup report with BI Publisher for Oracle Enterprise Manager/Cloud Control 13c