Grid Control: Create user-defined metrics at database level

User-Defined Metrics allow you to extend the reach of Enterprise Manager’s monitoring to conditions specific to particular environments via custom scripts or SQL queries and function calls. Once defined, User-Defined Metrics will be monitored, aggregated in the repository and trigger alerts, like regular metrics.

Login to Enterprise Manager and go to the Databases tab. Enter the database you choose and at the Related Link section click the User-defined Metrics link.
Press Create button and you will be transferred to the page shown at the image posted, where you can create your custom metric.

I will not explain every detail you see in this page, since a lot of them are obvious.
You should understand the meaning of thresholds. Your query should return a single value or a two-column set of values. In the first case, you set the Comparison Operator and the Warning and Critical Values according to what you want. In the second case, in the Warning Thresholds by Key and Critical Thresholds by Key fields you define for individual keys specific values that trigger an alert. An example:

SELECT ename FROM emp
Threshold settings for this example are shown.
Use the format key:value . Keys are case-sensitive.
■ Warning:500
■ Critical:300
■ Comparison Operator: < ■ Warning threshold by key: SMITH:250;JONES:400;CLARK:900 The warning threshold is set to 250 for SMITH, 400 for JONES, and 900 for CLARK. ■ Critical threshold by key: SMITH:100;JONES:200;CLARK:500 The critical threshold is set to 100 for SMITH, 200 for JONES, and 500 for CLARK. All other keys will use the threshold values specified in the Warning and Critical fields. When these conditions are met, a warning or critical alert will be risen on the main database page under the Alerts section.

Now, an example for a String metric type.
Supposedly, you want to raise an alert when a job in your database becomes broken (stops running). An appropriate query could be:

select broken from dba_jobs where what = 'KEEP_SIZE_AUD_LOG';

This will return either 'Y' or 'N' (a string value). So, I choose String Metric Type and Single Value SQL Query Output. For Comparison Operator we choose MATCH and and we enter Y in the field Critical. Warning alert has no meaning in our case, so this field will be left blank.
Now, when this particular job stops running a Critical Alert will be risen on the main database page under the Alerts section.

If you do not want to get as an Alert name something vogue, such as "[Metric Name]=Y", you may enter something else in the Alert Message field, like "Job KEEP_SIZE_AUD_LOG is broken." For the "SELECT ename FROM emp" example you could use "Underpaid Employee: %Key% has salary of %value%", so e.g. you will get a warning alert if Smith's [key] salary drops below 250 [value].

Scheduling is obvious. You define how frequently your metric will be monitored by the Enterprise Manager.

To be notified via e-mail for these types of alerts, just add the User Defined Numeric Metric and/or User-Defined String Metric in the notification rule you already have for your database or create a new one. Be sure to be subscribed to this notification rule.