Using Corrective Action to capture and email database status

As DBA, were you asked questions like:
I was blocked by other database sessions last night, could you please check who blocked me and what he was doing at that time?
My query ran into an temp tablespace space error, could you please check who was using large temporary space at that moment?
They are not hard questions, but you will find that it is not easy to answer them unless you have proactive consideration.

Here, I am going to show how easy it is to do that using Corrective Action in Oracle Enterprise Manager Cloud Control 12c.

1. Setting Up a Mail Server for Notifications
Log into OEM, from the [Setup] menu, select [Notifications], then select [Notification Methods]

2. Setting Up E-mail for Yourself
From [username] drop-down menu, select [Enterprise Manager Password & E-mail]. Click [Add Another Row] to create a new e-mail entry field in the E-mail Addresses table. Specify the e-mail associated with your Enterprise Manager account. All e-mail notifications you receive from  Enterprise Manager will be sent to the e-mail addresses you specify.

3. Set up a Notification Schedule 
Once you have defined your e-mail notification addresses, you will need to define a notification schedule.
From [Setup] menu, select [Notifications], then select [My Notification Schedule]. Follow the directions on the Notification Schedule page to specify when you want to receive e-mails

4.  Creating Incident Rules for the events you want to monitor
From the [Setup] menu, select [Incidents], then select [Incident Rules]. Click [Create Rule Set...]

In [Create Rule Set] page,  specify the [Name], [Description], and the [Targets] to which the rules set should apply. Then click [Create...] to display [Select Type of Rule to Create] window, select [Incoming Events and updates to events], click [Continue].

In [Create New Rule: Select Events] page, select [Specific events of type Metric Alert], click [Add] to display [Select Specific Metric Alert] page, select [User Block] for metric and appropriate options for [Severity] and [Corrective action status], then click [Ok]

Back to [Create New Rule: Select Events] page, click [Next] to display [Create New Rule: Add Actions] page. Click [Add] to display
[Add Actions] page, specify your username for [E-mail To] Notificatios, then click [Continue] to return to [Create New Rule: Add Actions] page. Click [Next] to specify Name and Description for new rule, then click [Next] to display [Create New Rule: Review] page, click [Continue]

Back to [Create Rule Set] page, click [Save] to return to [Incident Rules - All Enterprise Rules] page.

5.     Create Corrective Actions

In database home page, from [Oracle Database] menu, select [Monitoring], then click [Metric and Collection Settings] to display [Metric and Collection Settings]

In [Metric and Collection Settings], click [Disabled] in the [Collection Schedule] field after metric [User Block] to enable metric collection. Then click edit image after metric [Blocking Session Count] to display [Edit Advanced Settings: Blocking Session Count].

In [Edit Advanced Settings: Blocking Session Count], select [All others] then click [Edit] button to display [Edit Advanced Settings: Blocking Session Count for All others]

In  [Edit Advanced Settings: Blocking Session Count for All others], enter 2 for [Warning Threshold] and 5 for [Critical Threshold], then click [Add] button beside [Warning].

In [Add Corrective Action] page, select [SQL Script] from dropbox then click [Continue] button to display [Create 'SQL Script' Corrective Action].

In [Create 'SQL Script' Corrective Action]
* Enter Name and Description in tab [Gerneral]
* Enter SQL statements in tab [Parameters], here, SQL statements are what you want to run immediately after the alert is generated.
* Enter database credential and host credential information.

Then click [Continue] button and back to [Edit Advanced Settings: Blocking Session Count] page, then click [Continue], then click [Continue] again and back to [Metric and Collection Settings], then click [Ok] button to save changes.

6.    Make sure Notification Email include message of Corrective Actions' status

From [Setup] menu, select [Notifications], then select [Customize Email Formats]

Click [Customize] to bring up following window and make sure Corrective Actions' section is there (It is default configuration)

Now, you will receive a email when blocked sessions are more than 2 sessions and the query result of SQL statements created in Corrective Action will be included in the email.